const int AVERAGE_PROPERTY_LENGTH = 14 + 2; // assumed average property length, plus 2 characters for "@" and ("," or "="). #region Insert<T>() public static QueryInfo Insert<T>(T obj, Predicate<string> excludedProperties = null, string tableName = null) where T : class { if (tableName == null) tableName = obj.AsSqlName(); var dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj); var dictCount = dict.Count; var dictNew = new Dictionary<string, (object, Type)>(dictCount, Util.FastStringComparer.Instance); int i = 0; string currentKey, paramName; var sb = new StringBuilder("INSERT ", AVERAGE_PROPERTY_LENGTH * dictCount * 2 + 25).Append(tableName).Append(" ("); var sbParams = new StringBuilder(AVERAGE_PROPERTY_LENGTH * dictCount); foreach (var kvp in dict) { currentKey = kvp.Key; if (excludedProperties != null && excludedProperties(currentKey)) continue; if (i++ != 0) { sb.Append(','); sbParams.Append(','); } sb.Append('[').Append(currentKey).Append(']'); paramName = "@#" + currentKey; sbParams.Append(paramName); dictNew.Add(paramName, kvp.Value); }//foreach sb.Append(") VALUES (").Append(sbParams.ToString()).Append(')'); var result = QueryInfo.Create(sql: sb.ToString(), parameterMap: dictNew); return result; }// Insert<T>()
public async Task TestSubsequentQueriesSuccessAsync() { var data0 = (await db.QueryAsync("select 1 as [Id] where 1 = 0")).ToObjectArray <AsyncFoo0>(); Assert.IsTrue(data0.Length == 0); var data1 = (await db.QueryAsync(QueryInfo.Create("select 1 as [Id] where 1 = 0"))).ToObjectArray <AsyncFoo1>(); Assert.IsTrue(data1.Length == 0); var data2 = (await db.QueryAsync(QueryInfo.Create("select 1 as [Id] where 1 = 0"))).ToObjectArray <AsyncFoo2>(); Assert.IsTrue(data2.Length == 0); data0 = (await db.QueryAsync("select 1 as [Id] where 1 = 0")).ToObjectArray <AsyncFoo0>(); Assert.IsTrue(data0.Length == 0); data1 = (await db.QueryAsync(QueryInfo.Create("select 1 as [Id] where 1 = 0"))).ToObjectArray <AsyncFoo1>(); Assert.IsTrue(data1.Length == 0); data2 = (await db.QueryAsync(QueryInfo.Create("select 1 as [Id] where 1 = 0"))).ToObjectArray <AsyncFoo2>(); Assert.IsTrue(data2.Length == 0); }
public async Task SanityTestBatchedQueries() { { var batch1 = QueryBatch.Create(); for (int i = 0; i < 50; ++i) { batch1.AddQuery("select [Answer] = 2;"); } int result1 = await db.CommitQueryBatchAsync(batch1); Console.WriteLine(result1); Assert.IsTrue(result1 == -1); // -1 // No rows were changed per batch; 1 batch only (default batch size is 50). var batch2 = QueryBatch.Create(); for (int i = 0; i < 65; ++i) { batch2.AddQuery("select [Answer] = 2;"); } int result2 = await db.CommitQueryBatchAsync(batch2); Console.WriteLine(result2); Assert.IsTrue(result2 == -1); // -1 // No rows were changed per batch; 1 batch only (short 2nd batch of 15 queries merged into the 1st batch) // short batch is 50/3 = 16 queries or less } { var batch3 = QueryBatch.Create(); for (int i = 0; i < 70; ++i) { batch3.AddQuery("select [Answer] = 2;"); } int result3 = await db.CommitQueryBatchAsync(batch3); Console.WriteLine(result3); Assert.IsTrue(result3 == -2); // -2 // No rows were changed per batch; 2 batches: // 1st batch of 50 queries and 2nd batch of 20 queries // last batch is larger than short batch - triggers an additional db call } { var batch1 = QueryBatch.Create(); var batch2 = QueryBatch.Create(); for (int i = 0; i < 40; ++i) { batch1.AddQuery("select [Answer] = 1;"); batch2.AddQuery("select [Answer] = 2;"); } batch2.Append(batch1); // adding batch1 queries to batch2 int result = await db.CommitQueryBatchAsync(QueryBatch.Create(new[] { batch1, batch2 })); Console.WriteLine(result); Assert.IsTrue(result == -3); // -3 // No rows were changed per batch; 3 batches: // 1st batch of 50 queries; 2nd batch of 50 queries; 3rd batch of 20 queries // last batch is larger than short batch - triggers an additional db call } { var batch = QueryBatch.Create(); for (int i = 0; i < 43; ++i) { batch.AddQuery("select [Answer] = 1;"); } int result = await db.CommitQueryBatchAsync(queryBatch : batch, batchSize : 10); Console.WriteLine(result); Assert.IsTrue(result == -4); // -4 // 3 batches with 10 queries and last batch with 13 queries } { var dbTemp = DbContext.Create(db.ConnectionString); var batch = QueryBatch.Create(); for (int i = 0; i < 43; ++i) { batch.AddQuery("select [Answer] = 1;"); } dbTemp.BatchSize = 10; int result = await dbTemp.CommitQueryBatchAsync(batch); Console.WriteLine(result); Assert.IsTrue(result == -4); // -4 // 3 batches with 10 queries and last batch with 13 queries } { var queryBatch = QueryBatch.Create(); var queryInfo = QueryInfo.Create("declare @foo table(id int not null); insert @foo values (@id), (@id), (@id);", new { id = 37 }); queryBatch.AddQuery(queryInfo); int result = await db.CommitQueryBatchAsync(queryBatch); Console.WriteLine(result); Assert.IsTrue(result == 3); } }