public void Build_update_statment_with_several_where()
{
string verify = "UPDATE [faketable] SET [col1] = $col1, [col2] = $col2, [col3] = $col3 WHERE [col1] = $qPm0 AND [col2] >= $qPm1 OR [col3] = [col1]";
var columns = new List <string>()
{
"col1", "col2", "col3"
};
var parameters = new List <QueryParam>
{
new QueryParam("col1", null),
new QueryParam("col2", null),
new QueryParam("col3", null)
};
var builder = new UpdateSqlBuilder("faketable", session, columns, parameters);
builder.Where("col1").EqualToValue(1)
.And("col2").GreaterOrEqualToValue(10)
.Or("col3").EqualTo("col1");
var sqlBody = builder.Build();
var res = sqlBody.ToString(session.SessionFactory.DbSettings.SqlDialect);
Console.WriteLine(res);
Assert.AreEqual(verify, res);
}
public void UpdateTableSetColumnValueWhereIsEqualToOrWhereIsEqualToWithSqlCharacters()
{
var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Id").IsEqualTo(100122)
.OrWhere("Column1").IsEqualTo(11)
.ToSqlQuery();
Assert.Equal("UPDATE [Table] SET [Column1] = @p0,[Column2] = @p1 WHERE ([Id] = @p2) OR ([Column1] = @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(12, sqlQuery.Arguments[1].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
Assert.Equal(100122, sqlQuery.Arguments[2].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
Assert.Equal(11, sqlQuery.Arguments[3].Value);
}
/// <summary>
/// Updates the specified entity.
/// </summary>
/// <param name="entity">The entity.</param>
/// <returns></returns>
/// <exception cref="GoliathDataException"></exception>
public int Update(TEntity entity)
{
if (entityMap.PrimaryKey == null)
{
throw new GoliathDataException($"Cannot update entity {entityMap.FullName} because no primary key has been defined for table {entityMap.TableName}");
}
try
{
INonQuerySqlBuilder <TEntity> updateBuilder = new UpdateSqlBuilder <TEntity>(session, entityMap, entity);
var result = ExecuteUpdateOrDeleteEntity(updateBuilder, entity);
//if it's a trackable entity let's reset it
ResetTrackableEntity(entity as ITrackable);
return(result);
}
catch (GoliathDataException)
{
throw;
}
catch (Exception exception)
{
throw new GoliathDataException($"Error while trying to update mapped entity {entityMap.FullName} of type {entity.GetType()}", exception);
}
}
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 UpdateTableSetColumnValueWhereIsEqualToOrWhereIsEqualTo()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Id").IsEqualTo(100122)
.OrWhere("Column1").IsEqualTo(11)
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Id = ?) OR (Column1 = ?)", 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(100122, sqlQuery.Arguments[2].Value);
Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
Assert.Equal(11, sqlQuery.Arguments[3].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);
}
/// <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);
}
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);
}
/// <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 WhereThrowsArgumentExceptionForNullColumn()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var exception = Assert.Throws <ArgumentException>(
() => sqlBuilder.Where(null));
Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"), exception.Message);
}
public UpdateSqlBuilder GetUpdateSqlBuilder()
{
if (updateBuilder == null)
{
updateBuilder = CreateUpdateSqlBuilder();
}
return(updateBuilder);
}
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 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 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 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 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);
}
protected override string GetUpdateSql(Dictionary <string, Field> values)
{
UpdateSqlBuilder updateBuilder = Program.Container.GetUpdateSqlBuilder();
return(updateBuilder
.Update("Автомобиль", $"\"Номер_Госрегистрации\" = '{values["register_number"]}'")
.Set("Марка", values["mark"].Value)
.Set("Номер_Госрегистрации", values["new_register_number"].Value)
.Set("Год_Выпуска", values["year"].Value)
.Set("Изготовитель", values["creator"].Value)
.GetResult());
;
}
public void Can_Generate_UpdateSql_ForCustomer()
{
const string expectedSql = @"
UPDATE [dbo].[Customer]
SET [dbo].[Customer].[CustomerTypeId] = @customertypeid,
[dbo].[Customer].[Code] = @code,
[dbo].[Customer].[Phone] = @phone,
[dbo].[Customer].[Email] = @email,
[dbo].[Customer].[FirstName] = @firstname,
[dbo].[Customer].[LastName] = @lastname,
[dbo].[Customer].[OtherName] = @othername,
[dbo].[Customer].[MobilePhone] = @mobilephone,
[dbo].[Customer].[Dob] = @dob,
[dbo].[Customer].[UtcUpdated] = @utcupdated
WHERE (([dbo].[Customer].[OrganizationId] = @orgid) AND ([dbo].[Customer].[Id] = @id))";
var customer = new Customer
{
Id = 123,
OrganizationId = 1000,
Code = "testcode",
CustomerTypeId = null,
Dob = null,
Email = "*****@*****.**",
FirstName = "miao",
LastName = "huang",
MobilePhone = "123",
OtherName = "cang",
Phone = "321",
Uid = Guid.NewGuid(),
UtcCreated = DateTime.UtcNow,
UtcUpdated = DateTime.UtcNow,
//Version = "version1"
};
var obj = new UpdateSqlBuilder <Customer>();
obj.Analyze();
obj.AddValueParameters(customer);
var orgId = 1000;
obj.AddConditions <Customer>(i => i.OrganizationId == orgId && i.Id == customer.Id);
var sql = obj.GetSql();
Assert.AreEqual(TestUtility.NeutralizeString(sql), TestUtility.NeutralizeString(expectedSql));
}
public void Build_statement_with_no_where_should_throw()
{
var columns = new List <string>()
{
"col1", "col2", "col3"
};
var parameters = new List <QueryParam>
{
new QueryParam("col1", null),
new QueryParam("col2", null),
new QueryParam("col3", null)
};
var builder = new UpdateSqlBuilder("faketable", session, columns, parameters);
Assert.Throws <DataAccessException>(() => builder.Build());
}
public void Load_should_load_all_properties_even_parent_class()
{
Monkey monkey = new Monkey()
{
Id = 1,
Name = "shaker",
Age = 2,
Family = "unknown",
CanDoTricks = true,
Location = "XER3",
ReceivedOn = DateTime.Now
};
UpdateSqlBuilder <Monkey> builder = new UpdateSqlBuilder <Monkey>(session, monkey);
var build = builder.Build();
Assert.AreEqual(2, build.Statements.Count);
}
public void UpdateTableSetColumnValueWhereIsNotNull()
{
var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);
var sqlQuery = sqlBuilder
.Table("Table")
.SetColumnValue("Column1", "Foo")
.SetColumnValue("Column2", 12)
.Where("Id").IsNotNull()
.ToSqlQuery();
Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Id IS NOT 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);
}
UpdateSqlBuilder <T> UpdateInternal <T>(EntityMap entityMap, T entity)
{
var builder = new UpdateSqlBuilder <T>(this, entityMap, entity);
return(builder);
}
