In this article, I’ll go over how to link a DataTable (DataSet) to a WebGrid in ASP.Net Core MVC using an example.
ADO.Net will be utilised to populate the DataTable (DataSet) from Database Tables before using that data to populate the WebGrid in ASP.Net Core MVC.
MVC6 Grid for ASP.Net Core
As the WebGrid library is not included by default in.Net Core, this tutorial uses it to implement WebGrid.
Please see the article WebGrid Step By Step Tutorial with Example in ASP.Net Core MVC for more information on using MVC6 Grid.
Database:
I’m using Microsoft’s Northwind Database here. This is where you may download it.
Download and install Northwind Database
NameSpace:
You must import the namespaces listed below.
using System.Data; using System.Data.SqlClient;
Controller:
The following Action method is part of the Controller.
GET operation handling action method
The Customers Table’s Top 10 entries are retrieved using ADO.Net within this Action function.
Using a class object of the SqlDataAdapter class, the records are added to a DataSet.
The DataSet is then delivered back to the View.
public class HomeController : Controller
{
public IActionResult Index()
{
string constr = @"Data Source=.\SQL2019;Initial Catalog=Northwind;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string query = "SELECT TOP 10 CustomerID, ContactName, City, Country FROM Customers";
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return View(ds);
}
}
}
}
}
}
View:
The DataSet is identified as the View’s Model in the View’s very first line.
The Programme.NonFactors, Data.The View inherits the mvc.grid namespaces and mvc-grid.css file.
The DataTable is provided to the MVC6 Grid HTML Helper class’s Grid method.
The MVC6 Grid HTML Helper class’s Build function was used to add the columns.
A foreach loop will be run through the Columns inside the Build function of the MVC6 Grid HTML Helper class.
The Add method was used to dynamically add columns inside the loop, and the Titled function was used to set the column’s header text after receiving the column name.
@model DataSet
@using System.Data;
@using NonFactors.Mvc.Grid;
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/css/mvc-grid/mvc-grid.css" rel="stylesheet" />
</head>
<body>
@(Html.Grid(Model.Tables[0].Rows.Cast<DataRow>())
.Build(columns =>
{
foreach (DataColumn column in Model.Tables[0].Columns)
{
columns.Add(model => model[column.ColumnName]).Titled(column.ColumnName);
}
})
)
</body>
</html>
