コード例 #1
0
        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();
                }
        }
コード例 #2
0
        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++;
                    }
                }
        }
コード例 #3
0
        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();
                }
        }
コード例 #4
0
        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++;
                    }
                }
        }
コード例 #5
0
        public static void RunBenchmarks(string sqlConnectionString)
        {
            ISqlBulkHelpersConnectionProvider sqlConnectionProvider = new SqlBulkHelpersConnectionProvider(sqlConnectionString);

            using (var conn = sqlConnectionProvider.NewConnection())
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    var tableName = SqlBulkHelpersSampleApp.TestTableName;

                    ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(conn, transaction);

                    var timer = new Stopwatch();

                    //WARM UP THE CODE and initialize all CACHES!
                    timer.Start();
                    List <TestElement> testData = SqlBulkHelpersSample.CreateTestData(1);

                    sqlBulkIdentityHelper.BulkInsertOrUpdate(testData, tableName, transaction);
                    sqlBulkIdentityHelper.BulkInsertOrUpdate(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();
                        var results = sqlBulkIdentityHelper.BulkInsert(testData, tableName, transaction)?.ToList();
                        timer.Stop();

                        if (results.Count() != dataSize)
                        {
                            Console.WriteLine($"The results count of [{results.Count()}] does not match the expected count of [{dataSize}]!!!");
                        }

                        if (results.Any(t => t.Id <= 0))
                        {
                            Console.WriteLine($"Some items were returned with an invalid Identity Value (e.g. may still be initialized to default value [{default(int)})");
                        }

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