Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to bulk insert efficiently

, 23 Jul 2012
Rate this:
Please Sign up or sign in to vote.
To bulk insert efficiently, the SqlBulkCopy class must be used.
Using an ORM like Entity Framework is not efficient to perform bulk operations. To  bulk insert efficiently, the SqlBulkCopy class must be used.

To insert a generic list it must be converted to a DataTable:

public static DataTable ConvertToDataTable<T>(IList<T> list)
{
    PropertyDescriptorCollection propertyDescriptorCollection = TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for (int i = 0; i < propertyDescriptorCollection.Count; i++)
    {
        PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
        Type propType = propertyDescriptor.PropertyType;
        if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            table.Columns.Add(propertyDescriptor.Name, Nullable.GetUnderlyingType(propType));
        }
        else
        {
            table.Columns.Add(propertyDescriptor.Name, propType);
        }
    }
    object[] values = new object[propertyDescriptorCollection.Count];
    foreach (T listItem in list)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = propertyDescriptorCollection[i].GetValue(listItem);
        }
        table.Rows.Add(values);
    }
    return table;
}

Then the SqlBulkCopy can be used. In the example the user table is bulk inserted:

DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
    {
        sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
        sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
        sqlBulkCopy.ColumnMappings.Add("Password", "Password");
        sqlBulkCopy.DestinationTableName = "User";
        sqlBulkCopy.WriteToServer(dt);
    }
}

License

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

Share

About the Author

Rui Inacio
Team Leader
Portugal Portugal
I am an Experienced Senior Microsoft Consultant responsible for developing and defining architectures for various projects in a Microsoft environment, using a Service-Oriented Architecture (SOA) and web based applications.
I am also a project manager using agile methodologies and SCRUM.
Software Quality Assurance is a mandatory in every project I am responsible.
As someone said:
“Anyone can program, but not everyone is a programmer...”
Follow on   Twitter

Comments and Discussions

 
GeneralA wrapper for SqlBulkCopy PinmemberRui Jarimba24-Jul-12 1:46 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 23 Jul 2012
Article Copyright 2012 by Rui Inacio
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid