NHibernate – Bulk Manipulation with SQL Native

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(); }

Or

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 %.

  • Daniel

    Is it possible to use multiple statements in the query?

    Like
    set_identity_insert on
    update users u
    set u.Id = :newId where u.Id = :oldId
    set_identity insert_off

    I get errors if I use ; at the end of each line,
    I get errors if I don’t use.

    Thanks,
    Daniel