Practical ASP.NET

Dynamic Data Retrieval

You can't always make all of your data retrieval decisions at design time -- sometimes you have to wait for the user to tell you what data to get. Peter shows you how to work with a DataSource to retrieve data dynamically at runtime.

• By Peter Vogel
• 11/05/2009

One of my clients gave me a reporting page from Hell: The user would be able to select the database they were drawing data from, the fields that would be displayed and the criteria for selecting records. Normally, in using the ASP.NET DataSources, I can configure the DataSource and, at runtime, just pick up a few parameters from controls on the page. With my client's page, however, I would have to pick up all the settings at runtime.

My first step, as usual, was to put a DataView and a DataSource on the page. But then I deviated from my usual practice: I didn't configure either control. I didn't even attach the DataView to the DataSource.

When a page is first displayed to the user, if a DataView is connected to a DataSource, ASP.NET automatically retrieves the data for the DataView. For this page, however, I don't want the data to be retrieved until the user requests it, so I don't connect the DataView to the DataSource. (For more on deferring retrieval until the user requests it, see http://visualstudiomagazine.com/articles/2008/07/30/deferring-data-retrieval-in-masterdetail-pages.aspx.)

Selecting the Database
To control data access at runtime, I just set properties on the DataSource. The overall process is similar for both the ObjectDataSource and the SqlDataSource, but differs in the details.

For instance, with the SqlDataSource I let the user pick which database to draw from by setting the ConnectionString on the DataSource. This example assumes that there's a RadioButtonList of databases that the user can select from:

Me.SqlDataSource1.ConnectionString = ConfigurationManager. _
       ConnectionStrings(Select Case Me.rdbDatabases.SelectedValue()). _
       ConnectionString 

On the ObjectDataSource, you let the user select where the data comes from by choosing the right factory class. You specify the factory class by setting the TypeName property to the full name of the class. This example assumes the existence of a RadioButtonList that lets the user select a factory class:

Me.ObjectDataSource1.TypeName =  Me.rdbMethods.SelectedValue() 

Picking Fields
Back to the SqlDataSource: To let the user choose which fields to retrieve, you set the SqlDataSource's SelectCommand property to a SQL statement or the name of a stored procedure. The following code assumes that the user can select multiple items from a CheckBoxList of field names. This code loops through the ListItems in the CheckBoxList, adding the fieldnames for whatever ListItems are marked as Selected:

Dim sbFields As New System.Text.StringBuilder()
For Each li As ListItem In Me.chkFields.Items 
  If li.Selected = True Then 
    sbFields.Append(li.SelectedValue & ", ")
Next
sbSQL.Remove(sbSQL.Length-2, 2)
Me.SqlDataSource1.SelectCommand = _
   "Select " & sbFields.ToString() & " From Employees"

Before I get critiqued for concatenating values into SQL statements, let me say two things. First, the prohibition against concatenating SQL statements is a prohibition against using "user-entered values," which may include text that implements a SQL injection attack. I'm not doing that: I'm concatenating in my own values from my own CheckBoxList. Second, you're perfectly free to let the user select between names of stored procedures or between predefined lists of fields rather than require the user to select individual fields, as I do here. If you do use a stored procedure, make sure that you set the DataSource's SelectCommandType property to the StoredProcedure enum value:

Me.SqlDataSource1.SelectCommandType =  _
         SqlDataSourceCommandType.StoredProcedure

With the ObjectDataSource, to control the data retrieved you just set the ObjectDataSource's SelectMethod to the name of the method being used. This example assumes the existence of yet another RadioButtonList to let the user select the method to use:

Me.ObjectDataSource1.SelectMethod = Me.rdbMethods.SelectedValue() 

Timing
The question now is when you should make these changes. The answer is: At the last possible moment.

For my "page from hell", I put a button on the page that lets the user retrieve data. In the Click event for the button, I connect the DataView to the DataSource by setting the DataView's DataSourceId to the Id of the DataView. I then call the DataView's DataBind method to fetch the data:

Me.MyDataView.DataSourceId = Me.MyDataSource.Id
Me.MyDataView.DataBind()

When you call the DataBind method of a DataView, the DataView reaches over to the DataSource and calls the DataSource's Select method. This, in turn, causes the DataSource to fire its Selecting event just before retrieving the data. It's in that event that you should set the properties I've shown above.

However, I have one more issue to address. It will be an unusual SQL statement and a very unusual stored procedure or method that doesn't require parameters. But I'll discuss that in next week's column.