SqlDataSource

LANGUAGES: VB.NET

ASP.NET VERSIONS: 2.0

Almost any Web application you create will need some kindof 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 retrieveand bind data to server controls, ASP.NET 2.0 provides a declarative solutionfor creating data-driven Web applications, which requires no code at all forthe most common data scenarios such as sorting, paging, caching, updating,inserting, deleting, filtering, and so on. These new data source controls thatship with ASP.NET 2.0 are a major addition to this platform that can go a longway in realizing Microsoft s goal of 70% code reduction.

In this article, we ll come to understand the differentdata 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 howto use this control to create data-driven Web applications. Along the way, we llalso demonstrate some of the advanced concepts of the SqlDataSource control,such as enabling caching, declaratively passing values to the SQL statementdirectly from a control, and so on.

One of the limitations of ASP.NET 1.x is that it does notprovide a declarative model for binding data to data-aware controls such asDataGrid, DataList, and Repeater. Now in ASP.NET 2.0, you have a very powerfuland easy-to-use declarative model for binding data directly from the database. Byenabling the following capabilities, this new declarative model allows you tocreate dynamic data driven Web applications without writing any lines of code:

ASP.NET 2.0 provides many new data controls. Before wetake a look at this new set of controls, let s understand the two types of datacontrols. They are as follows:

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.0also provides the following data bound controls that you ll normally use todisplay data that is contained in the data source controls:

Now that we understand the concepts behind the data sourcecontrols 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 tounderstand how the combination of data source controls and data bound controlscan be used to create a data-driven Web page with no code. For the purposes ofthis example, let s consider the Categories table in the Northwind databasethat ships with SQL Server; we ll display all the Categories in a GridViewcontrol (see Figure 1).

<%@ Page Language="VB" %>

  DataSourceID="CategoriesDataSource"

  DataKeyNames="CategoryID"AutoGenerateColumns="False">

      "CategoryName"SortExpression="CategoryName" />

      "Description"SortExpression="Description" />

  ID="CategoriesDataSource"Runat="server"

  SelectCommand="SELECT* FROM [Categories]"

  ConnectionString="server=(local)

  SqlExpress;database=Northwind;

  trusted_connection=true"/> 

Figure 1: DisplayCategories in a GridView control.

Let s walk through the code shown in Figure 1. We start bydeclaring a SqlDataSource control named CategoriesDataSource. As the namesuggests, this control is used to get information from the Categories table inthe Northwind database. As part of the SqlDataSource control declaration, wealso specify the ConnectionString and the SQL statement to be executed as itsattributes. In the SelectCommand attribute, we specify the SQL statement to beexecuted. As you can see, the SQL statement returns everything from theCategories table. The SqlDataSource control can return data in two forms: as aDataSet or as a DataReader, which can be specified by setting the data sourcecontrol s DataSourceMode property. A DataSet contains all the data in memory,allowing you to manipulate the data in various ways after retrieving it; aDataReader provides a read-only cursor that can fetch individual records. Inthe GridView control declaration, we set the DataSourceID attribute to the IDof the SqlDataSource control. That s all there is to creating a data sourcecontrol and then binding it to a data-aware control. As you can see, we haveaccomplished all of this declaratively without writing even a single line ofcode. Figure 2 shows the output that results when you execute the code shown inFigure 1.

Figure 2: Run the code from Figure 1to get this output.

So far, we have seen a simple example that displays allthe Categories in a GridView control. In the next example, we ll learn how tosupply parameters to the SqlDataSource control.

Passing Parameters to the SqlDataSource Control

In this example, we ll display both Categories andProducts information in a single page. We ll display the Categories in aDropDownList, and when the selection changes, we ll refresh the list ofproducts displayed in a GridView control. The code required for implementingthis is shown in Figure 3.

<%@ Page Language="VB" %>

     DataTextField="CategoryName" DataSourceID="

     CategoriesDataSource"ID="DropDownList1"

     Runat="server"AutoPostBack="True">

   
><

     DataSourceID="ProductsDataSource"

     DataKeyNames="ProductID" AutoGenerateColumns="False">

         "ProductID"SortExpression="ProductID" />

         "ProductName"SortExpression="ProductName" />

         DataField="QuantityPerUnit"

           SortExpression="QuantityPerUnit" />

         DataField="UnitPrice"SortExpression=

         "UnitPrice"/>

     Runat="server"SelectCommand="SELECT *

     FROM [Products] WHERE CategoryID = @CategoryID"

     ConnectionString="<%$ConnectionStrings:Northwind %>">

          ControlID="DropDownList1"

          PropertyName="SelectedValue" />

     Runat="server"

      SelectCommand="SELECT

      * FROM[Categories]" ConnectionString="<%$

ConnectionStrings:Northwind %>" />

Figure 3: Displayboth Categories and Products information in a single page.

In the example shown in Figure 1, we hard-coded theconnection string in the SqlDataSource control declaration itself. But in theexample shown in Figure 3, the connection string is retrieved from theweb.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 theabove connection string from the web.config file. For example, using the syntax<%$ ConnectionStrings:Northwind %>, you can retrieve the above connectionstring from the web.config file. This is demonstrated in the value set in theConnectionString attribute in the SqlDataSource control declaration.

