ASP.NET

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

Note

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

Link Ref

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 to the WebGrid control, using the stored procedure in MVC 4.0 .

Step 1

We have to create one table .

Table syntax

  1. USE [YourDataBaseName]  
  2. GO  
  3.   
  4. SET ANSI_NULLS ON  
  5. GO  
  6.   
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9.   
  10. SET ANSI_PADDING ON  
  11. GO  
  12. CREATE TABLE [dbo].[Product](  
  13.     [pid] [bigint] IDENTITY(1,1) NOT NULL,  
  14.     [productID] [bigint] NULL,  
  15.     [Productname] [varchar](20) NULL,  
  16.     [Productprice] [varchar](20) NULL,  
  17.     [ProductDate] [datetime] NULL,  
  18.     [ProductGrade] [char](1) NULL,  
  19. [ProductMfg] [varchar](50) NULL,  
  20. PRIMARY KEY CLUSTERED   
  21. (  
  22.     [pid] ASC  
  23. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  24. ) ON [PRIMARY]  
  25.   
  26. GO  
  27.   
  28. SET ANSI_PADDING OFF  
  29. GO  
We have to insert some dummy data in the table

Result 1


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

Stored procedure syntax
  1. create procedure sp_product  
  2. as  
  3. begin  
  4. select * from Product  
  5. end  
In this stored procedure, the table name “Product” is mentioned (that you created earlier.)
  1. 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”.



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

Step 3

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

Code Ref
  1. <connectionStrings>  
  2.   
  3.     <add name="Mycon" connectionString ="Data Source=Server_Name;User ID=xxxx;Password=xxxx;Initial catalog=Database_Name" providerName="System.Data.SqlClient"/>  
  4.   
  5.  </connectionStrings>  
Here, you can check that I have mentioned one connection string name “Mycon” . This name is the name mentioned by you to access the database.



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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using System.Configuration;  
  8. using Gridsample.Models;  
  9.   
  10. namespace Gridsample.Connection  
  11. {  
  12.     public class Connection  
  13.     {  
  14.         public DataSet mydata()  
  15.         {  
  16.             SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mycon"].ToString());  
  17.             SqlCommand cmd = new SqlCommand("sp_product", con);  
  18.             cmd.CommandType = CommandType.StoredProcedure;  
  19.             SqlDataAdapter da = new SqlDataAdapter();  
  20.             da.SelectCommand = cmd;  
  21.             DataSet myrec = new DataSet();  
  22.             da.Fill(myrec);  
  23.             return myrec;  
  24.         }  
  25.   
  26.     }  
  27. }  
The above mentioned ADO.NET Objects In Connection.cs are described below very clearly. The connection string's name is “Mycon”. The stored procedure name is “sp_product”.



Step 5

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

Code Ref
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace Gridsample.Models  
  7. {  
  8.     public class modeldata  
  9.     {  
  10.         public Int64 pid { get; set; }  
  11.         public Int64 productID { get; set; }  
  12.         public string Productname { get; set; }  
  13.         public string Productprice { get; set; }  
  14.         public DateTime ProductDate { get; set; }  
  15.         public char ProductGrade { get; set; }  
  16.         public string ProductMfg { get; set; }  
  17.   
  18.     }  
  19. }  


Step 6

Create a controller class file GridviewController.cs in Controllers folder.

Code Ref
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using System.Data;  
  7. using Gridsample.Models;  
  8.   
  9. //To access Model class entities namespace added.  
  10.   
  11. namespace Gridsample.Controllers  
  12. {  
  13.     public class GridviewController : Controller  
  14.     {  
  15.   
  16.         public ActionResult grid()  
  17.         {  
  18.             List<modeldata> lmd = new List<modeldata>();    
  19.               
  20.             //creating list of model.  
  21.   
  22.             DataSet ds = new DataSet();  
  23.   
  24.             Connection.Connection con = new Connection.Connection();   
  25.               
  26.             //The connection namespace as we mentioned earlier .  
  27.             //namespace Gridsample.Connection.  
  28.   
  29.             // connection to getdata.  
  30.   
  31.             ds = con.mydata();   
  32.   
  33.              // fill dataset  
  34.   
  35.              
  36.            foreach (DataRow dr in ds.Tables[0].Rows)   
  37.   
  38.              // loop for adding add from dataset to list<modeldata>  
  39.             {  
  40.                 lmd.Add(new modeldata  
  41.                 {  
  42.              // adding data from dataset row in to list<modeldata>  
  43.   
  44.                     pid = Convert.ToInt64(dr["pid"]),   
  45.                     productID = Convert.ToInt64(dr["productID"]),  
  46.                     Productname = dr["Productname"].ToString(),  
  47.                     Productprice = dr["Productprice"].ToString(),  
  48.                     ProductDate = Convert.ToDateTime(dr["ProductDate"]),  
  49.                     ProductGrade = Convert.ToChar (dr["ProductGrade"]),  
  50.                     ProductMfg = dr["ProductMfg"].ToString()  
  51.                 });  
  52.             }  
  53.             return View(lmd);  
  54.   
  55.         }  


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

Step 7

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

Code Ref
  1. @model IEnumerable<Gridsample.Models.modeldata>  
  2.   
  3. @{  
  4.     ViewBag.Title = "grid";  
  5.     WebGrid grid = new WebGrid(Model, rowsPerPage: 4);  
  6. }  
  7. <h2>Grid</h2>  
  8.     <style type="text/css">  
  9.         /*.table 
  10.         { 
  11.             margin: 4px; 
  12.             border-collapse: collapse; 
  13.             width: 300px; 
  14.         }*/  
  15.         .table {  
  16.             font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;  
  17.             font-size: 1.2em;  
  18.             width: 100%;  
  19.             display: table;  
  20.             border-collapse: separate;  
  21.             border: solid 1px blue;  
  22.             background-color: white;  
  23.         }  
  24.         .header{  
  25.             background-color: yellow;  
  26.             font-weight: bold;  
  27.             color: blue;  
  28.             padding-top: 5px;  
  29.             padding: 3px 7px 2px;  
  30.           }  
  31.         .table th, .table td {  
  32.             border: 1px solid black;  
  33.             /*padding: 5px;*/  
  34.             padding: 3px 7px 2px;  
  35.         }  
  36.   
  37.         .webgrid-alternating-row {  
  38.             background-color: pink;  
  39.             padding: 3px 7px 2px;  
  40.         }  
  41.   
  42.         .webgrid-row-style {  
  43.             padding: 3px 7px 2px;  
  44.         }  
  45.         /*for alteranating row style*/  
  46.     </style>  
  47.       
  48. @grid.GetHtml(  
  49.   
  50.     tableStyle: "table", // applying style on grid  
  51.   
  52.     //fillEmptyRows: true,   
  53.     //show empty row when there is only one record on page to it will display all empty rows there.  
  54.          
  55.     headerStyle: "header", //applying style.  
  56.       
  57.     footerStyle: "grid-footer", //applying style.  
  58.     alternatingRowStyle: "webgrid-alternating-row",  
  59.     rowStyle: "webgrid-row-style",  
  60.     mode: WebGridPagerModes.All, //paging to grid   
  61.     firstText: "<< First",  
  62.     previousText: "< Prev",  
  63.     nextText: "Next >",  
  64.     lastText: "Last >>",  
  65.       
  66.     columns: new[]  // colums in grid  
  67.     {  
  68.         grid.Column("productID"), //the model fields to display  
  69.         grid.Column("Productname"  ),  
  70.         grid.Column("Productprice"),  
  71.         grid.Column("ProductDate"),  
  72.         grid.Column("ProductGrade"),  
  73.         grid.Column("ProductMfg")  
  74.    })  
The code mentioned above in 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
  1. routes.MapRoute(  
  2.                 "Default", // Route name  
  3.                 "{controller}/{action}/{id}", // URL with parameters  
  4.                 new { controller = "Gridview", action = "grid", id = UrlParameter.Optional } // Parameter defaults  
  5.             );  
Here, I have mentioned the new thing that 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 app_start folder. Check my previous documents carefully.
  1. Controller anme = "Gridview", action name / View name = "grid" ;  


Output

Out of total 9 records shown in GridView with each page has 4 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 new record, using 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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs