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());
}