Bind Data In WebGrid Control Using Stored Procedure In ASP.NET MVC 4.0 And SQL Server

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.Mvc;  
using System.Data;  
using Gridsample.Models;  
  
//To access Model class entities namespace added.  
  
namespace Gridsample.Controllers  
{  
    public class GridviewController : Controller  
    {  
  
        public ActionResult grid()  
        {  
            List<modeldata> lmd = new List<modeldata>();                  
            //creating list of model.  
  
            DataSet ds = new DataSet();    
            Connection.Connection con = new Connection.Connection();   
              
            //The connection namespace as we mentioned earlier .  
            //namespace Gridsample.Connection.  
  
            // connection to getdata.    
            ds = con.mydata();   
  
             // fill dataset  
  
             
           foreach (DataRow dr in ds.Tables[0].Rows)   
  
             // loop for adding add from dataset to list<modeldata>  
            {  
                lmd.Add(new modeldata  
                {  
             // adding data from dataset row in to list<modeldata>  
  
                    pid = Convert.ToInt64(dr["pid"]),   
                    productID = Convert.ToInt64(dr["productID"]),  
                    Productname = dr["Productname"].ToString(),  
                    Productprice = dr["Productprice"].ToString(),  
                    ProductDate = Convert.ToDateTime(dr["ProductDate"]),  
                    ProductGrade = Convert.ToChar (dr["ProductGrade"]),  
                    ProductMfg = dr["ProductMfg"].ToString()  
                });  
            }  
            return View(lmd);  
  
        }  

The code mentioned above, described with a red comment line, is for better understanding.

Step 7. Create a view called the grid.cshtml Inside Gridview folder of Views folder.

Code Ref

@model IEnumerable<Gridsample.Models.modeldata>  
  
@{  
    ViewBag.Title = "grid";  
    WebGrid grid = new WebGrid(Model, rowsPerPage: 4);  
}  
<h2>Grid</h2>  
    <style type="text/css">  
        /*.table 
        { 
            margin: 4px; 
            border-collapse: collapse; 
            width: 300px; 
        }*/  
        .table {  
            font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;  
            font-size: 1.2em;  
            width: 100%;  
            display: table;  
            border-collapse: separate;  
            border: solid 1px blue;  
            background-color: white;  
        }  
        .header{  
            background-color: yellow;  
            font-weight: bold;  
            color: blue;  
            padding-top: 5px;  
            padding: 3px 7px 2px;  
          }  
        .table th, .table td {  
            border: 1px solid black;  
            /*padding: 5px;*/  
            padding: 3px 7px 2px;  
        }  
  
        .webgrid-alternating-row {  
            background-color: pink;  
            padding: 3px 7px 2px;  
        }  
  
        .webgrid-row-style {  
            padding: 3px 7px 2px;  
        }  
        /*for alteranating row style*/  
    </style>  
      
@grid.GetHtml(  
  
    tableStyle: "table", // applying style on grid  
  
    //fillEmptyRows: true,   
    //show empty row when there is only one record on page to it will display all empty rows there.  
         
    headerStyle: "header", //applying style.  
      
    footerStyle: "grid-footer", //applying style.  
    alternatingRowStyle: "webgrid-alternating-row",  
    rowStyle: "webgrid-row-style",  
    mode: WebGridPagerModes.All, //paging to grid   
    firstText: "<< First",  
    previousText: "< Prev",  
    nextText: "Next >",  
    lastText: "Last >>",  
      
    columns: new[]  // colums in grid  
    {  
        grid.Column("productID"), //the model fields to display  
        grid.Column("Productname"  ),  
        grid.Column("Productprice"),  
        grid.Column("ProductDate"),  
        grid.Column("ProductGrade"),  
        grid.Column("ProductMfg")  
   })  

The code mentioned above in the cshtml file is described in green mark with the comment line. Also, check my previous article, as mentioned in the link URL above.

Step 8. Now, I have to set the start page at the page load time In Global.asax.cs file.

Code Ref

routes.MapRoute(  
                "Default", // Route name  
                "{controller}/{action}/{id}", // URL with parameters  
                new { controller = "Gridview", action = "grid", id = UrlParameter.Optional } // Parameter defaults  
            );  

Here, I have mentioned a new thing: you can set the start page using global.asax.cs like routeconfig.cs file in app_start folder.

In my previous documents, I mentioned the start page using routeconfig.cs file in the app_start folder. Check my previous documents carefully.

Controller anme = "Gridview", action name / View name = "grid" ;  

Output

Out of the total of 9 records shown in GridView, each page has four records, as mentioned in grid.cshtml, and the last page shows only one record.

Now, I will show you a new record by inserting a new record in the table “Product”. Check Result 1 in the table output records.

Step 9. Result 2

The new record is created by executing the stored procedure.

Step 10. Output In GridView is shown below after inserting a new record using the back end.

Now, the data connection is established, and the records are fetched from the database, as it is working successfully.

Similarly, you can bind the WebGrid with the database, using the stored procedure or using SQL Server in ASP.NET MVC 4.0.

Summary

1. How to bind the WebGrid to the database and fetch the data.
2. ADO.NET is the concept used in MVC to fetch the records and bind to the WebGrid.
3. How to execute the stored procedure by inserting the new records, which are shown in the WebGrid.
4. For other functionalities of the WebGrid, you can check my previous documents.

Happy coding.