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?
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);
}
Check out this method, SqlDataAdapter.FillSchema(), the artical on MSDN will tell you how to use it.
The conversion code works great. It does not take into consideration that databases may have nullable types. I have added a check for this and converts nullable fields to non-nullable.
Type type = typeof(Product); //or whatever the type is
DataTable table = new DataTable();
foreach(var prop in type.GetProperties())
{
Type colType = prop.PropertyType;
if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
System.ComponentModel.NullableConverter nc = new System.ComponentModel.NullableConverter(colType);
colType = nc.UnderlyingType;
}
table.Columns.Add(prop.Name, colType);
}
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...
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.
