In this article I will explain with an example, how to populate (bind) WebGrid using ADO.Net in ASP.Net MVC Razor.
Inside the Controller, the records from the Database Table will be fetched using traditional ADO.Net method i.e. using SqlConnection, SqlCommand, SqlDataAdapter, etc.
The fetched records will be populated into a DataSet which will be ultimately used to populate the WebGrid in ASP.Net MVC Razor.
 
 
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
Populate (Bind) WebGrid using ADO.Net in ASP.Net MVC
 
I have already inserted few records in the table.
Populate (Bind) WebGrid using ADO.Net in ASP.Net MVC
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
 
Controller
The Controller consists of the Index Action method. Inside this Action method, the records are fetched from the Customers Table using ADO.Net.
The records are inserted into a DataSet using SqlDataAdapter class object. Finally the DataSet is returned to the View.
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        DataSet ds = new DataSet();
        string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            string query = "SELECT * FROM Customers";
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds);
                }
            }
        }
 
        return View(ds);
    }
}
 
 
View
Inside the View, the DataSet is declared as Model for the View.
The WebGrid is initialized with the Dynamic Anonymous Type collection by making use of LINQ i.e. the records from the DataTable are converted into Dynamic Anonymous Type collection and are assigned to the WebGrid.
The WebGrid is created using the GetHtml method with the following parameters.
HtmlAttributes – It is used to set the HTML attributes to the HTML Table generated by WebGrid such as ID, Name, Class, etc.
Columns – It is used to specify the columns to be displayed in WebGrid and also allows to set specific Header Text for the columns.
@using System.Data
@using System.Linq
 
@model DataSet
@{
    Layout = null;
    WebGrid webGrid = new WebGrid(source: (from p in Model.Tables[0].AsEnumerable()
                                            select new
                                            {
                                                CustomerId = p.Field<int>("CustomerId"),
                                                Name = p.Field<string>("Name"),
                                                Country = p.Field<string>("Country")
                                            }));
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width"/>
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }
 
        .Grid {
            border: 1px solid #ccc;
            border-collapse: collapse;
        }
 
        .Grid th {
            background-color: #F7F7F7;
            font-weight: bold;
        }
 
        .Grid th, .Grid td {
            padding: 5px;
            border: 1px solid #ccc;
        }
 
        .Grid, .Grid table td {
            border: 0px solid #ccc;
        }
 
        .Grid th a, .Grid th a:visited {
            color: #333;
        }
    </style>
</head>
<body>
    @webGrid.GetHtml(
    htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
    columns: webGrid.Columns(
                webGrid.Column("CustomerId", "Customer Id"),
                webGrid.Column("Name", "Name"),
                webGrid.Column("Country", "Country")))
</body>
</html>
 
 
Screenshot
Populate (Bind) WebGrid using ADO.Net in ASP.Net MVC
 
 
Downloads
Download Code