Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Stored Procedure
The following
Stored Procedurestored Procedures accepts @
ContactName parameter which is used to perform a search on the records in
Customers Table.
CREATE PROCEDURE Customers_SearchCustomers
@ContactName NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Customers
WHERE ContactName LIKE '%' + @ContactName + '%'
END
Configuring Entity Framework
You will need to configure the
Entity Framework in order to connect to the database.
Configuring Stored Procedure in Entity Framework Model
In order to do so, you will need to Right Click the Entity Model and select Update Model from Database option from the context menu.
![Populate (Bind) WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC]()
Then, inside the Update Wizard, check (select) the Stored Procedure and click Finish.
![Populate (Bind) WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC]()
Thus you will need to Right Click the Entity Model, click Add and then click Function Import.
![Populate (Bind) WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC]()
This will open the Add Function Import dialog window. Here first you need to specify the Function Import Name which is the name of the function used to call the Stored Procedure and then select the Stored Procedure that will be executed when the function is called.
The Return Type is selected as Entities which is the Customer Entity class.
![Populate (Bind) WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC]()
Finally, you will see the function name in the Model Browser.
![Populate (Bind) WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC]()
Controller
The Controller consists of following Action methods.
Action method for handling GET operation
Inside the Index Action method, the Stored Procedure is called using the SearchCustomers function created using the Function Import procedure done earlier.
The parameter ContactName is passed as empty string and hence it gets all records from the Customers table of the Northwind database.
Finally, the list of Customers Entity is returned to the View.
Action method for handling POST operation
When the Form is submitted, the value of the Customer Name TextBox is submitted this Action method and the value is passed as parameter to the SearchCustomers function and the list of Customers Entity is returned to the View.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
NorthwindEntities entities = new NorthwindEntities();
return View(entities.SearchCustomers("").ToList());
}
[HttpPost]
public ActionResult Index(string customerName)
{
NorthwindEntities entities = new NorthwindEntities();
return View(entities.SearchCustomers(customerName).ToList());
}
}
View
HTML Markup
Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that it will be available as a Collection.
The View consists of an HTML Form which has been created using the Html.BeginForm method with the following parameters.
ActionName – Name of the Action. In this case the name is Index.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
Inside the Form, a TextBox is created using the Html.TextBox HTML Helper function and there is a Submit button which when clicked, the Form gets submitted.
WebGrid
The
WebGrid is initialized with the Model i.e.
IEnumerable collection of
Customer Entity class objects as source.
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.
Performing HTTP POST on WebGrid Paging
By default, when a Page Number Link is clicked in the
WebGrid, a HTTP GET called is performed and hence with the help of
jQuery the Page Number Links are forced to perform HTTP POST.
Displaying Empty Message
When the searching yields zero records, then an Empty Message such as No records found needs to be displayed.
In order to display the Empty Message, a jQuery script is wrapped inside an IF condition which evaluates to TRUE only when the Model.Count is 0 i.e. no records returned from the Stored Procedure.
The
jQuery script simply adds a new row with one cell to the HTML Table rendered by the
WebGrid and then
Empty Message is set in the Table cell.
@model List<WebGrid_Stored_Proc_MVC.Customer>
@{
Layout = null;
WebGridwebGrid = new WebGrid(source: Model, canSort: false, rowsPerPage: 5);
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { @id = "WebGridForm" }))
{
<span>Customer Name:</span> @Html.TextBox("CustomerName")
<input type="submit" value="Search" />
}
<br />
@webGrid.GetHtml(
htmlAttributes:new { @id = "WebGrid", @class = "Grid" },
columns:webGrid.Columns(
webGrid.Column("CustomerID", "Customer Id"),
webGrid.Column("ContactName", "Customer Name"),
webGrid.Column("City", "City"),
webGrid.Column("Country", "Country")
))
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script type="text/javascript">
$("body").on("click", ".Gridtfoot a", function () {
$('#WebGridForm').attr('action', $(this).attr('href')).submit();
return false;
});
</script>
@if (Model.Count == 0)
{
<script type="text/javascript">
$(function () {
var row = $("#WebGrid")[0].insertRow(-1);
var cell = $(row.insertCell(-1));
cell.html("No records found.");
cell.attr("colspan", "4").attr("align", "center");
});
</script>
}
</body>
</html>
Screenshot
![Populate (Bind) WebGrid using Stored Procedure and Entity Framework in ASP.Net MVC]()
Downloads
