LANGUAGES: VB.NET
ASP.NET VERSIONS: 2.0
Almost any Web application you create will need some kind of data access. Fortunately, ASP.NET 2.0 makes this extremely easy to do. Unlike ASP.NET 1.x, which required developers to write custom code to retrieve and bind data to server controls, ASP.NET 2.0 provides a declarative solution for creating data-driven Web applications, which requires no code at all for the most common data scenarios such as sorting, paging, caching, updating, inserting, deleting, filtering, and so on. These new data source controls that ship with ASP.NET 2.0 are a major addition to this platform that can go a long way in realizing Microsoft s goal of 70% code reduction.
In this article, we ll come to understand the different data source controls provided by ASP.NET 2.0, as well as their functionalities. After that, we ll dive deep into the SqlDataSource control and understand how to use this control to create data-driven Web applications. Along the way, we ll also demonstrate some of the advanced concepts of the SqlDataSource control, such as enabling caching, declaratively passing values to the SQL statement directly from a control, and so on.
One of the limitations of ASP.NET 1.x is that it does not provide a declarative model for binding data to data-aware controls such as DataGrid, DataList, and Repeater. Now in ASP.NET 2.0, you have a very powerful and easy-to-use declarative model for binding data directly from the database. By enabling the following capabilities, this new declarative model allows you to create dynamic data driven Web applications without writing any lines of code:
- sorting, paging, and caching data
- updating, inserting, and deleting data
- filtering master-details using parameters
ASP.NET 2.0 provides many new data controls. Before we take a look at this new set of controls, let s understand the two types of data controls. They are as follows:
- Data Source Controls. Data source controls have no rendering, but instead represent a particular back-end data store; for example, a database, business object, XML file, or XML Web service. Data source controls also enable rich capabilities over data, such as sorting, paging, filtering, updating, deleting, and inserting, which can then be used by the data bound UI controls. Once you have the data in the data source control, it can then be easily bound to data bound controls such as a dropdownlist or a checkbox, and so on.
- Data Bound Controls. The data bound controls are UI controls that render data as markup to the requesting client browser. A data bound control can auto-bind to data exposed from a data source, and will fetch data at the appropriate time in the page request lifecycle. These controls can also take advantage of data source capabilities, such as sorting, paging, filtering, updating, deleting, and inserting. A data bound control connects to a data source control through its DataSourceID property.
Now that you understand the theory behind these controls, let s look at the data source controls provided by ASP.NET 2.0:
Apart from the above data source controls, ASP.NET 2.0 also provides the following data bound controls that you ll normally use to display data that is contained in the data source controls:
Now that we understand the concepts behind the data source controls and data bound controls, let s take a look at an example.
Creating a Data-driven Web Application
In this section, we ll take a look at an example page to understand how the combination of data source controls and data bound controls can be used to create a data-driven Web page with no code. For the purposes of this example, let s consider the Categories table in the Northwind database that ships with SQL Server; we ll display all the Categories in a GridView control (see Figure 1).
<%@ Page Language="VB" %>
Figure 1: Display Categories in a GridView control.
Let s walk through the code shown in Figure 1. We start by declaring a SqlDataSource control named CategoriesDataSource. As the name suggests, this control is used to get information from the Categories table in the Northwind database. As part of the SqlDataSource control declaration, we also specify the ConnectionString and the SQL statement to be executed as its attributes. In the SelectCommand attribute, we specify the SQL statement to be executed. As you can see, the SQL statement returns everything from the Categories table. The SqlDataSource control can return data in two forms: as a DataSet or as a DataReader, which can be specified by setting the data source control s DataSourceMode property. A DataSet contains all the data in memory, allowing you to manipulate the data in various ways after retrieving it; a DataReader provides a read-only cursor that can fetch individual records. In the GridView control declaration, we set the DataSourceID attribute to the ID of the SqlDataSource control. That s all there is to creating a data source control and then binding it to a data-aware control. As you can see, we have accomplished all of this declaratively without writing even a single line of code. Figure 2 shows the output that results when you execute the code shown in Figure 1.
Figure 2: Run the code from Figure 1
to get this output.
So far, we have seen a simple example that displays all the Categories in a GridView control. In the next example, we ll learn how to supply parameters to the SqlDataSource control.
Passing Parameters to the SqlDataSource Control
In this example, we ll display both Categories and Products information in a single page. We ll display the Categories in a DropDownList, and when the selection changes, we ll refresh the list of products displayed in a GridView control. The code required for implementing this is shown in Figure 3.
<%@ Page Language="VB" %>
Figure 3: Display both Categories and Products information in a single page.
In the example shown in Figure 1, we hard-coded the connection string in the SqlDataSource control declaration itself. But in the example shown in Figure 3, the connection string is retrieved from the web.config file. The connection string entry is stored in the web.config file, as shown here:
With ASP.NET 2.0, there is a new syntax for retrieving the above connection string from the web.config file. For example, using the syntax <%$ ConnectionStrings:Northwind %>, you can retrieve the above connection string from the web.config file. This is demonstrated in the value set in the ConnectionString attribute in the SqlDataSource control declaration.
Then we set the DataSourceID property of the DropDownList to the CategoriesDataSource control. Then we also specify the DataTextField and DataValueField attributes for the DropDownList. We also declare one more SqlDataSource control and name it ProductsDataSource. In the SelectCommand attribute of the ProductsDataSource control, we specify a placeholder for the CategoryID parameter using the @CategoryID identifier. Then we specify the value for the SQL query parameter using the SelectParameters template. In this case, we need to retrieve the value of the category identifier using the SelectedValue property of the previously declared DropDownList. To do this, we set the ControlID and PropertyName attributes using the ControlParameter template. This allows the Category selected in the DropDownList to be used as an argument to the Products SQL query. In this example, we have used the ControlParameter template to specify the input values for the parameterized query specified in the SelectCommand attribute. Apart from ControlParameter, you can also use any one of the following parameter objects to provide values for the parameterized query:
- QueryStringParameter. Using this template, you can get the value of the parameter from a key-value combination in the current query string.
- SessionParameter. This allows you to get the parameter value from a specified Session variable.
- CookieParameter. This allows you to get the parameter value from a specified cookie.
- FormParameter. This allows you to get the parameter value from any property exposed in the current Request object, such as posted control values. The FormParameter object is a more general version of the QueryStringParameter and CookieParameter objects.
When you execute the code shown in Figure 3, you ll see an output that is somewhat similar to the screen capture shown in Figure 4.
Figure 4: Run the code from Figure 3
to get this output.
In the screen capture shown in Figure 4, when you select a Category in the dropdownlist, the GridView refreshes to display only those products that belong to that category.
Implementing Time-based Caching in a SqlDataSource Control
Caching in ASP.NET is one of the powerful features that can be immensely useful in increasing the performance of a Web application. The most dramatic way to improve the performance of a database-driven Web application is through caching. Retrieving data from a database is one of the slowest operations that you can perform in a Web site. If, however, you can cache the database data in memory, then you can avoid accessing the database with every page request, and dramatically increase the performance of your application. ASP.NET 2.0 provides many enhancements to the caching feature set in ASP.NET 1.x. One such new feature is the ability to specify the caching attributes as part of the data source control declaration itself. In this section, we ll discuss this new feature and look at an example that demonstrates it.
There are two properties that you can set in the SqlDataSource control to enable caching for a SqlDataSource control:
- EnableCaching. By setting this attribute to true, you can enable caching in a SqlDataSource control.
- CacheDuration. This property allows you to set or get the duration of the cached data in the SqlDataSource control. This attribute is specified in terms of seconds.
For the purposes of this example, let s modify our previous example and add the caching-related attributes to the SqlDataSource control (see Figure 5).
<%@ Page Language="VB" %>>
Figure 5: Add caching-related attributes to the SqlDataSource control.
Because the EnableCaching property is set to true in the SqlDataSource control declaration in the code shown in Figure 5, the data retrieved by the SelectCommand will automatically be cached. The CacheDuration property enables you to specify, in seconds, how long the data should be cached before it is refreshed from the database. By default, the SqlDataSource will cache data using an absolute expiration policy, meaning that the data will be refreshed for every so many seconds that is specified in the CacheDuration property. You also have the option of enabling a sliding expiration policy, which means the data will not be removed from the cache as long as it continues to be accessed. Employing a sliding expiration policy is useful whenever you have a large number of items that need to be cached, because this expiration policy enables you to keep only the most frequently accessed items in memory. In the example in Figure 5, we cached the results of the SQL query for 10 seconds by setting the EnableCaching and CacheDuration attributes.
Advanced Features of the SqlDataSource Control
So far, we have seen the use of the SqlDataSource control for creating data-driven Web applications. The SqlDataSource control also provides the following features:
- By handling all the low-level tasks, such as opening a connection to the database, executing a command, retrieving the results of the command, and closing the connection, the SqlDataSource control allows you to focus on the core business logic of the application.
- Apart from providing a powerful declarative model for creating rich data-driven Web applications, ASP.NET 2.0 also allows you to access the same set of features programmatically, retaining the flexibility of the database features.
- Using the declarative model provided by the SqlDataSource control, not only can you execute stored procedures, but you can also pass parameters to the stored procedures. For example, you can retrieve the value of a TextBox and pass it as a parameter to a stored procedure that can return appropriate rows depending on the value entered in the TextBox.
- Another excellent feature of the SqlDataSource control is that it offers the same basic Object model and API as the other data source controls, reducing the learning curve required for understanding the different types of data source controls.
Conclusion
This article presents a thorough discussion of the different data source controls provided by ASP.NET 2.0. This article also presents an example of the declarative data binding support provided by the SqlDataSource control that can be used to create data-driven Web applications with no code. The article also demonstrates the caching support provided by the SqlDataSource control. By taking advantage of these new features, you can not only create data-driven Web applications in significantly less time, but also greatly increase the performance of the Web applications.
The sample code in this article is available for download.
Thiru Thangarathinam works at Intel Corp. in Chandler, AZ. He specializes in architecting, designing, and developing distributed enterprise class applications using .NET-related technologies. He has co-authored a number of books in .NET-related technologies. He has also been a frequent contributor to leading technology-related online publications. He can be reached at mailto:[email protected].
