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); }