Data Points

Data Source Controls in ASP.NET 2.0

John Papa

Code download available at:DataPoints0501.exe(164 KB)

Contents

Data-Bound Controls
Data Source Controls
Command Types and Parameters
SqlDataSource Example
ObjectDataSource
Enhanced Strongly Typed DataSet
More Data Source Control Trivia
Wrapping Up

ASP.NET 2.0 introduces a series of new tools that improve data access including several data source and data bound controls. The new assortment of data source controls can eliminate a ton of repetitive code that was required in ASP.NET 1.x. For example, you can easily associate SQL statements or stored procedures with data source controls and bind them to data bound controls. Even more impressive, the ObjectDataSource control allows you to take advantage of the simplified development and reduction of code but still allows you to abstract your business and data access logic in separate tiers of your n-tiered architecture.

Before .NET, building data grids with traditional ASP often required you to write a lot of code to build an HTML table on the fly while looping through an ADO Recordset. ASP.NET 1.x made this type of development easier by allowing you to bind an XML-based DataSet to the ASP.NET DataGrid control. This reduced the code required to generate the grid. However, both traditional ASP and ASP.NET 1.x require code to implement paging, sorting, editing and row-selection features. With the improvements in ASP.NET 2.0, this code can be significantly reduced to produce a data-filled grid with full paging, sorting, and editing features.

In this installment of Data Points, I will begin by demonstrating how easy it is to develop a Web application with ASP.NET 2.0 using the SqlDataSource and some of the new data-bound controls. Note that I have used the Beta 1 release here.

Most enterprise applications are built on multitiered architectures with a middle tier that holds the business logic and a data access layer that works with the backend database (or databases). I will discuss how the ObjectDataSource is ideal for integrating with existing multitier components. By linking the ObjectDataSource control to business objects, you are able to leverage an existing multitiered architecture and take advantage of the significant reduction in code to generate a sophisticated Web UI. The ObjectDataSource control also contains some special properties that allow binding to the newly enhanced strongly typed DataSet and data components in ASP.NET 2.0 and ADO.NET 2.0. Other new features and improvements in ASP.NET 2.0 include the new two-way binding expressions, enhanced caching, and several new ASP.NET 2.0 controls that can be data bound to the new data source controls.

Data-Bound Controls

