In this article I will explain how to call and execute a SELECT Stored Procedure with parameters in Entity Framework in ASP.Net using C# and VB.Net.
The parameter value will be passed to the SELECT Stored Procedure and it will return multiple records using Entity Framework model in C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Stored Procedure
You will need to create the following Stored Procedure in the Northwind Database. The Stored Procedure 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 TOP 10 CustomerId
,ContactName
,City
,Country
FROM Customers
WHERE ContactName LIKE '%' + @ContactName + '%'
END
Configuring Entity Framework to use Stored Procedure
Now I will explain the steps to configure and add Entity Framework, connect it with the database and also call and execute the SELECT Stored Procedure with parameters.
You will need to add Entity Data Model to your project using Add New Item Dialog as shown below.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
As soon as you add the Entity Data Model to your project you will be prompted with the following dialog. You need to click YES button.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
Then the Entity Data Model Wizard will open up where you need to select Generate from database option.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
Now the wizard will ask you to connect and configure the connection string to the database.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
You need to select the
1. SQL Server Instance
2. Database
And then click Test Connection to make sure all settings are correct. Finally press OK to move to the next step.
Next you will need to choose the Stored Procedures you need to connect and work with Entity Framework. I have selected the Customers_SearchCustomers Stored Procedure discussed earlier.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
Above was the last step and you should now have the Entity Data Model ready. Now we will need to add the Stored Procedure into the Entity Framework.
Thus you will need to Right Click in the NorthwindModel, click Add and then click Function Import.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
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.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
After selecting the Stored Procedure you need to click on Get Column Information Button.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
After the Button is clicked, you will be presented the columns the Stored Procedure returns along with additional information.
Now you will need to select the Complex RadioButton and click the Create New Complex Type button which will generate a Complex Type Class for holding the Stored Procedure records.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
Finally you need to specify the name of the Complex Type Class and click OK.
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
HTML Markup
The HTML Markup consists of an ASP.Net GridView, a TextBox and a Button.
Customer Name:
<asp:TextBox ID="txtCustomerName" runat="server" />
<asp:Button Text="Search" runat="server" OnClick="SearchCustomers" />
<br />
<br />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" ItemStyle-Width = "80" />
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width = "150" />
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width = "100" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width = "100" />
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespace which is the name of the Model specified earlier.
C#
VB.Net
Executing a Stored Procedure using Entity Framework
The GridView is populated with the records returned from the Stored Procedure in the Page Load event of the page, where the SearchCustomers is called by passing the parameter value blank as we need to populate all records.
When the Search Button is clicked, the SearchCustomers function is called but this time the value of the txtContactName TextBox is passed to the functions which ultimately returns filtered records.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
NorthwindEntities entities = new NorthwindEntities();
gvCustomers.DataSource = entities.SearchCustomers(string.Empty);
gvCustomers.DataBind();
}
}
protected void SearchCustomers(object sender, EventArgs e)
{
NorthwindEntities entities = new NorthwindEntities();
gvCustomers.DataSource = entities.SearchCustomers(txtCustomerName.Text.Trim());
gvCustomers.DataBind();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim entities As New NorthwindEntities()
gvCustomers.DataSource = entities.SearchCustomers(String.Empty)
gvCustomers.DataBind()
End If
End Sub
Protected Sub SearchCustomers(sender As Object, e As EventArgs)
Dim entities As New NorthwindEntities()
gvCustomers.DataSource = entities.SearchCustomers(txtCustomerName.Text.Trim())
gvCustomers.DataBind()
End Sub
Screenshots
GridView displaying all records in Page Load
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
GridView displaying Filtered records
![Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net]()
Demo
Downloads
