public void SetVariableTypes(Type runnerType)
{
var stmtList = new StatementList();
var intVar = stmtList.DeclareSqlVariable <int>("intVar");
stmtList.SetSqlVariable(intVar, ctx => 1);
var decimalVar = stmtList.DeclareSqlVariable <decimal>("decimalVar");
stmtList.SetSqlVariable(decimalVar, ctx => 2.23M);
var intNullableVar = stmtList.DeclareSqlVariable <int?>("intNullableVar");
stmtList.SetSqlVariable(intNullableVar, ctx => 42);
var stringVar = stmtList.DeclareSqlVariable <string>("stringVar");
stmtList.SetSqlVariable(stringVar, ctx => "test");
var select = stmtList.Select(ctx => new {
intVar = intVar.Value,
decimalVar = decimalVar.Value,
intNullableVar = intNullableVar.Value,
stringVar = stringVar.Value
});
var runner = (IQueryRunner)Provider.GetRequiredService(runnerType);
ResetDb(runner);
var results = runner.ExecuteQuery(select).ToList();
Assert.AreEqual(1, results.Count, "Should be 1 result");
Assert.AreEqual(1, results[0].intVar, "intVar should be 1");
Assert.AreEqual(42, results[0].intNullableVar, "intNullableVar should be 42");
Assert.AreEqual(2.23M, results[0].decimalVar, "decimalVar should be 2.23");
Assert.AreEqual("test", results[0].stringVar, "stringVar should be 'test'");
}
public void SetVariableFromSelect(Type runnerType)
{
var stmtList = new StatementList();
var testVariable = stmtList.DeclareSqlVariable <int>("test");
stmtList.SetSqlVariable(testVariable,
varctx => DB.Products.Where(p => p.ProductId == 1).Project((ctx, p) => p.ProductId).AsExpression(varctx));
var select = stmtList.Select(ctx => new { Result = testVariable.Value });
var runner = (IQueryRunner)Provider.GetRequiredService(runnerType);
ResetDb(runner);
var results = runner.ExecuteQuery(select).ToList();
Assert.AreEqual(1, results.Count, "Should be 1 result");
Assert.AreEqual(1, results[0].Result, "Selected variable should be 1");
}
public void SetVariableIdentityExpression(Type runnerType)
{
var stmtList = new StatementList();
stmtList.Insert(DB.Products, insert => insert.Value(p => p.Name, "test insert product"));
var identity = stmtList.DeclareSqlVariable <int>("myident");
stmtList.SetSqlVariable(identity, (ctx) => Function.LastInsertIdentity <int>(ctx));
var select = stmtList.Select(ctx => new { Identity = identity.Value });
var runner = (IQueryRunner)Provider.GetRequiredService(runnerType);
ResetDb(runner);
var results = runner.ExecuteQuery(select).ToList();
Assert.AreEqual(1, results.Count, "Should be 1 result");
Assert.AreEqual(3, results[0].Identity, "New identity should be 3");
}
protected void ResetDb(IQueryRunner runner)
{
var stmtList = new StatementList();
stmtList.Insert(DB.Products, insert => insert.Value(p => p.Name, "Happy T-Shirt"));
var product1Id = stmtList.DeclareSqlVariable <int>("product1Id");
stmtList.SetSqlVariable(product1Id, ctx => Function.LastInsertIdentity <int>(ctx));
stmtList.Insert(DB.Products, insert => insert.Value(p => p.Name, "Test Product Without Units"));
var product2Id = stmtList.DeclareSqlVariable <int>("product2Id");
stmtList.SetSqlVariable(product2Id, ctx => Function.LastInsertIdentity <int>(ctx));
stmtList.Insert(DB.Units, insert => insert
.Value(p => p.Name, "Happy XL")
.Value(p => p.ProductId, product1Id.Value)
.Value(p => p.Price, 150)
.Value(p => p.UnitCode, "P10001"));
var unit1Id = stmtList.DeclareSqlVariable <int>("unit1Id");
stmtList.SetSqlVariable(unit1Id, ctx => Function.LastInsertIdentity <int>(ctx));
stmtList.Insert(DB.Units, insert => insert
.Value(p => p.Name, "Happy Large")
.Value(p => p.ProductId, product1Id.Value)
.Value(p => p.Price, 100)
.Value(p => p.UnitCode, "P10002"));
var unit2Id = stmtList.DeclareSqlVariable <int>("unit2Id");
stmtList.SetSqlVariable(unit2Id, ctx => Function.LastInsertIdentity <int>(ctx));
stmtList.Insert(DB.Units, insert => insert
.Value(p => p.Name, "Happy Small")
.Value(p => p.ProductId, product1Id.Value)
.Value(p => p.Price, 50)
.Value(p => p.UnitCode, "P10003"));
var unit3Id = stmtList.DeclareSqlVariable <int>("unit3Id");
stmtList.SetSqlVariable(unit3Id, ctx => Function.LastInsertIdentity <int>(ctx));
stmtList.Insert(DB.Inventories, insert => insert
.Value(p => p.UnitId, unit1Id.Value)
.Value(p => p.Stock, 10));
stmtList.Insert(DB.Inventories, insert => insert
.Value(p => p.UnitId, unit2Id.Value)
.Value(p => p.Stock, 50));
stmtList.Insert(DB.Inventories, insert => insert
.Value(p => p.UnitId, unit3Id.Value)
.Value(p => p.Stock, 0));
var date1 = new DateTime(2000, 1, 1, 14, 00, 00);
var date2 = new DateTime(2000, 1, 1, 14, 00, 10);
var bytes = new byte[200];
for (var i = 0; i < bytes.Length; i++)
{
bytes[i] = (byte)i;
}
// Insert some records to aggregate
stmtList.Insert(DB.TypeValues, insert => insert
.Value(t => t.ByteValue, 1)
.Value(t => t.BoolValue, false)
.Value(t => t.DateTimeValue, date1)
.Value(t => t.NullableDateTimeValue, null)
.Value(t => t.DecimalValue, 1.0M)
.Value(t => t.DoubleValue, 1.0)
.Value(t => t.FloatValue, 1.0f)
.Value(t => t.IntValue, 1)
.Value(t => t.NullableIntValue, 1)
.Value(t => t.LongValue, 1)
.Value(t => t.ShortValue, 1)
.Value(t => t.StringValue, "1")
.Value(t => t.IntEnumValue, IntEnumType.TestValue1)
.Value(t => t.BlobValue, bytes)
);
stmtList.Insert(DB.TypeValues, insert => insert
.Value(t => t.ByteValue, 10)
.Value(t => t.BoolValue, false)
.Value(t => t.DateTimeValue, date2)
.Value(t => t.NullableDateTimeValue, date2)
.Value(t => t.DecimalValue, 10.0M)
.Value(t => t.DoubleValue, 10.0)
.Value(t => t.FloatValue, 10.0f)
.Value(t => t.IntValue, 10)
.Value(t => t.NullableIntValue, 10)
.Value(t => t.LongValue, 10)
.Value(t => t.ShortValue, 10)
.Value(t => t.StringValue, "10")
.Value(t => t.IntEnumValue, IntEnumType.TestValue2)
.Value(t => t.BlobValue, bytes)
);
runner.ExecuteNonQuery(stmtList);
}