public void NullValueSingleWhere()
{
// Creating node hierarchy.
var node = new Node();
node.Add(new Node("table", "foo"));
var values = new Node("values");
values.Add(new Node("field2"));
node.Add(values);
var where = new Node("where");
var and = new Node("and");
and.Add(new Node("field2", "value2"));
where.Add(and);
node.Add(where);
var builder = new SqlUpdateBuilder(node, "'");
// Extracting SQL + params, and asserting correctness.
var result = builder.Build();
var sql = result.Get <string>();
Assert.Equal("update 'foo' set 'field2' = null where 'field2' = @0", sql);
var arg1 = result.Children.First();
Assert.Equal("@0", arg1.Name);
Assert.Equal("value2", arg1.Get <string>());
}
/// <summary>
/// Generate the SQL UPDATE that updates all the foreign keys to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteString()
{
var update = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.AddColumns(JoinColumnNames, "null");
if (CollectionType.UseLHSPrimaryKey)
{
update.SetIdentityColumn(KeyColumnNames, KeyType);
}
else
{
var ownerPersister = (IOuterJoinLoadable)OwnerEntityPersister;
update.SetJoin(ownerPersister.TableName, KeyColumnNames, KeyType, JoinColumnNames, ownerPersister.GetPropertyColumnNames(CollectionType.LHSPropertyName));
}
if (HasIndex)
{
update.AddColumns(IndexColumnNames, "null");
}
if (HasWhere)
{
update.AddWhereFragment(sqlWhereString);
}
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("delete one-to-many " + Role);
}
return(update.ToSqlCommandInfo());
}
/// <summary>
/// Generate the SQL UPDATE that updates a row
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateUpdateRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.AddColumns(ElementColumnNames, elementColumnIsSettable, ElementType);
if (hasIdentifier)
{
update.AddWhereFragment(new string[] { IdentifierColumnName }, IdentifierType, " = ");
}
else if (HasIndex && !indexContainsFormula)
{
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(IndexColumnNames, IndexType, " = ");
}
else
{
string[] cnames = ArrayHelper.Join(KeyColumnNames, ElementColumnNames, elementColumnIsInPrimaryKey);
SqlType[] ctypes = ArrayHelper.Join(KeyType.SqlTypes(Factory), ElementType.SqlTypes(Factory), elementColumnIsInPrimaryKey);
update.AddWhereFragment(cnames, ctypes, " = ");
}
if (Factory.Settings.IsCommentsEnabled)
update.SetComment("update collection row " + Role);
return update.ToSqlCommandInfo();
}
protected virtual SqlUpdateBuilder GenerateUpdate(bool[] includeProperty)
{
SqlUpdateBuilder builder = new SqlUpdateBuilder(factory);
builder.SetTableName(TableName);
for (int i = 0; i < HydrateSpan; i++)
{
if (includeProperty[i])
{
builder.AddColumns(propertyColumnNames[i], PropertyTypes[i]);
}
}
builder.SetIdentityColumn(IdentifierColumnNames, IdentifierType);
if (IsVersioned)
{
if (OptimisticLockMode == OptimisticLockMode.Version)
{
builder.SetVersionColumn(new string[] { VersionColumnName }, VersionType);
}
}
return(builder);
}
protected SqlString GenerateUpdateString(bool[] includeProperty, object[] oldFields)
{
SqlUpdateBuilder builder = GenerateUpdate(includeProperty);
if (OptimisticLockMode > OptimisticLockMode.Version && oldFields != null)
{
bool[] includeInWhere = OptimisticLockMode == OptimisticLockMode.All ?
PropertyUpdateability :
includeProperty;
for (int i = 0; i < HydrateSpan; i++)
{
if (includeInWhere[i])
{
if (oldFields[i] == null)
{
foreach (string column in propertyColumnNames[i])
{
builder.AddWhereFragment(column + " is null");
}
}
else
{
builder.AddWhereFragment(propertyColumnNames[i], PropertyTypes[i], "=");
}
}
}
}
return(builder.ToSqlString());
}
/// <summary>
/// Generate the SQL UPDATE that updates a particular row's foreign
/// key to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null");
if (HasIndex && !indexContainsFormula)
{
update.AddColumns(IndexColumnNames, "null");
}
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("delete one-to-many row " + Role);
}
//use a combination of foreign key columns and pk columns, since
//the ordering of removal and addition is not guaranteed when
//a child moves from one parent to another
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(ElementColumnNames, ElementType, " = ");
return(update.ToSqlCommandInfo());
}
public MultiTableUpdateExecutor(IStatement statement) : base(statement, log)
{
if (!Factory.Dialect.SupportsTemporaryTables)
{
throw new HibernateException("cannot perform multi-table updates using dialect not supporting temp tables");
}
var updateStatement = (UpdateStatement)statement;
FromElement fromElement = updateStatement.FromClause.GetFromElement();
string bulkTargetAlias = fromElement.TableAlias;
persister = fromElement.Queryable;
idInsertSelect = GenerateIdInsertSelect(persister, bulkTargetAlias, updateStatement.WhereClause);
log.Debug("Generated ID-INSERT-SELECT SQL (multi-table update) : " + idInsertSelect);
string[] tableNames = persister.ConstraintOrderedTableNameClosure;
string[][] columnNames = persister.ConstraintOrderedTableKeyColumnClosure;
string idSubselect = GenerateIdSubselect(persister);
IList <AssignmentSpecification> assignmentSpecifications = Walker.AssignmentSpecifications;
updates = new SqlString[tableNames.Length];
hqlParameters = new IParameterSpecification[tableNames.Length][];
for (int tableIndex = 0; tableIndex < tableNames.Length; tableIndex++)
{
bool affected = false;
var parameterList = new List <IParameterSpecification>();
SqlUpdateBuilder update =
new SqlUpdateBuilder(Factory.Dialect, Factory).SetTableName(tableNames[tableIndex])
.SetWhere(
string.Format("({0}) IN ({1})", StringHelper.Join(", ", columnNames[tableIndex]), idSubselect));
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("bulk update");
}
foreach (var specification in assignmentSpecifications)
{
if (specification.AffectsTable(tableNames[tableIndex]))
{
affected = true;
update.AppendAssignmentFragment(specification.SqlAssignmentFragment);
if (specification.Parameters != null)
{
for (int paramIndex = 0; paramIndex < specification.Parameters.Length; paramIndex++)
{
parameterList.Add(specification.Parameters[paramIndex]);
}
}
}
}
if (affected)
{
updates[tableIndex] = update.ToSqlString();
hqlParameters[tableIndex] = parameterList.ToArray();
}
}
}
/// <summary>
/// Implementation of your slot.
/// </summary>
/// <param name="signaler">Signaler used to raise the signal.</param>
/// <param name="input">Arguments to your slot.</param>
public void Signal(ISignaler signaler, Node input)
{
var builder = new SqlUpdateBuilder(input, "'");
var result = builder.Build();
input.Value = result.Value;
input.Clear();
input.AddRange(result.Children.ToList());
}
private void ConstructBody()
{
CSBlock usingBlock = new CSBlock("using (SqlCommand sqlCommand = new SqlCommand())");
string sqlString = $"{Quotes}{SqlUpdateBuilder.UpdateByColumn(datatable, datacolumn)}{Quotes}";
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString};");
usingBlock.Statements.Add($"SetSqlCommandParameter(sqlCommand, {ProperVarName.Get(datatable.TableName.ToLower())});");
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand);");
this.Statements.Add(usingBlock.ToString());
}
private void ConstructBody()
{
VBBlock usingBlock = new VBBlock(VBBlockStatement.Using, "Using sqlCommand As New SqlCommand()");
string sqlString = $"{Quotes}{SqlUpdateBuilder.UpdateByPrimaryKey(datatable)}{Quotes}";
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString}");
usingBlock.Statements.Add($"SetSqlCommandParameter(sqlCommand, {ProperVarName.Get(datatable.TableName.ToLower())})");
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand)");
this.Statements.Add(usingBlock.ToString());
}
public void Update()
{
var db = new FakeDbConnection();
var builder = new SqlUpdateBuilder(new SqlFlavorizer(SqlFlavorizer.VendorKind.PostgreSQL), s => s + " (customized)");
builder.AddNonKey("FirstName", "string", "Jane");
builder.AddNonKey("MiddleName", "nstring", null);
builder.AddNonKey("LastName", "string", null);
builder.Execute(db, "Emp", "EmpId", true).Wait();
Assert.AreEqual("update Emp set FirstName=@p0,MiddleName=@p1,LastName=@p2 where EmpId=@pk (customized)", db.TheCommand.CommandText);
}
/// <summary>
/// Generate the SQL UPDATE that updates a foreign key to a value
/// </summary>
/// <returns></returns>
protected override SqlString GenerateInsertRowString( )
{
SqlUpdateBuilder update = new SqlUpdateBuilder(factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, KeyType)
.SetIdentityColumn(ElementColumnNames, ElementType);
if (HasIndex)
{
update.AddColumns(IndexColumnNames, IndexType);
}
//identifier collections not supported for 1-to-many
return(update.ToSqlString());
}
private SqlString GenerateLockString()
{
ISessionFactoryImplementor factory = lockable.Factory;
SqlUpdateBuilder update = new SqlUpdateBuilder(factory.Dialect, factory);
update.SetTableName(lockable.RootTableName);
update.SetIdentityColumn(lockable.RootTableIdentifierColumnNames, lockable.IdentifierType);
update.SetVersionColumn(new string[] { lockable.VersionColumnName }, lockable.VersionType);
update.AddColumns(new string[] { lockable.VersionColumnName }, null, lockable.VersionType);
if (factory.Settings.IsCommentsEnabled)
{
update.SetComment(lockMode + " lock " + lockable.EntityName);
}
return(update.ToSqlString());
}
public void NoValueNode_Throws()
{
// Creating node hierarchy.
var node = new Node();
node.Add(new Node("table", "foo"));
var where = new Node("where");
var and = new Node("and");
and.Add(new Node("field2", "value2"));
where.Add(and);
node.Add(where);
var builder = new SqlUpdateBuilder(node, "'");
// Extracting SQL + params, and asserting correctness.
Assert.Throws <ArgumentException>(() => builder.Build());
}
/// <summary>
/// Generate the SQL UPDATE that updates all the foreign keys to null
/// </summary>
/// <returns></returns>
protected override SqlString GenerateDeleteString( )
{
SqlUpdateBuilder update = new SqlUpdateBuilder(factory)
.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null")
.SetIdentityColumn(KeyColumnNames, KeyType);
if (HasIndex)
{
update.AddColumns(IndexColumnNames, "null");
}
if (HasWhere)
{
update.AddWhereFragment(sqlWhereString);
}
return(update.ToSqlString());
}
/// <summary>
/// Generate the SQL UPDATE that updates a row
/// </summary>
/// <returns></returns>
protected override SqlString GenerateUpdateRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(factory)
.SetTableName(qualifiedTableName)
.AddColumns(ElementColumnNames, ElementType);
if (hasIdentifier)
{
update.AddWhereFragment(rowSelectColumnNames, rowSelectType, " = ");
}
else
{
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(rowSelectColumnNames, rowSelectType, " = ");
}
return(update.ToSqlString());
}
public void UpdateStringSqlTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlUpdateBuilder update = new SqlUpdateBuilder(factoryImpl.Dialect, factoryImpl);
update.SetTableName("test_update_builder");
update.AddColumns(new string[] { "intColumn" }, NHibernateUtil.Int32);
update.AddColumns(new string[] { "longColumn" }, NHibernateUtil.Int64);
update.AddColumn("literalColumn", false, (ILiteralType)NHibernateUtil.Boolean);
update.AddColumn("stringColumn", 5.ToString());
update.SetIdentityColumn(new string[] { "decimalColumn" }, NHibernateUtil.Decimal);
update.SetVersionColumn(new string[] { "versionColumn" }, (IVersionType)NHibernateUtil.Int32);
update.AddWhereFragment("a=b");
SqlCommandInfo sqlCommand = update.ToSqlCommandInfo();
Assert.AreEqual(CommandType.Text, sqlCommand.CommandType);
string falseString = factoryImpl.Dialect.ToBooleanValueString(false);
string expectedSql =
"UPDATE test_update_builder SET intColumn = ?, longColumn = ?, literalColumn = " + falseString + ", stringColumn = 5 WHERE decimalColumn = ? AND versionColumn = ? AND a=b";
Assert.AreEqual(expectedSql, sqlCommand.Text.ToString(), "SQL String");
SqlType[] actualParameterTypes = sqlCommand.ParameterTypes;
Assert.AreEqual(4, actualParameterTypes.Length, "Four parameters");
SqlType[] expectedParameterTypes = new SqlType[]
{
SqlTypeFactory.Int32,
SqlTypeFactory.Int64,
SqlTypeFactory.Decimal,
SqlTypeFactory.Int32
};
Assert.AreEqual(expectedParameterTypes[0], actualParameterTypes[0], "firstParam Type");
Assert.AreEqual(expectedParameterTypes[1], actualParameterTypes[1], "secondParam Type");
Assert.AreEqual(expectedParameterTypes[2], actualParameterTypes[2], "thirdParam Type");
Assert.AreEqual(expectedParameterTypes[3], actualParameterTypes[3], "fourthParam Type");
}
/// <summary>
/// Generate the SQL UPDATE that updates a foreign key to a value
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateInsertRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, KeyType);
if (HasIndex && !indexContainsFormula)
{
update.AddColumns(IndexColumnNames, IndexType);
}
//identifier collections not supported for 1-to-many
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("create one-to-many row " + Role);
}
update.SetIdentityColumn(ElementColumnNames, ElementType);
return(update.ToSqlCommandInfo());
}
static void Main(string[] args)
{
//var htmlBuilder = new HtmlBuilder("ul");
//htmlBuilder.AddChild("li", "Apple");
//htmlBuilder.AddChild("li", "Banana");
//Console.WriteLine(htmlBuilder.ToString());
//Console.ReadKey();
var sqlUpdateBuilder = new SqlUpdateBuilder("dbo.Persons");
sqlUpdateBuilder
.AddValueToBeSet("name", "Ann")
.AddValueToBeSet("sureName", "Ann")
.AddValueToBeSet("age", "29");
sqlUpdateBuilder
.AddCondition("country", "Poland")
.AddCondition("email", "*****@*****.**");
Console.WriteLine(sqlUpdateBuilder.ToString());
Console.ReadKey();
}
public void Signal(Node input)
{
var builder = new SqlUpdateBuilder(input, _signaler);
var sqlNode = builder.Build();
// Checking if this is a "build only" invocation.
if (builder.IsGenerateOnly)
{
input.Value = sqlNode.Value;
input.Clear();
input.AddRange(sqlNode.Children.ToList());
return;
}
// Executing SQL, now parametrized.
Executor.Execute(sqlNode, _connections, _signaler, (cmd) =>
{
input.Value = cmd.ExecuteNonQuery();
input.Clear();
});
}
public void MultipleValuesSingleWhere()
{
// Creating node hierarchy.
var node = new Node();
node.Add(new Node("table", "foo"));
var values = new Node("values");
values.Add(new Node("field1", "howdy"));
values.Add(new Node("field2", "world"));
node.Add(values);
var where = new Node("where");
var and = new Node("and");
and.Add(new Node("field2", "value2"));
where.Add(and);
node.Add(where);
var builder = new SqlUpdateBuilder(node, "'");
// Extracting SQL + params, and asserting correctness.
var result = builder.Build();
var sql = result.Get <string>();
Assert.Equal("update 'foo' set 'field1' = @v0, 'field2' = @v1 where 'field2' = @0", sql);
var arg1 = result.Children.First();
Assert.Equal("@v0", arg1.Name);
Assert.Equal("howdy", arg1.Get <string>());
var arg2 = result.Children.Skip(1).First();
Assert.Equal("@v1", arg2.Name);
Assert.Equal("world", arg2.Get <string>());
var arg3 = result.Children.Skip(2).First();
Assert.Equal("@0", arg3.Name);
Assert.Equal("value2", arg3.Get <string>());
}
private void ConstructBody()
{
VBBlock usingBlock = new VBBlock(VBBlockStatement.Using, "Using sqlCommand As New SqlCommand()");
string sqlString = $"{Quotes}{SqlUpdateBuilder.UpdateColumnByPrimaryKey(datatable, datacolumn)}{Quotes}";
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString}");
#region Updated Column
string parameterName = $"{Quotes}@{datacolumn.ColumnName.Replace(" ","")}{Quotes}";
string parameterValue = ProperVarName.Get(datacolumn.ColumnName.ToLower().Replace(" ", ""));
if (datacolumn.AllowDBNull)
{
parameterValue = $"If({parameterValue}, CType(DBNull.Value, Object))";
}
usingBlock.Statements.Add($"sqlCommand.Parameters.AddWithValue({parameterName}, {parameterValue})");
#endregion
#region Primary Key Column
string keyParameterName = string.Empty;
string keyParameterValue = string.Empty;
foreach (DataColumn clm in this.datatable.PrimaryKey)
{
keyParameterName = $"{Quotes}@{clm.ColumnName.Replace(" ", "")}{Quotes}";
keyParameterValue = ProperVarName.Get(clm.ColumnName.ToLower());
if (clm.AllowDBNull)
{
keyParameterValue += " ?? (object)DBNull.Value";
}
usingBlock.Statements.Add($"sqlCommand.Parameters.AddWithValue({keyParameterName}, {keyParameterValue})");
}
#endregion
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand)");
this.Statements.Add(usingBlock.ToString());
}
public void BootStrapTheApplication()
{
EventStoreDatabaseBootStrapper.BootStrap();
ReportingDatabaseBootStrapper.BootStrap();
var bus = new FakeBus();
ServiceLocator.Bus = bus;
//var storage = new InMemoryEventStore(bus);
//var storage = new NHibernateSqliteEventStore(bus, NHibernateSessionHelper.CreateSessionFactory(), new BinaryFormatter());
var storage = new SqliteEventStore(bus, new BinaryFormatter(), "Data Source=eventStore.db3");
var rep = new DomainRepository<InventoryItem>(storage);
var commands = new InventoryCommandHandlers(rep);
bus.RegisterHandler<CheckInItemsToInventory>(commands.Handle);
bus.RegisterHandler<CreateInventoryItem>(commands.Handle);
bus.RegisterHandler<DeactivateInventoryItem>(commands.Handle);
bus.RegisterHandler<RemoveItemsFromInventory>(commands.Handle);
bus.RegisterHandler<RenameInventoryItem>(commands.Handle);
var sqlInsertBuilder = new SqlInsertBuilder();
var sqlSelectBuilder = new SqlSelectBuilder();
var sqlUpdateBuilder = new SqlUpdateBuilder();
var sqlDeleteBuilder = new SqlDeleteBuilder();
var reportingRepository = new SQLiteReportingRepository("Data Source=reportingDataBase.db3", sqlSelectBuilder,
sqlInsertBuilder, sqlUpdateBuilder, sqlDeleteBuilder);
var detail = new InventoryItemDetailViewHandler(reportingRepository);
ServiceLocator.ReportingRepository = reportingRepository;
bus.RegisterHandler<InventoryItemCreated>(detail.Handle);
bus.RegisterHandler<InventoryItemDeactivated>(detail.Handle);
bus.RegisterHandler<InventoryItemRenamed>(detail.Handle);
bus.RegisterHandler<ItemsCheckedInToInventory>(detail.Handle);
bus.RegisterHandler<ItemsRemovedFromInventory>(detail.Handle);
var list = new InventoryItemListViewHandler(reportingRepository);
bus.RegisterHandler<InventoryItemCreated>(list.Handle);
bus.RegisterHandler<InventoryItemRenamed>(list.Handle);
bus.RegisterHandler<InventoryItemDeactivated>(list.Handle);
}
/// <summary>
/// Generate the SQL UPDATE that updates all the foreign keys to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null")
.SetIdentityColumn(KeyColumnNames, KeyType);
if (HasIndex)
{
update.AddColumns(IndexColumnNames, "null");
}
if (HasWhere)
{
update.AddWhereFragment(sqlWhereString);
}
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("delete one-to-many " + Role);
}
return(update.ToSqlCommandInfo());
}
public void SetUp()
{
_sqlUpdateBuilder = new SqlUpdateBuilder();
}
public void SetUp()
{
_sqlUpdateBuilder = new SqlUpdateBuilder();
}
public SqlUpdateOperation(SqlDataSet <TEntity> dataSet, IQueryProvider queryProvider) : base(dataSet, queryProvider)
{
_queryBuilder = new SqlUpdateBuilder <TEntity>(DataSet.DataModel);
}
