/// <summary>
/// INSERT INTO (...) SELECT ...
/// </summary>
public static int Insert <T, STFrom, ST>(this IQueryRunner runner, FromQuery <T> query, Query <STFrom, ST> subQuery, Expression <Action <ST, InsertBuilder <T> > > insertExpr) where T : new()
{
var stmtList = new StatementList();
stmtList.Insert(query, subQuery, insertExpr);
return(runner.ExecuteNonQuery(stmtList));
}
/// <summary>
/// UPDATE tbl JOIN tbl2 SET tbl.X = tbl2.Y ...
/// </summary>
public static int Update <T, TJoin>(this IQueryRunner runner, FlatQuery <T, TJoin> query, Expression <Action <TJoin, InsertBuilder <T> > > insertExpr) where T : new()
{
var stmtList = new StatementList();
stmtList.Update(query, insertExpr);
return(runner.ExecuteNonQuery(stmtList));
}
public static int Delete <T, TJoin>(this IQueryRunner runner, FlatQuery <T, TJoin> query) where T : new()
{
var stmtList = new StatementList();
stmtList.Delete(query);
return(runner.ExecuteNonQuery(stmtList));
}
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);
}