Bulk Inserts

Coordinator
May 10, 2009 at 4:28 PM

I added support for bulk inserts with the 0.65 release.  There are even some test that show the differences in the FluentAdo.SqlServerCe.Tests, InsertPerformanceTests.cs.

Here is how it looks:

// create a product list to insert:

        private List<Product> CreateProductList()
        {
            const int ProductCount = 2000;
            var list = new List<Product>(ProductCount);

            for (int i = 0; i < ProductCount; i++)
            {
                var cust = new Product { ID = i, Name = "TestProduct", Active = true, Manufacturer = "TestMfg", BarCode = i.ToString(), ProductCode = i.ToString(), Size = "Cup" };
                list.Add(cust);
            }

            return list;
        }

 

        [Test]
        public void FluentCommandKeepAlive()
        {
            using (FluentCommand<int> cmd = new FluentCommand<int>(_sql))
            {
                cmd.AddInt("id")
                    .AddString("code", 15)
                    .AddString("name", 255)
                    .AddString("mft", 56)
                    .AddString("size")
                    .AddBoolean("discontinued")
                    .AddString("barcode", 56);

                for (int i = 0; i < _productList.Count; i++)
                {
                    var product = _productList[i];
                    cmd.AsNonQuery(
                        product.ID, product.ProductCode, product.Name, product.Manufacturer, product.Size,
                        !product.Active, product.ProductCode
                        );
                }
            }
        }

The difference is in the AsNonQuery, there is a new version that allows you to pass in the values in a loop.  Also, the other AsNonQuery will kill the command object, this version does not.  So you have to kill it yourself (that would be the 'using' around the cmd variable).

For 2000 inserts, the performance went from 3 seconds down to .2 seconds.