CODEWITHSUNDEEP
c#sql-server
CsharperGuyInLondon
CsharperGuyInLondon

Reputation:

What’s the best way to bulk database inserts from c#?

How do I/what’s the best way to do bulk database inserts?

In C#, I am iterating over a collection and calling an insert stored procedure for each item in the collection.

How do I send all the data in one database call?

E.g. say I have a person list (List<Person>) containing 10 items. I am currently calling the InsertPerson stored proc 10 times. I would like to reduce this to 1 call.

I am using MS SQL Server 2005.

Upvotes: 31

Views: 31376

Answers (10)

Amir
Amir

Reputation: 2098

Re the solution for SqlBulkCopy, I created a class than takes Datatable or a List<T> and a Buffer size (CommitBatchSize). It will convert the list to a data table using an extension (in the second class).

It works very fast. On my PC, I am able to insert more than 10 million complicated records in less than 10 seconds.

Here is the class:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{

public class BulkUploadToSql<T>
{
    public IList<T> InternalStore { get; set; }
    public string TableName { get; set; }
    public int CommitBatchSize { get; set; }=1000;
    public string ConnectionString { get; set; }

    public void Commit()
    {
        if (InternalStore.Count>0)
        {
            DataTable dt;
            int numberOfPages = (InternalStore.Count / CommitBatchSize)  + (InternalStore.Count % CommitBatchSize == 0 ? 0 : 1);
            for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
                {
                    dt= InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
                BulkInsert(dt);
                }
        } 
    }

    public void BulkInsert(DataTable dt)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            // make sure to enable triggers
            // more on triggers in next post
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            // set the destination table name
            bulkCopy.DestinationTableName = TableName;
            connection.Open();

            // write the data in the "dataTable"
            bulkCopy.WriteToServer(dt);
            connection.Close();
        }
        // reset
        //this.dataTable.Clear();
    }

}

public static class BulkUploadToSqlHelper
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> data)
    {
        PropertyDescriptorCollection properties =
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }
}

}

Here is an example when I want to insert a List of my custom object List<PuckDetection> (ListDetections):

var objBulk = new BulkUploadToSql<PuckDetection>()
{
        InternalStore = ListDetections,
        TableName= "PuckDetections",
        CommitBatchSize=1000,
        ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();

Upvotes: 3

JohnB
JohnB

Reputation: 18982

CsharperGuyInLondon, here's a simple example of SqlBulkCopy code:

using System.Data.SqlClient;

DataTable table = new DataTable("States");
// construct DataTable
table.Columns.Add(new DataColumn("id_state", typeof(int))); 
table.Columns.Add(new DataColumn("state_name", typeof(string)));

// note: if "id_state" is defined as an identity column in your DB,
// row values for that column will be ignored during the bulk copy
table.Rows.Add("1", "Atlanta");
table.Rows.Add("2", "Chicago");
table.Rows.Add("3", "Springfield");

using(SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
  bulkCopy.BulkCopyTimeout = 600; // in seconds
  bulkCopy.DestinationTableName = "state";
  bulkCopy.WriteToServer(table);
}

Upvotes: 29

Gulzar Nazim
Gulzar Nazim

Reputation: 52178

I construct the list as an xml string and pass it to the stored proc. In SQL 2005, it has enhanced xml functionalities to parse the xml and do a bulk insert.

check this post: Passing lists to SQL Server 2005 with XML Parameters

Upvotes: 2

Ian Ringrose
Ian Ringrose

Reputation: 51917

Create a XML document that contains all the items to be inserted. Then inside of a stored procedure, use the TSQL xml support (OPENXML) to read all the data from the XML document and insert it into your tables with hopefully one insert statement for each table.

However if you are only inserting data into a single table and don’t need any database side logic, why not use SqlBulkCopy?

Upvotes: 1

John Rasch
John Rasch

Reputation: 63445

Here's a good example of SqlBulkCopy in action:

http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062780

Well, 10 items isn't what I call bulk, but for larger sets, SqlBulkCopy is your friend. All you need to do is feed it either a DataTable or an IDataReader (my preferred option, 'cos I like streaming APIs). I did something similar here (you can ignore the xml side - just subclass the SimpleDataReader).

Upvotes: 25

dove
dove

Reputation: 20674

You could update with an Xml document, Sql 2005 works very well with them. One node per row, but just one parameter for Xml.

Upvotes: 1

MichaelGG
MichaelGG

Reputation: 10006

The .NET SqlBulkCopy class works quite well.

Upvotes: 7

daanish.rumani
daanish.rumani

Reputation: 317

You can build a BLOB (image) and send it as a parameter to a stored procedure. Inside the stored procedure, you can fetch all the items using substring().

Upvotes: 2

Restore The Data Dumps Again
Restore The Data Dumps Again

Reputation: 39325

Dump your data to a pipe delimited (or something else if your data has pipes in it) text file and use Bulk Insert.

Upvotes: 1

Related Questions