Hi i am using the transaction-per-request (session-in-view) pattern for an asp.net web application. I have a couple of points in the application where i want to Save an NHibernate managed entity and then do a couple of more inserts and updates using common sql. These inserts/updates depend on the ID that the NH saved entity will take.
The problem is that the generated id does not exist in the transactions' scope. If i force a flush/commit the id is persisted but if the inserts/updates fail i have to rollback but the flushed/committed entity will not. Currently I'm doing a manual insert for these cases but that is something i want to change. So, is there a way to execute the SQL statement (inside the already open transaction) after the Save() but without forcing a flush/commit?
EDIT: I'm adding a semi-pseudocode example, i got 4 wrong answers so i think people don't understand (how NHibernate works)
At the Begin request i issue a
nhsession.BeginTransaction()
then at some point i do
FooClass fc = new FooClass("value");
nhsession.Save(fc);
ITransaction trans = nhsession.Transaction;
SqlCommand sc = new SqlCommand("some insert/update query that depends on fc's id", (SqlConnection)nhsession.Connection);
sc.Parameters.Add("id", fc.Id); //NHibernate generates the id, note i'm using assigned/hi-lo so no round trip to the db takes place
transaction.Enlist(sc);
try {
sc.ExecuteNonQuery();
}
catch (SqlException ex){
transaction.RollBack();
nhsession.Close();
}
and at the end of the Request i issue a CommitTransaction() and nhsession.Close()
Now this will do absolutely nothing: the FooClass (fc) has not been flushed/commited to the database. The Save() operation that NH has done is up to that point in-memory. That means no sql command has been issued by nhibernate and that means that the SqlCommand (sc) that i fire afterwards will fail miserably as the id does not exist.
If i do a flush/commit between Save() and the SqlCommand the FooClass(fc) _cannot_be_rolled_back_ and that is a bad bad thing.
Currently, for this to work i make vanila sql insert using an SqlCommand, and i want to change that. (Why? because i don't want to make vanilla inserts they are susceptible to errors due to schema/model changes, and i got the OR/M for that)
How? i want to notify NHibernate somehow to execute the SqlCommand to corresponds to the Save() insert (hell, it can do all the SqlCommands it has gathered) but without it commiting or flushing!.
Currently i'm also searching for the prepared sql statement that nhibernate produces when flushing/commiting a saved object. Maybe i can just take that string and run it in my SqlCommand that is enlisted in the Transaction.
maybe I don't understand but can't you do it like so...
using(var tx = session.BeginTransaction())
{
session.Save(entity);
session.Flush();
//perform non NH operations with entity.id
tx.Commit();
}//uncommitted transaction would be rolled back on dispose()
Can you not use nested transactions or transaction scopes to support this?
Essentially what you are asking is for nh to assign the id before you use it, generated ids are only assigned when changes are flushed, changes are flushed only at transaction boundaries therefore can you not create an inner transaction scope to wrap the nh related persistence forcing the flush and id generation but still allowing the outer transaction to fail and roll back all work if one of the stored procedures fail.
How do I do nested transactions in NHibernate? has some sample code that demos an approach using scopes.
Sounds like you want ´dirty read´ isolation level during your transaction. This will allow you to read ´uncommited´ but ´saved´ content. I´m however not fluent enough in NHibernate to give any insight on how to do it there.
I´m sure someone else can hook in on how to do ´dirty read´ isolation level for NH.
I'm using Castle ActiveRecord on top of NHibernate but the princple should be the same, my DTC is disabled so I'm not positive this works, but this should be the correct direction.
Imports Castle.ActiveRecord
imports NHibernate.Expression
<ActiveRecord> _
Public Class Test1
Inherits ActiveRecordBase(Of Test1)
Private _id As Guid
<PrimaryKeyAttribute(PrimaryKeyType.Guid)> _
Public overridable Property Id as Guid
Get
return _id
End Get
Set(value As guid)
_id = value
End Set
End Property
Private _prop1 As String
<[Property]> _
Public overridable Property prop1 as String
Get
return _prop1
End Get
Set(value As String)
_prop1 = value
End Set
End Property
End Class
<ActiveRecord> _
Public Class Test2
Inherits ActiveRecordBase(Of Test2)
Private _id As Guid
<PrimaryKey(PrimaryKeyType.Guid)> _
Public overridable Property Id as Guid
Get
return _id
End Get
Set(value As guid)
_id = value
End Set
End Property
Private _prop1 As String
<[Property]> _
Public overridable Property prop1 as String
Get
return _prop1
End Get
Set(value As String)
_prop1 = value
End Set
End Property
Private _t1 As Test1
<BelongsTo()> _
Public overridable Property t1 as Test1
Get
return _t1
End Get
Set(value As test1)
_t1 = value
End Set
End Property
End Class
Imports Castle.ActiveRecord
Imports NHibernate.Expression
Imports System.Data.SqlClient
Imports System.Transactions
imports System.Configuration
Public Module Module1
Public Class modMain
Public Shared Sub Main()
Castle.ActiveRecord.ActiveRecordStarter.Initialize()
Using T As New System.Transactions.TransactionScope(ondispose.Rollback)
Dim x As New Test1()
x.prop1 = "Hello"
x.Save
using c As New SqlConnection()
c.ConnectionString = ConfigurationManager.ConnectionStrings("Development").ConnectionString
Dim cmd As SqlCommand = c.CreateCommand()
cmd.CommandText = "insert into test2(prop1, t1) values(@prop1, @t1)"
Dim p As SqlParameter = cmd.CreateParameter()
p.Direction = parameterdirection.Input
p.DbType = dbtype.Guid
p.ParameterName = "@prop1"
p.Value = "Test"
cmd.Parameters.Add(p)
p = cmd.CreateParameter()
p.Direction = parameterdirection.Input
p.DbType = dbtype.Guid
p.ParameterName = "@t1"
p.Value = x.Id
c.Open
cmd.ExecuteNonQuery
end using
t.Complete
end using
End Sub
End Class
End Module
Have you tried using NHibernate's direct SQL features? Instead of breaking out into SQLCommand which NHibernate knows nothing about you can use ISession.CreateSQLQuery("native SQL here"). If you then do this inside a transaction after your Save method NHibernate should execute all the statements in order when the Commit happens. Thus:
using(var tx = session.BeginTransaction())
{
try
{
FooClass fc = new FooClass("value");
nhsession.Save(fc);
var nativeQuery = nhsession.CreateSQLQuery("some insert/update query that depends on fc's id");
// Add parameters to nativeQuery using SetXXX methods
nativeQuery.SetXXX(...);
// Save nativeQuery
nativeQuery.Save(); // I think...
tx.Commit();
}
catch (...)
{
tx.Rollback();
}
}
See this for another similar question regarding using the NH native query: Hibernate NHibernate - Native SQL
