Executing stored procedure that returns DTO using NHibernate

Lately, I was creating a BaseRepository (repository pattern) using NHibernate while creating the infrastructure that could be used by the developers who work on the DAL.

There are several stored procedures which returned some custom result sets (classes which didn't have any mappings in .hbm). The idea was to provide a generic method to the developers which can be used without implementing underlying logic each time to call these stored procedures.

So, what I did is create method that takes a sql statement or a stored procedure's name, the list of the parameters and execute the sql statement or stored procedure and return the custom result set.

With these requirements I came up with a generic method something like below:

public IList < TReturnType > ExecuteAdhocQueryAndReturn< TReturnType >(string sql, params KeyValuePair[] parms)
  where TReturnType : class
{
  using (ISession session = sessionFactory.OpenSession())
  {
using (ITransaction transaction = session.BeginTransaction())
{
 try
 {
var sqlQuery = session.CreateSQLQuery(sql);
parms.ToList().ForEach(parm => sqlQuery.SetParameter(parm.Key, parm.Value));
var transformedQuery = sqlQuery.SetResultTransformer(Transformers.AliasToBean());
var result = transformedQuery.List();
transaction.Commit();
return result;
 }
 catch
 {
transaction.Rollback();
throw;
 }
}
  }
}

And we can call this method like following:


var parms = new Dictionary();
      parms["id"] = 2;
      var userRepo = new BaseRepository();
      var users = userRepo.ExecuteAdhocQueryAndReturn("select ID, LoginName, Code from dbo.[user] where Id = :id", parms.ToArray());


Or if we had a stored procedure then we'll be doing this:


var parms = new Dictionary();
      parms["id"] = 2;
      var userRepo = new BaseRepository();
      var users = userRepo.ExecuteAdhocQueryAndReturn("exec dbo.GetUsers :id", parms.ToArray());


GetUsers is the stored procedure which takes id as parameter.

And if there is no parameter, then can we go like this:


 var userRepo = new BaseRepository();
 var users = userRepo.ExecuteAdhocQueryAndReturn("select ID, LoginName, Code from dbo.[user]");


We can use the similar concept for the stored procedures which don't return anything. What we need to do is skip the applying of Transformer and do sqlQuery.ExecuteUpdate instead.