To use the data source controls you must have a data-bound control to bind them to. There are several new data-bound controls in ASP.NET 2.0, including the GridView, DetailsView and FormView controls. If you are fond of the ASP.NET 1.x DataGrid control, you will love the ASP.NET 2.0 GridView control. The GridView is like a DataGrid on steroids as it can be bound to the new data source controls, and can be used to implement sorting, editing, and paging, all with much less code than the DataGrid required (for more information on the GridView, refer to Dino Esposito's article from the August 2004 issue of MSDN®Magazine,).

To bind a GridView to a data source control, you set the GridView's DataSourceID property to the ID of the data source control. There are several other properties of the GridView that can be set to enhance appearance and user interaction that I will demonstrate in the later examples:

<asp:GridView ID="gvwOrders" Runat="server" DataSourceID="sdsOrdersDataSource" AutoGenerateColumns="True">

Other controls, such as the DropDownList, can also be bound to the data source controls. For example, a DropDownList control can be bound to a SqlDataSource control that retrieves a list of employees. The employee's full name could be displayed in the DropDownList while the EmployeeID could be bound to the control as its underlying data value field. The following example defines a DropDownList that will display a list of customer names that can be selected. The customer data is bound to the SqlDataSource control named sdsCustomerDataSource which gets a list of customer CompanyName and CustomerID fields:

<asp:DropDownList ID="ddlCustomers" Runat="server" AutoPostBack="True" DataSourceID="sdsCustomersDataSource" DataTextField="CompanyName" DataValueField="CustomerID"> </asp:DropDownList>

Binding a control to a data source control is quite simple in ASP.NET 2.0 and does not require any code in a codebehind. However, you can still write code to explicitly bind to the controls if you want to. In fact, the data source and DataMember proper-ties of data-bound controls have been brought forward from ASP.NET 1.x, as well.

Data Source Controls

There are several new data source controls in ASP.NET 2.0, such as the SqlDataSource, ObjectDataSource, XmlDataSource, AccessDataSource, and SiteMapDataSource (shown in Figure 1). They all can be used to retrieve data from their respective types of data sources and can be bound to various data-bound controls. Data source controls simplify the amount of custom code that needs to be written to retrieve and bind data, and even to sort, page through, or edit data.

Figure 1 ASP.NET 2.0 Data Source Controls

Data Source Control Retrieves Its Data From
SqlDataSource A SQL Server, Oracle, or other OLE DB accessible database
AccessDataSource An access database
ObjectDataSource A business object or method
XmlDataSource An XML file or stream (used for hierarchical data)
SiteMapDataSource An XML-based sitemap source (used for hierarchical data)

Each data source control has similar properties that allow it to interact with its respective data source. The SiteMapDataSource and the XmlDataSource are built to retrieve hierarchical data while the other data source controls are a built to retrieve set-based data with columns and rows.

The AccessDataSource is built specifically to retrieve data from Access databases. The SqlDataSource may sound like it works only with SQL Server™, but that's not the case. It can actually be used to retrieve data from any OLE DB or ODBC-compliant data source.

Command Types and Parameters

The SqlDataSource control has four command properties that can be set to tell the SqlDataSource how to get, insert, update, and delete its data. The SelectCommand property can be set to a SQL statement or a stored procedure. In either case, parameters can be passed in if needed. The InsertCommand, UpdateCommand, and DeleteCommand properties are used to tell the SqlDataSource what SQL statements (or stored procedures) to use to modify the data in the underlying database. The code example in Figure 2 shows a SqlDataSource that has its SelectCommand and UpdateCommand properties set to parameterized SQL statements. Notice that UpdateParameters uses a Parameter element to indicate the names and data types of the fields to use for the parameters. Once a GridView is bound to this SqlDataSource, these UpdateParameters values are bound to affected row's columns with the same names.

Figure 2 SqlDataSource Control

<asp:SqlDataSource ID="sdsCustomersDataSource" Runat="server" ProviderName="System.Data.SqlClient" ConnectionString="Server=(local);Integrated Security=True;Database=Northwind" SelectCommand="SELECT CustomerID, CompanyName FROM Customers" UpdateCommand="UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID"> <SelectParameters> <asp:ControlParameter Name="CustomerID" Type="String" ControlID="ddlCustomers"></asp:ControlParameter> </SelectParameters> <UpdateParameters> <asp:Parameter Name="CompanyName" Type="String"></asp:Parameter> <asp:Parameter Name="CustomerID" Type="String"></asp:Parameter> </UpdateParameters> </asp:SqlDataSource>

The easiest way to try this out for yourself is to create a Web Form in Visual Studio® 2005, connect to the local SQL Server Northwind database in the Server Explorer window, and drag a table to the Web Form. This will automatically create a SqlDataSource control as well as a GridView. Visual Studio will automatically set the SqlDataSource control's ProviderName and ConnectionString properties to the SQL Server Northwind database. Also, all four of the command properties will be set to the appropriate SQL statements. Then, all you have to do to edit the Web Form is use the Smart Tag to check the Enable Editing checkbox (see Figure 3).

Figure 3 GridView Settings

Figure 3** GridView Settings **

The data source controls can use parameters from other controls, too. For example, a data source control may retrieve all orders for a given customer. In this case, the CustomerID could be a parameter that is passed to a SQL statement or a stored procedure of a SqlDataSource control's SelectCommand property. The CustomerID could be retrieved from another control, such as a DropDownList, and passed directly into the SQL SelectCommand of the SqlDataSource control.

You can link a control's value directly to a parameter of any of the SqlDataSource control's SQL statements (SelectCommand, InsertCommand, UpdateCommand, or DeleteCommand). You can also specify exactly which property of a control you want to use for the parameter. For example, if you did not want the default property of the DropDownList in the previous example, but instead wanted its DataTextField, you could set the ControlParameter's PropertyName property to DataTextField.

Besides the ControlParameter, there are other parameter types that can be used with data source controls. A ProfileParameter can be used to retrieve a parameter's value from a profile object if you're using the new ASP.NET personalization features. Then there are several parameter object types that all retrieve their data from the standard Request object's collections. For example, a CookieParameter can be used to retrieve a parameter's value from a cookie. The QueryStringParameter gets its value from any request string variable and the FormParameter gets its value from an HTML Form's input field. Finally, the SessionParameter can be used to retrieve its value from a session variable. These types of parameters give data source controls several options on how to set their parameters' values.

SqlDataSource Example

Now that I've finished the overview, I will compare and contrast the retrieval and modification of data using both the SqlDataSource and the ObjectDataSource. The SqlDataSource control, which uses the ADO.NET 2.0 DbProviderFactory object, has properties that link it directly to an OLE DB or ODBC data source. When loading an ASP.NET page that contains a data-bound control linked to a SqlDataSource, the SqlDataSource communicates directly with an underlying database. Thus, the SqlDataSourceData source control does not integrate with existing business objects.

To see the SqlDataSource in action, let's take a look at the Orders_SDS.aspx page (see Figure 4). There is a DropDownList control that is bound to a SqlDataSource source control the retrieves a list of customers from the Northwind database.

Figure 4 Retrieving Customers Through a SqlDataSource

Figure 4** Retrieving Customers Through a SqlDataSource **

There is also another SqlDataSource called sdsOrdersDataSource which retrieves all Orders for the selected customer. The code in Figure 5, taken from Orders_SDS.aspx (which is available in the download), shows the two SqlDataSource controls and the DropDownList. Notice that the sdsOrdersDataSource SqlDataSource control uses the selected value of the DropDownList for its stored procedure's parameter (prGet_Orders's parameter).

