Introduction :
In this tutorial we have learn about about how to Display Data in GridView Using ASP.Net Core MVC with use of sql server database and visual studio 2019.Here we will bind data in razor page using sql server database(store procedure) and visual studio 2019.
Can you also read our previous article : CRUD Operation Using Asp.Net Core Mvc with Repository Pattern In this tutorial we have already implemented crud operation(insert, update,delete,edit, Read) using repository pattern with sql database
Tools we have used ?
Sql server database and visual studio 2019
Step 1: Create Asp.Net Mvc web application
Step 2 : Create sql database
Here we will create sql database and create one table named “Employee” then after create store procedure to retrieve data from database.
Create Sql table “Employee”
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
Store procedure for View all Employee Records
CREATE PROCEDURE [dbo].[EMPLOYEE_SELECT] AS begin select * from Employee end
Step 3 : Create model class
After Creating web application we will create a model class named “EmployeeModel” within model folder
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; }
}
Step 4 : Now Create one folder and named it repository
For data access layer we have used repository patter, so can you create one folder with named “Repository” and add interface with named “IEmployeeRepository.cs” also create one class with named “EmployeeRepository.cs” this class will implement interface methods.
Interface : IEmployeeRepository.cs
public interface IEmployeeRepository
{
List GetAllEmployee();
}
Now we will create class named “EmployeeRepository.cs” here in this class we will implement all interface method.
EmployeeRepository.cs
public class EmployeeRepository : IEmployeeRepository
{
private readonly IConfiguration configuration;
public EmployeeRepository(IConfiguration configuration)
{
this.configuration = configuration;
}
public List GetAllEmployee()
{
try
{
using (var con = new SqlConnection(this.Sqlconnection()))
{
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter("EMPLOYEE_SELECT", con);
adp.Fill(dt);
List lst = new List();
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;
}
}
catch (Exception)
{
throw;
}
}
private string Sqlconnection()
{
return configuration.GetConnectionString("MySqlCon").ToString();
}
}
STEP 5: Create Connection String and get it on Repository Class
Now we have to define our database connection string. As we know Asp.Net Core uses appsettings.json to define your app related all configurations. So, we are going to add our connection string inside the appsettings.json file as follows.
Can you also read previous Tutorial : Asp.Net Core: Read Connection String dynamically from AppSettings.json file
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"MySqlCon": "server=DEVLAP-4;database=MyCoredb;Trusted_Connection=true;"
}
}
To get this connection string on Repository we have already made code with above repository using “SqlConnection()” method that gives us “Connection String” as following code shown.
private string Sqlconnection()
{
return configuration.GetConnectionString("MySqlCon").ToString();
}
Step 6: Create Razor View
Now create Razor view for display data in gridview, so now we will creating below razor view inside the view folder
- Index.cshtml
Step 7 : Implement Code in Razor view for displaying data in gridview
Before adding the code , you have to register your dependency class in Startup.cs as follows inside the ConfigurService method.
Startup.cs
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
services.AddSingleton(Configuration);
services.AddTransient<IEmployeeRepository, EmployeeRepository>();
}
First can you add controller action method for displaying data in gridview.
Home Controller :
public class HomeController : Controller
{
private readonly IEmployeeRepository _employeeRepository;
public HomeController(IEmployeeRepository employeeRepository)
{
_employeeRepository = employeeRepository;
}
public IActionResult Index()
{
return View(_employeeRepository.GetAllEmployee());
}
}
Index.cshtml File
@model IEnumerable<DotNetCoreRepository.Models.EmployeeModel>
@{
ViewData[“Title”] = “Index”;
}
<h5>Display Employee List</h5>
<table class=”table”>
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.UserName)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.State)
</th>
<th>
@Html.DisplayNameFor(model => model.Mobile)
</th>
<th>
@Html.DisplayNameFor(model => model.Profile)
</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.UserName)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.State)
</td>
<td>
@Html.DisplayFor(modelItem => item.Mobile)
</td>
<td>
@Html.DisplayFor(modelItem => item.Profile)
</td>
</tr>
}
</tbody>
</table>
Now, All set up is done and now we can run the application. To run the application just presses F5 and application is ready for run. once application is run
Run application(ASP.Net Core MVC Gridview Example) : Press F5
Summary :
In this tutorial(GridView ASP.Net Core MVC) we have implemented Display Data in GridView(Grid) Using ASP.Net Core MVC with sql server database and ado.net
I hope this tutorial will help you. If you have any doubts please ask your doubts or query in the comment section, Also Please put your feedback on comment section which helps me to improve for upcomming tutorial. And If you like this post then please share it with your friends.
2 Comments