public void WhereQuery_AliasedWhereFact() { var fromTable = new Table("dbo.from", "f"); var addressCol = new AliasedExpression <Expression>( new Expression("CONCAT(Street, Number)"), "Address"); var zipCityCol = new AliasedExpression <Expression>( new Expression("CONCAT(Zip, City)"), "ZipCity"); var builder = new SqlExpressionBuilderSelect(); builder .From(fromTable) .Select(addressCol) .Select(zipCityCol) .Where( new Compare <string, string>(addressCol.Alias, "@address")); builder.Where( new Compare <string, IDbDataParameter>(zipCityCol.Alias, new SqlParameter { ParameterName = "@zc" })); Assert.Equal( "SELECT CONCAT(Street, Number) AS Address, CONCAT(Zip, City) AS ZipCity FROM dbo.from f " + "WHERE Address = @address AND ZipCity = @zc", builder.ToString()); Assert.Equal(2, builder.Parameters.Count()); }
public void BindParameter_BindAndRebindParameterFact() { var builder = new SqlExpressionBuilderSelect(); builder.From("table", "t"); builder.Where("t.foo = @foo"); var param = new SqlParameter("@foo", SqlDbType.Int) { Value = 5 }; builder.BindParameter(param); Assert.Equal(1, builder.Parameters.Count()); Assert.Equal(param, builder.Parameters.First()); // Rebind by new param var newParam = new SqlParameter("@foo", SqlDbType.Int) { Value = 6 }; builder.BindParameter(newParam); Assert.Equal(1, builder.Parameters.Count()); Assert.Equal(newParam, builder.Parameters.First()); // Rebind by overwrite builder.BindParameter("@foo", 7); Assert.Equal(1, builder.Parameters.Count()); Assert.Equal(newParam, builder.Parameters.First()); Assert.Equal(7, builder.Parameters.First().Value); }
public void WhereQuery_ComparerFact() { var fromTable = new Table("dbo.from", "f"); var builder = new SqlExpressionBuilderSelect(); builder.From(fromTable); builder.Where(fromTable.GetColumn("foo"), "@foo"); builder.Where(fromTable.GetColumn("bar"), new SqlParameter { ParameterName = "@bar" }); builder.Where(fromTable.GetColumn("baz"), Expression.Null, CompareOperations.IsNot); builder.Where(fromTable.GetColumn("moo"), new Expression("(SELECT value FROM options o WHERE key = 'mooKey')")); Assert.Equal("SELECT * FROM dbo.from f WHERE f.foo = @foo AND f.bar = @bar AND f.baz IS NOT NULL " + "AND f.moo = (SELECT value FROM options o WHERE key = 'mooKey')", builder.ToString()); Assert.Equal(2, builder.Parameters.Count()); }
public void BindParameter_DoesNotThrowOnParameterFoundFact() { var builder = new SqlExpressionBuilderSelect(); builder.From("table", "t"); builder.Where("t.foo = @foo"); Assert.DoesNotThrow(() => builder.BindParameter("@foo", DbType.Int32, 12)); }
public void WhereQuery_SimpleFact() { var fromTable = new Table("dbo.from", "f"); var builder = new SqlExpressionBuilderSelect(); builder.From(fromTable); builder.Where("bla = @bla OR blubb = @bla"); Assert.Equal("SELECT * FROM dbo.from f WHERE bla = @bla OR blubb = @bla", builder.ToString()); Assert.Equal(1, builder.Parameters.Count()); }
public void FillCommand_ThrowsIfIncompleteFact() { var command = new SqlCommand(); var builder = new SqlExpressionBuilderSelect(); var fromTable = new Table("table", "t"); var param = new SqlParameter("@bla", SqlDbType.VarChar); builder.From(fromTable); builder.Where("t.col = @bla", new[] { param }); Assert.Throws <Exception>(() => builder.FillCommand(command)); }
public void FillCommand() { var command = new SqlCommand(); var builder = new SqlExpressionBuilderSelect(); var fromTable = new Table("table", "t"); var param = new SqlParameter("@bla", SqlDbType.VarChar) { Value = 5 }; builder.From(fromTable); builder.Where("t.col = @bla", new[] { param }); builder.FillCommand(command); Assert.Equal(1, command.Parameters.Count); Assert.Equal(param, command.Parameters.Cast <SqlParameter>().First()); Assert.Equal(builder.ToString(), command.CommandText); }