Visual Basic 2005: A Developer's Notebook

Bind to Data Without Writing Code

Most serious web applications need to retrieve records from a database. In .NET, the database access framework of choice is ADO.NET. However, writing the code to perform common database operations with ADO.NET, such as opening a connection and fetching a table of results, can be tedious and repetitive, which is not what VB programmers have come to expect. To simplify such tasks, ASP.NET 2.0 introduces several new data source controls that greatly simplify the task of retrieving data and binding it to a web page.

Note

With the new ASP.NET data provider controls, you can generate and bind all your database code at design time, without writing a single line of code.

How do I do that?

To use a new ASP.NET 2.0 data source control, all you need to do is to drag it from the Visual Studio toolbox to a web page, configure a few of its properties, and then bind it to other controls that display the data it exposes. When you run the web page, the data source control performs the heavy lifting, contacting your database and extracting the rows you need.

ASP.NET ships with several data source controls, and more are planned. Although the list has changed from build to build, the latest release includes:

SqlDataSource

Interacts with a SQL Server database (Version 7.0 or later).

XmlDataSource

Interacts with XML data from a file or some other data source.

ObjectDataSource

Interacts with a custom object that you create. The next lab, "Bind Web Controls to a Custom Class," provides more details about this technique.

To try out no-code data binding, drag a new SqlDataSource onto the design surface of a web page from the Data section of the toolbox. Then, click the control's smart tag and choose Configure Data Source. Visual Studio will walk you through a short wizard in which you specify the connection string for your database (which is then set in the ConnectionString property) and the query you want to perform (which is then set in the SelectCommand property). Find your database server, and select the Northwind database. Although you can build the query dynamically by selecting the columns in a table, for this example just specify the SQL string "SELECT ContactName FROM Customers".

Note

Other data sources are planned to allow easy retrieval of everything from directory listings to web service data.

When you're finished, Visual Studio will have added a SqlDataSource control tag to your web page, which looks something like this:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
  SelectCommand="SELECT ContactName FROM Customers"
  ConnectionString=
  "Data Source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=Northwind">
</asp:SqlDataSource>

This data source defines a connection to the Northwind database, and a Select operation that retrieves a list of all contact names in the Customers table.

Note

Remember, to modify any ASP. NET control, you have two choices. You can select it and make changes in the Properties window, or you can switch to Source view and edit the control tag.

Binding a control to your data source is easy. To try this out, drag a BulletedList control onto your web page, which you can use to show the list of contact names from the Customers table. Click the smart tag, and select Connect to Data Source. You can then choose the data source you want to use (which is the data source created in the last step) and the field you want to display (which is ContactName).

Here's the finished tag:

<asp:BulletedList ID="BulletedList1" Runat="server" 
  DataSourceID="SqlDataSource1"
  DataTextField="ContactName">
</asp:BulletedList>

Amazingly enough, these two control declarations are all you need to create this data-bound page. When you run the page, the BulletedList will request data from the SqlDataSource, which will fetch it from the database using the query you've defined. You don't need to write a line of code.

For a little more sophistication, you could use another control to filter the list of contacts by some other piece of criteria, like country of residence. This raises a new problem—namely, how can you update the query in the SqlDataSource.SelectCommand according to the value entered in the other control?

ASP.NET solves this problem neatly with parameters. To try it out, start by adding a new data source that fetches a list of customer countries from the database. Here's an example that works in this case:

<asp:SqlDataSource ID="Countries" Runat="server" ConnectionString="..."
  SelectCommand="SELECT DISTINCT Country FROM Customers">
</asp:SqlDataSource>

Next, add a DropDownList control named lstCountries to expose the country list. You can use the same approach as when you wired up the BulletedList, or you can type the tag in by hand. Here's the completed tag you need:

 <asp:DropDownList ID="lstCountries" Runat="server" 
  DataValueField="Country" DataTextField="Country"
  DataSourceID="Countries" AutoPostBack="True">
</asp:DropDownList>

Now you can modify the query that creates the customer list. First, you insert a named parameter into your query. Remember to place an @ symbol at the beginning of the parameter name so SqlDataSource can recognize it. In this example, use @Country. (The @ denotes a named parameter when using the SQL Server provider.)

Here's what the revised data source tag should look like:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
  SelectCommand="SELECT ContactName FROM Customers WHERE Country='@Country'
...
</asp:SqlDataSource>

Next, you add a definition that links the parameter to the appropriate control. Once again, you can configure this information in Visual Studio or by hand. In Visual Studio, select the SqlDataSource control and click the ellipses next to the SelectQuery property in the Properties window. (Truthfully, there is no real SelectQuery. That's just the way Visual Studio exposes the SelectCommand and SelectParameters properties to make it easier to edit them as a single unit at design time.) In this case, you need to create a new control parameter that retrieves the SelectedValue property of the lstCountries control.

Here's the revised data source tag once you've added the parameter definition:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
  SelectCommand="SELECT ContactName FROM Customers WHERE Country=@Country"
  ConnectionString=
  "Data Source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=Northwind"
    
  <SelectParameters>
               <asp:ControlParameter Name="Country" 
      ControlID="lstCountries" PropertyName="SelectedValue">
               </asp:ControlParameter>
               </SelectParameters>
</asp:SqlDataSource>

Note that the name of the control parameter matches the name of the parameter in the SQL expression, with one minor quirk: the leading @ symbol is always left out.

Figure 4-5 shows the completed page. When you select a country from the drop-down list, the bulleted customer list is refreshed with the matching customers automatically. You now have a fair bit of functionality, and still have not written any code.

Figure 4-5. Linked data-bound controls with no code

This example should already suggest possibilities where you can use multiple data source controls. For example, imagine you want to provide a master-detail view of orders by customer. You could use one data source to fill a listbox with customers. When the user selects a customer, you could then use your other data source to perform a query for the linked orders and show it in a different control.

What about...

...reasons not to use the new code-free data-binding controls? Many right-thinking developers steer clear of data-binding techniques because they embed database details into the user-interface code. In fact, that's exactly what this example does, which has negative consequences for maintainability, optimization, and debugging. Quite simply, with database details strewn everywhere in a large site, it's hard to stay consistent.

ASP.NET developers haven't forgotten about this side of things. With a little care, you can use the data source providers and still centralize your database logic. One of the best ways to do so is to use the ObjectDataSource control, which allows you to link to a custom class that you've created with data access code. The next lab, "Bind Web Controls to a Custom Class," demonstrates this technique.

Data sources also provide a useful place to add more advanced functionality. One of the most interesting examples is caching. If you set EnableCaching to True, the data source control will automatically insert the retrieved data into the ASP.NET cache and reuse it in future requests, potentially reducing your database load dramatically. You can configure the amount of time an item is cached by setting the CacheDuration and CacheExpirationPolicy properties.

Where can I learn more?

For more on caching and other advanced scenarios, look up the index entry "data source controls" in the MSDN help library.