public void AndWhereThrowsArgumentExceptionForNullPredicate()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.AndWhere(null, new object()));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("predicate"), exception.Message);
}
public void AverageThrowsArgumentExceptionForNullColumnAlias()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.Average("Column", null));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("columnAlias"), exception.Message);
}
public void AndWhereThrowsArgumentExceptionForEmptyColumn()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.AndWhere(""));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"), exception.Message);
}
public void AndWhereThrowsArgumentNullExceptionForEmptyArgs()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentNullException>(
() => sqlBuilder.AndWhere("x = ?", null));
Assert.Equal("args", exception.ParamName);
}
public void SelectDistinctColumnWithSqlCharacters()
{
var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, (string)null);
var sqlQuery = sqlBuilder
.Distinct("CreditLimit")
.From(typeof(Customer))
.ToSqlQuery();
Assert.Equal("SELECT DISTINCT [CreditLimit] FROM [Sales].[Customers]", sqlQuery.CommandText);
Assert.Equal(0, sqlQuery.Arguments.Count);
}
public void SelectDistinctColumn()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, (string)null);
var sqlQuery = sqlBuilder
.Distinct("CreditLimit")
.From(typeof(Customer))
.ToSqlQuery();
Assert.Equal("SELECT DISTINCT CreditLimit FROM Sales.Customers", sqlQuery.CommandText);
Assert.Equal(0, sqlQuery.Arguments.Count);
}
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 NotBetweenThrowsArgumentExceptionForNullUpper()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentNullException>(
() => sqlBuilder.NotBetween(1, null));
Assert.Equal("upper", exception.ParamName);
}
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 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 SelectAverageWithOtherColumn()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Id");
var sqlQuery = sqlBuilder
.Average("CreditLimit")
.From(typeof(Customer))
.Where("CustomerStatusId").IsEqualTo(CustomerStatus.Active)
.GroupBy("CustomerStatusId")
.ToSqlQuery();
Assert.Equal("SELECT Id,AVG(CreditLimit) AS CreditLimit FROM Sales.Customers WHERE (CustomerStatusId = ?) GROUP BY CustomerStatusId", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
Assert.Equal(CustomerStatus.Active, sqlQuery.Arguments[0].Value);
}
public void SelectAverageWithAlias()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, (string)null);
var sqlQuery = sqlBuilder
.Average("CreditLimit", columnAlias: "AverageCreditLimit")
.From(typeof(Customer))
.Where("CustomerStatusId").IsEqualTo(CustomerStatus.Active)
.ToSqlQuery();
Assert.Equal("SELECT AVG(CreditLimit) AS AverageCreditLimit FROM Sales.Customers WHERE (CustomerStatusId = ?)", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
Assert.Equal(CustomerStatus.Active, sqlQuery.Arguments[0].Value);
}
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 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 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 NotInThrowsArgumentNullExceptionForNullSqlQuery()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId");
Assert.Throws<ArgumentNullException>(
() => sqlBuilder.From("Customer").Where("Column").NotIn((SqlQuery)null));
}
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 SelectChainingMultipleFunctions()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1");
var sqlQuery = sqlBuilder
.Count("Column2", "Col2")
.Max("Column3", "Col3")
.From("Table")
.ToSqlQuery();
Assert.Equal("SELECT Column1,COUNT(Column2) AS Col2,MAX(Column3) AS Col3 FROM Table", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
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 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 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 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 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 MinThrowsArgumentExceptionForNullColumn()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.Min(null));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("columnName"), 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 SelectCountWithAlias()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, (string)null);
var sqlQuery = sqlBuilder
.Count("Id", columnAlias: "CustomerCount")
.From(typeof(Customer))
.ToSqlQuery();
Assert.Equal("SELECT COUNT(Id) AS CustomerCount 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 SelectCountWithOtherColumn()
{
var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerStatusId");
var sqlQuery = sqlBuilder
.Count("Id")
.From(typeof(Customer))
.GroupBy("CustomerStatusId")
.ToSqlQuery();
Assert.Equal("SELECT CustomerStatusId,COUNT(Id) AS Id FROM Sales.Customers GROUP BY CustomerStatusId", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
/// <summary>
/// Builds an SqlQuery to select the database record with the specified identifier for the type specified by the IObjectInfo.
/// </summary>
/// <param name="objectInfo">The object information.</param>
/// <param name="identifier">The identifier of the instance to select.</param>
/// <returns>
/// The created <see cref="SqlQuery" />.
/// </returns>
public SqlQuery BuildSelectSqlQuery(IObjectInfo objectInfo, object identifier)
{
if (objectInfo == null)
{
throw new ArgumentNullException("objectInfo");
}
string selectCommand;
if (!this.selectCommandCache.TryGetValue(objectInfo.ForType, out selectCommand))
{
var selectSqlQuery = new SelectSqlBuilder(this.SqlCharacters)
.From(objectInfo)
.Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(identifier)
.ToSqlQuery();
var newSelectCommandCache = new Dictionary<Type, string>(this.selectCommandCache);
newSelectCommandCache[objectInfo.ForType] = selectSqlQuery.CommandText;
this.selectCommandCache = newSelectCommandCache;
return selectSqlQuery;
}
return new SqlQuery(selectCommand, identifier);
}
public void SelectCountWithSqlCharacters()
{
var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, (string)null);
var sqlQuery = sqlBuilder
.Count("Id")
.From(typeof(Customer))
.Where("CustomerStatusId").IsEqualTo(CustomerStatus.Active)
.ToSqlQuery();
Assert.Equal("SELECT COUNT([Id]) AS Id FROM [Sales].[Customers] WHERE ([CustomerStatusId] = @p0)", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
Assert.Equal(CustomerStatus.Active, sqlQuery.Arguments[0].Value);
}