| private From ( IObjectInfo objectInfo ) : IWhereOrOrderBy | ||
| objectInfo | IObjectInfo | |
| return | IWhereOrOrderBy | |
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);
}