Figure 5 SqlDataSource Controls

<asp:SqlDataSource ID="sdsCustomersDataSource" Runat="server" ProviderName="System.Data.SqlClient" ConnectionString="Server=(local);Integrated Security=True; Database=Northwind" SelectCommand="prGet_Customers"> </asp:SqlDataSource> <asp:SqlDataSource ID="sdsOrdersDataSource" Runat="server" ProviderName="System.Data.SqlClient" ConnectionString="Server=(local);Integrated Security=True; Database=Northwind" SelectCommand="prGet_Orders" UpdateCommand="prUpdate_Order"> <UpdateParameters> <asp:Parameter Type="DateTime" Name="OrderDate"></asp:Parameter> <asp:Parameter Type="String" Name="ShipCity"></asp:Parameter> <asp:Parameter Type="String" Name="ShipCountry"></asp:Parameter> <asp:Parameter Type="Int32" Name="OrderID"></asp:Parameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter Name="CustomerID" Type="String" ControlID="ddlCustomers"></asp:ControlParameter> </SelectParameters> </asp:SqlDataSource> <asp:DropDownList ID="ddlCustomers" Runat="server" DataSourceID="sdsCustomersDataSource" DataTextField="CompanyName" DataValueField="CustomerID" AutoPostBack="True"> </asp:DropDownList>

The combination of the ProviderName and the ConnectionString properties tell the SqlDataSource controls which data store to get the data from. While these properties make the SqlDataSource a simple solution for retrieving and modifying data, they unfortunately expose database connection strings and SQL statements or stored procedures in the presentation layer's ASPX files. This is generally not a good idea. It's much safer to store this data encrypted in a configuration repository (like a configuration file or the registry).

Figure 4 shows the Orders_SDS.aspx page in edit mode. Paging is automatically implemented by the GridView by setting the GridView AllowPaging property to true and the PageSize property to a desired size, like 10. This tells the grid to page the rows in the grid and automatically reload the grid and the page when the next or previous page links are clicked. The GridView displays the selected row's editable columns using TextBox controls. The data-bound columns are defined with either the asp:BoundField or TemplateField elements. The BoundField can be bound to the GridView's associated data source by setting the DataField property:

<asp:BoundField HeaderText="ShipCity" DataField="ShipCity" SortExpression="ShipCity"></asp:BoundField>

This tells the GridView control that it is to display the ShipCity value in a span element when it is in view mode. When the GridView is in edit mode, this column in the selected row will be displayed in the appropriate HTML element. In this case, the HTML element is the TextBox because it is a string value. The element is chosen based on the data type of the bound column. For example, if the column is defined as a bit in SQL Server, then the CheckBox control would be used to display the column in edit mode.

The TemplateField element offers greater flexibility on how the bound column should behave. Take a look at the following code sample from the Orders_SDS.aspx page:

