CRUD Operation Using ASP.Net MVC-5

In This post we will learn Basic CRUD operation using ASP.Net MVC, JSON, ADO.Net, and Stored Procedure with a sample MVC Application.

Our first step is to create and design a SQL Database, below step’s we have the demonstration of creating a database table and very basic CRUD Stored Procedure.

Let’s Create a Sample Database:
Let’s Create a Sample database named ‘SampleDB’ with SQL Management Studio.Using the ‘SampleDB’ we will create a Table name ‘tblCustomer’.

Create Table Script:

CREATE TABLE [dbo].[tblCustomer](
        [CustID] [bigint] NOT NULL,
        [CustName] [nvarchar](50) NULL,
        [CustEmail] [nvarchar](50) NOT NULL,
        [CustAddress] [nvarchar](256) NULL,
        [CustContact] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED 
(
        [CustID] ASC,
        [CustEmail] 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

Stored Procedure:
Now the step we will go through to perform CRUD operations with stored procedure:

First we will create a stored procedure (SP) to RETRIVE record from Customer table
Now we will create a stored procedure( SP) to INSERT record into Customer table
Now we will create another procedure (SP) to UPDATE existing data in our Customer table.
The last step we will create a stored procedure to DELETE existing record from customer table.

Stored Procedure to RETRIVE Record:

-- =============================================
-- Author:              
-- Create date: <05/10/2015,,>
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[READ_CUSTOMER]
        -- Add the parameters for the stored procedure here
          INT
        , INT
        , INT
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        SET NOCOUNT ON;
    -- Select statements for procedure here

        IF( = 0)
        BEGIN
                SELECT top* FROM [dbo].[tblCustomer]
                ORDER BY CustID DESC
        END

        IF( = 1)
        BEGIN
                DECLARE  INT
                SET  = ( - 1) * 

                SELECT * FROM [dbo].[tblCustomer]
                ORDER BY CustID DESC
        
                OFFSET  ROWS FETCH NEXT  ROWS ONLY
        END
END

Stored Procedure to INSERT Record:

-- =============================================
-- Author:              
-- Create date: <05/10/2015,,>
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[CREATE_CUSTOMER]
        -- Add the parameters for the stored procedure here
(
          NVarchar(50)
        , NVarchar(50)
        , NVarchar(256)
        ,  NVarchar(50)
)
AS
BEGIN
        ---- SET NOCOUNT ON added to prevent extra result sets from
        SET NOCOUNT ON;

        ---- Try Catch--
        BEGIN TRY
                BEGIN TRANSACTION

                DECLARE  Bigint
                        SET  = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')

                -- Insert statements for procedure here
                INSERT INTO [dbo].[tblCustomer] ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])
                VALUES(,,,,)
                SELECT 1
                COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
                        DECLARE  NVARCHAR(4000), INT, INT;
                        SELECT  = ERROR_MESSAGE(), = ERROR_SEVERITY(), = ERROR_STATE();
                        RAISERROR (,,);
                ROLLBACK TRANSACTION
        END CATCH

END

Stored Procedure to UPDATE Record:

-- =============================================
-- Author:              
-- Create date: <05/10/2015,,>
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[UPDATE_CUSTOMER]
        -- Add the parameters for the stored procedure here
          BIGINT
        , NVarchar(50)
        , NVarchar(50)
        , NVarchar(256)
        ,  NVarchar(50)
AS
BEGIN
        ---- SET NOCOUNT ON added to prevent extra result sets from
        SET NOCOUNT ON;

        ---- Try Catch--
        BEGIN TRY
                BEGIN TRANSACTION

                -- Update statements for procedure here
                UPDATE [dbo].[tblCustomer]
                SET [CustName] = ,
                        [CustAddress] = ,
                        [CustContact] = 
                WHERE [CustID] =  AND [CustEmail] = 
                SELECT 1
                COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
                        DECLARE  NVARCHAR(4000), INT, INT;
                        SELECT  = ERROR_MESSAGE(), = ERROR_SEVERITY(), = ERROR_STATE();
                        RAISERROR (,,);
                ROLLBACK TRANSACTION
        END CATCH

END

Stored Procedure to DELETE Record:

-- =============================================
-- Author:              
-- Create date: <05/10/2015,,>
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[DELETE_CUSTOMER]
        -- Add the parameters for the stored procedure here
           BIGINT
