May 10

How to Call Stored Procedure using Unit of Work and Entity Framework

In my last post we learned how to call stored procedure using Repository Pattern and Entity Framework. In ExecuteReader function we saw how to map selected rows to plain(without complex) domain object.  Class DataReaderMapper used reflection concept to map properties. However this approach will use only for repository and not for ViewModel or DTO’s or any custom class. 

Suppose you wanted to execute complex dynamic query and fetch result with respect to class or view object then stored procedure using repository pattern approach will not work. To overcome this drawback, move such database calling logic to unit of work.

So, Unit of Work will use context object to invoke stored procedure. Using unit of work we can execute stored procedure, views and map result to any custom object. We can even invoke stored procedure to return scalar/non scalar results through command objects on “DBContext.Database.Connection”

Let’s see how to implement this. You can refer complete Repository Pattern and Unit of Work in my previous post.

First add two new interface functions ExecuteReader and ExecuteNonQuery to IUnitOfWork interface.

 

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

namespace DataAccessLayer
{
    public interface IUnitOfWork
    {
        IRepository Repository() where T : class;

        void Save();

        IEnumerable ExecuteReader(string sqlQuery);

        IEnumerable ExecuteReader(string storedProcedureName, SqlParameter[] parameters = null);

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

Now implement newly added functions in implementation class. To support stored procedure execution on SQL Profiler we need to use string builder class to generate stored procedure execution query like,

EXEC SP_NAME @Param1=’some text’, @Param2=100

 

using System;
using System.Collections.Generic;
using Domain;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Linq;

namespace DataAccessLayer
{
    public class UnitOfWork : IDisposable, IUnitOfWork
    {
        private bool disposed;
        private Dictionary<string, object> repositories;
        private GenericRepositoryDBContext context;

        public UnitOfWork()
        {
            context = new GenericRepositoryDBContext();
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        public virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    context.Dispose();
                }
            }
            disposed = true;
        }

        public IRepository Repository() where T : class
        {
            if (repositories == null)
            {
                repositories = new Dictionary<string, object>();
            }

            var type = typeof(T).Name;

            if (!repositories.ContainsKey(type))
            {
                var repositoryType = typeof(Repository<>);
                var repositoryInstance = Activator.CreateInstance(repositoryType.MakeGenericType(typeof(T)), context);
                repositories.Add(type, repositoryInstance);
            }
            return (Repository)repositories[type];
        }

        public void Save()
        {
            context.SaveChanges();
        }

        public IEnumerable ExecuteReader(string sqlQuery) 
        {
            try
            {
                var result = context.Database.SqlQuery(sqlQuery);
                return result.ToList();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public IEnumerable ExecuteReader(string storedProcedureName, SqlParameter[] parameters = null) 
        {
            if (parameters != null && parameters.Any())
            {
                var parameterBuilder = new StringBuilder();
                parameterBuilder.Append(string.Format("EXEC {0} ", storedProcedureName));

                for (int i = 0; i < parameters.Length; i++)
                {
                    if (parameters[i].SqlDbType == SqlDbType.VarChar 
                        || parameters[i].SqlDbType == SqlDbType.NVarChar
                        || parameters[i].SqlDbType == SqlDbType.Char 
                        || parameters[i].SqlDbType == SqlDbType.NChar
                        || parameters[i].SqlDbType == SqlDbType.Text 
                        || parameters[i].SqlDbType == SqlDbType.NText)
                    {
                        parameterBuilder.Append(string.Format("{0}='{1}'", parameters[i].ParameterName, 
                            string.IsNullOrEmpty(parameters[i].Value.ToString()) 
                            ? string.Empty : parameters[i].Value.ToString()));
                    }
                    else if (parameters[i].SqlDbType == SqlDbType.BigInt 
                       || parameters[i].SqlDbType == SqlDbType.Int
                       || parameters[i].SqlDbType == SqlDbType.TinyInt 
                       || parameters[i].SqlDbType == SqlDbType.Decimal
                       || parameters[i].SqlDbType == SqlDbType.Float 
                       || parameters[i].SqlDbType == SqlDbType.Money
                       || parameters[i].SqlDbType == SqlDbType.SmallInt 
                       || parameters[i].SqlDbType == SqlDbType.SmallMoney)
                    {
                        parameterBuilder.Append(string.Format("{0}={1}", parameters[i].ParameterName
                            , parameters[i].Value));
                    }
                    else if (parameters[i].SqlDbType == SqlDbType.Bit)
                    {
                        parameterBuilder.Append(string.Format("{0}={1}", parameters[i].ParameterName, 
                            Convert.ToBoolean(parameters[i].Value)));
                    }

                    if (i < parameters.Length - 1)
                    {
                        parameterBuilder.Append(",");
                    }
                }

                var query = parameterBuilder.ToString();
                var result = context.Database.SqlQuery(query);
                return result.ToList();
            }
            else
            {
                var result = context.Database.SqlQuery(string.Format("EXEC {0}", storedProcedureName));
                return result.ToList();
            }
        }

        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();
        }
    }
}

Now change the business service class.

 

using System.Collections.Generic;
using System.Linq;
using DataAccessLayer;
using Domain;
using System.Data.SqlClient;
using System.Data;

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

        public EmployeeService()
        {
            employeeRepository = unitOfWork.Repository();
        }
        
        public IEnumerable ExecuteReader(string sqlQuery)
        {
            return unitOfWork.ExecuteReader(sqlQuery);
        }

        public IEnumerable ExecuteReader(string commandText, SqlParameter[] parameters = null)
        {
            return unitOfWork.ExecuteReader(commandText, parameters);
        }
    }
}

Now let’s create console based application to execute stored procedure through business service class

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Domain;

using System.Data;
using System.Data.SqlClient;
namespace TestClient
{
    class Program
    {
        static void Main(string[] args)
        {
            var employeeService = new EmployeeService();

            var sqlParameters = new List
            {
                new SqlParameter
                {
                    ParameterName = "@SearchTerm",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.VarChar,
                    Value = string.Empty
                },
                new SqlParameter
                {
                    ParameterName = "@SortColumn",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.VarChar,
                    Value = "FirstName"
                },
                new SqlParameter
                {
                    ParameterName = "@SortOrder",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.VarChar,
                    Value = "ASC"
                },
                new SqlParameter
                {
                    ParameterName = "@PageNumber",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.Int,
                    Value = 10
                },
                new SqlParameter
                {
                    ParameterName = "@PageSize",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.Int,
                    Value = 10
                },
            };

            var employees = employeeService.ExecuteReader("usp_Employee_GetAll", sqlParameters.ToArray());

            Console.ReadKey();
        }
    }
}

That’s it. I hope you enjoyed this article.

 

2 comments

  1. Getting error in Class UnitOfWork for every SqlQuery:
    Severity Code Description Project File Line Suppression State
    Error CS0411 The type arguments for method ‘Database.SqlQuery(string, params object[])’ cannot be inferred from the usage. Try specifying the type arguments explicitly. DataAccessLayer H:\GenericRepositoryPattern\DataAccessLayer\UnitOfWork.cs 82 Active

  2. Also when implemented this i lost entity framework features whcih were stored in EmployeeService such as GetAll, Update etc.. and only stays those new once… What is the sense then i would like to keep entity’s framework methods as long as our new direct ExecuteReader in this post.

Leave a Reply