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.

 

May 06

A reference guide to access data in .Net

Data access layer is one one of important aspect in any application. I have written many posts on data access later using ADO.NET, DBProviderFactories, generic repository and Entity Framework. So in this post I am consolidating all  posts in one place.

I will update this section whenever I publish new post related to data access.
 

May 01

Tech News: Microsoft has released in .NET Framework 4.7.2

Microsoft has announced the release of the .NET Framework 4.7.2 it is included in windows 10 April 2018 Update. It is also available on windows 7+ and windows server 2008 R2+

You can download .NET Framework 4.7.2 using Web Installer or Offline Installer

What’s new in .NET Framework 4.7.2?

  1. ASP.NET Changes
    • Support for SameSite cookie in ASP.NET in Page, Custom Handler and User controls
    • Enable ASP.NET developers to specify MaxLength attribute for Multiline asp:TextBox.
    • Support for ASP.NET Dependency Injection
  2. Click Once
    • Per-monitor support for WPF and HDPI-aware VSTO apps deployed via ClickOnce
  3. Base Class Library
    • Enhanced .NET Framework support for .NET Standard 2.0
    • Cryptography improvements
    • Support for Programmatically creating X.509 certificates and certificate signing requests.
  4. SQL
    • Added support for Always Encrypted leveraging enclave technologies
    • Integrating Azure AD Universal and Multi-factor authentication (MFA) support with SQL .NET driver
  5. WPF
    • Diagnostic enhancements

You can see complete list of improvements in the .NET Framework 4.7.2 release notes

Apr 30

Microsoft Certification Roadmap for Developers

Using Microsoft Certifications you can get hired, demonstrate clear business impact and advance your skill sets and take your career to the next level.

Microsoft has segregated these certifications broadly in six major categories,

Microsoft Certifications Categories

Microsoft Certifications Categories – Reference from  https://www.microsoft.com/en-in/learning/certification-overview.aspx#/

Today I am going to guide you the Microsoft Certification paths (marked in above figure) for web/database developer who develops web application using HTML5, CSS, Javascript, Web API, WCF, Windows Azure and SQL Server. In this post I will focus on best certification roadmap, syllabus and study material available on internet.

Microsoft Virtual Academy provides free training videos developed by their experts and Microsoft Press Store provides PDF format books with effective cost ($30-$50). I would recommend you to read these books available in your companies or college libraries.

There are many certification paths available on Microsoft Learning website. However I am explaining the best path for Web and Database developers.

Microsoft offers three professional specialization in certification and one entry level certification as below,

MTA: Microsoft Technology Associate

It is fundamental technical knowledge entry-level certification intended for people who’s more focus on basic concepts. This certification do not qualify for any MCP certification nor they are prerequisite for MCSA, MCSE or MCSD certifications. This certification is best for those doesn’t have computer background.

MCP: Microsoft Certified Professional

You will earn this certification once you pass any of the examination mentioned in MCSA/MCSE/MCSD 

1. MCSA: Microsoft Certified Solutions Associate

It is certification program intended for people who seek entry-level jobs in information technology industry. It is prerequisite for more advanced Microsoft certifications like MCSE and MCSD

This certificate validates the holder’s ability to build , design and develop solutions using core Microsoft technologies.

2. MCSE: Microsoft Certified Solutions Expert

This certification show the IT professional’s skills to build, design and develop advanced solutions which integrate multiple Microsoft technologies, both on-premises and in the cloud. It requires MCSA prerequisite. 

This certification available in Cloud and Data platform.

3. MCSD: Microsoft Certified Solutions Developer

This certification proves the IT professional’s skills in building, designing and developing advanced application solutions using multiple Microsoft technologies, both on-premises and in the cloud. It requires MCSA prerequisite.

This certification is available in Web application platform.

Typical Roadmap for Web and Database developers.

Cloud Certificate Path

To achieve cloud certifications you need to pass both exam 70-532 and exam 70-535. If you either pass the exam you will receive specialist certification credit.

 

Windows Azure Cretification

Windows Azure Certification

Once you clear both the exams you will gain both MCSA(Cloud platform) and MCSE (Cloud Platform and Infrastructure) certifications. If you are developer and most focus development part on windows azure rather infrastructure then I would recommend you to go for exam 70-532 Developing Microsoft Azure Solutions as this exam is elective exam to achieve MCSD in Web application

I will discuss more on syllabus part in my next article.

Web Development Certification Path

