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