Dec 25

How to call stored procedure using repository pattern and unit of work

Yesterday I received one complain about performance issue of entity framework for bigger LINQ queries using repository pattern. Client had asked me my opinion about using entity framework and moving complex logic to stored procedures. However, developers were not aware of calling stored procedures using repository pattern hence they had created edmx file and called stored procedure from that.

Personally, I feels above is not right approach to create separate edmx file just to call stored procedure. I advised to call it from repository itself. Last 6+ years I worked with Entity framework (code first, database first and model fist) and I felt repository pattern and unit of work i.e. code first approach is the best solution to work with.

Here I am not going to debate of all approaches here rather concentrating on performance and best practice to use entity framework.  There are couple of ways to use entity framework effectively and in my upcoming blog post I will explain it.

Personally, I feels the use of  hybrid (Entity+Stored Procedure) approach would help you to achieve your goals. I would recommend to use 10%-15% of your code using stored procedures and 85%-90% of code using entities. The disadvantage of this approach is that mocking of repository. The stored procedure approach in entity framework is not advisable as it makes mocking of repository difficult.

But if your goal is performance first then you should go with hybrid approach.

I my previous post we discussed about generic repository pattern and unit of work. Here i will use same database and code and just add additional code to call stored procedure using repository pattern and unit of work.

Create DataReaderMapper in data access layer. This class used to convert DataReader to domain object. This class uses reflection concept to map class properties to sql table properties.

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;

namespace DataAccessLayer
{
    public class DataReaderMapper where T : class
    {
        public List MapToList(DbDataReader dr)
        {
            if (dr != null && dr.HasRows)
            {
                var entity = typeof(T);
                var entities = new List();
                var propDict = new Dictionary<string, PropertyInfo>();
                var props = entity.GetProperties(BindingFlags.Instance | BindingFlags.Public);
                propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p);

                T newObject = default(T);
                while (dr.Read())
                {
                    newObject = Activator.CreateInstance();

                    for (int index = 0; index < dr.FieldCount; index++)
                    {
                        if (propDict.ContainsKey(dr.GetName(index).ToUpper()))
                        {
                            var info = propDict[dr.GetName(index).ToUpper()];
                            if ((info != null) && info.CanWrite)
                            {
                                var val = dr.GetValue(index);
                                info.SetValue(newObject, (val == DBNull.Value) ? null : val, null);
                            }
                        }
                    }

                    entities.Add(newObject);
                }

                return entities;
            }

            return null;
        }
    }
}

There are two approaches to call SQL Queries/Stored Procedure using repository pattern.

  1. SqlQuery function provided by entity framework
  2. Creating command object using DBContext

Add below interface signatures to IRepository interface

public interface IRepository : IDisposable
    {
        // 1. SqlQuery approach
        ICollection ExcuteSqlQuery(string sqlQuery, CommandType commandType, SqlParameter[] parameters = null);

        // 2. SqlCommand approach
        void ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters = null);

        ICollection ExecuteReader(string commandText, CommandType commandType, SqlParameter[] parameters = null);
    }

Now implement above newly added interface functions in Repository class

