CODEWITHSUNDEEP
c#.netgenericsreflectiondatatable
dimiguel
dimiguel

Reputation: 1579

How do I make a DataTable from a collection to use it as an SQL table parameter?

I'm having performance issues inserting a large amount of data into SQL using Entity Framework even with validation off.

I saw online that inserting a DataTable into a stored procedure with a table parameter is extremely quick, but I don't want to be writing this type of code to arrange a DataTable to be inserted: 

var table = new DataTable();
table.Columns.Add("Id");
table.Columns.Add("Store");
table.Columns.Add("Name");
table.Columns.Add("Amount");
table.Columns.Add("Active");
table.Columns.Add("Status");

foreach (var s in model.Sales)
{
    var row = table.NewRow();
    row["Id"] = l.Id;
    row["Store"] = (int)l.Store;
    row["Name"] = l.Name;
    row["Amount"] = l.Amount;
    row["Active"] = true;
    row["Status"] = string.IsNullOrEmpty(l.Status) ? null : l.Status;
    table.Rows.Add(row);
}

var parameter = new SqlParameter("Table", SqlDbType.Structured)
    {
        Value = table,
        TypeName = "SqlDefinedTableType"
    };

How can I make a DataTable from a collection and use it as an SQL table parameter?

Upvotes: 0

Views: 2027

Answers (1)

dimiguel
dimiguel

Reputation: 1579

You can solve problems like these using generics. Here is a method that converts a collection of a type into a DataTable:

public static DataTable CreateDataTable<T>(ICollection<T> values)
{
    var table = new DataTable();

    // Get the generic type from the collection
    var type = values.GetType().GetGenericArguments()[0];

    // Add columns base on the type's properties
    foreach (var property in type.GetProperties())
    {
        /* It is necessary to evaluate whether each property is nullable or not.
         * This is because DataTables only support null values in the form of
         * DBNull.Value.
         */
        var propertyType = property.PropertyType;
        var computedType =
            // If the type is nullable
            propertyType.IsGenericType
                && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>) 
            // Get its underlying type
            ? propertyType.GetGenericArguments()[0]
            // If it isn't, get return the property type.
            : propertyType;

        table.Columns.Add(new DataColumn(property.Name, computedType));
    }

    // Add rows into the DataTable based off of the values
    foreach (var value in values)
    {
        var row = table.NewRow();
        foreach (var property in value.GetType().GetProperties())
        {
            // Create a container to hold the data in the value
            object data = null;
            // If the property we are adding exists...
            if (row.Table.Columns.Contains(property.Name))
                // Then get the value of that property
                data = value.GetType().GetProperty(property.Name).GetValue(value, null);

            // If the value is null, convert the value to DBNull
            row[property.Name] = data ?? DBNull.Value;
        }
        table.Rows.Add(row);
    }

    return table;
}

You can then use the DataTable returned from the function above to create an SqlParameter and pass it to the SqlCommand of your choice.

var parameter = new SqlParameter("Table", SqlDbType.Structured)
    {
        Value = CreateDataTable(model.Sales),
        TypeName = "SqlDefinedTableType"
    };

Edit: I edited the function in my original answer to support nullable types.

Upvotes: 2

Related Questions