ASP.NET version 1.0 and 1.1 were giant leaps forward in making data-driven Web sites easier to create. With only a few lines of code you can create Web sites that dynamically query and update databases, XML files, and more. Furthering the goal of making Web development easier, ASP.NET version 2.0 makes even those few lines of code unnecessary, thanks to a new set of server controls called Data Source controls.

Using the new Data Source controls you can declaratively create a link between the Web user interface (ASP.NET and HTML controls) and the data used to populate them. The standard CRUD (create, read, update, delete) operations are supported, including parameterized queries and stored procedures, all of which are defined using standard HTML syntax. This declarative model allows you to focus on how the data is used instead of how to get to it. It also means you won't need to write and rewrite ADO.NET code. In two- and even three-tiered architectures, Data Source controls put data into the hands of the Web designer faster and earlier, making dynamic data-driven Web sites easier than ever to build.

There are currently six Data Source controls available (pre-beta) with more on the way (see Table 1).

SqlDataSource
The SqlDataSource control is a powerful new control that provides easy access to SQL-based databases such as SQL Server and Oracle. Like other ASP.NET server controls, it can be defined in a page's HTML and instantiated at runtime. All of the control's properties are available as attributes of the HTML tags, which gives the Web developer the freedom to customize the control without writing any code.

To begin defining a SqlDataSource control, start by specifying the basic connection properties. This is done using the ProviderName and ConnectionString properties. The possible options for ProviderName are System.Data.Odbc, System.Data .OleDb, System.Data.SqlClient, and System.Data.OracleClient, which correspond to the standard ADO.NET data providers. The ConnectionString property is dependent on the provider used but has the same syntax as previous versions of ADO.NET.

After setting the connection properties, you can define the operations that the control will support using the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties. You can implement any combination of operations using parameterized SQL queries or stored procedure names. The following code snippet is an example of a simple SqlDataSource control used to return a list of Employees from the Northwind database for binding to a DropDownList.

<asp:sqldatasource id="sqldsEmployeeList" runat="server"
selectcommand="Select FirstName + ' ' + LastName as
FullName, EmployeeID from Employees"
providername="System.Data
.SqlClient"
connectionstring="User ID=bosco;Initial Catalog=Northwind;
Data Source=HERMES-WIN2K3" enablecaching="True"
cacheduration="300">
</asp:sqldatasource>

Note: In ASP.NET v2.0 the attributes for controls default to lowercase.

To bind this Data Source control to a DropDownList, set the new DropDownList.DataSourceID property to the ID of the SqlDataSourceControl. Next, set the DataTextField and DataValueField to display the FullName field and use EmployeeID for the value. The DropDownList control should then look like this:

<asp:dropdownlist id="ddlEmployeeList"
runat="server"
datavaluefield="EmployeeID" datasourceid="sqldsEmployeeList"
datatextfield="FullName" autopostback="True" />

At runtime the Data Source control creates the necessary ADO.NET objects needed to retrieve the data. The ADO.NET controls generated can be a combination of either DataReader/ Command objects or DataAdapter/DataSet objects. The default is the DataAdapter/ Dataset combination, but you can choose which mode to use by setting the DataSourceMode property of the SqlDataSource control to DataReader or DataSet. The DataSet mode allows the data retrieved to be cached as well as sorted and filtered. The DataReader mode is faster but can't be used with the caching, filtering, or sorting features.

Seldom are database queries as simple as the one listed above, and most require that you provide some set of parameters. Support for parameterized queries is another area where this control shines. Each operation can include a ParameterCollection to list the parameters used in the command text. In HTML the ParameterCollection can be defined using <selectparameters>, <insertparameters>, <deleteparameters>, and <updateparameters> tags.

The parameter collections can contain any combination of six different parameter types, each one accessing data elements from the page to fill the Value property of the parameter. Each of the parameter types has one or more properties used to identify the data used to fill the Value property of the parameter. The parameter types and specific properties are shown in Table 2.

Listing 1 shows an example of a SqlDataSource control that has two operations, Select and Update. Each operation uses a stored procedure with parameters filled by other controls. The EmployeeID parameter used in the select operation is linked to the SelectedValue property of the DropDownList from our first SqlDataSource example. We'll bind the new SqlDataSource control to an ASP.NET server control called a DetailsView. The DetailsView control is a new control used to create a table-based display of a single row of a dataset. It provides automatic functionality for editing, deleting, and inserting records based on the operations defined by its Data Source control. It will automatically link itself to the parameters declared in the update operation's ParameterCollection without requiring the developer to specify the parameter type and control IDs.

In addition to the basic data access operations, you can also specify a filter expression for the Data Source, which in turn has its own parameter collection. This allows you to limit the results bound to the Web control based on prior user interactions or even the individual user.

All of the Data Source controls have built-in support for caching. The results of the Select operation can be cached based on the parameter values used to retrieve the data from the database. To enable caching, set the EnableCaching property to "true" and set the CacheDuration property to the number of seconds to hold the data in cache. You can also set the caching expiration policy to either Absolute or Sliding by using the CacheExpirationPolicy property.

Another new feature of ASP.NET v2.0 caching is the ability to specify a cache dependency based on modifications to a database table. To set this cache dependency on a Data Source control, use the SqlCacheDependency property.

While most of the features of the Data Source controls don't require coding, that doesn't mean that it's not an option. The SqlDataSource control defines numerous events that can be handled by custom code for every step in the data access process. There are two events per operation that are fired just before and just after an operation is performed. Table 3 shows a list of events for the SqlDataSource control.

The method to be called for each event can be specified in the HTML using an "onEvent" syntax. For example, if you want to execute some code just before a select operation you would add the "onselect" attribute and set it to the name of the method you want to call. The following example shows a method that is called when the SqlDataSource control from Listing 1 updates a record to cause the DropDownList to requery the data in case the user changed an Employee's name.

void Employee_Updated(object sender, SqlDataSourceStatus-EventArgs e)
{
ddlEmployee.DataBind();
}

Listing 2 shows our completed Web page's HTML source, which will query the Northwind database for a list of employees and then, based on the employee selected in the DropDownList, will query the database for the employee's detailed data to be used by the DetailsView control - no ADO.NET coding and no code-behind.

Conclusion
Using the new Data Source controls in ASP.NET v2.0 you can quickly build very data-centric Web sites. The SqlDataSource control is just one of the helpful new additions to the Visual Studio .NET toolbox. In future articles I'll look at the other Data Source controls and how they can be used to build dynamic menus, read and transform XML data, and query a Web service.

• Source Code
• Table 1
• Table 2
• Table 3