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.
Step 2 : Once creating project add Employee folder under pages folder, and add razor pages under pages folder as mentioned in below image.
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
@{
}
<div class=”row”>
<div class=”col-md-4″>
<form method=”post“>
<div asp-validation-summary=”ModelOnly” class=”text-danger”></div>
<div class=”form-group”>
<label asp-for=”employee.UserName” class=”control-label”></label>
<input asp-for=”employee.UserName” class=”form-control” />
<span asp-validation-for=”employee.UserName” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.City” class=”control-label”></label>
<input asp-for=”employee.City” class=”form-control” />
<span asp-validation-for=”employee.City” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.State” class=”control-label”></label>
<input asp-for=”employee.State” class=”form-control” />
<span asp-validation-for=”employee.State” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.Mobile” class=”control-label”></label>
<input asp-for=”employee.Mobile” class=”form-control” />
<span asp-validation-for=”employee.Mobile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.Profile” class=”control-label”></label>
<input asp-for=”employee.Profile” class=”form-control” />
<span asp-validation-for=”employee.Profile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<input type=”submit” value=”Create” class=”btn btn-primary” />
</div>
</form>
</div>
</div>
<div>
<a asp-page=”/Employee/Index“>Back to List</a>
</div>
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.
@page “/EditEmployee/{Id:int}”
@model WebApplication1.Pages.Employee.EditEmployeeModel
@{
}
<div class=”row”>
<div class=”col-md-4″>
<form method=”post“>
<div asp-validation-summary=”ModelOnly” class=”text-danger”></div>
<div class=”form-group”>
<input asp-for=”employee.UserID” type=”hidden” />
<label asp-for=”employee.UserName” class=”control-label”></label>
<input asp-for=”employee.UserName” class=”form-control” />
<span asp-validation-for=”employee.UserName” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.City” class=”control-label”></label>
<input asp-for=”employee.City” class=”form-control” />
<span asp-validation-for=”employee.City” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.State” class=”control-label”></label>
<input asp-for=”employee.State” class=”form-control” />
<span asp-validation-for=”employee.State” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.Mobile” class=”control-label”></label>
<input asp-for=”employee.Mobile” class=”form-control” />
<span asp-validation-for=”employee.Mobile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”employee.Profile” class=”control-label”></label>
<input asp-for=”employee.Profile” class=”form-control” />
<span asp-validation-for=”employee.Profile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<input type=”submit” value=”Create” class=”btn btn-primary” />
</div>
</form>
</div>
</div>
<div>
<a asp-page=”/Employee/Index“>Back to List</a>
</div>
@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:
List Of Employee 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
Also Learn More Tutorial :
- ASP.Net Core: Form Submit (Post) with Example
- File(Image) Upload in ASP.NET Core MVC with Example
- .Net Core: Read Connection String from AppSettings.json file
- CRUD Operation Using Asp.Net Core Mvc with Repository Pattern
- Display Data in GridView(Grid) Using ASP.Net Core MVC
- Asp.Net Core: DbContext In EntityFramework core
- Difference between AddSingleton vs AddScoped vs AddTransient in asp.net core
- Difference between .NET Core and .NET Framework
- Difference between TempData keep() And Peek() in Asp.Net MVC
- Asp.Net Image Upload in 3-Tier Architecture and store in sql database
- ViewImports in ASP.NET Core MVC
1 Comment