NHibernate – Bulk Manipulation with SQL Native

By On December 11, 2007 · 1 Comment

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

 
If you enjoyed this article, please consider sharing it!
Reddit Facebook Twitter Delicious
  • 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 = :o ldId
    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