C# loops and mass insert

Question

The follow code will insert some values in my database. It gets 6 random values, puts the stuff in an array and then inserts it in the database.

    public void LottoTest(object sender, EventArgs e)
    {
        Dictionary<int, int> numbers = new Dictionary<int, int>();
        Random generator = new Random();
        while (numbers.Count < 6)
        {
            numbers[generator.Next(1, 49)] = 1;
        }

        string[] lotto = numbers.Keys.OrderBy(n => n).Select(s => s.ToString()).ToArray();

        foreach (String _str in lotto)
        {
            Response.Write(_str);
            Response.Write(",");
        }


        var connectionstring = "Server=C;Database=lotto;User Id=lottoadmin;Password=password;";

        using (var con = new SqlConnection(connectionstring))  // Create connection with automatic disposal
        {
            con.Open();
            using (var tran = con.BeginTransaction())  // Open a transaction
            {
                // Create command with parameters  (DO NOT PUT VALUES IN LINE!!!!!)
                string sql =
                    "insert into CustomerSelections(val1,val2,val3,val4,val5,val6) values (@val1,@val2,@val3,@val4,@val5,@val6)";
                var cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("val1", lotto[0]);
                cmd.Parameters.AddWithValue("val2", lotto[1]);
                cmd.Parameters.AddWithValue("val3", lotto[2]);
                cmd.Parameters.AddWithValue("val4", lotto[3]);
                cmd.Parameters.AddWithValue("val5", lotto[4]);
                cmd.Parameters.AddWithValue("val6", lotto[5]);


                cmd.Transaction = tran;
                cmd.ExecuteNonQuery(); // Insert Record

                tran.Commit();  // commit transaction
                Response.Write("<br />");
                Response.Write("<br />");
                Response.Write("Ticket has been registered!");
            }
        }


    }

What is the best way to loop and insert MASS entries into the database. Lets say, 100,000 records via C#? I want to be able to generate the random numbers by my method and utilize the insert which i have too..

Use [SqlBulkCopy](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx) — Simon Belanger, Jul 01 '13 at 18:38
@PriceCheaperton Well of course it's different.. It would be weird to have a bulk insert that is very fast but insert only the same values over and over. SqlBulkCopy will just read the SqlDataReader, it's agnostic as to how the values were generated. — Simon Belanger, Jul 01 '13 at 18:48
@SimonBelanger indeed; I've done some meta-programming work recently to re-expose generic enumerators (iterator blocks) as `IDataReader`s *specifically* designed (and tested) to target `SqlBulkCopy` — Marc Gravell, Jul 01 '13 at 18:52

score 8 · Answer 1 · answered Jul 01 '13 at 18:47

8

For true large scale inserts, SqlBulkCopy is your friend. The easy but inefficient way to do this is just to fill a DataTable with the data, and throw that at SqlBulkCopy, but it can be done twice as fast (trust me, I've timed it) by spoofing an IDataReader. I recently moved this code into FastMember for convenience, so you can just do something like:

class YourDataType {
    public int val1 {get;set;}
    public string val2 {get;set;}
    ... etc
    public DateTime val6 {get;set;}
}

then create an iterator block (i.e. a non-buffered forwards only reader):

public IEnumerable<YourDataType> InventSomeData(int count) {
    for(int i = 0 ; i < count ; i++) {
        var obj = new YourDataType {
           ... initialize your random per row values here...
        }
        yield return obj;
    }
}

then:

var data = InventSomeData(1000000);
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data))
{ // note that you can be more selective with the column map
    bcp.DestinationTableName = "CustomerSelections";
    bcp.WriteToServer(reader);
}

answered Jul 01 '13 at 18:47

Marc Gravell

1,026,079
266
2,566
2,900

