How to map SQL Server tables to DataTables correctly?

Question

I wrote a method which copies a table from SQL Server to a .NET-DataTable:

public static DataTable SQLtoDataTable(string _connectionString, string _tableName)
{
    DataTable dt = new DataTable();
    string queryString = "SELECT * FROM " + _tableName;

    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        using (SqlCommand queryCMD = new SqlCommand(queryString))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(queryCMD))
            {
                queryCMD.Connection = connection;
                connection.Open();
                da.Fill(dt);
                connection.Close();
            }
        }
   }

   return dt;
}

Unfortunately this method does not seem to work 100% correctly. While columns and row data are transferred 1:1, it seems like the AllowDBNULL value is set to true for every single column no matter if it is set to true or false in the database. Do I miss something or is there a better way to insert this kind of information into a DataTable?

"How to map SQL-tables to DataTables correctly?" - correct is subjective; personally, I'm of the opinion that the "correct" mapping to DataTable is "as rarely as possible, when all other possible models have been eliminated"... is there any reason you can't represent this in a proper object model? — Marc Gravell
– Marc Gravell, Commented Jul 29, 2014 at 9:42
@MarcGravell Well by correctly I mean fetching as much correct data from SQL to DataTable as possible. What exactly do you mean by representing this in a proper object model, LINQ? — チーズパン
– チーズパン, Commented Jul 29, 2014 at 9:47
For example, if you need to model customers and orders, a class Customer {...} and class Order {...}. DataTable is not the way to go for data access / storage, IMO. — Marc Gravell
– Marc Gravell, Commented Jul 29, 2014 at 9:48
@MarcGravell Because my methods are supposed to be generic. I do not know if my table will be called Customer or Order. — チーズパン
– チーズパン, Commented Jul 29, 2014 at 9:54
public static List<T> ReadTable<T>(...). Table name could be passed in as a string, or could just use the typeof(T).Name, or typeof(T).GetCustomAttribute<TableName>().Name. Plenty of options there. The only time you need to use DataTable (or a similar approach) is when writing a tool like SSMS, in which the structure cannot be known in advance. — Marc Gravell
– Marc Gravell, Commented Jul 29, 2014 at 9:57

Adriaan Stander · Accepted Answer · 2014-07-29 09:40:06Z

Have a look at using SqlDataAdapter.FillSchema Method with your code.

Adds a DataTable to a DataSet and configures the schema to match that in the data source.

As an example also have a look at DbDataAdapter.FillSchema Method (DataSet, SchemaType)

A FillSchema operation adds a DataTable to the destination DataSet. It then adds columns to the DataColumnCollection of the DataTable, and configures the following DataColumn properties if they exist at the data source:

• AllowDBNull

• AutoIncrement. You must set AutoIncrementStep and AutoIncrementSeed separately.

• MaxLength

• ReadOnly

• Unique

FillSchema also configures the PrimaryKey and Constraints properties according to the following rules:

• If one or more primary key columns are returned by the SelectCommand, they are used as the primary key columns for the DataTable.

• If no primary key columns are returned but unique columns are, the unique columns are used as the primary key if, and only if, all the unique columns are nonnullable. If any of the columns are nullable, a UniqueConstraint is added to the ConstraintCollection, but the PrimaryKey property is not set.

• If both primary key columns and unique columns are returned, the primary key columns are used as the primary key columns for the DataTable.

The example used

DataSet dataSet = new DataSet(dataSetName);

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT CustomerID, CompanyName, ContactName FROM dbo.Customers", connection);

    DataTableMapping mapping = adapter.TableMappings.Add("Table", "Customers");
    mapping.ColumnMappings.Add("CompanyName", "Name");
    mapping.ColumnMappings.Add("ContactName", "Contact");

    connection.Open();

    adapter.FillSchema(dataSet, SchemaType.Mapped);
    adapter.Fill(dataSet);

    return dataSet;
}

Collectives™ on Stack Overflow

How to map SQL Server tables to DataTables correctly?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related