Create DataTable with DB table structure.

Question

What is the best way to create a DataTable with the same structure as a table in my SqlServer database? At present, I am using SqlDataAdapter.Fill() with a query that brings back the columns but no rows. That's works fine, but it seems klutzy.

Is there a better way?

Answer 1

Well, don't you already know the structure?

The "SET FMT_ONLY ON", or "WHERE 1 = 0" tricks are both very tested (with different results).

Is the query here dynamic? I can't help thinking you should know the schema already...

Answer 2

Well, if you use Linq2Sql, you can reflect over the entity class, and create the datatable based on the properties name and datatype.

            Type type = typeof(Product); //or whatever the type is
            DataTable table = new DataTable();
            foreach(var prop in type.GetProperties())
            {
                table.Columns.Add(prop.Name, prop.PropertyType);
            }

Answer 3

Check out this method, SqlDataAdapter.FillSchema(), the artical on MSDN will tell you how to use it.

Answer 4

Yes, I know what the schema is now, but it may change, and the code should adapt. Besides, who wants to type in specs for 33 fields. If I understand correctly, my solution was of the "where 1 = 0" type. I think that FillSchema should also work, but there are hidden hazards since it wants to enforce the primary key. Since I am reading data that may have errors as part of the validation process, I can't be sure the primary key won't be duplicated.

Thanks.