Tuesday, September 20, 2011

Calling a Stored Procedure with OUT parameters using NHibernate

Hibernate/NHibernate is one product that I appreciate a lot.  I absolutely love the flexibility that NHibernate offers.

Lots of ORM products out there work well when we are developing application from scratch, however they fail miserable when we have to work with existing legacy database.  Hibernate/NHibernate on other end, works very well when application are build from scratch as well as when we have to work with legacy database.  Hibernate/NHibernate provides lots of hooks because of which, using it with any legacy database is completely painfree.

In this post, we are going to see how we could use NHibernate to get around one such legacy issue.  One of the common technique employed in legacy databases to exchange information between the DB and Application layer is, having stored procedures with OUT parameters.  OUT parameters are special type of parameters in which the stored procedure returns values which can then be accessed by the Application layers.

We will not go into details whether OUT parameters are good or bad but, we will see how exactly we could use NHibernate to invoke stored procedures with OUT parameters.

Calling stored procedures without any OUT parameters using NHibernate is fairly straight forward, but calling stored procedures having OUT parameters using NHibernate is a bit tricky.  Without wasting any more time lets straight away dive into the solution.
How do they do it?

To call stored procedures with OUT parameters we have to fallback to the System.Data.SqlClient.SqlCommand (assuming that the database is MSSQL) to get the job done. 

Let's say that,
  • We have a stored procedure called encrypt that is supposed to encrypt any string
  • It takes INPUT parameter called stringToEncrypt
  • It returns the encrypted string as an OUTPUT parameter called encryptedValue 
We want to invoke this stored procedure using NHibernate.  The steps to do this would be as follows
  • Get an instance of Session using the NHibernateSessionFactory (or get it some how injected into the DAO class using Dependency Injection)
  • Start the transaction on the NHibernate session using BeginTransaction method
  • Create a new instance of System.Data.SqlClient.SqlCommand
  • Set the Connection property of the SqlCommand instance using the NHibernate Session's Connection property.
  • Set the SqlCommand instances CommandType property as CommandType.StoredProcedure
  • Set the SqlCommand instances CommandText property as the stored procedure name, in our case encrypt
  • Add the INPUT parameter to the SqlCommand instance.  In our case, adding the parameter with name stringToEncrypt and setting the value of the parameter to the string we want to encrypt
  • Add the OUTPUT parameter to the SqlCommand instance with Direction property set to ParameterDirection.Output.  In our case, add the parameter encryptedValue.  Since, this is an output parameter we will have to specify its SqlDbType as SqlDbType.NVarChar and size as 255.  But the most important thing here is, to set the Direction property of SqlParameter to ParameterDirection.Output.  This tells the SqlCommand that this parameter is an output parameter.  After executing the stored procedure we will be able to get the value out of this parameter. 
  • Enlist the SqlCommand instance with the NHibernate transaction.  This is another crucial step.  If this is not done SqlCommand will throw an error saying "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized" 
  • Execute the command using ExecuteNonQuery method on the command instance.
  • Get the output parameter value from the SqlCommand instance
  • Commit the NHibernate transaction and Close the NHibernate Session.
As you can see there are quite some steps involved while invoking a stored procedure with OUT parameters.  But looking at the code would definately make things easier.
And that is how its done!
Have some Fun!