CRUD Operation Using Asp.Net Core Razor Page with Ado.Net

Introduction :

This article will demonstrate you about how to perform CRUD operations with Asp.Net Core Razor Page using Ado.Net,here we are create simple employee management system to and perform insert,edit,delete and display operation.

Tools we have used here

Visual studio 2019 and Asp.Net core 3.1 and sqlserver database

To complete crude operation we have to perform below some steps.

STEP 1: Create Razor Page in Asp.Net Core 3.1

I hope, you have installed the lasted version of Visual Studio 2019 with .Net Core SDK 3.1, so open Visual Studio and Create new Asp.Net core web application, Also can you select here asp.net core 3.1 from framework selection dropdown.

CRUD Operation with Asp.Net Core Razor Page with Ado.Net

Step 2 : Once creating project add Employee folder under pages folder, and add razor pages under pages folder as mentioned in below image.

Asp.Net core 3.1 Razor page

Step 3 : Create Database and Table

Create Employee Table


CREATE TABLE [dbo].[Employee](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[Profile] [nvarchar](50) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
[UserID] 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

Step 4 : Now Create two class EmployeeModel.cs and EmployeeService.cs for database operation

In this demonstration, we will perform CRUD operations for Employee. How to see Employee data, how to add a new Employee, how to edit existing Employee and how to delete existing Employee.

EmployeeModel.cs:

    public class EmployeeModel
    {
            public int UserID { get; set; }
            public string UserName { get; set; }
            public string City { get; set; }
            public string State { get; set; }
            public string Mobile { get; set; }
            public string Profile { get; set; }

    }

EmployeeService.cs : In this class we have create logic for crude(insert,update,delete,display) operations


public class EmployeeService
    {
        public void InsertEmployee(EmployeeModel em)
        {
            SqlConnection con = new SqlConnection(Startup.connectionstring);

            SqlCommand cmd;
            con.Open();
            cmd = new SqlCommand("Employee_InsertUpdate", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserID", em.UserID);
            cmd.Parameters.AddWithValue("@UserName", em.UserName);
            cmd.Parameters.AddWithValue("@City", em.City);
            cmd.Parameters.AddWithValue("@State", em.State);
            cmd.Parameters.AddWithValue("@Mobile", em.Mobile);
            cmd.Parameters.AddWithValue("@Profile", em.Profile);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public List<EmployeeModel>EmployeeList()
        {
            SqlConnection con = new SqlConnection(Startup.connectionstring);

            DataTable dt = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter("EMPLOYEE_SELECT", con);
            adp.Fill(dt);

            List<EmployeeModel>lst = new List<EmployeeModel>();

            foreach (DataRow dr in dt.Rows)
            {
                EmployeeModel em = new EmployeeModel();
                em.UserID = Convert.ToInt32(dr["UserID"]);
                em.UserName = Convert.ToString(dr["UserName"]);
                em.Mobile = Convert.ToString(dr["Mobile"]);
                em.Profile = Convert.ToString(dr["Profile"]);
                em.State = Convert.ToString(dr["State"]);
                em.City = Convert.ToString(dr["City"]);
                lst.Add(em);
            }
            return lst;
        }

        public EmployeeModel GetEmployeeByUserID(int UserId)
        {
            SqlConnection con = new SqlConnection(Startup.connectionstring);

            EmployeeModel em = new EmployeeModel();
            con.Open();
            SqlCommand cmd = new SqlCommand("EMPLOYEE_SELECT",con);
            cmd.Parameters.AddWithValue("UserID", UserId);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                em.UserID = Convert.ToInt32(dr["UserID"]);
                em.UserName = Convert.ToString(dr["UserName"]);
                em.Mobile = Convert.ToString(dr["Mobile"]);
                em.Profile = Convert.ToString(dr["Profile"]);
                em.State = Convert.ToString(dr["State"]);
                em.City = Convert.ToString(dr["City"]);
            }
            con.Close();
            return em;
        }

        public void DeleteEmployee(int UserID)
        {
            SqlConnection con = new SqlConnection(Startup.connectionstring);
            SqlCommand cmd;
            con.Open();
            cmd = new SqlCommand("Employee_Delete", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserID", UserID);
            cmd.ExecuteNonQuery();
            con.Close();
        }


    }

Step 5: Now for Create connection string on EmployeeService.cs see previous tutorial : .Net Core: Read Connection String from AppSettings.json file

Step 6: Create Razor Pages for CRUD Operations

Index.cshtml File

@page “/EmployeeList”
@model WebApplication1.Pages.Employee.IndexModel
@{

}

<div class=”text-center”>
<table class=”table”>
<thead>
<tr>
<th>
UserID
</th>
<th>
UserName
</th>
<th>
City
</th>
<th>
State
</th>
<th>
Mobile
</th>
<th>
Profile
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (EmployeeModel item in Model.EmpList)
{
<tr>
<td>
@item.UserID
</td>
<td>
@item.UserName
</td>
<td>
@item.City
</td>
<td>
@item.State
</td>
<td>
@item.Mobile
</td>
<td>
@item.Profile
</td>
<td>
<form method=”post“>
<a asp-page=”/Employee/EditEmployee” asp-route-id=”@item.UserID” style=”height: 29px;” >
Edit
</a> <nbsp;></nbsp;>

<button type=”submit” asp-page-handler=”Delete” asp-route-UserID=”@item.UserID” style=”height: 27px;”>
Delete
</button>
</form>

</td>
</tr>
}
</tbody>
</table>
</div>

Index.cshtml.cs File

This is basically code-behind class for Index.cshtml file which contains the code for getting the data and delete the selected records.

 public class IndexModel : PageModel
    {
        public List<EmployeeModel> { get; set; }
        public void OnGet()
        {
            EmployeeService es = new EmployeeService();
            EmpList = es.EmployeeList();
        }

        public IActionResult OnPostDelete(int UserID)
        {
            if (UserID > 0)
            {
                EmployeeService es = new EmployeeService();
                es.DeleteEmployee(UserID);
                RedirectToPage("/Employee/Index");
                return Page();
            }
            return Page();
        }
    }

Insert.cshtml File

@page “/InsertEmplyee”
@model WebApplication1.Pages.Employee.InsertModel
@{
}

Insert.cshtml.cs File

This is code-behind file for Insert.cshtml. It has a responsibility to add a new record and record added successfully then redirect to Employee’s home page.

    public class InsertModel : PageModel
    {
        [BindProperty]
        public EmployeeModel employee { get; set; }
        public void OnGet()
        {
        }

        public ActionResult OnPost(EmployeeModel em)
        {
            EmployeeService es = new EmployeeService();
            es.InsertEmployee(employee);
            return RedirectToPage("/Employee/Index");
        }
    }

EditEmployee.cshtml File

This Page is responsible for editing the existing record.

Facebook

@page “/EditEmployee/{Id:int}”
@model WebApplication1.Pages.Employee.EditEmployeeModel
@{
}

@section Scripts {
@{await Html.RenderPartialAsync(“_ValidationScriptsPartial“);}
}

EditEmployee.cshtml.cs File


    public class EditEmployeeModel : PageModel
    {

        [BindProperty]
        public EmployeeModel employee { get; set; }
        public void OnGet(int Id)
        {
            EmployeeService es = new EmployeeService();
            employee = es.GetEmployeeByUserID(Id);
        }

        public ActionResult OnPost(EmployeeModel em)
        {
            EmployeeService es = new EmployeeService();
            es.InsertEmployee(employee);

            return RedirectToPage("/Employee/Index");
        }
    }

Apart from that to bind the data automatically with model, we are using [BindProperty] attribute.

        [BindProperty]
        public EmployeeModel employee { get; set; }

Now, everything has set up and now we can run the application. To run the application just presses F5 and application is ready for crude operation. You have to go to the following URL to perform CRUD operations for employee management system.

http://localhost:63282/InsertEmplyee

Insert Employee operation:

Asp.Net core insert operation

List Of Employee Data :

asp.net core grid data

You can perform Edit and Delete operations using Edit and Delete button also able to add new employee on link insert employee.

Summary :

This article will demonstrate you about how to perform CRUD operations with Asp.Net Core Razor Page using Ado.Net.

I hope this post will help you. Please put your comment on comment box if you have any doubts or have any suggestion regarding article . Also If you like this post then share it with your friends

codelerner

Pages

Categories

CRUD Operation Using Asp.Net Core Razor Page with Ado.Net

Introduction :

Summary :

1 Comment

Leave a Reply Cancel reply