AS
BEGIN
        ---- SET NOCOUNT ON added to prevent extra result sets from
        SET NOCOUNT ON;

        ---- Try Catch--
        BEGIN TRY
                BEGIN TRANSACTION

                -- Delete statements for procedure here
                DELETE [dbo].[tblCustomer]
                WHERE [CustID] =  
                SELECT 1
                COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
                        DECLARE  NVARCHAR(4000), INT, INT;
                        SELECT  = ERROR_MESSAGE(), = ERROR_SEVERITY(), = ERROR_STATE();
                        RAISERROR (,,);
                ROLLBACK TRANSACTION
        END CATCH

END

Stored Procedure to VIEW Single Record Details:

-- =============================================
-- Author:              
-- Create date: <05/10/2015,,>
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[VIEW_CUSTOMER]
        -- Add the parameters for the stored procedure here
         BIGINT
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        SET NOCOUNT ON;
    -- Select statements for procedure here

        SELECT * FROM [dbo].[tblCustomer]
        WHERE [CustID] =  
END

Let’s Start:

Open Visual Studio 2015, Click File> New > Project. In this window give a name the project and solution.

Click ok and another window will appear with project template, choose MVC:

Click ok and the visual studio will create and load a new ASP.NET application template. In this case we are using ASP.NET MVC 5. The new window will look like:

Our next step is to create a new model. Now right click on model folder and add new item choose data from left side menu, select ADO.NET Entity Data Model. Follow the process click next.

Next step is to connect to data base to generate .edmx file:

Let’s Start with View(UI):

Here are the .cshtml file of Customer CRUD operations, in the index view we have used a partial view to render the Customer list with Infinite scroll.

Index.cshtml

@{
    ViewBag.Title = "Index";
}

Index

    .ActionLink("Create New", "Create")





     Loading...

Partial view to view Customers:

 IEnumerable


 (var item in Model)
{
    
        Name: .DisplayFor(modelItem => item.CustName)
    
    
        Adddress: .DisplayFor(modelItem => item.CustAddress)
    
    
        .DisplayFor(modelItem => item.CustEmail)
    
    
        .DisplayFor(modelItem => item.CustContact)
    
    
        .ActionLink("Edit", "Edit", new { id = item.CustID }) |
        .ActionLink("Delete", "Delete", new { id = item.CustID }, new {  = "delete", @id = item.CustID })
    
    
}

Create.cshtml

In this view we have send a JSON object to MVC Action Method and have your Action Method return you a JSON result.

 CRUD_MVC5.Models.tblCustomer

@{
    ViewBag.Title = "Create";
}

Create


     Loading...


 (Html.BeginForm(null, null, FormMethod.Post, new { @id = "CustomerForm",  = "form" }))
{
    .AntiForgeryToken()

    
        Create Customer
        
        .ValidationSummary(true, "", new {  = "text-danger" })

        
            .LabelFor(model => model.CustName, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustName, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustName, "", new {  = "text-danger" })
            
        

        
            .LabelFor(model => model.CustEmail, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustEmail, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustEmail, "", new {  = "text-danger" })
            
        

        
            .LabelFor(model => model.CustAddress, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustAddress, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustAddress, "", new {  = "text-danger" })
            
        

        
            .LabelFor(model => model.CustContact, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustContact, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustContact, "", new {  = "text-danger" })
            
        

        
            
                
            
        
    
}


    .ActionLink("Back to List", "Index")

Edit.cshtml

In this view we have send a JSON object to MVC Action Method and have your Action Method return you a JSON result.

 CRUD_MVC5.Models.tblCustomer

@{
    ViewBag.Title = "Edit";
}

Edit


     Loading...


 (Html.BeginForm(null, null, FormMethod.Post, new { @id = "CustomerForm",  = "form" }))
{
    .AntiForgeryToken()
    
    
        Edit Customer
        
        .ValidationSummary(true, "", new {  = "text-danger" })
        .HiddenFor(model => model.CustID)

        
            .LabelFor(model => model.CustName, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustName, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustName, "", new {  = "text-danger" })
            
        

        .HiddenFor(model => model.CustEmail)

        
            .LabelFor(model => model.CustAddress, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustAddress, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustAddress, "", new {  = "text-danger" })
            
        

        
            .LabelFor(model => model.CustContact, htmlAttributes: new {  = "control-label col-md-2" })
            
                .EditorFor(model => model.CustContact, new { htmlAttributes = new {  = "form-control" } })
                .ValidationMessageFor(model => model.CustContact, "", new {  = "text-danger" })
            
        

        
            
                
            
        
    
}


    .ActionLink("Back to List", "Index")

