[10 Steps] CRUD Operation In MVC Using Ado.Net Step By Step

CRUD Operation In MVC Using Ado.Net Step By Step: In this tutorial, we are going to develop the Create Read Update Delete (CRUD) Operation.

Prerequisites

Basic SQL Query Knowledge
SQL Connection
Razor Page
C#

Make Sure to Follow every step carefully to achive the CRUD Operation

Step #1

Create a table from the given SQL Script.

create database dotnet_tutorial

use dotnet_tutorial

CREATE TABLE [dbo].[tbl_customer](

[customer_id] [bigint] IDENTITY(1,1) NOT NULL,

[customer_name] [varchar](200) NOT NULL,

[address_text] [varchar](600) NULL,

[contact_no] [varchar](50) NULL,

[email] [varchar](500) NULL,

[password] [varchar](500) NULL,

)

Step #2

Create a procedure

Create Procedure [dbo].[UserRegistration]

@action int,

@customer_id int=null,

@customer_name varchar(200)= NULL,

@address_text varchar(600) =NULL,

@contact_no varchar(50)= NULL,

@email varchar(500) =NULL,

@password varchar(500)= NULL

Begin

if(@action=1)--Insert

BEGIN

insert into tbl_customer(customer_name,address_text,contact_no,email,password)

values (@customer_name,@address_text,@contact_no,@email,@password)

Select 1 StatusCode, 'Record Saved !' as Msg

END

ELSE if(@action=2)--Get

BEGIN

Select * from tbl_customer

END

ELSE if(@action=3)--Edit

BEGIN

Select * from tbl_customer where customer_id=@customer_id

END

ELSE if(@action=4)--Update

BEGIN

update tbl_customer

SET customer_name=@customer_name,

address_text=@address_text,

contact_no=@contact_no,

email=@email,

password=@password

Where

customer_id=@customer_id

Select 1 StatusCode, 'Record Updated !' as Msg

END

End

Step #3

Create a DataLayer.cs Class in Models Folder by Right click on Models Folder

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

namespace MyFirstApp.Models

{

public class DataLayer

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCON"].ConnectionString);

public DataTable ExecProc(string ProName, SqlParameter[] Param)

{

DataTable dt = new DataTable();

try

{

con.Open();

SqlCommand cmd = new SqlCommand(ProName, con);

cmd.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter prm in Param)

{

cmd.Parameters.Add(prm);

}

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(dt);

}

catch (Exception)

{

}

finally

{

con.Close();

}

return dt;

}

Step #4

Create a MiddleLayer.cs Class in Models Folder by Right click on Models Folder.

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

namespace MyFirstApp.Models

{

public class MiddleLayer

{

DataLayer objDL = new DataLayer();

public DataTable CustomerCRUD(Customer Objp, string ProcName)

{

DataTable dtt = new DataTable();

SqlParameter[] param = new SqlParameter[]

{

new SqlParameter("@action",Objp.action),

new SqlParameter("@customer_id",Objp.customer_id),

new SqlParameter("@customer_name",Objp.customer_name),

new SqlParameter("@address_text",Objp.address_text),

new SqlParameter("@contact_no",Objp.contact_no),

new SqlParameter("@email",Objp.email),

new SqlParameter("@password",Objp.password)

};

dtt = objDL.ExecProc(ProcName, param);

return dtt;

}

public DataTable AutoCompele(string ProcName)

{

DataTable dtt = new DataTable();

SqlParameter[] param = new SqlParameter[]

{

};

dtt = objDL.ExecProc(ProcName, param);

return dtt;

}

Step #5

Create a Customer.cs Class in Models Folder by Right click on Models folder.

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Data;

namespace MyFirstApp.Models

{

[Bind(Exclude = "action")]

public class Customer

{

public int action { get; set; }

public int customer_id { get; set; }