In this article I will explain with an example, how to search database records using Stored Procedure and display the results using GridView control in ASP.Net.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
StoredProcedure
The following StoredProcedure accepts a parameter @ContactName and its value is used to search records in the Customers table of the Northwind database using LIKE operator.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Customers_SearchCustomers]
@ContactName NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerId
,ContactName
,City
,Country
FROM Customers
WHERE ContactName LIKE @ContactName + '%'
END
Implement Search using Stored Procedure in ASP.Net
The following HTML Markup consists of an ASP.Net GridView control, a TextBox and a Button.
The GridView is populated with records from the Customers table of the Northwind database using SqlDataSource.
The SqlDataSource has been set with following properties.
SelectCommand – Name of the StoredProcedure to be executed.
SelectCommandType – Value set as StoredProcedure. Default value is Text.
The SqlDataSource also has a SelectParameter. Here SelectParameter of type ControlParameter is used as the value of an ASP.Net control i.e. TextBox needs to be passed as parameter.
The ControlParameter is set with the following properties.
Name – Name of the parameter being passed to StoredProcedure. The name of the parameter must be exact same as the StoredProcedure parameter name.
ControlID – ID of the control whose value will be passed as Parameter.
DefaultValue – The StoredProcedure used in this article displays all records when the parameter value is blank or empty string and the default value is set to blank.
ConvertEmptyStringToNull – As the name suggests it will pass blank or empty string as NULL if set to True. Since here blank or empty string needs to be passed, the value is set to False.
Search Customer:
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search"/>
<hr/>
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
runat="server" AutoGenerateColumns="false" DataSourceID="GridDataSource" AllowPaging="true">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150"/>
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150"/>
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150"/>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConStr %>"
SelectCommand="Customers_SearchCustomers" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="ContactName" ControlID="txtSearch" PropertyName="Text"
DefaultValue="" ConvertEmptyStringToNull="false"/>
</SelectParameters>
</asp:SqlDataSource>
Screenshots
GridView displaying all records
![Implement Search using Stored Procedure in ASP.Net]()
GridView records filtered using StoredProcedure
![Implement Search using Stored Procedure in ASP.Net]()
Demo
Downloads
