In this article I will explain with an example, how to use StoredProcedure with SqlDataSource and how to pass values to parameters of StoredProcedure using SqlDataSource in ASP.Net.
In this article, a GridView will be populated and filtered using a StoredProcedure and SqlDataSource 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
Passing parameter to StoredProcedure using SqlDataSource 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
![SqlDataSource StoredProcedure example: Pass parameters to StoredProcedure using SqlDataSource in ASP.Net]()
GridView records filtered using StoredProcedure using Parameter
![SqlDataSource StoredProcedure example: Pass parameters to StoredProcedure using SqlDataSource in ASP.Net]()
Demo
Downloads
