Stored procedure is a native way of interacting with most relational databases. Lot of legacy systems I have worked on used stored procedures extensively. On the application side, we would use ADO.NET to prepare SQL commands that execute stored procedures and return result sets. ADO.NET is well capable of executing any valid SQL that you send down the wire. So instead of sending commands to execute stored procedures, you can dynamically build a SQL and execute it. That is what NHibernate does. But most DBAs that I have come across prefer stored procedures. There were two main reasons for this preferred choice. They are as follows:

There may be other reasons why stored procedures are recommended even today, but these are the main ones that keep coming back in discussions that I have with DBAs.

Besides the preceding, a completely different scenario to consider is when you are working on a small application which is part of a bigger legacy application. You would obviously need to work with the legacy database of the old system. It is possible that this legacy database has some stored procedures already defined and you are asked to use those. It is also likely that stored procedures will contain business logic that you do not intend to duplicate in your application. Having business logic in stored procedures is not ideal, but you need to work with what is available. This situation is entirely different from the previous situation I described. In the previous situation, you will be most likely to get 4 stored procedures per database table, one each to insert, update, delete, and read a record from the table.

That is your only interface to database tables. In the second situation, you may be allowed to use a tool such as NHibernate to carry out database interaction, but you need to make use of the existing stored procedures wherever appropriate. NHibernate supports executing any kind of stored procedure and hydrating entities using the result sets that are returned. Let's first look at the most simple way of executing stored procedures.

Executing a stored procedure is same as executing any valid SQL command. Suppose you have got a stored procedure named [dbo].[Get_Employee] that looks like the following code:

CREATE PROCEDURE [dbo].[Get_Employee]
  @id int
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Id, Firstname, Lastname
  FROM Employee
  WHERE Id = @id;
END

This stored procedure takes identifier value as input and returns the Id, Firstname, and Lastname column values from the Employee table for the record that matches the supplied identifier. In order to execute the preceding stored procedure, you would need to write the following SQL command:

EXEC [dbo].[GetEmployee] @id  = 55;

Since the preceding line is a valid SQL query we could just use the ISession.CreateSQLQuery method to build a SQL command, and have it executed. We could even use parameter binding for the @id parameter, so that there is no scope for SQL injection attacks under any circumstances. Following is how that code looks:

var query = Database.Session.CreateSQLQuery(@"EXEC [dbo].[Get_Employee] @id = :id");
query.SetInt32("id", 55);
var result = query.UniqueResult();

Calling the UniqueResult method on IQuery would execute the SQL and return the results in object array. This gets the work done but is not very elegant. You would want to be able to parse the returned data into an instance of Employee entity. You can do that by using something called result transformers. Result transformers are quite a useful set of tools if you are using raw SQL or HQL. We did not cover result transformers in great detail because our recommendation is to stay away from SQL and HQL if you can. In this example, we can use a result transformer named

AliasToBeanResultTransformer, which takes the result set coming from the database and transforms it into an entity that you specify. Let's use this transformer to make the preceding code return us a list of Employee instances:

var query = Database.Session.CreateSQLQuery(@"EXEC [dbo].[Get_Employee] @id = :id");
query.SetInt32("id", 55); 
query.SetResultTransformer(Transformers.AliasToBean<Employee>());
var employees = query.List<Employee>();

This is better than the previous version but still lacks the elegance of NHibernate's object oriented way of querying data. What would happen if the returned Employee instance has a collection of Benefit entities on it? Obviously, it would not be populated automatically for us. We would need to write plumbing code to do that.

There are two other ways in which stored procedures can be used. The first one is named queries. Named queries let you declare queries in your mapping files and then refer to them using a specified name. This makes queries reusable. The second way is to use a custom SQL to create, update, and delete. Usually NHibernate dynamically generates a SQL for inserting, updating, and deleting entities. But if, for any reason, you want to be able to use your own SQL, then you are allowed to do that. We will look into both of these in the next chapter. Both of these features support stored procedures in place of custom SQL.

Before we move on to the next topic, a word of caution—NHibernate's support for stored procedure is not extensive. Use it if it's your only option. If you are using it because DBAs in your team have a rule that any access to data must go via stored procedures, then try to bring them to the ORM camp. If not, you might find that using ADO.NET directly may be better, even though you would end up writing a lot more code.