<asp:TemplateField SortExpression="OrderDate" HeaderText="OrderDate"> <ItemTemplate> <asp:Label ID="lblOrderDate_Item" Runat="server" Text='<%# Bind("OrderDate", "{0:d}") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtOrderDate_Edit" Runat="server" Text='<%# Bind("OrderDate", "{0:d}") %>'></asp:TextBox> </EditItemTemplate> </asp:TemplateField>

It uses a TemplateColumn to display the OrderDate column in a Label control when the GridView is in view mode and in a TextBox control when it is in edit mode. You can also tell the Template column to display this column differently in the footer by using a FooterTemplate. There is also a HeaderTemplate, an AlternatingItemTemplate, and even an InsertItemTemplate. Also notice the shortened binding syntax introduced in ASP.NET 2.0. The value for the OrderDate is set by calling the Bind expression and passing to it the name of the column in the GridView's associated data source and an optional data format string expression. In this case, I used the data format string that represents a short date. These properties are all easily set through the properties accessible via the new Smart Tag feature in Visual Studio 2005 (see Figure 6).

Figure 6 Template Column Properties

Figure 6** Template Column Properties **

ObjectDataSource

One of the coolest features of the GridView and the other data-bound controls is that once they are set up, they can be bound to either an ObjectDataSource control or a SqlDataSource control by simply changing a single property. For example, all you have to do is create a new ObjectDataSource control and change the DataSourceID property of the GridView to this new ObjectDataSource control's ID.

Unlike the SqlDataSource, the ObjectDataSource control lets you abstract the database-specific settings out of the ASPX page and the presentation layer and move them to a lower tier in a multitier architecture (see Figure 7). For example, the ConnectionString, ProviderName, and SelectCommand properties of the SqlDataSource control do not exist in the ObjectDataSource control. Instead, they are replaced with other properties which tell the ObjectDataSource control which business class to instantiate and which method to use to retrieve or modify the data.

Figure 7 Tiers

Figure 7** Tiers **

To set up an ObjectDataSource control to access another tier's business class and its methods, you must first set the ObjectDataSource control's TypeName property to the name of the business class (like TypeName="MSDN2005Jan_BLL.Orders"). Follow that by setting the SelectMethod property to the name of the method in the business class that will be used to retrieve the data for the data source. The business class's method must return an enumerable list such as a collection, array, DataSet, or DataReader. In order for this to work, the ObjectDataSource must be able to execute the specified method. If it's a static method, nothing special needs to be done. If the method is an instance method, the ObjectDataSource must be able to create an instance of the class. The easiest way for this to happen is to write the business class to include a default constructor. Alternatively, you can handle the ObjectDataSource's ObjectCreating event, which allows you to instantiate the object with whatever constructor you desire and then pass that object instance to the data source control.

The code sample in Figure 8 (excerpted from Orders.aspx, also in the download) shows two ObjectDataSource controls that replace the two SqlDataSouce controls used in the previous example. The odsOrdersDataSource uses the MSDN2005Jan_BLL.Orders class and its GetData method to retrieve its list of orders. In this example, the GetData method in the Orders class simply creates an instance of a strongly typed DataSet named OrdersDataSet and its related adapter called OrdersTableAdapter. It then calls the Fill method and returns the strongly typed DataSet:

public OrdersDataSet GetData(string CustomerID) { OrdersDataSet oDs = new OrdersDataSet(); OrdersTableAdapter oDa = new OrdersTableAdapter(); oDa.Fill(oDs, CustomerID); return oDs; }

Figure 8 Defining the ObjectDataSource Controls

<asp:ObjectDataSource ID="odsCustomersDataSource" Runat="server" TypeName="MSDN2005Jan_BLL.Customers" SelectMethod="GetData" EnableCaching="True" CacheDuration="1200"> </asp:ObjectDataSource> <asp:ObjectDataSource ID="odsOrdersDataSource" Runat="server" TypeName="MSDN2005Jan_BLL.Orders" SelectMethod="GetData" UpdateMethod="UpdateData"> <UpdateParameters> <asp:Parameter Type="Int32" Name="OrderID"></asp:Parameter> <asp:Parameter Type="DateTime" Name="OrderDate"></asp:Parameter> <asp:Parameter Type="String" Name="ShipCity"></asp:Parameter> <asp:Parameter Type="String" Name="ShipCountry"></asp:Parameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter Name="CustomerID" Type="String" ControlID="ddlCustomers" PropertyName="SelectedValue"></asp:ControlParameter> </SelectParameters> </asp:ObjectDataSource>

