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
- 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.