public class Repository : IRepository where T : class 
    {
        public DbContext context;
        public DbSet dbSet;

        public Repository(DbContext context)
        {
            this.context = context;
            dbSet = context.Set();
        }

        public ICollection ExcuteSqlQuery(string sqlQuery, CommandType commandType, SqlParameter[] parameters = null)
        {
            if (commandType == CommandType.Text)
            {
                return SqlQuery(sqlQuery, parameters);
            }
            else if (commandType == CommandType.StoredProcedure)
            {
                return StoredProcedure(sqlQuery, parameters);
            }

            return null;
        }

        public void ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            if (context.Database.Connection.State == ConnectionState.Closed)
            {
                context.Database.Connection.Open();
            }

            var command = context.Database.Connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            command.ExecuteNonQuery();
        }

        public ICollection ExecuteReader(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            if (context.Database.Connection.State == ConnectionState.Closed)
            {
                context.Database.Connection.Open();
            }

            var command = context.Database.Connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            using (var reader = command.ExecuteReader())
            {
                var mapper = new DataReaderMapper();
                return mapper.MapToList(reader);
            }
        }

        private ICollection SqlQuery(string sqlQuery, SqlParameter[] parameters = null)
        {
            if (parameters != null && parameters.Any())
            {
                var parameterNames = new string[parameters.Length];
                for (int i = 0; i < parameters.Length; i++)
                {
                    parameterNames[i] = parameters[i].ParameterName;
                }

                var result = context.Database.SqlQuery(string.Format("{0}", sqlQuery, string.Join(",", parameterNames), parameters));
                return result.ToList();
            }
            else
            {
                var result = context.Database.SqlQuery(sqlQuery);
                return result.ToList();
            }
        }

        private ICollection StoredProcedure(string storedProcedureName, SqlParameter[] parameters = null)
        {
            if (parameters != null && parameters.Any())
            {
                var parameterNames = new string[parameters.Length];
                for (int i = 0; i < parameters.Length; i++)
                {
                    parameterNames[i] = parameters[i].ParameterName;
                }

                var result = context.Database.SqlQuery(string.Format("EXEC {0} {1}", storedProcedureName, string.Join(",", parameterNames), parameters));
                return result.ToList();
            }
            else
            {
                var result = context.Database.SqlQuery(string.Format("EXEC {0}", storedProcedureName));
                return result.ToList();
            }
        }
    }

Business service class to call above logic is as below,

public class EmployeeService
    {
        private UnitOfWork unitOfWork = new UnitOfWork();
        private IRepository employeeRepository;

        public EmployeeService()
        {
            employeeRepository = unitOfWork.Repository();
        }

        public ICollection ExcuteSqlQuery(string sqlQuery, CommandType commandType, SqlParameter[] parameters = null)
        {
            return employeeRepository.ExcuteSqlQuery(sqlQuery, commandType, parameters);
        }

        public void ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            employeeRepository.ExecuteNonQuery(commandText, commandType, parameters);
        }

        public ICollection ExecuteReader(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            return employeeRepository.ExecuteReader(commandText, commandType, parameters);
        }
    }

Now lets create some stored procedures and View to test this.

USE [GenericRepository]
GO

CREATE VIEW [dbo].[VW_Employee]
AS
SELECT * FROM Employee
GO

create procedure [dbo].[GR_Employee_GetAll]
as
select * from Employee
GO

create procedure [dbo].[GR_Employee_GetById]
@EmployeeId INT
as
select * from Employee
where id = @EmployeeId
GO

Now test it using console based application we created before

class Program
    {
        static void Main(string[] args)
        {
            
            var employeeService = new EmployeeService();

            //Approach 1: SqlQuery approach
            //var employees = employeeService.ExcuteSqlQuery("GR_Employee_GetAll", System.Data.CommandType.StoredProcedure, null);
            //var employees = employeeService.ExcuteSqlQuery("SELECT * FROM Employee", System.Data.CommandType.Text, null);
            //var employees = employeeService.ExcuteSqlQuery("SELECT * FROM VW_Employee", System.Data.CommandType.Text, null);
            //var employee = employeeService.ExcuteSqlQuery("SELECT * FROM Employee WHERE ID = 2", System.Data.CommandType.Text, null);

            // Approach 2: SqlCommand approach
            //employeeService.ExecuteNonQuery("Delete from employee where id = 2", System.Data.CommandType.Text, null);
            var employees = employeeService.ExecuteReader("GR_Employee_GetAll", System.Data.CommandType.StoredProcedure, null);

            Console.ReadKey();
        }
    }

Now run the console application. It will display following output.

Repository pattern - stored procedure calling

Repository pattern – stored procedure calling

Thats it.. Isn’t simple like this ??

I hope you enjoined this post don’t forget to like, share, subscribe and comment.

2 comments

  1. Can i say that employeeservice class i like employeeDal (when using ado.net)?

  2. I would love to see extension of this article to have also second database for instance oracle and factory which could get one database or another because at the moment we working with one database. Also some place that we could say those data from first database and some other data from second. For me would be nic a mix of ef and ado.net.

Leave a Reply