I love and as ORMs. They do exactly as it says on the tin. I love the fact that I can develop an application in a DDD (Domain Driven Development) fashion and almost forget about the fact that I need a relational database.
I also love the fact that I don't have to write lengthy Stored Procedures to get data back....but there is always an application out there where this has to change.

I currently working on a new project that requires a lot of reporting and data warehousing, so it has come to the point where I will require to pull out my SQL Books and remember what a Stored Procedure is!

Ok...writting Stored Procedures is like riding a bike, once you get started again it all comes flooding back. However, I still want to use as my ORM as 90% of database work can be handled by my standard data repository.

I want a nice way to call a a method in my repository, pass the SP name, some parameters and then return a type safe list of objects.

Solution

This is what I ended up with:

var parameters = new Dictionary<string, object> { { "@Author", "Joe Bloggs" } };
IList<Book> bookList = Repository.ExecuteSqlProc<Book>("sp_GetBooks", parameters);

I know this example is really simple; so simple in fact that you would not use a SP for this, but it does show calling a SP and then mapping back to the object. What is really cool is that if you enable Lazy loading you can then get all the nested objects back no problem.

Right this is how it's done:

    1     public virtual IList<T> ExecuteSqlProc<T>(string sqlProc, IDictionary<string, object> parameters)
    2     {
    3         var targetType = typeof(T);
    4 
    5         var holder = ActiveRecordMediator.GetSessionFactoryHolder();
    6         var config = holder.GetConfiguration(holder.GetRootType(targetType));
    7         var session = config.BuildSessionFactory().OpenStatelessSession();
    8 
    9         var parameterNames = GetParameterNames(sqlProc);
   10 
   11         IQuery query;
   12         try
   13         {
   14             query = session.GetNamedQuery(sqlProc);
   15         }
   16         catch (MappingException)
   17         {                
   18             var paramlist = string.Empty;
   19 
   20             foreach (var parameterName in parameterNames)
   21             {
   22                 paramlist += ":" + parameterName +", ";
   23             }
   24 
   25             paramlist = paramlist.TrimEnd(new[] {' ', ','});
   26 
   27             var sqlQueryMapping = @"<sql-query name='{0}'><return class='{1}' />EXEC dbo.{0} {2}</sql-query>";
   28 
   29             var sqlQuery = string.Format(sqlQueryMapping, sqlProc, targetType.Name, paramlist);
   30 
   31             var mapping_hbm_xml =
   32                 string.Format(@"<hibernate-mapping xmlns='{0}' assembly='{1}' namespace='{2}'>{3}</hibernate-mapping>",
   33                               Configuration.MappingSchemaXMLNS, targetType.Assembly.FullName, targetType.Namespace, sqlQuery);
   34 
   35             config.AddXmlString(mapping_hbm_xml);
   36 
   37             session = config.BuildSessionFactory().OpenStatelessSession();
   38             query = session.GetNamedQuery(sqlProc);
   39         }
   40 
   41         foreach (var parameterName in parameterNames)
   42         {
   43             if(parameters.ContainsKey(parameterName))
   44             {
   45                 query.SetParameter(parameterName, parameters[parameterName]);
   46             }
   47             else
   48             {
   49                 query.SetParameter(parameterName, "null" );
   50             }
   51         }
   52 
   53         return query.List<T>();
   54     }
   55 
   56     private static readonly Dictionary<string,string[]> parameterlist = new Dictionary<string, string[]>();
   57     private static string[] GetParameterNames(string sqlProc)
   58     {
   59         if (!parameterlist.ContainsKey(sqlProc))
   60         {
   61             var session = new ARContext().Session;
   62             IList<string> returnObj = new List<string>();
   63             IDbConnection conn;
   64             IDbCommand cmd = null;
   65 
   66             try
   67             {
   68                 conn = session.Connection;
   69                 cmd = conn.CreateCommand();
   70                 cmd.CommandText =
   71                     "SELECT p.name FROM sys.objects AS o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE o.object_id = OBJECT_ID('" +
   72                     sqlProc + "')";
   73                 cmd.CommandType = CommandType.Text;
   74 
   75                 using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
   76                 {
   77                     while (reader.Read())
   78                     {
   79                         returnObj.Add(reader.GetString(0));
   80                     }
   81                 }
   82             }
   83             catch (Exception ex)
   84             {
   85                 throw new ActiveRecordException("Could not perform GetParameters for " + sqlProc, ex);
   86             }
   87             finally
   88             {
   89                 if (cmd != null) cmd.Dispose();
   90             }
   91 
   92             parameterlist.Add(sqlProc, returnObj.ToArray());
   93         }
   94 
   95         return parameterlist[sqlProc];
   96 
   97     }
   98 
   99 }

Does any of this make sense or do I need to explain!?

Hope this helps.