So far, the latest NH (1.2.1.GA) still only generates something like DELETE_SP id if custom delete sql is being specified in hbm.xml file. In our situation, all the delete stored procedure mandatory validate every column before doing the actual deleting, so called dirty check which is often used by update sp.
I looked into the source code of NH, it’s dynamic-update and optimistic-lock settings can implement the same dirty check by generating the DELETE SQL with all updatable properties, but if our DBA to change so many existing delete sp in our database?
That means I have to change NH’s source code and build my own fix version of NH.
My modifications:
In NHibernate.Persister.Entity.AbstractEntityPersister:
public void Delete(object id, object version, object obj, ISessionImplementor session)
……
for (int j = span – 1; j >= 0; j–)
{object[] deleteState = null;
EntityKey key = new EntityKey(id, this);
object entity = session.GetEntity(key);
if (entity != null)
{
EntityEntry entry = session.GetEntry(entity);
deleteState = entry.DeletedState;
}////////////////////////////////////
Delete(id, deleteState, version, j, obj, deleteStrings[j], session, loadedState);
public void Delete(object id, object[] fields, object version, int j, object obj, SqlCommandInfo sql, ISessionImplementor session,
object[] loadedState)
{
…….try
{
int index = 0;//index += expectation.Prepare(statement, factory.ConnectionProvider.Driver);
// Do the key. The key is immutable so we can use the _current_ object state
//IdentifierType.NullSafeSet(statement, id, index, session);
//index += IdentifierColumnSpan;////////////////////////////////////
/// Frank’s fix for delete sp.
///////////////////////////////////
bool[] includeProperty = GetPropertiesToInsert(fields);
index = Dehydrate(id, fields, includeProperty, propertyColumnInsertable, j, statement, session, index);
///////////////////////////////////
………protected SqlCommandInfo GenerateDeleteString(int j)
{
SqlDeleteBuilder deleteBuilder = new SqlDeleteBuilder(Factory);// deleteBuilder
// .SetTableName(GetTableName(j))
// .SetIdentityColumn(GetKeyColumns(j), IdentifierType);
////////////////////////////////////
/// Frank’s Fix for delete SP
/// ////////////////////////////////
deleteBuilder
.SetTableName(GetTableName(j));bool[] includeProperty = PropertyInsertability;
bool hasColumns = false;
for (int i = 0; i < entityMetamodel.PropertySpan; i++)
{
if (includeProperty[i] && IsPropertyOfTable(i, j))
{
deleteBuilder.AddColumns(GetPropertyColumnNames(i), propertyColumnInsertable[i], PropertyTypes[i]);
hasColumns = hasColumns || GetPropertyColumnSpan(i) > 0;
}
}
// dehydrate() always add the id column as the last param.
deleteBuilder.SetIdentityColumn(GetKeyColumns(j), IdentifierType);///////////////////////////////////////////
if (j == 0 && IsVersioned)
{
deleteBuilder.SetVersionColumn(new string[] {VersionColumnName}, VersionType);
}return deleteBuilder.ToSqlCommandInfo();
}
In NHibernate.SqlCommand.SqlDeleteBuilder, add those methods similar to SqlInsertBuilder :
////////////////////////////////////////////
/// Frank’s fix for delete sp.
/// ////////////////////////////////////////private ArrayList columnNames = new ArrayList(); // name of the column
private ArrayList columnValues = new ArrayList(); //string or a Parameter/// <summary>
/// Add a column with a specific value to the DELETE sql
/// </summary>
/// <param name=”columnName”>The name of the Column to add.</param>
/// <param name=”val”>The value to set for the column.</param>
/// <param name=”literalType”>The NHibernateType to use to convert the value to a sql string.</param>
/// <returns>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumn(string columnName, object val, ILiteralType literalType)
{
return AddColumn(columnName, literalType.ObjectToSQLString(val));
}/// <summary>
/// Add a column with a specific value to the DELETE sql
/// </summary>
/// <param name=”columnName”>The name of the Column to add.</param>
/// <param name=”val”>A valid sql string to set as the value of the column.</param>
/// <returns>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumn(string columnName, string val)
{
columnNames.Add(columnName);
columnValues.Add(val);return this;
}/// <summary>
/// Adds columns with a specific value to the DELETE sql
/// </summary>
/// <param name=”columnName”>The names of the Column sto add.</param>
/// <param name=”val”>A valid sql string to set as the value of the column.</param>
/// <returns>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumns(string[] columnName, string val)
{
for (int i = 0; i < columnName.Length; i++)
{
columnNames.Add(columnName[i]);
columnValues.Add(val);
}return this;
}/// <summary>
/// Adds the Property’s columns to the UPDATE sql
/// </summary>
/// <param name=”columnNames”>An array of the column names for the Property</param>
/// <param name=”propertyType”>The IType of the property.</param>
/// <returns>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumns(string[] columnNames, IType propertyType)
{
return AddColumns(columnNames, null, propertyType);
}public SqlDeleteBuilder AddColumns(string[] columnNames, bool[] insertable, IType propertyType)
{
SqlType[] sqlTypes = propertyType.SqlTypes(Mapping);for (int i = 0; i < columnNames.Length; i++)
{if (insertable == null || insertable[i])
{parameterTypes.Add(sqlTypes[i]);
}
}return this;
}
Compiled and re-build, change the reference to this new NH, now my app’s delete stored procedure can take more parameters than just primary key as before.
