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.

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.

About the Author

Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.

comments powered by Disqus

Featured

  • Visual Studio 2019 for Mac v8.9 Ships with .NET 6 Preview 1 Support

    During its Ignite 2021 online event for IT pros and developers this week, Microsoft shipped Visual Studio 2019 for Mac v8.9, arriving with out-of-the-box support for .NET 6 Preview 1, which the company also released recently.

  • Analyst: TypeScript Now Firmly in Top 10 Echelon (Ruby, Not So Much)

    RedMonk analyst Stephen O'Grady believes TypeScript has achieved the rare feat of firmly ensconcing itself into the top 10 echelon of his ranking, now questioning how high it might go.

  • Black White Wave IMage

    Neural Regression Using PyTorch: Training

    The goal of a regression problem is to predict a single numeric value, for example, predicting the annual revenue of a new restaurant based on variables such as menu prices, number of tables, location and so on.

  • Microsoft Ships Visual Studio 2019 v16.9 Servicing Baseline Release

    Microsoft is urging enterprises and professional coders to standardize on the new Visual Studio 2019 v16.9, a servicing baseline release that's guaranteed to receive official support for an extended period.

Upcoming Events