/// <summary>
/// Builds an SqlQuery to delete 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 delete.</param>
/// <returns>
/// The created <see cref="SqlQuery" />.
/// </returns>
public SqlQuery BuildDeleteSqlQuery(IObjectInfo objectInfo, object identifier)
{
if (objectInfo == null)
{
throw new ArgumentNullException("objectInfo");
}
string deleteCommand;
if (!this.deleteCommandCache.TryGetValue(objectInfo.ForType, out deleteCommand))
{
var deleteSqlQuery = new DeleteSqlBuilder(this.SqlCharacters)
.From(objectInfo)
.WhereEquals(objectInfo.TableInfo.IdentifierColumn.ColumnName, identifier)
.ToSqlQuery();
var newDeleteCommandCache = new Dictionary<Type, string>(this.deleteCommandCache);
newDeleteCommandCache[objectInfo.ForType] = deleteSqlQuery.CommandText;
this.deleteCommandCache = newDeleteCommandCache;
return deleteSqlQuery;
}
return new SqlQuery(deleteCommand, identifier);
}
public void AndWhereThrowsArgumentExceptionForEmptyColumn()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.AndWhere(""));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"), exception.Message);
}
public void DeleteFromSpecifyingTableNameWithSqlCharacters()
{
var sqlBuilder = new DeleteSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.From("Table")
.ToSqlQuery();
Assert.Equal("DELETE FROM [Table]", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void DeleteFromSpecifyingTypeWithSqlCharacters()
{
var sqlBuilder = new DeleteSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.From(typeof(Customer))
.ToSqlQuery();
Assert.Equal("DELETE FROM [Sales].[Customers]", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void DeleteFromSpecifyingType()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From(typeof(Customer))
.ToSqlQuery();
Assert.Equal("DELETE FROM Sales.Customers", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void DeleteFromWhereEquals()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.WhereEquals("Column1", "Foo")
.ToSqlQuery();
Assert.Equal("DELETE FROM Table WHERE Column1 = ?", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal("Foo", sqlQuery.Arguments[0]);
}
public void DeleteFromNotInMultipleSqlQueries()
{
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 DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").NotIn(subQuery1, subQuery2)
.ToSqlQuery();
Assert.Equal("DELETE 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 FromThrowsArgumentExceptionForEmptyTableName()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.From(""));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("table"), exception.Message);
}
public void DeleteFromWhereNotInArgs()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").NotIn(1, 2, 3)
.ToSqlQuery();
Assert.Equal("DELETE FROM Table WHERE (Column1 NOT IN (?,?,?))", 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 DeleteFromWhereNotBetween()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").NotBetween(1, 199)
.ToSqlQuery();
Assert.Equal("DELETE 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(199, sqlQuery.Arguments[1].Value);
}
public void DeleteFromWhereIsNull()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").IsNull()
.ToSqlQuery();
Assert.Equal("DELETE FROM Table WHERE (Column1 IS NULL)", sqlQuery.CommandText);
Assert.Equal(0, sqlQuery.Arguments.Count);
}
public void DeleteFromWhereIsNotLike()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").IsNotLike("Foo%")
.ToSqlQuery();
Assert.Equal("DELETE FROM Table WHERE (Column1 NOT LIKE ?)", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
Assert.Equal("Foo%", sqlQuery.Arguments[0].Value);
}
public void DeleteFromWhereIsLessThanOrEqualTo()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").IsLessThanOrEqualTo(10)
.ToSqlQuery();
Assert.Equal("DELETE FROM Table WHERE (Column1 <= ?)", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
Assert.Equal(10, sqlQuery.Arguments[0].Value);
}
public void DeleteFromWhereIsEqualToOrWhereIsEqualTo()
{
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").IsEqualTo("Foo")
.OrWhere("Column2").IsEqualTo("Bar")
.ToSqlQuery();
Assert.Equal("DELETE 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 DeleteFromWhereInSqlQuery()
{
var subQuery = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 1024);
var sqlBuilder = new DeleteSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").In(subQuery)
.ToSqlQuery();
Assert.Equal("DELETE FROM Table WHERE (Column1 IN (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 DeleteFromWhereEqualsWithSqlCharacters()
{
var sqlBuilder = new DeleteSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.From("Table")
.WhereEquals("Column1", "Foo")
.ToSqlQuery();
Assert.Equal("DELETE FROM [Table] WHERE [Column1] = @p0", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal("Foo", sqlQuery.Arguments[0]);
}
public void DeleteFromWhereIsEqualToWithSqlCharacters()
{
var sqlBuilder = new DeleteSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.From("Table")
.Where("Column1").IsEqualTo("Foo")
.ToSqlQuery();
Assert.Equal("DELETE FROM [Table] WHERE ([Column1] = @p0)", sqlQuery.CommandText);
Assert.Equal(1, sqlQuery.Arguments.Count);
Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
Assert.Equal("Foo", sqlQuery.Arguments[0].Value);
}
/// <summary>
/// Builds the command text to delete a database record for the specified <see cref="IObjectInfo"/>.
/// </summary>
/// <param name="objectInfo">The object information.</param>
/// <returns>
/// The created command text.
/// </returns>
protected virtual string BuildDeleteCommandText(IObjectInfo objectInfo)
{
if (objectInfo == null)
{
throw new ArgumentNullException("objectInfo");
}
var deleteSqlQuery = new DeleteSqlBuilder(this.SqlCharacters)
.From(objectInfo)
.Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(0)
.ToSqlQuery();
return deleteSqlQuery.CommandText;
}
