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:              
-- Create date: 
-- 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:              
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[Get_Customer] 
        -- Add the parameters for the stored procedure here
         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* 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:              
-- Create date: 
-- 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:              
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[Set_Customer]
        -- Add the parameters for the stored procedure here
          Nvarchar(100)
        , 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,,)
        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:              
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[Update_Customer]
        -- Add the parameters for the stored procedure here
         @Id Bigint
        , Nvarchar(100)
        , 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] =  ,[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 : IDisposable where T : class
    {
        IEnumerable ExecuteQuery(string spQuery, object[] parameters);
        T ExecuteQuerySingle(string spQuery, object[] parameters);
        int ExecuteCommand(string spQuery, object[] parameters);
    }
}

interface IRepository

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.

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 : IRepository where T : class
    {

        Customer_Entities context = null;
        private DbSet entities = null;

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

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

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

        /// 
        /// Insert/Update/Delete Data To Database
        /// Use it when to Insert/Update/Delete data through a stored procedure
        /// 
        public int ExecuteCommand(string spQuery, object[] parameters)
        {
            int result = 0;
            try
            {
                using (context = new Customer_Entities())
                {
                    result = context.Database.SqlQuery(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 CustRepository;

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

        public IEnumerable 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 , 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
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
  
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:

says:

Shashangka Shekhar says:

Giancarlo Perez says:

rajneesh says:

Leave a Reply