Notice that the GetData method also accepts a CustomerID parameter passed in by the ObjectDataSource control's SelectParameter properties. The UpdateData method's parameters are also passed in from the ObjectDataSource as indicated by the UpdateParameters properties shown in Figure 8. The following is the signature of the Orders class's UpdateData method:

public void UpdateData(int OrderID, DateTime OrderDate, string ShipCity, string ShipCountry)

The names and data types of the UpdateData method's parameters must match the names and data types of update parameters in the ObjectDataSource control. The data source controls don't work with batch updates, so you can't pass multiple rows to the update method all in one shot. Instead, you must pass each value as a single parameter to the update method. In addition to the SelectMethod and UpdateMethod properties, the ObjectDataSource control also has DeleteMethod and InsertMethod properties, too.

Enhanced Strongly Typed DataSet

The previous example demonstrated how to bind a GridView to an ObjectDataSource control to link to a business tier's class so you can retrieve and update data via the class's methods. This example works great if you have existing business layer logic and a multitiered architecture. It can also call methods of a Web service client proxy or any other referenced class that follows the class and method requirements.

One aspect of the previous example I intentionally delayed discussing until now is the role of the strongly typed DataSet. Using the wizards in Visual Studio 2005 you can also define methods right in the typed DataSet class to select, insert, update, and delete data. Thus, you can avoid directly writing any ADO.NET code in the business or data access layer and instead use the wizards to add the ADO.NET logic right into a typed DataSet.

You are not required to retrieve a typed DataSet, but one would be valuable in this situation, thanks to some of their new enhancements. The typed DataSet creates a default Fill method, which is appended to a TableAdapter class in the typed DataSet's definition. This optional TableAdapter class can be set up to store the connection string as well as the stored procedures or SQL statements to select, update, insert, and delete records from a database.

Figure 9 Orders DataSet

Figure 9** Orders DataSet **

The typed DataSet also allows you to create custom methods to retrieve and modify data. In the Orders DataSet, shown in Figure 9, I created a typed DataSet based on the prGet_Orders stored procedure. Then, using the data component query configuration wizard, I added two custom methods to the OrdersTableAdapter: GetData and UpdateData. These methods are defined within the class file associated with the typed DataSet's XSD; in this case my file is called OrdersDataSet.Designer.cs. If you are curious, you can open this auto-generated file (but you should not modify it since your changes will be overwritten if the file is regenerated) and review the custom GetData and Update methods as well as all of the standard code created for a typed DataSet. If you run the sample page Orders2.aspx, it binds directly to these custom methods. This can dramatically reduce the amount of code you manually write for your middle tiers.

More Data Source Control Trivia

Now that we're finished, you might be interested to know that the data source controls also expose caching functionality through a series of properties. By setting EnableCaching to true and setting the CacheDuration to a number of seconds, the data will be stored in cache for that period of time. The CacheExpirationPolicy property can be set to Absolute or Sliding, as well. Absolute is the default, which tells cache to begin its countdown to expire as soon as the cache is loaded. A Sliding policy tells the cache to reset the expiration countdown each time the cached data is accessed. Caching techniques can help optimize your applications in situations where you are loading data that is not very volatile. For example, it would be advantageous to use caching in a data source control that loads a DropDownList of states, cities, or even product categories, since they do not change frequently.

The ObjectDataSource control wraps the code that hooks into business objects to invoke business methods. It also works in concert with data bound controls such as the GridView to perform paging, sorting, and data changes that had to be coded by hand in ASP.NET 1.x.

Wrapping Up

The improvements in ASP.NET 2.0, specifically in the areas of data-source and data-bound controls, significantly reduce the amount of code needed to produce a data-filled grid with full paging, sorting, and editing features. Even though the data source controls eliminate a ton of code that you used to have to write manually, you can still write code to interact with the data source controls. While you can just point and click to create a data-driven Web page, you can also write code to use the data source controls' events such as the Selected, Selecting, Updated, or Updating events.

Send your questions and comments for John to  mmdata@microsoft.com.

John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive.