Best way to convert IEnumerable to Datatable for SQL Bulk Copy

Question

I have a model, and a list of instances (about 5000) I need copied into a database.

I'm trying to assimilate my objects into a datatable but I don't know how to do it:

public class BookingType {

    public int ID { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int RandomProperty { get; set; }
    public int RandomProperty2 { get; set; }

}

public void InsertSomeStuff(IEnumerable<BookingType> bookings) {
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) {
        conn.Open();

        DataTable dt = new DataTable();

        using (SqlBulkCopy copy = new SqlBulkCopy(conn)) {
            copy.ColumnMappings.Add(0, 1);

            copy.DestinationTableName = "dbo.Bookings";
            copy.WriteToServer(dt);
        }
    }
}

How do I do this?

You can use the generic method [shown here](http://msdn.microsoft.com/en-us/library/bb669096.aspx) to create a datatable given an `IEnumerable` of any arbitrary type. — Servy, May 30 '13 at 17:00

Lorentz Vedeler · Accepted Answer · 2013-05-30T17:21:05.537

Are you using linq to sql? In that case, this approach is pretty sweet: Using SQL bulk copy with your LINQ-to-SQL datacontext

partial class MyDataContext
{
    partial void OnCreated()
    {
        CommandTimeout = 5 * 60;
    }

    public void BulkInsertAll<T>(IEnumerable<T> entities)
    {
        entities = entities.ToArray();

        string cs = Connection.ConnectionString;
        var conn = new SqlConnection(cs);
        conn.Open();

        Type t = typeof(T);

        var tableAttribute = (TableAttribute)t.GetCustomAttributes(
            typeof(TableAttribute), false).Single();
        var bulkCopy = new SqlBulkCopy(conn) { 
            DestinationTableName = tableAttribute.Name };

        var properties = t.GetProperties().Where(EventTypeFilter).ToArray();
        var table = new DataTable();

        foreach (var property in properties)
        {
            Type propertyType = property.PropertyType;
            if (propertyType.IsGenericType &&
                propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propertyType = Nullable.GetUnderlyingType(propertyType);
            }

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

        foreach (var entity in entities)
        {
            table.Rows.Add(properties.Select(
              property => GetPropertyValue(
              property.GetValue(entity, null))).ToArray());
        }

        bulkCopy.WriteToServer(table);
        conn.Close();
    }

    private bool EventTypeFilter(System.Reflection.PropertyInfo p)
    {
        var attribute = Attribute.GetCustomAttribute(p, 
            typeof (AssociationAttribute)) as AssociationAttribute;

        if (attribute == null) return true;
        if (attribute.IsForeignKey == false) return true; 

        return false;
    }

    private object GetPropertyValue(object o)
    {
        if (o == null)
            return DBNull.Value;
        return o;
    }
}

Will this run at the same performance as Bulk to SQL I've got in my answer?? (yes I am using L2S) — Smithy, May 30 '13 at 17:56
I believe the code works but I'm getting: The given value of type DateTime from the data source cannot be converted to type int of the specified target column. — Smithy, May 30 '13 at 18:04
My guess is that you are using some special column mappings, but it is kinda hard to tell without knowing how your class is mapped to sql. — Lorentz Vedeler, May 30 '13 at 18:19
You're totally right barring one point, the models property order has to match the databases column order. Fantastic extrapolation of my question and much more relevant answer than it's been marked a duplicate of. Thanks :) — Smithy, Jun 01 '13 at 12:33
I added [NotMapped] to some of my fields. Then I changed this line t.GetProperties().Where(p => !Attribute.IsDefined(p, typeof(NotMappedAttribute)) && (p.PropertyType.IsValueType || p.PropertyType == typeof(string))); — rollsch, Sep 04 '18 at 06:23

Best way to convert IEnumerable to Datatable for SQL Bulk Copy

1 Answers1