Let’s Start with Coding:

In this app we are going to apply CRUD operation on a single table named Customer. To do first we need to create a Controller for the operations. To add a new Controller file we need to click right mouse an option menu will appear Click Add > Controller.

Let’s name it CustomerController. In the controller we will create action methods to perform CRUD operations:

Below code sample is for Customer CRUD operations:

// Customer Controller 
public class CustomerController : Controller
{
        // GET: Customer
        public ActionResult Index()
        {
            return View();
        }

        // GET: Customer
        [HttpGet]
        public ActionResult GetCustomer(int RowCountPerPage)
        {
            try
            {
                int PageNo = 0;
                int IsPaging = 0;

                CrudDataService objCrd = new CrudDataService();
                List modelCust = objCrd.GetCustomerList(PageNo, RowCountPerPage, IsPaging);
                return PartialView("_ListCustomer", modelCust);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // GET: Customer/InfinitScroll
        [HttpGet]
        public ActionResult GetCustomer_Scroll(int PageNo, int RowCountPerPage)
        {
            try
            {
                Thread.Sleep(2000);
                int IsPaging = 1;
                CrudDataService objCrd = new CrudDataService();
                List modelCust = objCrd.GetCustomerList(PageNo, RowCountPerPage, IsPaging);
                return PartialView("_ListCustomer", modelCust);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // GET: Customer/Create
        public ActionResult Create()
        {
            return View();
        }

        // GET: Customer/Create
        [HttpPost]
        public JsonResult Create(tblCustomer objCust)
        {
            try
            {
                CrudDataService objCrd = new CrudDataService();
                Int32 message = 0;

                if ((objCust.CustName != null) && (objCust.CustEmail != null)) message = objCrd.InsertCustomer(objCust);
                else message = -1;
                return Json(new
                {
                    Success = true,
                    Message = message
                });
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        // GET: Customer/Edit
        public ActionResult Edit()
        {
            return View();
        }

        // GET: Customer/Edit
        [HttpGet]
        public ActionResult Edit(long? id)
        {
            try
            {
                CrudDataService objCrd = new CrudDataService();
                tblCustomer modelCust = objCrd.GetCustomerDetails(id);
                return View(modelCust);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // GET: Customer/Edit
        [HttpPost]
        public JsonResult Edit(tblCustomer objCust)
        {
            try
            {
                CrudDataService objCrd = new CrudDataService();
                Int32 message = 0;
                message = objCrd.UpdateCustomer(objCust);
                return Json(new
                {
                    Success = true,
                    Message = message
                });

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        // GET: Customer/Delete
        [HttpPost]
        public JsonResult Delete(long? id)
        {
            try
            {
                CrudDataService objCrd = new CrudDataService();
                Int32 message = 0;
                message = objCrd.DeleteCustomer(id);
                return Json(new
                {
                    Success = true,
                    Message = message
                });

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

As we know earlier that we will use ADO.NET, Stored Procedure so to connect data base we need to modify our config file to add Connection String for database connection:

Now we need to create another class to use connection string and open our database connection, let’s name it dbConnector.

// Database Connection
public class dbConnector
{
        private SqlConnection SqlConn = null;

        public SqlConnection GetConnection
        {
            get { return SqlConn; }
            set { SqlConn = value; }
        }

        public dbConnector()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
            SqlConn = new SqlConnection(ConnectionString);
        }
}

To perform CRUD operations we will create a separate class called CrudDataService. In this class we have five methods that will interact with database to perform CRUD operations.

// Database Service
public class CrudDataService
{
        public List GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging)
        {
            dbConnector objConn = new dbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            try
            {
                List _listCustomer = new List();

                if (Conn.State != System.Data.ConnectionState.Open)
                    Conn.Open();

                SqlCommand objCommand = new SqlCommand("READ_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("", PageNo);
                objCommand.Parameters.AddWithValue("", RowCountPerPage);
                objCommand.Parameters.AddWithValue("", IsPaging);
                SqlDataReader _Reader = objCommand.ExecuteReader();

                while (_Reader.Read())
                {
                    tblCustomer objCust = new tblCustomer();
                    objCust.CustID = Convert.ToInt32(_Reader["CustID"]);
                    objCust.CustName = _Reader["CustName"].ToString();
                    objCust.CustEmail = _Reader["CustEmail"].ToString();
                    objCust.CustAddress = _Reader["CustAddress"].ToString();
                    objCust.CustContact = _Reader["CustContact"].ToString();
                    _listCustomer.Add(objCust);

                }

                return _listCustomer;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        public tblCustomer GetCustomerDetails(long? id)
        {

            dbConnector objConn = new dbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            try
            {
                tblCustomer objCust = new tblCustomer();

                if (Conn.State != System.Data.ConnectionState.Open)
                    Conn.Open();

                SqlCommand objCommand = new SqlCommand("VIEW_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("", id);
                SqlDataReader _Reader = objCommand.ExecuteReader();

                while (_Reader.Read())
                {
                    objCust.CustID = Convert.ToInt32(_Reader["CustID"]);
                    objCust.CustName = _Reader["CustName"].ToString();
                    objCust.CustEmail = _Reader["CustEmail"].ToString();
                    objCust.CustAddress = _Reader["CustAddress"].ToString();
                    objCust.CustContact = _Reader["CustContact"].ToString();
                }

                return objCust;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        public Int32 InsertCustomer(tblCustomer objCust)
        {
            dbConnector objConn = new dbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            int result = 0;

            try
            {
                if (Conn.State != System.Data.ConnectionState.Open)
                    Conn.Open();

                SqlCommand objCommand = new SqlCommand("CREATE_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("", objCust.CustName);
                objCommand.Parameters.AddWithValue("", objCust.CustEmail);
                objCommand.Parameters.AddWithValue("", objCust.CustAddress);
                objCommand.Parameters.AddWithValue("", objCust.CustContact);

                result = Convert.ToInt32(objCommand.ExecuteScalar());

                if (result > 0)
                {
                    return result;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        public Int32 UpdateCustomer(tblCustomer objCust)
        {
            dbConnector objConn = new dbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            int result = 0;

            try
            {
                if (Conn.State != System.Data.ConnectionState.Open)
                    Conn.Open();

                SqlCommand objCommand = new SqlCommand("UPDATE_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("", objCust.CustID);
                objCommand.Parameters.AddWithValue("", objCust.CustName);
                objCommand.Parameters.AddWithValue("", objCust.CustEmail);
                objCommand.Parameters.AddWithValue("", objCust.CustAddress);
                objCommand.Parameters.AddWithValue("", objCust.CustContact);

                result = Convert.ToInt32(objCommand.ExecuteScalar());

                if (result > 0)
                {
                    return result;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        public Int32 DeleteCustomer(long? id)
        {
            dbConnector objConn = new dbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            int result = 0;

            try
            {
                if (Conn.State != System.Data.ConnectionState.Open)
                    Conn.Open();

                SqlCommand objCommand = new SqlCommand("DELETE_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("", id);
                result = Convert.ToInt32(objCommand.ExecuteScalar());

                if (result > 0)
                {
                    return result;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }
    }

Note: This is a very basic app to demonstrate the process of CRUD using MVC, who are still confused how to start, my goal was to make it simple.

Hope this will help someone 🙂… Thanks.

Download The Sample Application

3 thoughts on “CRUD Operation Using ASP.Net MVC-5”

says:

October 9, 2015 at 11:40 am


Sir,
Tutorial given by you is fine. Please help me to solve my problem.
I have implemented few things of AngularJs tutorials in my website: . I am new to MVC and angularJS. I am searching for a job. My application is working fine on localhost . In localhot it saves data in App_data folder (abc.mdf file). On remote server I have installed database on another server(ip address). But it fails to submit data on remote server. Please check “Request for a qoute” and “Career” web page on it. Here data in dropdownlist is coming from database. But fails to submit(insert). I am unable to save data on remote server database using this file upload. Please give solution to submit(insert) data
Valter says:

December 28, 2017 at 8:25 pm


“Hope this will help someone… Thanks.” This is helping me, so much dude, and will help more people! Thank you!
Shubham Batra says:

September 12, 2020 at 4:56 pm


Follow the Blog for MVC Crud Operation with Interfaces and Repository Pattern with ADO.Net
https://code2night.com/Blog/MyBlog/MVC-Crud-Operation-with-Interfaces-and-Repository-Pattern-with-ADONet

SHASHANGKA SHEKHAR'S BLOG

Index

Create

Create Customer

Edit

Edit Customer

Author: Shashangka Shekhar

3 thoughts on “CRUD Operation Using ASP.Net MVC-5”

says:

Valter says:

Shubham Batra says:

Leave a Reply