Deleting a row with custom sql statement in Nhibernate

Question

I am using NHibernate and have a requirement where in when i am deleting a row i should not hard delete the row instead update the status to delted and set few system properties like who deleted etc.

For doing this i have decided to go with custom sql statement like.

<sql-delete>
  update PPDE set SysStatusID = 2 where PPDID =? 
</sql-delete>

in the sql statement i am able to get reference to just id , but how can i update the SysUserID to the user who delted this row.

Basically how to set up dynamic paramater values in custom sql statements.

Any help is greatly appreciated.

Answer 1

Can you do this using an interceptor

    public void OnDelete(object entity,
                         object id,
                         object[] state,
                         string[] propertyNames,
                         IType[] types)
    {
        // if entity is myentity 
        // update user property
    }

Answer 2

Hibernate will try to automatically replace "?"s with values from your entity. When you call session.Delete(entity) it will use the value of the property that is mapped to the PPDID column.

That being said, I think it is a bad idea to use a custom sql statement in this case. IMHO your code will be difficult to understand and maintain. When a someone sees session.Delete(entity) he would expect a DELETE statement to be performed but instead it will get an UPDATE statement. I would stick to a session.Update(entity) having set the correct values before.

Answer 3

One way to do what you want, is to implement a DeleteEvent-listener, which is triggered when nhibernate decides to delete of entities. From there, based on some logic (typically you look for an interface on the entity), you can decide if you want to hard-delete or soft-delete the entity. And in case you are soft-deleting it, you can update properties such as "IsDeleted", "DeletedAt", "DeletedBy", etc..

A good blog-post describing the above in details can be found at: The NHibernate FAQ - Soft Deletes

Answer 4

But there is not a good aproach to have business rules inside a persistance operation. Your business must be independient from the persistance operations like delete.