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?

Answer 1

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 2

Try: SELECT TOP 0 * FROM [TableName]

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

Answer 3

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 4

You can always create your own:

        DataTable table = new DataTable("TableName");

        table.Columns.Add(new DataColumn("Col1", typeof(int)));
        table.Columns.Add(new DataColumn("Col2", typeof(int)));
        table.Columns.Add(new DataColumn("Col3", typeof(string)));
        table.Columns.Add(new DataColumn("Col4", typeof(int)));
        table.Columns.Add(new DataColumn("Col5", typeof(string)));

The obvious draw back being that you will have to update your code whenever the database schema changes.

Answer 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.