Hibernate version:
v1.1.4322

I'm trying to get SQL Server application roles working with nhibernate. I can do this with ASP.NET and now LINQ, but we have a fairly large library of nhibernate code that I need to get working with application roles.

The problem is I don't know how to get at the underlying connection on NHibernate so that I can call sp_setapprole to elevate privileges before a query is sent to the server. I need to attach to a connection state change event of some sort to pre-inject execution of that stored procedure.

How would I do this with nhibernate? Am I utterly screwed?

I was able to find a solution to execute code after connection. the link to the forum is at: http://forum.hibernate.org/viewtopic.ph ... highlight=

It was posted by BillHawes:

You can obtain an IDbConnection object from the NHibernate session, and execute any commands using this. The following code assumes that the session object is an open NH session, and runs stored procedure MyStoredProc.

IDbConnection cn = session.Connection;
IDbCommand cmd = cn.CreateCommand();

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SP_SetAppRole @RoleName = 'Users', @Password = 'Password'";
cmd.ExecuteNonQuery();

What you can't currently do is run a stored procedure that returns data and map the data to persistent objects.

You also need to be aware that if your stored procedure amends the data in your tables, this will not be reflected in any persistent objects already loaded.

I hope this is what you're looking for.

Good Luck!

I have the same problem, as you can see in my post Code to be executed after any connection.open(), and I got from eluxa the same answer (s)he gave you.

I hope there is a solution. I'm sure there must be one! If somone gives you the right suggestion, please share it with me!

Pietro

By implementing a ConnectionProvider you can obtain what you need. I described my solution in my original post's thread: http://forum.hibernate.org/viewtopic.php?p=2386466.

If this solves your problem too, please rate this answer to give me the credits!