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

Article

Note. Check out my previous article, “Basic Implementation Of WebGrid Control In ASP.NET MVC 4.0.”

Link Ref

Basic Implementation Of WebGrid Control In ASP.NET MVC 4.0

Just go through the link shown above to get a very basic idea about WebGrid in MVC for how to write the code to show the WebGrid in view part as well as the basic functionality.

New Session About Web Grid

In this article, I will show you how to fetch the data dynamically from the database and bind it to the WebGrid control using the stored procedure in MVC 4.0.

Step 1. We have to create one table.

Table syntax

USE [YourDataBaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
    [pid] [bigint] IDENTITY(1,1) NOT NULL,
    [productID] [bigint] NULL,
    [Productname] [varchar](20) NULL,
    [Productprice] [varchar](20) NULL,
    [ProductDate] [datetime] NULL,
    [ProductGrade] [char](1) NULL,
[ProductMfg] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [pid] 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

SET ANSI_PADDING OFF
GO

We have to insert some dummy data in the table.

Result 1

Dummy data in the table

Now, we have to create one stored procedure for select statement.

Stored procedure syntax

create procedure sp_product
as
begin
select * from Product
end

In this stored procedure, the table name “Product” is mentioned (that you created earlier.)

exec sp_product

To know or show the data, execute the stored procedure. It will show the above-mentioned records that you have inserted earlier.

Step 2. Create a project named “Gridsample”.

Create project Gridsample

You can give any name, per your wish or requirement. To know more, follow my blogs for a better understanding.

Step 3. Put connection string in web.config file to connect to the database and access the data.

Code Ref

<connectionStrings>
    <add name="Mycon" connectionString ="Data Source=Server_Name;User ID=xxxx;Password=xxxx;Initial catalog=Database_Name" providerName="System.Data.SqlClient"/>
 </connectionStrings>

Here, you can check that I have mentioned one connection string name “Mycon” . This name is the name mentioned to you to access the database.

Connection string name

Step 4. Create a Connection.cs class file in a Connection folder to connect to the database and execute the stored procedure to show the data.

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Gridsample.Models;

namespace Gridsample.Connection
{
    public class Connection
    {
        public DataSet mydata()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mycon"].ToString());
            SqlCommand cmd = new SqlCommand("sp_product", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet myrec = new DataSet();
            da.Fill(myrec);
            return myrec;
        }

    }
}

The above-mentioned ADO.NET Objects In Connection.cs are described clearly below. The connection string's name is “Mycon”. The stored procedure name is “sp_product”.

ADO.NET Objects In Connection.cs

Step 5. Create a model class file named modeldata.cs to declare some entities, which should be the same as the table parameter in the stored procedure to access the data from the database.

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Gridsample.Models
{
    public class modeldata
    {
        public Int64 pid { get; set; }
        public Int64 productID { get; set; }
        public string Productname { get; set; }
        public string Productprice { get; set; }
        public DateTime ProductDate { get; set; }
        public char ProductGrade { get; set; }
        public string ProductMfg { get; set; }

    }
}

model class file named modeldata.cs

Step 6. Create a controller class file GridviewController.cs in the Controllers folder.

Code Ref

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" ;

routeconfig.cs file

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

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

Happy coding.