update TABLE set x='test' where y>1 and y<100 --> P

cunfried · **Joined:** Fri Sep 28, 2007 8:40 am **Posts:** 4

I want to update some columns in all rows with the matching criteria.

Is this possible with NHibernate 1.2? I searched the documention but couldn't find anything...

Please don't tell me the only way to do this, is query for all the items and then update them one by one.

karlchu · **Posted:** Fri Sep 28, 2007 9:16 am

Batch updating is not NHibernate's strength. You might be better off issuing a raw SQL statement using ADO.NET directly. The next question that seems to come up a lot is "how do I tie the IDbCommand into the NH-managed transaction?" The answer to that is ITransaction.Enlist().

cunfried · **Joined:** Fri Sep 28, 2007 8:40 am **Posts:** 4

Thanks for the information.

I haven't done anything directly with ADO.NET (only via NHibernate) and here is what i tried:

Code:

ITransaction trans = _session.BeginTransaction();

System.Data.IDbCommand cmd = _session.Connection.CreateCommand();
cmd.CommandText = "update Patient set FirstName='Maximilian' where LastName='Mustermann'";
                
trans.Enlist(cmd);
trans.Commit();
_session.Flush();

Unfortunatly this doesn't work. I activated SQL Output and don't see the statement being executed and the database hasn't changed too.

Any hints what I'm doing wrong.

karlchu · **Posted:** Fri Sep 28, 2007 11:29 am

Anything you do with ADO.NET does not have anything to do with NHibernate anymore; except it execute in the same transaction created by NHibernate. Also, the CommandText needs to be raw SQL, System.Data.IDbCommand knows nothing about NHibernate and the mapping files.

By the way, no need to Flush() after trans.Commit(); in fact, it may cause problem. So you may want to take that out.

cunfried · **Joined:** Fri Sep 28, 2007 8:40 am **Posts:** 4

karlchu wrote:

Anything you do with ADO.NET does not have anything to do with NHibernate anymore; except it execute in the same transaction created by NHibernate. Also, the CommandText needs to be raw SQL, System.Data.IDbCommand knows nothing about NHibernate and the mapping files.

I'm aware of that. It's raw SQL, but the problem is, that it will not be executed!

karlchu wrote:

By the way, no need to Flush() after trans.Commit(); in fact, it may cause problem. So you may want to take that out.

Removed it --> nothing changed.

cunfried · **Joined:** Fri Sep 28, 2007 8:40 am **Posts:** 4

I solved the problem...

NHibernate of course doesn't execute the command, this must be done manually:

Code:

ITransaction trans = _session.BeginTransaction();

System.Data.IDbCommand cmd = _session.Connection.CreateCommand();
cmd.CommandText = "update Patient set FirstName='Maximilian' where LastName='Mustermann'";
               
trans.Enlist(cmd);

cmd.ExecuteNonQuery();

trans.Commit();