public void BatchInsertCommand() { var dbFactory = new DbFactory(SqlClientFactory.Instance); var cmdGenerator = new DbBatchCommandBuilder(dbFactory); Assert.Throws <InvalidOperationException>(() => { cmdGenerator.EndBatch(); }); cmdGenerator.BeginBatch(); cmdGenerator.GetInsertCommand("test", new { FieldA = "A", FieldB = 0 }); cmdGenerator.GetInsertCommand("test", new { FieldC = "C", FieldD = 1 }); var batchCmd = cmdGenerator.EndBatch(); Assert.Equal( @"INSERT INTO test (FieldA,FieldB) VALUES (@p0,@p1);INSERT INTO test (FieldC,FieldD) VALUES (@p2,@p3)", batchCmd.CommandText); Assert.Equal(4, batchCmd.Parameters.Count); }
static void RunBatchInserts(DbContext dbContext) { // about SQL statements batches: https://msdn.microsoft.com/en-us/library/ms712553%28v=vs.85%29.aspx // note that depending on database/ADO.NET connector, executing SQL statements in batch may not lead to performance boost // (for example, for SQLite) // usually batches are efficient for inserting/updating many records in cloud DB // (like Azure SQL: https://azure.microsoft.com/en-us/documentation/articles/sql-database-use-batching-to-improve-performance/ ) var sw = new Stopwatch(); var batchCmdBuilder = new DbBatchCommandBuilder(dbContext.DbFactory); // lets create 10,000 records to insert var insertsCount = 10000; var insertRecords = new List <Dictionary <string, object> >(); for (int i = 0; i < insertsCount; i++) { insertRecords.Add(new Dictionary <string, object>() { { "EmployeeID", 1001 + i }, { "FirstName", "First" + i.ToString() }, { "LastName", "Last" + i.ToString() } }); } sw.Start(); // insert in batch (10-per-command) var batchSize = 10; var startIdx = 0; using (var tr = dbContext.Connection.BeginTransaction()) { while (startIdx < insertRecords.Count) { batchCmdBuilder.BeginBatch(); for (var i = 0; i < batchSize && (i + startIdx) < insertRecords.Count; i++) { batchCmdBuilder.GetInsertCommand("Employees", insertRecords[startIdx + i]); } var cmd = batchCmdBuilder.EndBatch(); cmd.Connection = dbContext.Connection; cmd.Transaction = tr; cmd.ExecuteNonQuery(); startIdx += batchSize; if ((startIdx % 1000) == 0) { Console.WriteLine($"Inserted {startIdx} records..."); } } tr.Commit(); } sw.Stop(); Console.WriteLine($"Inserted {insertsCount} records in {sw.Elapsed}"); // ensure that records are really inserted var employeesCountCmd = dbContext.CommandBuilder.GetSelectCommand(new Query("Employees").Select(QField.Count)); employeesCountCmd.Connection = dbContext.Connection; Console.WriteLine("Number of records in 'Employees' table: {0}", employeesCountCmd.ExecuteScalar()); }