public void InsertSelectTwo(Type runnerType)
{
var runner = (IQueryRunner)Provider.GetRequiredService(runnerType);
ResetDb(runner);
var stmtList = new StatementList();
stmtList.Insert(DB.Products, DB.Units.Where(u => u.UnitId == 1), (u, insert) => insert.Value(p => p.Name, "test insert product 1"));
stmtList.Insert(DB.Products, DB.Units.Where(u => u.UnitId == 1), (u, insert) => insert.Value(p => p.Name, "test insert product 2"));
var affectedRows = runner.ExecuteNonQuery(stmtList);
Assert.AreEqual(2, affectedRows);
}
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");
}
public void MigrateToLatest(SqlQueryRunner runner)
{
if (AppliedMigrations.Count > Migrations.Count)
{
throw new InvalidOperationException("There are more applied migrations than actual migrations.");
}
if (AppliedMigrations.Count == 0)
{
EnsureMigrationTable(runner);
}
for (var i = 0; i < Migrations.Count; i++)
{
var assemblyMigration = Migrations[i];
if (i < AppliedMigrations.Count)
{
var appliedMigration = AppliedMigrations[i];
if (appliedMigration.Name != assemblyMigration.Name)
{
throw new InvalidOperationException("Have applied migration " + appliedMigration.Name + ", expected " + assemblyMigration.Name);
}
continue;
}
assemblyMigration.Up(runner);
var stmtList = new StatementList();
stmtList.Insert(Context.Migrations,
insert => insert
.Value(m => m.Name, assemblyMigration.Name)
.Value(m => m.Version, "Version X"));
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);
}