+1 - It's absolutely hilarious that it's twice as fast by *spoofing* and `IDataReader`. Same data, same size, more efficient. Man that algorithm that uses the `DataTable` must be **hideous!** – Mike Perrenoud Jul 01 '13 at 19:19
@MichaelPerrenoud it isn't quite "same data" - don't forget that `DataTable` requires you to have it all in memory at once, and includes "advanced" (by which in this context I mean "unnecessary") features such as defaults, constraints, indexes, change-tracking, old/new values, dynamic column adding, etc - I can fully understand how `DataTable` has some overheads. When used *appropriately* `DataTable` is actually fairly good at what it does. The problem, IMO, is when it is used for things **outside** of what it is good at. – Marc Gravell Jul 01 '13 at 19:34
Fantastic response Marc, I appreciate the insight because now that you mention all of that overhead it makes perfect sense that the class alone just doesn't perform as well as having some POCO objects that could even be optimized to be read from something directly off disk instead of holding it all in memory because of the simplicity of its implementation! Thanks again friend, I always learn something from you! – Mike Perrenoud Jul 01 '13 at 19:38
@Michael indeed, an iterator block is ideally suited for streaming forwards-only from files, etc. Obviously you wouldn't do this if it was just CSV - you'd just use the BCP command-line; but for other formats (XML, protobuf, JSON, etc) with the c# just materialising individual objects as the bulk-copy needs them - sweet spot – Marc Gravell Jul 01 '13 at 21:09

score 1 · Answer 2 · answered Jul 01 '13 at 18:40

1

You need Sql bulk insert. There is a nice tutorial on msdn http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

answered Jul 01 '13 at 18:40

Piotr Stapp

19,392
11
68
116

It depends how you will generate data. I always generate an array of ints and then randomly change indexes. After this I used bulk insert to load data to database – Piotr Stapp Jul 01 '13 at 18:46
@PriceCheaperton - See [this](http://msdn.microsoft.com/en-us/library/ex21zs8x.aspx) example that bulk copies data that was generated in code. – mbeckish Jul 01 '13 at 18:49
@PriceCheaperton it isn't obvious, but that example us using values from code - the values are in the `DataTable`. – Marc Gravell Jul 01 '13 at 18:51
i was going to just call the method on page load and loop through 100k times lol – PriceCheaperton Jul 01 '13 at 20:03

John Kraft · Answer 3 · 2013-07-01T18:49:56.423

1

MSDN Table Value Parameters

Basically, you fill a datatable with the data you want to put into SqlServer.

DataTable tvp = new DataTable("LottoNumbers");
forach(var numberSet in numbers)
    // add the data to the dataset

Then you pass the data through ADO using code similar to this...

command.Parameters.Add("@CustomerLottoNumbers", SqlDbType.Structured);
command.Parameters["CustomerLottoNumbers"].Value = tvp;

Then you could use sql similar to this...

INSERT CustomerSelections
SELECT * from @CustomerLottoNumbers

edited Jul 01 '13 at 18:49

answered Jul 01 '13 at 18:40

John Kraft

6,811
4
37
53

My C# app takes care of the unique lotto numbers... So how does this help?? – PriceCheaperton Jul 01 '13 at 18:43
@PriceCheaperton - You haven't really explained why you HAVE to generate the random numbers in code. – mbeckish Jul 01 '13 at 18:45
It's the sql you would use to insert it into the database with. – John Kraft Jul 01 '13 at 18:45
I am working on a statistical app, so i need to be able to use my C# code to generate the random values in the array and then insert them to the database. The loop needs to go round the whole method... – PriceCheaperton Jul 01 '13 at 18:47
@PriceCheaperton - TSQL can generate [random](http://msdn.microsoft.com/en-us/library/ms177610.aspx) values. – mbeckish Jul 01 '13 at 18:48
` protected void Page_Load(object sender, EventArgs e) { int length = 101; for (int i = 0; i < length; i++) { LottoTest; } }` – PriceCheaperton Jul 01 '13 at 19:54

C# loops and mass insert

3 Answers3

Linked