Click here to Skip to main content
15,906,574 members
Articles / Database Development / SQL Server
Tip/Trick

Using SQL bulk copy with your LINQ-to-SQL datacontext

Rate me:
Please Sign up or sign in to vote.
4.96/5 (18 votes)
25 Jan 2012CPOL 92.7K   29   12
How to use bulk insert with your LINQ-to-SQL datacontext

Linq-to-SQL is great for many things but it doesn't do very well with huge amounts of inserts. Extending the data context partial class with the following though will do the trick. I also wrote about this here. All you need to do is to create your LINQ entities just as you usually do and call the BulkInsertAll method on the datacontext. Also, do not use this datacontext for anything else before doing the bulk insert. The connection string of the datacontext that we use to create the SQLConnection here loses its password after its first use for some reason and that makes it, obviously, a bit difficult to connect.


C#
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;
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Tånneryd IT AB
Sweden Sweden
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionConverted to VB.NET Pin
cod-jmjohnson14-Mar-18 10:07
cod-jmjohnson14-Mar-18 10:07 
Thanks so much for sharing this. Converted to VB.NET:

Partial Class ImportNewVehiclesContext

    Private Overloads Sub OnCreated()
        Dim CommandTimeout As Integer = 5 * 60
    End Sub

    Public Sub BulkInsertAll(Of T)(entities As IEnumerable(Of T))
        entities = entities.ToArray()
        Dim conn = New SqlClient.SqlConnection(Connection.ConnectionString)
        conn.Open()
        Dim type As Type = GetType(T)
        Dim bulkCopy = New SqlClient.SqlBulkCopy(conn) With {.DestinationTableName = type.Name}
        Dim properties = type.GetProperties().Where(AddressOf EventTypeFilter).ToArray()
        Dim table = New DataTable()
        For Each [property] In properties
            Dim propertyType As Type = [property].PropertyType
            If propertyType.IsGenericType AndAlso propertyType.GetGenericTypeDefinition() = GetType(Nullable(Of )) Then
                propertyType = Nullable.GetUnderlyingType(propertyType)
            End If

            table.Columns.Add(New DataColumn([property].Name, propertyType))
        Next

        For Each e In entities
            table.Rows.Add(properties.[Select](Function([property]) GetPropertyValue([property].GetValue(e, Nothing))).ToArray())
        Next

        bulkCopy.BatchSize = 1000
        bulkCopy.WriteToServer(table)
        conn.Close()
    End Sub

    Private Function EventTypeFilter(p As System.Reflection.PropertyInfo) As Boolean
        Dim attribute = TryCast(System.Attribute.GetCustomAttribute(p, GetType(Linq.Mapping.AssociationAttribute)), Linq.Mapping.AssociationAttribute)
        If attribute Is Nothing Then Return True
        If attribute.IsForeignKey = False Then Return True
        Return False
    End Function

    Private Function GetPropertyValue(o As Object) As Object
        If o Is Nothing Then Return DBNull.Value
        Return o
    End Function

End Class


modified 28-Jun-18 13:29pm.

SuggestionAdding BatchSize due to timeout issue Pin
mparkuk11-Nov-13 0:09
mparkuk11-Nov-13 0:09 
GeneralMy vote of 5 Pin
Kashif Hewitt6-Sep-13 1:18
Kashif Hewitt6-Sep-13 1:18 
GeneralRe: My vote of 5 Pin
Måns Tånneryd8-Sep-13 21:50
Måns Tånneryd8-Sep-13 21:50 
GeneralMy vote of 5 Pin
masarius24-Jul-13 5:51
masarius24-Jul-13 5:51 
QuestionQuestion re: column order in table Pin
jkshay5-Jun-13 9:19
jkshay5-Jun-13 9:19 
AnswerRe: Question re: column order in table Pin
masarius24-Jul-13 6:15
masarius24-Jul-13 6:15 
Generalnice Pin
member6011-Dec-11 18:50
member6011-Dec-11 18:50 
GeneralReason for my vote of 5 nice Pin
member6011-Dec-11 18:50
member6011-Dec-11 18:50 
GeneralReason for my vote of 5 Reason for my vote of 5 Nice Tips Pin
RaviKrSingh9-Dec-11 20:29
RaviKrSingh9-Dec-11 20:29 
GeneralReason for my vote of 5 Nice! Pin
Wendelius9-Dec-11 10:03
mentorWendelius9-Dec-11 10:03 
GeneralReason for my vote of 5 lol... that is pretty slick! Pin
HoyaSaxa939-Dec-11 7:05
HoyaSaxa939-Dec-11 7:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.