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);
}
public void Select_MultipleResultSets()
{
var batchCmdBuilder = new DbBatchCommandBuilder(DbAdapter.CommandBuilder.DbFactory);
batchCmdBuilder.BeginBatch();
batchCmdBuilder.GetSelectCommand(new Query("companies"));
batchCmdBuilder.GetSelectCommand(new Query("contacts"));
var selectMultipleCmd = batchCmdBuilder.EndBatch();
(var companies, var contacts) = DbAdapter.Select(selectMultipleCmd).ExecuteReader((rdr) => {
var companiesRes = new DataReaderResult(rdr).ToList <CompanyModelAnnotated>();
rdr.NextResult();
var contactsRes = new DataReaderResult(rdr).ToList <ContactModel>();
return(companiesRes, contactsRes);
});
Assert.Equal(2, companies.Count);
Assert.Equal(5, contacts.Count);
}
public static void RunSelectMultipleResultSet(DbContext dbContext)
{
// lets generate several selects in one command
var batchCmdBuilder = new DbBatchCommandBuilder(dbContext.DbFactory);
Console.WriteLine("Composing 2 selects in one DbCommand (multiple result sets)");
batchCmdBuilder.BeginBatch();
batchCmdBuilder.GetSelectCommand(new Query("Customers", (QField)"Country" == (QConst)"Germany"));
batchCmdBuilder.GetSelectCommand(new Query("Orders",
new QConditionNode((QField)"CustomerID", Conditions.In,
new Query("Customers.c", (QField)"c.Country" == (QConst)"Germany").Select("c.CustomerID")
)
));
var multiSelectCmd = batchCmdBuilder.EndBatch();
multiSelectCmd.Connection = dbContext.Connection;
RecordSet customerRS = null;
RecordSet orderRS = null;
dbContext.Connection.Open();
try {
using (var rdr = multiSelectCmd.ExecuteReader()) {
customerRS = RecordSet.FromReader(rdr);
if (rdr.NextResult())
{
orderRS = RecordSet.FromReader(rdr);
}
}
} finally {
dbContext.Connection.Close();
}
Console.WriteLine($"Loaded {customerRS.Count} customers and {orderRS.Count} their orders from one data reader");
}
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());
}