This is the first in a series on the development of support for stored procedures in a Code First style. See Code First Stored Procedures for the final product.
One of the biggest (and few) drawbacks to CodeFirst is that it doesn’t support calls to stored procedures. Now, I understand that stored procedures are messy beasts. They can return data accept either single values or tables – or both at the same time – as parameters, and can return multiple results that can be either single values or tables. On the other hand, most stored procedure calls are straightforward. Send over some parameters, get back a table of results. Since Code First doesn’t implement this yet, here’s how to do it for yourself.
First order of business is that this needs to be lightweight. I don’t want to have to load the full EntityFramework into my project just to call a few stored procedures. I want something lighter weight than that. Second order of business is that this needs to work for any “simple” stored procedure that returns a dataset, without having to make a copy for each stored procedure I’m calling.
So, we’re going to write a stored procedure call in the old-fashioned style, but throw in generics so we can re-use this. Since we want to be able to call this with any stored procedure that fits the parameters-in, table-out pattern, we’ll want to make the procedure name and its parameter list to be inputs to our routine. So we begin with:
public static List<T> ReadTableFromStoredProc<T>(this DbContext context, String procname, IEnumerable<ParmInfo> parms) where T : class
Note that we’re creating an extension method call, off of the DbContext object. This is for a couple of reasons. First, is that the DbContext contains all of the ‘other’ database access routines, so this really belongs here. Second, the DbContext contains the database connection property that we’ll need to make the call to the stored procedure. To handle the list of parameters to the stored procedure, we’re creating a list of ParmInfo objects. The ParmInfo class is declared as:
public class ParmInfo { public String Name { get; set; } public Object Value { get; set; } public System.Data.DbType ParmType { get; set; } public Boolean isDbNull { get; set; } // not implemented public Boolean outParm { get; set; } // not implemented public ParmInfo() { // set default values outParm = false; isDbNull = false; } }
This simple class just holds the information we’ll need to create parameters later in the process. ‘Name’ is, of course, the name of the stored procedure parameter, ‘Value’ is the data being passed in and ‘ParmType’ is an optional value to describe the database type of the Value.
Here’s the main routine. There’s nothing really that interesting about it; the pattern should be familiar to anyone who’s done any old-school database programming.
public static List<T> ReadTableFromStoredProc<T>(this DbContext context, String procname, IEnumerable<ParmInfo> parms) where T : class { List<T> result = null; var connection = context.Database.Connection; try { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = procname; cmd.CommandType = System.Data.CommandType.StoredProcedure; foreach (ParmInfo p in parms) cmd.AddParameter(p.Name, p.Value, p.ParmType); var reader = cmd.ExecuteReader(); result = reader.SaveRecord<T>(); reader.Close(); } } catch (Exception ex) { throw new Exception("Error reading from stored proc" + procname + ": " + ex.Message, ex); } finally { connection.Close(); } return result ?? new List<T>(); }
Basically, we open the database connection, create a command object, and fill it in for calling a stored procedure. The ‘AddParameters’ routine was borrowed from Andy Edinborough. The really interesting bit, though is the ‘reader.SaveRecord<T>();’ call. This routine fills in the list of objects (of whatever type we tell it) using reflection to read values from the database results and store them into the object. Note that since this is outside of the normal DbContext processing, we need to match property names exactly with returned database column names.
The SaveRecord routine creates an object of our generic type, fills in the properties using reflection and then adds the result to a list of that type, which it then returns. Here’s the routine:
public static List<T> SaveRecord<T>(this System.Data.Common.DbDataReader reader) { List<T> result = new List<T>(); while (reader.Read()) { T t = (T)(typeof(T).GetConstructor(System.Type.EmptyTypes).Invoke(new object[0])); PropertyInfo[] props = t.GetType().GetProperties(); object[] indexer = null; foreach (PropertyInfo p in props) { p.SetValue(t, reader[p.Name], indexer); } result.Add(t); } return result; }
So, all together we have the means to call an ordinary stored procedure quickly and easily. Here’s an example of how to use this:
List<ParmInfo> parms = new List<ParmInfo>(); parms.Add(new ParmInfo() { Name="code", Value=code, ParmType=System.Data.DbType.String }); parms.Add(new ParmInfo() { Name="amt", Value=amt, ParmType=System.Data.DbType.Int32 }); PermitAppEntities pe = new PermitAppEntities(); return pe.ReadTableFromStoredProc<Fees>("permitapp.requesttotalfees", parms);
The result is a reasonably natural call method that can return a list of whatever object is needed (that matches the stored procedure’s result dataset), and should make a decent ‘stopgap’ until the codefirst guys get their act together. 🙂
Hi, we are getting the following error:
“Error reading from stored proc .DepartmentsGetAll: Property set method not found.”
but it is defined as `public StoredProc DepartmentsGetAll { get; set; }`
Any advice?
Based on that error message, I would guess that the property set method that’s missing is on one of the data elements being returned from calling the stored procedure. Apparently, the library is trying to write the data returned from the stored proc into the appropriate data member, which doesn’t have a “Set” property.
Thank you.
Yes that set us on the right direction. I had a property (DisplayName) on my DTO with only a getter that concatenated two of the other fields. My Stored Proc only returned the other two fields so I assumed the code just mapped from the return fields to the DTO, but it must be the other way around. I have now removed the property which was just a “getter” and moved the concatenation elsewhere, and all works sweet again. The error message does make sense now.
I wonder if you might be able to change the code in the framework to highlight the property name when it throws the exception?
[…] Please Note: This is an on-going project and has been inspired by and some of the ocde will be strongly based upon the great work carried out by “bluemoonsailor” at “Mindless Passenger”. See link: [https://mindlesspassenger.wordpress.com/2011/02/02/code-first-and-stored-procedures/] […]