{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Changing the values of stored procedure parameters with SqlDataSource
Summary
You have defined a stored procedure that takes parameters. You have created an SqlDataSource that uses these parameters. Now how do you change these parameters at run-time from C# (or VB.Net) code?
 
Table of Contents

The SqlDataSource Control

Various Parameters for the SqlDataSource Control

The Select Events

OnSelecting Event

OnSelected Event

 

By just dragging and dropping the SqlDataSource control on to a form and setting a few parameters, you will have created a data layer that handles the database connectivity, provides you with records to manipulate, etc. This article looks at selecting records from a database via a stored procedure. It focuses on setting the parameter values for that stored procedure just before executing that stored procedure.

The SqlDataSource Control

Let’s say you have dragged an SqlDataSource control (which is in the Data section of the Toolbox) onto a form and set the parameters like the following (article below exaplains these parameters):

<asp:SqlDataSource ID="sdsCustomerListing"
    OnSelected="sdsCustomerListing_Selected"
    OnSelecting="sdsCustomerListing_Selecting"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:YourDB %>"
    ProviderName="<%$ ConnectionStrings: YourDB.ProviderName %>"
    SelectCommandType="StoredProcedure"
    SelectCommand="GetCustomers"
    ConflictDetection="CompareAllValues">
    <SelectParameters>
        <asp:Parameter DefaultValue="%" Name="category" Type="String" />
        <asp:Parameter DefaultValue="%" Name="subcategory" Type="String" />
        <asp:Parameter DefaultValue="1" Name="pageNumber" Type="Int16" />
        <asp:Parameter DefaultValue="5" Name="pageSize" Type="Int16" />
        <asp:Parameter Direction="Output" Name="CustomerCount" Size="3" DefaultValue="" />
    </SelectParameters>
</asp:SqlDataSource>

Various Parameters for the SqlDataSource Control

This SqlDataSource depends on the stored procedure named GetCustomers you would have written in your database. This is set with the two following parameters:

    SelectCommandType="StoredProcedure"
    SelectCommand="GetCustomers"

The GetCustomers stored procedure four input parameters (category, subcategory, pageNumber, and pageSize) and one output parameter (CustomerCount). These parameters can be set graphically by clicking on the SelectQuery property from the Properties window. Using this ’Command and Parameter Editor’, you can set various properties like:

  • DefaultValue (I am using ’%’ as a default value from some parameters above because it comes in handy for using in the LIKE phrases of the query)
  • Direction (whether it’s input or output; here I am returning the total customer count as an output parameter so that I can use it right away on the UI)
  • Type and Size (I can also specify whether it’s an int or string, etc.)

Or, you can enter all the above information declaratively to make up the following parameters for the stored procedure:

    <SelectParameters>
        <asp:Parameter DefaultValue="%" Name="category" Type="String" />
        <asp:Parameter DefaultValue="%" Name="subcategory" Type="String" />
        <asp:Parameter DefaultValue="1" Name="pageNumber" Type="Int16" />
        <asp:Parameter DefaultValue="5" Name="pageSize" Type="Int16" />
        <asp:Parameter Direction="Output" Name="CustomerCount" Size="3" DefaultValue="" />
    </SelectParameters>

This uses the ConnectionString set up in your web.config file. The database information comes from the two following parameters:

    ConnectionString="<%$ ConnectionStrings:YourDB %>"
    ProviderName="<%$ ConnectionStrings: YourDB.ProviderName %>"

The Select Events

With the above parameters, the SqlDataSource now knows:

  • Which database to connect to
  • Which stored procedure to execute
  • What the parameters are

However, it doesn’t have the values for those parameters that it needs to pass on to the stored procedure. These values are known only at the runtime -- basically the user is asking for the customers that are from a particular category and subcategory. Probably you would have provided with dropdown lists of category and subcategory for the end users to select from.

So, you need a mechanism to:

Pass on the parameter values at run time just before executing the select query

Need to know what results came back just after the results have come back so that you can do some additional processing

Above two tasks can be performed from the following two event handlers:

    OnSelecting="sdsCustomerListing_Selecting"
    OnSelected="sdsCustomerListing_Selected"

OnSelecting Event

This is where you will setup the parameters at runtime.

/// <summary>
/// Event that occurs just before the stored procedure is called.
/// The parameters for the stored proc are set here.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void sdsCustomerListing_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    if (Request.QueryString["Category"] != null)
        e.Command.Parameters["@category"].Value = Request.QueryString["Category"];
 
    if (Request.QueryString["SubCategory"] != null)
        e.Command.Parameters["@subcategory"].Value = Request.QueryString["SubCategory"];
 
    if (Request.QueryString["Page"] != null)
        e.Command.Parameters["@pageNumber"].Value = Request.QueryString["Page"];
}

In the above code, the the parameter values are passed on via the query string. If no query string has been passed on, then the default values that we setup in the SelectParameters of the SqlDataSource will be passed on to the stored procedure.

OnSelected Event

Now let’s take a quick look at what do we do when the SELECT query returns.

/// <summary>
/// Selected event occurs just after the results from the
/// stored procedure are feteched.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void sdsCustomerListing_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    // Value from output parameters is now available
    int numCustomers = Convert.ToInt16(e.Command.Parameters["@CustomerCount"].Value.ToString());
 
    // ... Do whatever with the above value ...
 
    // Input parameters are also available
    int pageSize = Convert.ToInt16(e.Command.Parameters["@pageSize"].Value.ToString());
    int pageNumber = Convert.ToInt16(e.Command.Parameters["@pageNumber"].Value.ToString());
 
    // ... Do whatever with the above value ...
 
}

The list of customers the GetCustomers stored procedure returned is available to UI controls (Repeater, DataList, etc.) that are bound to this SqlDataSource. However, you can also do something with the output parameters returned by the query. Here, the value numCustomers has been returned and is accessible from the ..._Selected event handler. Here, for example, you can set up a label that shows the total number of customers, etc.

Bookmark and Share This

More Articles With Similar Tags
This quick snippet shows how to get data using SqlDataSource and attach it to a Repeater to display that data.
Storing HTML in the database with all the HTML tags is straight-forward; however if the entered text has some tags (either HTML or XML) that are to be displayed as they are (i.e. not interpreted as formatting tags), you will run into some trouble with the browser. This article discusses a couple of options in dealing with these issues.
Sometimes you need to execute a stored procedure from the query window to view the results in a quick and dirty way. This article shows how to execute a stored procedure that's already there in the db with output parameters.
About  Contact  Privacy Policy  Site Map