NHibernate – Bulk Manipulation with SQL Native

Posted by Dario on December 11, 2007 Leave a comment (0) Go to comments

Some days ago Fabio Maulo were made this announce at NHibernate-Hispano Group (as usual) talking about this new feature available in NHibernate: Bulk Manipulation.

For now it’s only works with SQL Native and here is an example:

using (ISession s = sf.OpenSession()){
    using(ITransaction tx = s.BeginTransaction()){
        s.CreateSQLQuery("update Book set Price = Price*1.1")
            .ExecuteUpdate();
        tx.Commit();
    }

Or:

using (ISession s = sf.OpenSession()){
    using(ITransaction tx = s.BeginTransaction()){
        s.CreateSQLQuery("update Book set Price = Price*:increase")
            .SetDecimal("increase",1.1m)
            .ExecuteUpdate();
        tx.Commit();
    }

using (ISession s = sf.OpenSession()){
    using(ITransaction tx = s.BeginTransaction()){
        s.GetNamedQuery("change-book-price")
            .SetDecimal("value",1.1m)
            .ExecuteUpdate();
        tx.Commit();
    }

And the named query for the last query is:

  <sql-query name="change-book-price">
    update Book set Price = Price*:value
  </sql-query>

One of the common scenarios in ORM taken it as a drawback, it is the fact that we need load al objects at memory in order to make an update to all of them, a update in mass. Now there no need to do this, you can use this feature in order to get it.

The example show a simple entity Book and has a Price property (decimal). The example show how to increase the Price in a 10 %.