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++; } } }