In this article I will explain with an example, how to use Optional Parameters with SqlDataSource and how to pass Optional parameters to 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 an optional parameter @CustomerID and its value is used to fetch the Customer from the Customers table of the Northwind database.
It is termed as optional parameter, since if the value of the parameter is passed blank or empty string then it will return all records present in the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Customers_GetCustomer]
@CustomerID NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerId
,ContactName
,City
,Country
FROM Customers
WHERE (CustomerID = @CustomerID OR @CustomerID = '')
END
Passing optional 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.
Customer ID:
<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_GetCustomer" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="CustomerID" ControlID="txtSearch" PropertyName="Text" DefaultValue="" ConvertEmptyStringToNull="false" />
</SelectParameters>
</asp:SqlDataSource>
Screenshots
GridView displaying all records
![SqlDataSource Optional Parameters: Pass Optional parameters to Stored Procedure using SqlDataSource in ASP.Net]()
GridView records filtered using StoredProcedure using Optional Parameter
![SqlDataSource Optional Parameters: Pass Optional parameters to Stored Procedure using SqlDataSource in ASP.Net]()
Demo
Downloads