Then we set the DataSourceID property of the DropDownListto the CategoriesDataSource control. Then we also specify the DataTextField andDataValueField attributes for the DropDownList. We also declare one moreSqlDataSource control and name it ProductsDataSource. In the SelectCommandattribute of the ProductsDataSource control, we specify a placeholder for theCategoryID parameter using the @CategoryID identifier. Then we specify thevalue for the SQL query parameter using the SelectParameters template. In thiscase, we need to retrieve the value of the category identifier using theSelectedValue property of the previously declared DropDownList. To do this, weset the ControlID and PropertyName attributes using the ControlParametertemplate. This allows the Category selected in the DropDownList to be used asan argument to the Products SQL query. In this example, we have used theControlParameter template to specify the input values for the parameterizedquery specified in the SelectCommand attribute. Apart from ControlParameter,you can also use any one of the following parameter objects to provide valuesfor the parameterized query:

When you execute the code shown in Figure 3, you ll see anoutput that is somewhat similar to the screen capture shown in Figure 4.

Figure 4: Run the code from Figure 3to get this output.

In the screen capture shown in Figure 4, when you select aCategory in the dropdownlist, the GridView refreshes to display only thoseproducts that belong to that category.

Implementing Time-based Caching in a SqlDataSource Control

Caching in ASP.NET is one of the powerful features thatcan be immensely useful in increasing the performance of a Web application. Themost dramatic way to improve the performance of a database-driven Webapplication is through caching. Retrieving data from a database is one of theslowest operations that you can perform in a Web site. If, however, you cancache the database data in memory, then you can avoid accessing the databasewith every page request, and dramatically increase the performance of yourapplication. ASP.NET 2.0 provides many enhancements to the caching feature setin ASP.NET 1.x. One such new feature is the ability to specify the cachingattributes as part of the data source control declaration itself. In thissection, we ll discuss this new feature and look at an example that demonstratesit.

There are two properties that you can set in theSqlDataSource control to enable caching for a SqlDataSource control:

For the purposes of this example, let s modify ourprevious example and add the caching-related attributes to the SqlDataSourcecontrol (see Figure 5).

<%@ Page Language="VB" %>>

     DataTextField="CategoryName"

    DataSourceID="CategoriesDataSource"

    ID="DropDownList1" Runat="server"AutoPostBack="True">

     DataSourceID="ProductsDataSource"

     DataKeyNames="ProductID"AutoGenerateColumns="False">

         DataField="ProductID"

         SortExpression="ProductID"/>

         DataField="ProductName"

         SortExpression="ProductName"/>

         DataField="QuantityPerUnit"

         SortExpression="QuantityPerUnit" />

         DataField="UnitPrice"

         SortExpression="UnitPrice" />

   SelectCommand="SELECT * FROM [Products]

   WHERE CategoryID = @CategoryID"ConnectionString=

   "<%$ ConnectionStrings:Northwind%>"

   EnableCaching="True" CacheDuration="10">

        ControlID="DropDownList1"

        PropertyName="SelectedValue" />

    Runat="server"SelectCommand="SELECT *

    FROM[Categories]" ConnectionString="<%$

    ConnectionStrings:Northwind %>"

    EnableCaching="True" CacheDuration="10"/>

Figure 5: Addcaching-related attributes to the SqlDataSource control.

Because the EnableCaching property is set to true in theSqlDataSource control declaration in the code shown in Figure 5, the dataretrieved by the SelectCommand will automatically be cached. The CacheDurationproperty enables you to specify, in seconds, how long the data should be cachedbefore it is refreshed from the database. By default, the SqlDataSource willcache data using an absolute expiration policy, meaning that the data will berefreshed for every so many seconds that is specified in the CacheDurationproperty. 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 continuesto be accessed. Employing a sliding expiration policy is useful whenever youhave a large number of items that need to be cached, because this expirationpolicy 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 10seconds by setting the EnableCaching and CacheDuration attributes.

Advanced Features of the SqlDataSource Control

So far, we have seen the use of the SqlDataSource controlfor creating data-driven Web applications. The SqlDataSource control alsoprovides the following features:

Conclusion

This article presents a thorough discussion of thedifferent data source controls provided by ASP.NET 2.0. This article also presentsan example of the declarative data binding support provided by theSqlDataSource control that can be used to create data-driven Web applicationswith no code. The article also demonstrates the caching support provided by theSqlDataSource control. By taking advantage of these new features, you can notonly create data-driven Web applications in significantly less time, but alsogreatly increase the performance of the Web applications.

The sample code in this article isavailable for download.

Thiru Thangarathinamworks at Intel Corp. in Chandler, AZ. He specializes in architecting,designing, and developing distributed enterprise class applications using .NET-relatedtechnologies. He has co-authored a number of books in .NET-relatedtechnologies. He has also been a frequent contributor to leading technology-relatedonline publications. He can be reached at mailto:[email protected].