public void ReadAppliedMigrations(SqlQueryRunner runner) { var stmtList = new StatementList(); var select = stmtList.Select(Context.Migrations.OrderBy(builder => builder.Value(m => m.Name, true))); try { AppliedMigrations = runner.ExecuteQuery(select).ToList(); } catch (Exception) { // Ignore exception when the table does not exist. // Should ideally not ignore more serious errors, or even more ideally // check db schema if table exists } }
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"); }
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'"); }