public void TableThrowsArgumentExceptionForEmptyTableName()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.Table(""));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("tableName"), exception.Message);
}
public void OrWhereThrowsArgumentExceptionForEmptyColumn()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws<ArgumentException>(
() => sqlBuilder.OrWhere(""));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"), exception.Message);
}
public void UpdateSpecifyingTypeWithSqlCharacters()
{
var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Table(typeof(Customer))
.ToSqlQuery();
Assert.Equal("UPDATE [Sales].[Customers] SET ", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void UpdateSpecifyingType()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table(typeof(Customer))
.ToSqlQuery();
Assert.Equal("UPDATE Sales.Customers SET ", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void UpdateSpecifyingTableName()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.ToSqlQuery();
Assert.Equal("UPDATE Table SET ", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void UpdateSpecifyingTableNameWithSqlCharacters()
{
var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Table("Table")
.ToSqlQuery();
Assert.Equal("UPDATE [Table] SET ", sqlQuery.CommandText);
Assert.Empty(sqlQuery.Arguments);
}
public void UpdateTableSetColumnValue()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.WhereEquals("Id", 100122)
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE Id = ?", sqlQuery.CommandText);
Assert.Equal(3, sqlQuery.Arguments.Count);
Assert.Equal("Foo", sqlQuery.Arguments[0]);
Assert.Equal(12, sqlQuery.Arguments[1]);
Assert.Equal(100122, sqlQuery.Arguments[2]);
}
/// <summary>
/// Builds the command text to update 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 BuildUpdateCommandText(IObjectInfo objectInfo)
{
if (objectInfo == null)
{
throw new ArgumentNullException("objectInfo");
}
var builder = new UpdateSqlBuilder(this.SqlCharacters)
.Table(objectInfo);
for (int i = 0; i < objectInfo.TableInfo.Columns.Count; i++)
{
var columnInfo = objectInfo.TableInfo.Columns[i];
if (columnInfo.AllowUpdate)
{
builder.SetColumnValue(columnInfo.ColumnName, null);
}
}
var updateSqlQuery = builder
.Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(0)
.ToSqlQuery();
return updateSqlQuery.CommandText;
}
/// <summary>
/// Creates an SqlQuery to perform an update based upon the values in the object delta.
/// </summary>
/// <param name="objectDelta">The object delta to create the query for.</param>
/// <returns>
/// The created <see cref="SqlQuery" />.
/// </returns>
public SqlQuery BuildUpdateSqlQuery(ObjectDelta objectDelta)
{
if (objectDelta == null)
{
throw new ArgumentNullException("objectDelta");
}
if (log.IsDebug)
{
log.Debug(LogMessages.SqlDialect_CreatingSqlQuery, "UPDATE");
}
var objectInfo = ObjectInfo.For(objectDelta.ForType);
var builder = new UpdateSqlBuilder(this.SqlCharacters)
.Table(objectInfo);
foreach (var change in objectDelta.Changes)
{
builder.SetColumnValue(change.Key, change.Value);
}
var sqlQuery = builder
.Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(objectDelta.Identifier)
.ToSqlQuery();
return sqlQuery;
}
public void UpdateTableSetColumnValueWithSqlCharacters()
{
var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.WhereEquals("Id", 100122)
.ToSqlQuery();
Assert.Equal("UPDATE [Table] SET [Column1] = @p0,[Column2] = @p1 WHERE [Id] = @p2", sqlQuery.CommandText);
Assert.Equal(3, sqlQuery.Arguments.Count);
Assert.Equal("Foo", sqlQuery.Arguments[0]);
Assert.Equal(12, sqlQuery.Arguments[1]);
Assert.Equal(100122, sqlQuery.Arguments[2]);
}
public void UpdateTableSetColumnValueWhereNotInSqlQuery()
{
var subQuery = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 1024);
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Column3").NotIn(subQuery)
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Column3 NOT IN (SELECT Id FROM Table WHERE Column = ?))", sqlQuery.CommandText);
Assert.Equal(3, 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(12, sqlQuery.Arguments[1].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
Assert.Equal(1024, sqlQuery.Arguments[2].Value);
}
public void UpdateTableSetColumnValueWhereNotInArgs()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Column3").NotIn(1, 2, 3)
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Column3 NOT IN (?,?,?))", sqlQuery.CommandText);
Assert.Equal(5, 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(12, sqlQuery.Arguments[1].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
Assert.Equal(1, sqlQuery.Arguments[2].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
Assert.Equal(2, sqlQuery.Arguments[3].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[4].DbType);
Assert.Equal(3, sqlQuery.Arguments[4].Value);
}
public void UpdateTableSetColumnValueWhereNotBetween()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Id").NotBetween(1, 199)
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Id NOT BETWEEN ? AND ?)", 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(12, sqlQuery.Arguments[1].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
Assert.Equal(1, sqlQuery.Arguments[2].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
Assert.Equal(199, sqlQuery.Arguments[3].Value);
}
public void UpdateTableSetColumnValueWhereIsNull()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Id").IsNull()
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Id IS NULL)", 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(12, sqlQuery.Arguments[1].Value);
}
/// <summary>
/// Builds an SqlQuery to update the database record for the specified instance with the current property values of the instance.
/// </summary>
/// <param name="objectInfo">The object information.</param>
/// <param name="instance">The instance to update.</param>
/// <returns>
/// The created <see cref="SqlQuery" />.
/// </returns>
public SqlQuery BuildUpdateSqlQuery(IObjectInfo objectInfo, object instance)
{
if (objectInfo == null)
{
throw new ArgumentNullException("objectInfo");
}
string updateCommand;
if (!this.updateCommandCache.TryGetValue(objectInfo.ForType, out updateCommand))
{
var builder = new UpdateSqlBuilder(this.SqlCharacters)
.Table(objectInfo);
for (int i = 0; i < objectInfo.TableInfo.Columns.Count; i++)
{
var columnInfo = objectInfo.TableInfo.Columns[i];
if (columnInfo.AllowUpdate)
{
builder.SetColumnValue(columnInfo.ColumnName, null);
}
}
var updateSqlQuery = builder.WhereEquals(objectInfo.TableInfo.IdentifierColumn.ColumnName, objectInfo.GetIdentifierValue(instance))
.ToSqlQuery();
var newUpdateCommandCache = new Dictionary<Type, string>(this.updateCommandCache);
newUpdateCommandCache[objectInfo.ForType] = updateSqlQuery.CommandText;
updateCommand = updateSqlQuery.CommandText;
this.updateCommandCache = newUpdateCommandCache;
}
var updateValues = objectInfo.GetUpdateValues(instance);
return new SqlQuery(updateCommand, updateValues);
}
/// <summary>
/// Creates an SqlQuery to perform an update based upon the values in the object delta.
/// </summary>
/// <param name="objectDelta">The object delta to create the query for.</param>
/// <returns>
/// The created <see cref="SqlQuery" />.
/// </returns>
public SqlQuery BuildUpdateSqlQuery(ObjectDelta objectDelta)
{
if (objectDelta == null)
{
throw new ArgumentNullException("objectDelta");
}
var objectInfo = ObjectInfo.For(objectDelta.ForType);
var builder = new UpdateSqlBuilder(this.SqlCharacters)
.Table(objectInfo);
foreach (var change in objectDelta.Changes)
{
builder.SetColumnValue(change.Key, change.Value);
}
var sqlQuery = builder
.WhereEquals(objectInfo.TableInfo.IdentifierColumn.ColumnName, objectDelta.Identifier)
.ToSqlQuery();
return sqlQuery;
}
public void UpdateTableSetColumnValueWhereIsEqualToWithSqlCharacters()
{
var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Id").IsEqualTo(100122)
.ToSqlQuery();
Assert.Equal("UPDATE [Table] SET [Column1] = @p0,[Column2] = @p1 WHERE ([Id] = @p2)", sqlQuery.CommandText);
Assert.Equal(3, 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(12, sqlQuery.Arguments[1].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
Assert.Equal(100122, sqlQuery.Arguments[2].Value);
}
