SqlBulkCopy from a List<>

How can I make a big insertion with SqlBulkCopy from a List<> of simple object ?

Do I implement my custom IDataReader ?

  • How can I retrieve a list of parameters from a stored procedure in SQL Server
  • aspnet_Users table with huge indexsize
  • SQL Server SUM then MAX
  • How do i import a mysql dump to SQL Server database
  • About PHP and Microsoft SQL Server
  • java.util.Date to java.sql.Date doesn't contain Time
  • How can I get a list of element names from an XML value in SQL Server
  • Average without calculate zero
  • How can I sort a table by a numerical string as a number when this sorted columns results contain all numbers in SQL?
  • Flat File to Relational Table
  • Check if role consists of particular user in DB?
  • How to scaffold a SQL Server database with ASP.Net Entity Framework
  • 4 Solutions collect form web for “SqlBulkCopy from a List<>”

    Simply create a DataTable from your list of objects and call SqlBulkCopy.WriteToServer, passing the data table.

    You might find the following useful:

    • Adding columns to a DataTable. Add a column for each property/field you wish to write.
    • Adding rows to a DataTable. Add a row for each object in your list.

    For maximum performance with SqlBulkCopy, you should set an appropriate BatchSize. 10,000 seems to work well – but profile for your data.

    You might also observe better results when using SqlBulkCopyOptions.TableLock.

    An interesting and informative analysis of SqlBulkCopy performance can be found here.

    With FastMember, you can do this without ever needing to go via DataTable (which, in my tests, more-than-doubles the performance):

    using(var bcp = new SqlBulkCopy(connection))
    using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
    {
        bcp.DestinationTableName = "SomeTable";
        bcp.WriteToServer(reader);
    }
    

    Note that ObjectReader can also work with non-generic sources, and it is not necessary to specify the member-names in advance (although you probably want to use the ColumnMappings aspect of SqlBulkCopy if you don’t specify them in the ObjectReader itself).

    Late to the party, but if you add this EntityDataReader class from Microsoft, there’s an AsDataReader() extension method that does exactly that: https://github.com/matthewschrager/Repository/blob/master/Repository.EntityFramework/EntityDataReader.cs

    (example [List].AsDataReader() implementation:)

    var connStr = "";
    using (var connection = new SqlConnection(connStr)) 
    {
        var startTime = DateTime.Now;
        connection.Open();
        var transaction = connection.BeginTransaction();
        try
        {
            //var connStr = connection.ConnectionString;
            using (var sbCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                sbCopy.BulkCopyTimeout = 0;
                sbCopy.BatchSize = 10000;
                sbCopy.DestinationTableName = "Foobars";
                var reader = Foobars.AsDataReader();
                sbCopy.WriteToServer(reader);
            }
            transaction.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            transaction.Rollback();
        }
        finally
        {
            transaction.Dispose();
            connection.Close();
            var endTime = DateTime.Now;
            Console.WriteLine("Upload time elapsed: {0} seconds", (endTime - startTime).TotalSeconds);
        }
    }
    

    Depending on what you are trying to accomplish by calling SqlBulkCopy in the first place, it might make more sense to use a Table-Valued Parameter (TVP). Using a TVP would make it trivial to send in a collection of any custom type. The data can be streamed in so you can avoid the DataTable (much like in @Marc Gravell’s answer) and you can avoid SqlBulkCopy as well. TVP’s allow for completely flexibility of how to handle the data once it gets to SQL Server as you call a Stored Procedure to pass the TVP data into and it appears as a Table Variable that you can do anything with, not just INSERT (which is the case with SqlBulkCopy). You can also get data back via a SqlDataReader, data such as newly created IDENTITY values. I added an example and some additional notes on this answer: How can I insert 10 million records in the shortest time possible?. And several years ago I wrote an article on SQL Server Central (free registration required), Streaming Data Into SQL Server 2008 From an Application, which is also noted in that linked answer, providing a working example of passing in a Generic List of a custom type, streamed in from a 3 million row text file.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.