Now a days full stack development is one of the important aspect in web development. The candidate who have good hands on experience on HTML, CSS, JavaScript, C#, ASP.NET MVC, Web API, WCF and Windows azure can go with this certification path. For .Net web developer needs three exams to earn MCSD certification. The best path is as below,

Web Application Certification

Web Application Certification

Here you need pass exam 70-480 and exam 70-486 to earn MCSA in Web Applications certification and to earn MCSD in App Builder, you need to clear exam 70-487 along with MCSA certification as prerequisite.

Another best path available is to choose elective exam before appearing for MCSD. Below certification path shows the another track,

Web and Cloud Certification

Web and Cloud Certification

This is same path as Web development track but you have option to choose any one exam from 70-487,  70-532 and 70-535.

Confusion between exam 70-487 and exam 70-532?

This is the one of confusing question comes into mind which exam should I choose as the name suggest Developing Windows Azure. Well, personally I feel the name given to exam 70-487 needs to be change as it is mostly focused on Web Services and Web API concepts and very few questions from Windows Azure. However exam 70-532 more focus on windows azure development only. I hope my answer helps you to decide right certification.

Database Development Certification Path

For database development you need to pass total four exams which consist of database development, database administration, SQL data warehouse. This is one of the toughest certification in data category.

SQL Server 2012/2014 certification consist of three mandatory exams (exam 70-461, 70-462 and 70-463) to gain MCSA certification and One elective(exam 70-464, 70-465, 70-762) to gain MCSE certification.

 

SQL Server 2012/2014 Development

SQL Server 2012/2014 Development

Generally, developers confuse between the development track to choose. In my personal opinion this is  not right track to start with if you mostly focus on database development rather than administration stuff.

Last year Microsoft introduced another path for database certification and I feel the best one to clear only two exams to achieve MCSA and MCSE both i.e. SQL 2016 Database development. 

 

SQL Server 2016 Development Certification

SQL Server 2016 Development Certification

This certification contains only two relevant skills experience certification exam 70-761 which mostly focus on querying and basic concepts around SQL queries and exam 70-762 focus mostly on advanced concepts like database performance, ACID properties.

I personally feels one should always go with this path as this also saves your money and time to prepare 🙂

Frequently Asked Questions(FAQ’s)

Q: How to register for exam?

First create Microsoft Learning account and fill the necessary details like personal details, address details and contact details. After registration you will get MCID and PearsonVue Registation ID. Please note this both registrations ID’s for future exams. Do not create multiple MCID and Person Registration ID as it would be difficult to you to generate transcript.

  1. You can register for exam online and appear any Pearsonvue test center near you. 
  2. You can go to Personvue exam center and share Exam Code to appear, MCID, Personvue ID, Voucher Code(It is not mandatory to purchase voucher code, you can pay exam fee at test center as well)

Q: How many questions will be there in exam and what are the types?

A: The exam generally consist of 40-60 questions of scenarios based, objective, multiple choice single select, multiple choice multiple select, drag and drop questions

Q: How much time will I have to complete the exam?

A: Exam generally consist of 120 to 150 minutes. 

Q: Where I can take this exam?

A: You can give this exam in any Personvue center available near by your location,  

Q: Are there any vouchers available for exam?

A: Yes you can purchase voucher for exam. Sometimes voucher also provides exam plus retake facility which saves your money. 

Q: How can I prepare myself for exam?

A: The preparation material available on Microsoft Virtual Academy and e-books available on Microsoft Press Store.

Q: What are identity requirements for exam?

A: You need to present two identities on test day: One primary identity (government-issued with name, photo, and signature) and one secondary ID (name and signature). Here first name and last name must exactly match with identity presented on test day.

Primary identification should either one from Passport, Driving License, Military ID (including spouse and dependents), Identification card (national or local), Registration card (such as green card, permanent resident, visa).

Secondary identification should any original, valid ID that demonstrates at least your name and signature.

Q: What if I fail to exam?

A: You can retake exam. Please refer retake policy before appearing for exam.

Q: Is there any negative marking in exam?

A: No there is no negative marking in exam. Each question have marks and partial marks

Q: How and When will I get my exam results?

A: After completing exam, you will immediately get exam result print out at Pearsonvue center which mentions marks scored, and status of exam PASSED/FAILED with performance of skills areas measured  using horizontal bar chart. Microsoft generally updates your learning account within 24 hrs if you pass the exam.

Q: If I do not pass exam, can I have a full or partial refund?

A: No

Q: My question not mentioned above?

A: Please visit Microsoft Certification Policies for more details.

 

I hope this post help you to decide right certification path for your career. If you like this post don’t forget to like, subscribe and comment.

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.