Calling a Stored Procedure with OUT parameters using NHibernate

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

public void ShouldEncryptTheValue() {    //Getting the instance of session factory using,    //the NHibernateSessionHelper custom class.     //This step could be different in your case.    //May be the session factory gets injected in some way.    ISessionFactory sessionFactory = NHibernateSessionHelper.GetSessionFactory();           //Step - 1 - Opening a NHibernate session using the session factory.     var session = sessionFactory.OpenSession();           //Step - 2 - Starting the NHibernate transaction.     session.BeginTransaction();           //Step - 3 - Creating an instance of SqlCommand     IDbCommand command = new SqlCommand();           //Step - 4 - Setting the connection property of the command instance     //with NHibernate session's Connection property.     command.Connection = session.Connection;           //Step - 5 - Setting the CommandType property     //as CommandType.StoredProcedure     command.CommandType = CommandType.StoredProcedure;           //Step - 6 - Setting the CommandText to the name     //of the stored procedure to invoke.     command.CommandText = "encrypt";       //Step - 7 - Set input parameter,     //in our case its "@stringToEncrypt" with the value to encrypt,     //in our case "Hello World"     command.Parameters.Add(new SqlParameter("@stringToEncrypt", "Hello World"));       //Step - 8 - Set output parameter, in our case "encryptedValue".      //Notice that we are specifying the parameter type as second argument     //and its size as the third argument to the constructor.      //The Direction property is initialized in the initialization block     //with ParameterDirection.Output.     SqlParameter outputParameter =         new SqlParameter("@encryptedValue", SqlDbType.VarChar, 255)                        {                            Direction = ParameterDirection.Output                        };     command.Parameters.Add(outputParameter);       //Step - 9 - Enlisting the command with the NHibernate transaction.     session.Transaction.Enlist(command);           //Step - 10 - Executing the stored procedure.     command.ExecuteNonQuery();       //Step - 11 - Getting the value set by the stored procedure     //in the output parameter.     var encryptedValue =         ((SqlParameter)command.Parameters["@encryptedValue"]).Value.ToString();     Console.WriteLine(encryptedValue);           //Step - 12 - Cleanup.  Committing the NHibernate Transaction     //and closing the NHibernate session.     session.Transaction.Commit();     session.Close(); }