Need to get empty datatable in .net with database

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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;
}