Click here to Skip to main content
13,355,538 members (71,902 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 23 Jul 2012

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));
            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);
    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))
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
        sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
        sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
        sqlBulkCopy.ColumnMappings.Add("Password", "Password");
        sqlBulkCopy.DestinationTableName = "User";


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


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...”

You may also be interested in...


Comments and Discussions

GeneralA wrapper for SqlBulkCopy Pin
Rui Jarimba24-Jul-12 2:46
memberRui Jarimba24-Jul-12 2:46 

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.

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