public static async Task RunSampleAsync(string sqlConnectionString)
{
//Initialize with a Connection String (using our Config Key or your own, or any other initialization
// of the Connection String (e.g. perfect for DI initialization, etc.):
//NOTE: The ISqlBulkHelpersConnectionProvider interface provides a great abstraction that most projects don't
// take the time to do, so it is provided here for convenience (e.g. extremely helpful with DI).
ISqlBulkHelpersConnectionProvider sqlConnectionProvider = new SqlBulkHelpersConnectionProvider(sqlConnectionString);
//Initialize large list of Data to Insert or Update in a Table
List <TestElement> testData = SqlBulkHelpersSample.CreateTestData(1000);
//Bulk Inserting is now as easy as:
// 1) Initialize the DB Connection & Transaction (IDisposable)
// 2) Instantiate the SqlBulkIdentityHelper class with ORM Model Type & Schema Loader instance...
// 3) Execute the insert/update (e.g. Convenience method allows InsertOrUpdate in one execution!)
using (SqlConnection conn = await sqlConnectionProvider.NewConnectionAsync())
using (SqlTransaction transaction = conn.BeginTransaction())
{
ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(conn, transaction);
await sqlBulkIdentityHelper.BulkInsertOrUpdateAsync(
testData,
SqlBulkHelpersSampleApp.TestTableName,
transaction);
transaction.Commit();
}
}
public async Task TestBulkInsertOrUpdateWithMultipleCustomMatchQualifiersAsync()
{
var testData = TestHelpers.CreateTestDataWithIdentitySetter(10);
int count = 1;
foreach (var t in testData)
{
t.Id = count++;
t.Key = $"MULTIPLE_QUALIFIER_TEST-{t.Key}";
}
var sqlConnectionString = SqlConnectionHelper.GetSqlConnectionString();
ISqlBulkHelpersConnectionProvider sqlConnectionProvider = new SqlBulkHelpersConnectionProvider(sqlConnectionString);
using (var conn = await sqlConnectionProvider.NewConnectionAsync())
using (SqlTransaction transaction = conn.BeginTransaction())
{
ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(
sqlConnectionProvider, TestHelpers.SqlTimeoutSeconds);
var results = await sqlBulkIdentityHelper.BulkInsertOrUpdateAsync(
testData,
TestHelpers.TestTableName,
transaction,
new SqlMergeMatchQualifierExpression(
nameof(TestElement.Id),
nameof(TestElement.Value),
nameof(TestElement.Key)
)
);
transaction.Commit();
//ASSERT Results are Valid...
Assert.IsNotNull(results);
//We Sort the Results by Identity Id to ensure that the inserts occurred in the correct
// order with incrementing ID values as specified in the original Data!
//This validates that data is inserted as expected for Identity columns so that it can
// be correctly sorted by Incrementing Identity value when Queried (e.g. ORDER BY Id)
var resultsSorted = results.OrderBy(r => r.Id).ToList();
Assert.AreEqual(resultsSorted.Count(), testData.Count);
var i = 0;
foreach (var result in resultsSorted)
{
Assert.IsNotNull(result);
Assert.IsTrue(result.Id > 0);
Assert.AreEqual(result.Key, testData[i].Key);
Assert.AreEqual(result.Value, testData[i].Value);
i++;
}
}
}
public static async Task RunBenchmarksAsync(string sqlConnectionString)
{
ISqlBulkHelpersConnectionProvider sqlConnectionProvider = new SqlBulkHelpersConnectionProvider(sqlConnectionString);
using (var conn = await sqlConnectionProvider.NewConnectionAsync())
using (SqlTransaction transaction = conn.BeginTransaction())
{
var tableName = SqlBulkHelpersSampleApp.TestTableName;
ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(
conn, transaction, SqlBulkHelpersSampleApp.SqlTimeoutSeconds);
var timer = new Stopwatch();
//WARM UP THE CODE and initialize all CACHES!
timer.Start();
var testData = SqlBulkHelpersSample.CreateTestData(1);
await sqlBulkIdentityHelper.BulkInsertOrUpdateAsync(testData, tableName, transaction);
await sqlBulkIdentityHelper.BulkInsertOrUpdateAsync(testData, tableName, transaction);
timer.Stop();
Console.WriteLine($"Warm Up ran in [{timer.ElapsedMilliseconds} ms]...");
//NOW RUN BENCHMARK LOOPS
int itemCounter = 0, batchCounter = 1, dataSize = 1000;
timer.Reset();
for (; batchCounter <= 20; batchCounter++)
{
testData = SqlBulkHelpersSample.CreateTestData(dataSize);
timer.Start();
await sqlBulkIdentityHelper.BulkInsertAsync(testData, tableName, transaction);
timer.Stop();
itemCounter += testData.Count;
}
transaction.Commit();
Console.WriteLine($"[{batchCounter}] Bulk Uploads of [{dataSize}] items each, for total of [{itemCounter}], executed in [{timer.ElapsedMilliseconds} ms] at ~[{timer.ElapsedMilliseconds / batchCounter} ms] each!");
var tableCount = 0;
using (var sqlCmd = conn.CreateCommand())
{
sqlCmd.CommandText = $"SELECT COUNT(*) FROM [{tableName}]";
tableCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
}
Console.WriteLine($"[{tableCount}] Total Items in the Table Now!");
Console.ReadKey();
}
}
public async Task TestBulkInsertResultSortOrderWithIdentitySetterSupportAsync()
{
var testData = TestHelpers.CreateTestDataWithIdentitySetter(10);
var sqlConnectionString = SqlConnectionHelper.GetSqlConnectionString();
ISqlBulkHelpersConnectionProvider sqlConnectionProvider = new SqlBulkHelpersConnectionProvider(sqlConnectionString);
var sqlBulkHelpersSchemaLoader = SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(sqlConnectionProvider);
using (var conn = await sqlConnectionProvider.NewConnectionAsync())
using (SqlTransaction transaction = conn.BeginTransaction())
{
ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(sqlBulkHelpersSchemaLoader);
var results = await sqlBulkIdentityHelper.BulkInsertOrUpdateAsync(
testData,
TestHelpers.TestTableName,
transaction
);
transaction.Commit();
//ASSERT Results are Valid...
Assert.IsNotNull(results);
//We Sort the Results by Identity Id to ensure that the inserts occurred in the correct
// order with incrementing ID values as specified in the original Data!
//This validates that data is inserted as expected for Identity columns so that it can
// be correctly sorted by Incrementing Identity value when Queried (e.g. ORDER BY Id)
var resultsSorted = results.OrderBy(r => r.Id).ToList();
Assert.AreEqual(resultsSorted.Count(), testData.Count);
var i = 0;
foreach (var result in resultsSorted)
{
Assert.IsNotNull(result);
Assert.IsTrue(result.Id > 0);
Assert.AreEqual(result.Key, testData[i].Key);
Assert.AreEqual(result.Value, testData[i].Value);
i++;
}
}
}