ASP.Net MVC-5 CRUD Operation using Repository Pattern with SP

In this article we will focus on accessing data only using Stored Procedure using Entity Framework.

At first we need to create a database named CRUD_Sample. In sample db we have to create a table named Customer.

USE [CRUD_Sample]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 1/23/2016 10:02:11 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Customers](
	[Id] [bigint] NOT NULL,
	[CustName] [varchar](100) NULL,
	[CustEmail] [varchar](150) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Store Procuders:

USE [CRUD_Sample]
GO

/****** Object:  StoredProcedure [dbo].[Delete_Customer]    Script Date: 1/23/2016 10:01:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Delete_Customer]
	-- Add the parameters for the stored procedure here
	 @Id Bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM[dbo].[Customers] WHERE [Id] = @Id
	SELECT 1
END

GO

/****** Object:  StoredProcedure [dbo].[Get_Customer]    Script Date: 1/23/2016 10:01:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Get_Customer] 
	-- Add the parameters for the stored procedure here
	@Count BIGINT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT top(@Count)* FROM [dbo].[Customers]
END

GO

/****** Object:  StoredProcedure [dbo].[Get_CustomerbyID]    Script Date: 1/23/2016 10:01:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Get_CustomerbyID] 
	-- Add the parameters for the stored procedure here
	@Id BIGINT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM [dbo].[Customers]
	WHERE Id=@Id
END

GO

/****** Object:  StoredProcedure [dbo].[Set_Customer]    Script Date: 1/23/2016 10:01:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Set_Customer]
	-- Add the parameters for the stored procedure here
	 @CustName Nvarchar(100)
	,@CustEmail Nvarchar(150)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @Id bigint 
	SET @Id = ISNULL(((SELECT MAX(Id) FROM [Customers])+1),'1')

    -- Insert statements for procedure here
	INSERT INTO [dbo].[Customers] ([Id],[CustName],[CustEmail])
	VALUES(@Id,@CustName,@CustEmail)
	SELECT 1
END

GO

/****** Object:  StoredProcedure [dbo].[Update_Customer]    Script Date: 1/23/2016 10:01:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Update_Customer]
	-- Add the parameters for the stored procedure here
	 @Id Bigint
	,@CustName Nvarchar(100)
	,@CustEmail Nvarchar(150)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE [dbo].[Customers] SET[CustName] = @CustName ,[CustEmail]= @CustEmail
	WHERE [Id] = @Id
	SELECT 1
END

GO

Repository Pattern:

The Repository Pattern allows to centralise all data access logic in one place.

It is a common construct to avoid duplication of data access logic throughout our application.

The Repository pattern adds a separation layer between the data and business layers of an application.

Generic Repository Pattern:

With generic feature, we can reduce the amount of code we need for common scenarios.

namespace WebApplication1.Repository
{
    interface IRepository<T> : IDisposable where T : class
    {
        IEnumerable<T> ExecuteQuery(string spQuery, object[] parameters);
        T ExecuteQuerySingle(string spQuery, object[] parameters);
        int ExecuteCommand(string spQuery, object[] parameters);
    }
}

interface IRepository<T>

Show an interface of a generic repository of type T, which is a LINQ to SQL entity. It provides a basic interface with operations like Insert, Update, Delete, GetById and GetAll.

IDisposable

The IDisposable Interface, Provides a mechanism for releasing unmanaged resources.

where T : class

This is constraining the generic parameter to a class. Click for more

The type argument must be a reference type; this applies also to any class, interface, delegate, or array type.

namespace WebApplication1.Repository
{
    public class GenericRepository<T> : IRepository<T> where T : class
    {

        Customer_Entities context = null;
        private DbSet<T> entities = null;

        public GenericRepository(Customer_Entities context)
        {
            this.context = context;
            entities = context.Set<T>();
        }

        /// <summary>
        /// Get Data From Database
        /// <para>Use it when to retive data through a stored procedure</para>
        /// </summary>
        public IEnumerable<T> ExecuteQuery(string spQuery, object[] parameters)
        {
            using (context = new Customer_Entities())
            {
                return context.Database.SqlQuery<T>(spQuery, parameters).ToList();
            }
        }

        /// <summary>
        /// Get Single Data From Database
        /// <para>Use it when to retive single data through a stored procedure</para>
        /// </summary>
        public T ExecuteQuerySingle(string spQuery, object[] parameters)
        {
            using (context = new Customer_Entities())
            {
                return context.Database.SqlQuery<T>(spQuery, parameters).FirstOrDefault();
            }
        }

        /// <summary>
        /// Insert/Update/Delete Data To Database
        /// <para>Use it when to Insert/Update/Delete data through a stored procedure</para>
        /// </summary>
        public int ExecuteCommand(string spQuery, object[] parameters)
        {
            int result = 0;
            try
            {
                using (context = new Customer_Entities())
                {
                    result = context.Database.SqlQuery<int>(spQuery, parameters).FirstOrDefault();
                }
            }
            catch { }
            return result;
        }

        private bool disposed = false;

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

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

Use of Middle-tire:

namespace WebApplication1.Services
{
    public partial class CustomerService
    {
        private GenericRepository<Customer> CustRepository;

        //CustomerRepository CustRepository;
        public CustomerService()
        {
            this.CustRepository = new GenericRepository<Customer>(new Customer_Entities());
        }

        public IEnumerable<Customer> GetAll(object[] parameters)
        {
            string spQuery = "[Get_Customer] {0}";
            return CustRepository.ExecuteQuery(spQuery, parameters);
        }

        public Customer GetbyID(object[] parameters)
        {
            string spQuery = "[Get_CustomerbyID] {0}";
            return CustRepository.ExecuteQuerySingle(spQuery, parameters);
        }

        public int Insert(object[] parameters)
        {
            string spQuery = "[Set_Customer] {0}, {1}";
            return CustRepository.ExecuteCommand(spQuery, parameters);
        }

        public int Update(object[] parameters)
        {
            string spQuery = "[Update_Customer] {0}, {1}, {2}";
            return CustRepository.ExecuteCommand(spQuery, parameters);
        }

        public int Delete(object[] parameters)
        {
            string spQuery = "[Delete_Customer] {0}";
            return CustRepository.ExecuteCommand(spQuery, parameters);
        }
    }
}

Use of Generic Repository in MVC-Application:

namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        private CustomerService objCust;

        //CustomerRepository CustRepository;
        public HomeController()
        {
            this.objCust = new CustomerService();
        }

        // GET: Home
        public ActionResult Index()
        {
            int Count = 10;
            object[] parameters = { Count };
            var test = objCust.GetAll(parameters);
            return View(test);
        }


        public ActionResult Insert()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Insert(Customer model)
        {
            if (ModelState.IsValid)
            {
                object[] parameters = { model.CustName, model.CustEmail };
                objCust.Insert(parameters);
            }
            return RedirectToAction("Index");
        }
        public ActionResult Delete(int id)
        {
            object[] parameters = { id };
            this.objCust.Delete(parameters);
            return RedirectToAction("Index");
        }

        public ActionResult Update(int id)
        {
            object[] parameters = { id };
            return View(this.objCust.GetbyID(parameters));
        }

        [HttpPost]
        public ActionResult Update(Customer model)
        {
            object[] parameters = { model.Id, model.CustName, model.CustEmail };
            objCust.Update(parameters);
            return RedirectToAction("Index");
        }
        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
        }
    }
}

Source Code: I’ve uploaded the full source code to download/clone @github, Hope this will help 🙂

9 thoughts on “ASP.Net MVC-5 CRUD Operation using Repository Pattern with SP”

priya says:

April 24, 2016 at 10:08 pm


I am new to asp.net mvc in this , u didnt mention the connection string of database.not required?
- rakesh says:
  
  October 7, 2016 at 2:03 pm
  
  
  Hi Priya,
  
  Here using entity data model, model name Customer_Entities. entity data model have a connection string
ruchi says:

March 13, 2017 at 10:55 pm


i am new in mvc , could u please tell me how i teach mvc with angular js very well.I wants ur all articles
Valter says:

December 28, 2017 at 7:59 pm


Thank you so much for share your knowledge!
satyesh says:

January 20, 2018 at 8:06 pm


Best articles
Mukesh P Depani says:

February 14, 2018 at 9:32 am


Can I have code sample for above please ?
- Shashangka Shekhar says:
  
  February 15, 2018 at 8:33 am
  
  
  https://github.com/ShashangkaShekhar/ASP.NET-MVC5-Repository-SP
Giancarlo Perez says:

April 3, 2018 at 11:59 pm


thanks for comming back ,a saved in my markups your page and it was offline
rajneesh says:

October 6, 2018 at 8:02 am


Actully i referred that code but i get on e exception i.e dbcontext is not disposed

SHASHANGKA SHEKHAR'S BLOG

ASP.Net MVC-5 CRUD Operation using Repository Pattern with SP

Author: Shashangka Shekhar

9 thoughts on “ASP.Net MVC-5 CRUD Operation using Repository Pattern with SP”

priya says:

rakesh says:

ruchi says:

Valter says:

satyesh says:

Mukesh P Depani says:

Shashangka Shekhar says:

Giancarlo Perez says:

rajneesh says:

Leave a Reply Cancel reply