public void FromThrowsArgumentExceptionForEmptyTableName() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty); var exception = Assert.Throws<ArgumentException>( () => sqlBuilder.From("")); Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("table"), exception.Message); }
public void SelectFromType() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty); var sqlQuery = sqlBuilder .From(typeof(Customer)) .ToSqlQuery(); Assert.Equal("SELECT Created,CreditLimit,DateOfBirth,Id,Name,CustomerStatusId,Updated,Website FROM Sales.Customers", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void SelectFromWhereComplex() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2", "Column3"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1 = @p0 OR @p0 IS NULL", "Foo") .AndWhere("Column2 BETWEEN @p0 AND @p1", DateTime.Today.AddDays(-1), DateTime.Today) .OrWhere("Column3 IN (@p0, @p1, @p2, @p3)", 1, 2, 3, 4) .ToSqlQuery(); Assert.Equal("SELECT Column1,Column2,Column3 FROM Table WHERE (Column1 = @p0 OR @p0 IS NULL) AND (Column2 BETWEEN @p1 AND @p2) OR (Column3 IN (@p3, @p4, @p5, @p6))", sqlQuery.CommandText); Assert.Equal(7, sqlQuery.Arguments.Count); Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType); Assert.Equal("Foo", sqlQuery.Arguments[0].Value); Assert.Equal(DbType.DateTime, sqlQuery.Arguments[1].DbType); Assert.Equal(DateTime.Today.AddDays(-1), sqlQuery.Arguments[1].Value); Assert.Equal(DbType.DateTime, sqlQuery.Arguments[2].DbType); Assert.Equal(DateTime.Today, sqlQuery.Arguments[2].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType); Assert.Equal(1, sqlQuery.Arguments[3].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[4].DbType); Assert.Equal(2, sqlQuery.Arguments[4].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[5].DbType); Assert.Equal(3, sqlQuery.Arguments[5].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[6].DbType); Assert.Equal(4, sqlQuery.Arguments[6].Value); }
public void SelectFromSpecifyingColumnsAndTableName() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2"); var sqlQuery = sqlBuilder .From("Table") .ToSqlQuery(); Assert.Equal("SELECT Column1,Column2 FROM Table", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void SelectFromSpecifyingColumnsAndType() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Name", "DateOfBirth"); var sqlQuery = sqlBuilder .From(typeof(Customer)) .ToSqlQuery(); Assert.Equal("SELECT Name,DateOfBirth FROM Sales.Customers", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void OrderByDescendingAppendsColumnsCorrectlyForSingleColumn() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId"); var sqlQuery = sqlBuilder.From("Customer") .OrderByDescending("CustomerId") .ToSqlQuery(); Assert.Equal("SELECT CustomerId FROM Customer ORDER BY CustomerId DESC", sqlQuery.CommandText); }
public void SelectFromOrderByDescendingThenAscending() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2"); var sqlQuery = sqlBuilder .From("Table") .OrderByDescending("Column1") .OrderByAscending("Column2") .ToSqlQuery(); Assert.Equal("SELECT Column1,Column2 FROM Table ORDER BY Column1 DESC,Column2 ASC", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void SelectWhereOrWhereInArgs() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column2").IsEqualTo("FOO") .OrWhere("Column1").In(1, 2, 3) .ToSqlQuery(); Assert.Equal("SELECT Column1 FROM Table WHERE (Column2 = ?) OR (Column1 IN (?,?,?))", sqlQuery.CommandText); Assert.Equal(4, sqlQuery.Arguments.Count); Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType); Assert.Equal("FOO", sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(1, sqlQuery.Arguments[1].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType); Assert.Equal(2, sqlQuery.Arguments[2].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType); Assert.Equal(3, sqlQuery.Arguments[3].Value); }
public void SelectWhereOrWhereInArgsWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column2").IsEqualTo("FOO") .OrWhere("Column1").In(1, 2, 3) .ToSqlQuery(); Assert.Equal("SELECT [Column1] FROM [Table] WHERE ([Column2] = @p0) OR ([Column1] IN (@p1,@p2,@p3))", sqlQuery.CommandText); Assert.Equal(4, sqlQuery.Arguments.Count); Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType); Assert.Equal("FOO", sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(1, sqlQuery.Arguments[1].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType); Assert.Equal(2, sqlQuery.Arguments[2].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType); Assert.Equal(3, sqlQuery.Arguments[3].Value); }
public void SelectWhereNotInArgsWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1").NotIn(1, 2, 3) .ToSqlQuery(); Assert.Equal("SELECT [Column1] FROM [Table] WHERE ([Column1] NOT IN (@p0,@p1,@p2))", sqlQuery.CommandText); Assert.Equal(3, sqlQuery.Arguments.Count); Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType); Assert.Equal(1, sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(2, sqlQuery.Arguments[1].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType); Assert.Equal(3, sqlQuery.Arguments[2].Value); }
public void SelectWhereNotInMultipleSqlQueries() { var subQuery1 = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 1024); var subQuery2 = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 2048); var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1").NotIn(subQuery1, subQuery2) .ToSqlQuery(); Assert.Equal("SELECT Column1 FROM Table WHERE (Column1 NOT IN ((SELECT Id FROM Table WHERE Column = ?), (SELECT Id FROM Table WHERE Column = ?)))", sqlQuery.CommandText); Assert.Equal(2, sqlQuery.Arguments.Count); Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType); Assert.Equal(1024, sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(2048, sqlQuery.Arguments[1].Value); }
public void SelectWhereNotExistsSqlQuery() { var subQuery = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 1024); var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where().NotExists(subQuery) .ToSqlQuery(); Assert.Equal("SELECT Column1 FROM Table WHERE NOT EXISTS (SELECT Id FROM Table WHERE Column = ?)", sqlQuery.CommandText); Assert.Equal(1, sqlQuery.Arguments.Count); Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType); Assert.Equal(1024, sqlQuery.Arguments[0].Value); }
public void SelectWhereNotBetweenWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1").NotBetween(1, 10) .ToSqlQuery(); Assert.Equal("SELECT [Column1] FROM [Table] WHERE ([Column1] NOT BETWEEN @p0 AND @p1)", sqlQuery.CommandText); Assert.Equal(2, sqlQuery.Arguments.Count); Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType); Assert.Equal(1, sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(10, sqlQuery.Arguments[1].Value); }
public void SelectWhereNotBetween() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1").NotBetween(1, 10) .ToSqlQuery(); Assert.Equal("SELECT Column1 FROM Table WHERE (Column1 NOT BETWEEN ? AND ?)", sqlQuery.CommandText); Assert.Equal(2, sqlQuery.Arguments.Count); Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType); Assert.Equal(1, sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(10, sqlQuery.Arguments[1].Value); }
public void NotInThrowsArgumentNullExceptionForNullSqlQuery() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId"); Assert.Throws<ArgumentNullException>( () => sqlBuilder.From("Customer").Where("Column").NotIn((SqlQuery)null)); }
public void SelectWhereOrWhereInSqlQuery() { var subQuery = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 1024); var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column2 = ?", "FOO") .OrWhere("Column1").In(subQuery) .ToSqlQuery(); Assert.Equal("SELECT Column1 FROM Table WHERE (Column2 = ?) OR (Column1 IN (SELECT Id FROM Table WHERE Column = ?))", sqlQuery.CommandText); Assert.Equal(2, sqlQuery.Arguments.Count); Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType); Assert.Equal("FOO", sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(1024, sqlQuery.Arguments[1].Value); }
public void OrderByAscendingAppendsColumnsCorrectlyForMultipleColumns() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId"); var sqlQuery = sqlBuilder.From("Customer") .OrderByAscending("FirstName", "LastName") .ToSqlQuery(); Assert.Equal("SELECT CustomerId FROM Customer ORDER BY FirstName ASC,LastName ASC", sqlQuery.CommandText); }
public void GroupByAppendsColumnsCorrectlyForSingleColumn() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId"); var sqlQuery = sqlBuilder.From("Customer") .GroupBy("CustomerId") .ToSqlQuery(); Assert.Equal("SELECT CustomerId FROM Customer GROUP BY CustomerId", sqlQuery.CommandText); }
public void OrderByDescendingThrowsArgumentNullException() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId"); Assert.Throws<ArgumentException>( () => sqlBuilder.From("Customer").OrderByDescending((string)null)); Assert.Throws<ArgumentException>( () => sqlBuilder.From("Customer").OrderByDescending(string.Empty)); Assert.Throws<ArgumentNullException>( () => sqlBuilder.From("Customer").OrderByDescending((string[])null)); }
public void SelectWhereOrWhereInSqlQueryWithSqlCharacters() { var subQuery = new SqlQuery("SELECT Id FROM Table WHERE Column = @p0", 1024); var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column2 = @p0", "FOO") .OrWhere("Column1").In(subQuery) .ToSqlQuery(); Assert.Equal("SELECT [Column1] FROM [Table] WHERE (Column2 = @p0) OR ([Column1] IN (SELECT Id FROM Table WHERE Column = @p1))", sqlQuery.CommandText); Assert.Equal(2, sqlQuery.Arguments.Count); Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType); Assert.Equal("FOO", sqlQuery.Arguments[0].Value); Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType); Assert.Equal(1024, sqlQuery.Arguments[1].Value); }
public void SelectFromOrderByDescendingWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1", "Column2"); var sqlQuery = sqlBuilder .From("Table") .OrderByDescending("Column1", "Column2") .ToSqlQuery(); Assert.Equal("SELECT [Column1],[Column2] FROM [Table] ORDER BY [Column1] DESC,[Column2] DESC", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void SingleColumnPredicatesShouldAppendOperand() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column2").In("Opt1", "Opt2") .AndWhere("Column3").IsEqualTo(1) .AndWhere("Column4").IsGreaterThan(2) .AndWhere("Column5").IsGreaterThanOrEqualTo(3) .AndWhere("Column6").IsLessThan(4) .AndWhere("Column7").IsLessThanOrEqualTo(5) .AndWhere("Column8").IsLike("%J") .AndWhere("Column9").IsNotEqualTo(6) .AndWhere("Column10").IsNotNull() .AndWhere("Column11").IsNull() .ToSqlQuery(); Assert.Equal(@"SELECT Column1 FROM Table WHERE (Column2 IN (?,?)) AND (Column3 = ?) AND (Column4 > ?) AND (Column5 >= ?) AND (Column6 < ?) AND (Column7 <= ?) AND (Column8 LIKE ?) AND (Column9 <> ?) AND (Column10 IS NOT NULL) AND (Column11 IS NULL)", sqlQuery.CommandText); }
public void SelectFromSpecifyingColumnsAndTableNameWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1", "Column2"); var sqlQuery = sqlBuilder .From("Table") .ToSqlQuery(); Assert.Equal("SELECT [Column1],[Column2] FROM [Table]", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void GroupByThrowsArgumentNullException() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId"); Assert.Throws<ArgumentException>(() => sqlBuilder.From("Customer").GroupBy((string)null)); Assert.Throws<ArgumentNullException>(() => sqlBuilder.From("Customer").GroupBy((string[])null)); }
public void SelectFromSpecifyingColumnsAndTypeWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Name", "DateOfBirth"); var sqlQuery = sqlBuilder .From(typeof(Customer)) .ToSqlQuery(); Assert.Equal("SELECT [Name],[DateOfBirth] FROM [Sales].[Customers]", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void InThrowArgumentNullExceptionForNullSqlQueries() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty); var exception = Assert.Throws<ArgumentNullException>( () => sqlBuilder.From("Customer").Where("Column").In((SqlQuery[])null)); Assert.Equal("subQueries", exception.ParamName); }
public void SelectFromTypeWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance); var sqlQuery = sqlBuilder .From(typeof(Customer)) .ToSqlQuery(); Assert.Equal("SELECT [Created],[CreditLimit],[DateOfBirth],[Id],[Name],[CustomerStatusId],[Updated],[Website] FROM [Sales].[Customers]", sqlQuery.CommandText); Assert.Empty(sqlQuery.Arguments); }
public void NotExistsThrowArgumentNullExceptionForNullSqlQuery() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty); var exception = Assert.Throws<ArgumentNullException>( () => sqlBuilder.From("Customer").Where().NotExists(null)); Assert.Equal("subQuery", exception.ParamName); }
public void SelectFromWhereOr() { var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1").IsEqualTo("Foo") .OrWhere("Column2").IsEqualTo("Bar") .ToSqlQuery(); Assert.Equal("SELECT Column1,Column2 FROM Table WHERE (Column1 = ?) OR (Column2 = ?)", sqlQuery.CommandText); Assert.Equal(2, sqlQuery.Arguments.Count); Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType); Assert.Equal("Foo", sqlQuery.Arguments[0].Value); Assert.Equal(DbType.String, sqlQuery.Arguments[1].DbType); Assert.Equal("Bar", sqlQuery.Arguments[1].Value); }
public void SelectWhereColumnIsNullWithSqlCharacters() { var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1"); var sqlQuery = sqlBuilder .From("Table") .Where("Column1") .IsNull() .ToSqlQuery(); Assert.Equal("SELECT [Column1] FROM [Table] WHERE ([Column1] IS NULL)", sqlQuery.CommandText); Assert.Equal(0, sqlQuery.Arguments.Count); }