CODEWITHSUNDEEP
c#sql-server
SeaDrive
SeaDrive

Reputation: 4292

Create DataTable with DB table structure

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?

Upvotes: 4

Views: 5724

Answers (5)

ezpl
ezpl

Reputation: 11

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

Upvotes: 1

SeaDrive
SeaDrive

Reputation: 4292

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.

Upvotes: 0

Rohan West
Rohan West

Reputation: 9298

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

Upvotes: 1

BFree
BFree

Reputation: 103740

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

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062650

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

Upvotes: 0

Related Questions