2.21.2012

Entity Framework (Code First): the easy way to run stored procedures

Hello,

an ordinal way to execute stored procedure in EF CF is something like this:

public Product GetProduct(int id)
{
    Product product = null;

    using (var context = new NorthwindData())
    {
        string query = "Product_GetByID @productId";
        SqlParameter productId = new SqlParameter("@productId", id);

        product = context.Database.SqlQuery<Product>(query, productId).FirstOrDefault();
    }

    return product;
}

If you have more parameters for SP, you should specify all of them. Sometimes it's boring... So, I wrote several simple extensions to Database class to simplify this task.

See code below how GetProduct method will look like when using extensions:

public Product GetProduct(int id)
{
    Product product = null;

    using (var context = new NorthwindData())
    {
        product = context.Database.SqlQuerySmart<Product>("Product_GetByID", new
        {
            productId = id
        }).FirstOrDefault();
    }

    return product;
}

As you can see, you just create anonymous type with fields that has names exactly as parameters of SP and extension takes care about correct SQL code and other things. But, extension works only in simple cases, when there are no OUT parameters in SP.

Code of extension provided below, enjoy!

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Reflection;

namespace HennadiyKurabko.Data.EF
{
    public static class Extensions
    {
        public static int ExecuteSqlCommandSmart(this Database self, string storedProcedure, object parameters = null)
        {
            if (self == null)
                throw new ArgumentNullException("self");
            if (string.IsNullOrEmpty(storedProcedure))
                throw new ArgumentException("storedProcedure");

            var arguments = PrepareArguments(storedProcedure, parameters);
            return self.ExecuteSqlCommand(arguments.Item1, arguments.Item2);
        }

        public static IEnumerable<TElement> SqlQuerySmart<TElement>(this Database self, string storedProcedure, object parameters = null)
        {
            if (self == null)
                throw new ArgumentNullException("self");
            if (string.IsNullOrEmpty(storedProcedure))
                throw new ArgumentException("storedProcedure");

            var arguments = PrepareArguments(storedProcedure, parameters);
            return self.SqlQuery<TElement>(arguments.Item1, arguments.Item2);
        }

        public static IEnumerable SqlQuerySmart(this Database self, Type elementType, string storedProcedure, object parameters = null)
        {
            if (self == null)
                throw new ArgumentNullException("self");
            if (elementType == null)
                throw new ArgumentNullException("elementType");
            if (string.IsNullOrEmpty(storedProcedure))
                throw new ArgumentException("storedProcedure");

            var arguments = PrepareArguments(storedProcedure, parameters);
            return self.SqlQuery(elementType, arguments.Item1, arguments.Item2);
        }

        private static Tuple<string, object[]> PrepareArguments(string storedProcedure, object parameters)
        {
            var parameterNames = new List<string>();
            var parameterParameters = new List<object>();

            if (parameters != null)
            {
                foreach (PropertyInfo propertyInfo in parameters.GetType().GetProperties())
                {
                    string name = "@" + propertyInfo.Name;
                    object value = propertyInfo.GetValue(parameters, null);

                    parameterNames.Add(name);
                    parameterParameters.Add(new SqlParameter(name, value ?? DBNull.Value));
                }
            }

            if (parameterNames.Count > 0)
                storedProcedure += " " + string.Join(", ", parameterNames);

            return new Tuple<string, object[]>(storedProcedure, parameterParameters.ToArray());
        }
    }
}

Shout it

kick it on DotNetKicks.com

11 comments:

  1. Good Explanation, very well said..

    ReplyDelete
  2. in the line ...
    product = context.Database.SqlQuerySmart("Product_GetByID", new ...

    is something missing?

    ReplyDelete
    Replies
    1. Hi,

      it is instantiation of anonymous type, you could read more about it by following the link provided below:

      http://msdn.microsoft.com/en-us/library/bb397696.aspx

      Delete
  3. Hi Henk.
    I'd like to use something similar for http://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838
    You'd get full credit for it, and I'd link back to your blog for reference.
    Simon Hughes
    simon@hicrest.net

    ReplyDelete
  4. What about when stored procedure has 10 OUTPUT parameters. Thats what I am working on

    ReplyDelete
  5. Nice! I'm applying this. Thx.

    ReplyDelete
  6. Can We return output parameter by using above code

    ReplyDelete
  7. Great! Spared me some time!

    ReplyDelete
  8. I use the above code its working fine . But i don't want to pass any parameter at that time we will get the error

    ReplyDelete