Need to get empty datatable in .net with database table schema

Question

What is the best way to create an Empty DataTable object with the schema of a sql server table?

Solution 1

A statement I think is worth mentioning is SET FMTONLY:

SET FMTONLY ON;
SELECT * FROM SomeTable
SET FMTONLY OFF;

No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

The reason this can be handy is because you can supply any query/stored procedure and return just the metadata of the resultset.

Solution 2

All of these solutions are correct, but if you want a pure code solution that is streamlined for this scenario.

No Data is returned in this solution since CommandBehavior.SchemaOnly is specified on the ExecuteReader function(Command Behavior Documentation)

The CommandBehavior.SchemaOnly solution will add the SET FMTONLY ON; sql before the query is executed for you so, it keeps your code clean.

public static DataTable GetDataTableSchemaFromTable(string tableName, SqlConnection sqlConn, SqlTransaction transaction)
{
    DataTable dtResult = new DataTable();

    using (SqlCommand command = sqlConn.CreateCommand())
    {
        command.CommandText = String.Format("SELECT TOP 1 * FROM {0}", tableName);
        command.CommandType = CommandType.Text;
        if (transaction != null)
        {
            command.Transaction = transaction;
        }

        SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);

        dtResult.Load(reader);

    }

    return dtResult;
}

Solution 3

Try: SELECT TOP 0 * FROM [TableName]

and use SQLDataAdapter to fill a DataSet, then get the Table from that DataSet.

Solution 4

Assuming that you can connect to the SQL database which contains the table you want to copy at the point it time you want to do this, you could use a conventional resultset to datatable conversion, using

select * from <tablename> where 1=2

as your source query.

This will return an empty result set with the structure of the source table.

Solution 5

Here's what I did:

var conn = new SqlConnection("someConnString");
var cmd = new SqlCommand("SET FMTONLY ON; SELECT * FROM MyTable; SET FMTONLY OFF;",conn); 
var dt = new DataTable();
conn.Open();
dt.Load(cmd.ExecuteReader());
conn.Dispose();

Works well. Thanks AdaTheDev.

View more solutions

Comments

[Dave Hampel]

SELECT * FROM [tablename] WHERE ID = -1 --- accomplishes the needed result also