Practical ASP.NET

Dynamically Setting Parameters

At design time, you can tie parameters in the DataSource to a variety of data sources. But sometimes the source for your values can't be set at design time. Here's how to set those values at runtime.

• By Peter Vogel
• 11/12/2009

In last week's column (Dynamic Data Retrieval), I looked at configuring DataSources at runtime to respond to user input. By the end of that column, I'd discussed dynamically picking the source for your data and what fields you would retrieve. However, as I pointed out, it's an unusual query or method that doesn't require setting parameters.

At design time you can usually tie those parameters to controls on the page (or objects in the Session object or cookies or... lots of things). However, if you're setting your retrieval method at run time this isn't going to be possible, especially if you're letting your user select their criteria from options on the page. Even when you aren't using the runtime techniques I covered in last week's column, you may need to set your parameters from data that isn't on the page -- from a property of an object that isn't displayed on the page, for instance.

In this column I'm going to look at setting parameter values from code. And at the end of this column, I'm going to start discussing how to choose between using the DataView and the DataSource where the two objects share functionality.

Setting Parameter Values
In last week's column, I had recommended that you configure your DataSource by setting properties in its Selecting event (this event is fired just before the DataSource retrieves its data). This is also the event where you should set the values for any parameters required by method calls (for the ObjectDataSource) or SQL statements (for the SqlDataSource).

A couple of background points before getting to the code. SQL statements (e.g., "Select * From MyTable Where MyField = @Value") and methods (e.g., GetData(Aparameter)) have parameters. In code, though, you work with Parameter objects. The good news is that the DataSources will generate those Parameter objects for you when you set the SelectCommand (on a SqlDataSource) or the SelectMethod (on the ObjectDataSource). All you have to do in the Selecting event is to retrieve the Parameter object from the appropriate collection on the DataSource and give it a value.

On the ObjectDataSource, the e parameter passed to the Selecting event has a collection named InputParameters that contains the Parameter objects for the method being called. This example retrieves a Parameter object named Aparameter and sets it to the string "SomeValue":

e.InputParameters("Aparameter") = "SomeValue"

In the SqlDataSource, the e parameter has a Property called Command and it's on that object that you'll find the Parameters collection. This code creates an object from a control on the page, retrieves a parameter by name, and sets the parameter's value to a property on the object:

Dim sp As SalesPerson
sp = New SalesPerson(Me.SalesPersonId.Text)
e.Command.Parameters("@Value").Value = sp.Region

A little known fact is that the ObjectDataSource, when retrieving data, causes the DataSource to actually raise its Selecting event twice: Once when getting the objects and once when getting the count of the objects (and in that order). If, as I've recommended, you're putting code in the Selecting event, you may only want to execute that code once (typically, on the first call, when the objects are being retrieved). You can check for that condition by seeing if the e parameter's ExecutingSelectCount property is set to False as this code does:

If e.ExecutingSelectCount = False Then
   …code to configure the DataSource…
End If

DataViews and DataSources
While the Selecting event is a great place to configure a DataSource and to set parameter values, there are some additional operations you can perform in the Selecting event when using the SqlDataSource. The SQLDataSource provides this additional functionality through the Arguments property on the e parameter. This includes the ability, for instance, to create custom paging solutions or to control how the data you're retrieving is to be sorted.

However, my best advice is to let the DataView controls handle these functions. For instance, while I can control sort order in the DataSource, I've never taken advantage of that feature. I prefer using the Sorting event on the DataView (see my columns Extend Sorting in the GridView and Sorting in the ObjectDataSource).

Because of that, next week I'll move on from just retrieving data to doing updates. We'll still be looking at events, but next week it will be events fired by the DataView.