Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

How to Bulk Insert Efficiently

5.00/5 (4 votes)
23 Jul 2012CPOL 28.6K  
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:

C#
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:

C#
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)