Introduction

Data access is a common term used to describe how data is moved and used from one source to another. Data access can take on different forms, from simply retrieving data from a database to reading data from an Excel spreadsheet or an unmanaged component. To reduce the complexity of the code required to perform these tasks, Microsoft has introduced new web server and data source controls within Visual Studio 2005.

From a traditional ASP or ASP.NET 1.1 point of view, data access tasks involved a decision process - developers had to decide whether to use a simple wizard that Visual Studio provided or code it manually. The latter often prevailed as the wizard was too simplistic and didn't address issues like synchronization, paging and the sorting of data. Although data can be retrieved, what to do with it was a different task. With ASP.NET 2.0 the wizard takes care of these complex tasks by encapsulating the logic within the web server control.

This article will provide an overview of the data source control as well as the new web server controls. An example will also be developed in a step by step manner to help show how some of these new controls can be used.

System Requirements

To run the code sample you should have the following:

• Windows 2000 or greater
• The .NET Framework v2.0 Beta 2
• Visual Studio 2005 Beta 2
• MSDE or SQL Server with the Northwind database installed.

Installing and Compiling the Sample Code

The sample download for this article contains a VS 2005 Beta 2 solution written in C#. The solution shows different ways to access data from different data sources via the use of the new web server controls.

To install the sample, create a directory called DataSourceExample and copy the downloaded files into this folder. In SQL Server, update the web.config to match your environment for SQL Server.

Data Source Controls - An Overview

A data source control is basically another server control. Its datasource can come from an XML file, a database (SQL Server, Oracle etc) or even an Excel spreadsheet. It doesn't have a user interface, however it does encapsulate all the basic SQL statements insert , update , delete and select . It is self describing and lets the bound server control know what functionality it supports. As an example take the details view component, which is new to v2.0, which can enable editing resulting in the displaying of an edit column. Data source controls are to be used with web server controls and is not to be used for any other purpose. The relationship between these two controls is critical, without a data source control the web server control will not work. You can code the data source control either through code or dragging a specific data source, like the SQLDatasource server control via the toolbox.

The following is an example of code syntax used to link a stored procedure, CustOrdersOrder to a GridView with no code behind required.

<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"  
  SelectCommand="CustOrdersOrders"
  ConnectionString="<%$ ConnectionStrings:connstring %>">
  <SelectParameters>
    <asp:ControlParameter Name="CustomerID" Type="String" 
      ControlID="TextBox1"   PropertyName="Text"></asp:ControlParameter>
  </SelectParameters>
</asp:SqlDataSource>

You will find there is a new property for the GridView control called DataSourceID , this will bind the control to its datasource .

If you were using the wizard, the connectionstring property is determined based on whether you want to store your connectiongstring in the web.config file or hard code it in the page. The code sample above utilizes the web.config file to store the connectionstring .

In ASP.NET v2.0 the web.config file introduces a new storage area that is specifically for connection strings. The connectionStrings tag uses the XML syntax and requires at least three properties, namely the name , connectionString and providerName .

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <appSettings/>
    <connectionStrings>
      <add name="connstring" connectionString="Server=localhost;User  
        ID=Test;Password=Test;Database=Northwind;Persist Security Info=True"
        providerName="System.Data.SqlClient" />
    </connectionStrings>
…

Below is a table that defines all the new types of data source controls available to ASP.NET v2.0 with a detailed description of each:

Data Source Component	Description
SqlDataSource	The SQLDataSource accesses data from any relational database (SQL, Oracle, etc/). The main transport protocol is the OLEDB and ODBC data providers and is connected via a connection string. Caching data via the SqlDataSource control is not enabled by default. To enable it you must set the EnableCaching to true. The CacheDuration property determines the length of time (in seconds) to cache the data. Specifically for SQL Server Yukon (2005), there is also a cache dependency ( SQLCacheDependency ) feature that caches the database and table (represented as database:table ). Changes made to a table are monitored by change notifications. The user account monitoring the table must have permissions to create tables, stored procedures and more specifically for a table, triggers. Once a change is made, the cache gets updated. This is an extremely important feature to provide high performance data access in your own web application as you can minimize data retrieval to only when the data has been refreshed. When it comes to updating data you may have the situation where two people may be updating the same record. The SQLDataSource control has a ConflictDetection property that can be either set to CompareAllValues or OverwriteChanges . By default the property is set to OverwriteChanges , which means that each update to a record overwrites the existing data. CompareAllValues allows the developer to code the update/delete method manually. With this option, the control passes the original values within the method which allows you to compare new and old values and determine whether to update or not.
AccessDataSource	The AccessDataSource inherits from the SqlDataSource , however the Connectionstring and the Provider does not have to be set. As Access databases are file based, it uses the OLEDB - Jet provider to link to this data source by using a physical file location.
ObjectDataSource	The ObjectDatasource allows the data source to bind to a method of a business object (class). This is highly invaluable if applications are designed around a multi-tier architecture. For the data bind to work successfully the class must have a default constructor and have at least one method that maps to one of the methods defined in the ObjectDataSource control. Namely the, SelectMethod(), UpdateMethod(), InsertMethod() or the DeleteMethod() .
XMLDataSource	The XML data source either requires an XML file, a URL to the XML representation or a string formatted as XML to perform data binding. This data source is mostly used for hierarchical data. With the use of XSLT you can transform the XML data to a structure more favorable to bind to your page. By using the TransformFile property of the XMLDatasource this action can be used. However the underlying data will be marked as read-only. By using XPATH you can retrieve back a subset of your XML Data.
SiteMapDataSource	Gets its data from a sitemap source - web.sitemap. It is used for hierarchical data and utilizes the XML format. As an example the site map data can be bound to the tree view or a menu control.

Within the data source controls there are also properties that constitute functionality and behavior. The table below lists some of the available commands that can be used:

Property	Description
SelectCommand UpdateCommand DeleteCommand InsertCommand	As the name of the properties suggests, each property determines what sort of statement is used The command itself can either be a sql statement or a stored procedure. You can either code the sql statement manually or via the data source wizard. The sample will provide an example of how to do this.

Within these command properties you can also attach parameters. So how do we attach values to these parameters? You can do this in either of two ways, through the data source wizard (please look at sample code for help), or via codebehind. The GridView example, shown above, utilizes a Control Parameter that links to a textbox named textbox1 and uses the text property of the textbox to attain the value. The declaration of the parameter is held within the SelectParameters tag that constitutes the parameters for the SelectCommand .

Below are the different types of parameters that can be used within the command properties mentioned previously:

Parameters	Description
ControlParameter	Links a data parameter to a server control on the page.
CookieParameter	Links a data parameter to a cookie. The cookies are stored on the client machine that can be passed to method.
FormParameter	Links a data parameter to a form object. This may well be the same as the ControlParameter object, use the FormParameter object only if the controls ID is only known at runtime.
QueryStringParameter	Links a data parameter to a QueryString object.
SessionParameter	Links a data parameter to a Session object.
ProfileParameter	Links a data parameter to a profile (personalization) object.

I would like to now show you a step by step walkthrough example of how a sql data source control is used in a web application.

Data Source Controls - A Walk-through Sample

As an example I have provided a walk through of how to link a stored procedure from a SQL Server database and display the results inside the new details view server control. It will ask the user to enter in a Customer ID and it will then display the orders linked to this customer.

The steps below detail how to recreate the sample in a step by step fashion. If you have any problems you can download the sample code for this article to see where you went wrong. The sample will display data using a SQLDatasource and DetailsView control.

1. Open Visual Studio.NET Beta 2.
2. Select New → Web Site → C# → ASP.NET Web Site and call the project Example .
3. You should see the default.aspx page is in source view, go to the design view for this page.
4. Drag a TextBox , Button and GridView control to the web page.
5. Drag the SQLDataSource control from the toolbox to the web page.



Figure 1. Configuring the Data source

6. When the SQLDatasource control is dragged onto the page you will see a menu called Common sqldatasouce tasks . Select Configure datasource and a wizard will be displayed.
7. Click the New Connection button.
8. Click on SQL Data Provider from the Data sources list and click OK .



Figure 2. Setting up the connection

9. Select the server name where the database is located.
10. Select the security type whether its windows or sql authentication (this is the userid and password of your database that you have assigned).
11. Select Northwind as the database .
12. Click on Test Connection button to test whether the connection information is correct. Press OK when it tests good.



Figure 3. Testing the newly created connection

13. A list of data sources that you have created will be shown in a dropdown. Click the OK button.
14. As the connection string has been generated automatically you can either store the connection string within the page or to a configuration file ( web.config ). If you don't select Save this connection as the connection will be stored in the page itself. For this sample, leave the Save this connection as AppConnectionString1 checked, click the Next button.
15. As a mandatory requirement the SQLDatasource control requires at least one sql command to be provided. This can be either be a stored procedure or a sql statement, table or view.
16. Select Specify a custom sql statement or Stored Procedure and click Next .

Select Stored Procedure and within the dropdown select CustOrdersOrders . As CustOrdersOrders requires a parameter we have to map this parameter to a control from the page (namely the textbox). From the parameter source section select Control .

1. The ControlID is TextBox1 and you can optionally leave the default value blank, click the Next button.



Figure 4. Specifying a control parameter

2. You can optionally test your query by clicking Test Query , Click the Finish button. At this point the data source has been configured.
3. From the design view , select the detailview control.
4. From the data source dropdown select SQLDataSource1 .
5. As I am using a button control rather than a dropdownlist to postback the parameter data I will need 1 line of code in the onclick event of the button. In design view double click on the button to open the event handler.
6. A partial codebehind class is displayed, enter the following code where the cursor is pointing at:

GridView1.DataBind();

1. Make sure debugging is on by setting the Debug = True in the web.config file.

We are now ready to compile and test the application. You can type AROUT in the textbox and click the button to process the request. A sample of the results can be seen in Figure 5.

Figure 5. The sample application in action

The New Web Server Controls - An Overview

The ASP.NET 2.0 framework introduces various new web server controls that work with the data source controls. These are the GridView , the DetailsView and FormView controls. The difference between these three controls is in how you want to manage and display your data. You can use the GridView to manage a set of database records, the DetailsView for a single record and lastly if you want complete control of your display, consider using the FormView .

Another important feature of the data access model is that of backward compatibility. Web server controls like the Datagrid and Datalist controls are supported in ASP.NET 2.0.

The GridView Control

The GridView control is the successor of the Datagrid control with the added advantage of writing less code. The GridView can:

• Display a set of records
• Sort a set of records
• Page through a set of records
• Edit a record
• Quickly create master/detail forms

Here is a summary of the new field types that are contained within the GridView and a brief description of what has been updated since the DataGrid was introduced in ASP.NET 1.x:

Field Type	Description
BoundField	Displays the value of the field, and acts the same as the BoundColumn .
ButtonField	Displays the value of a field as a Command button. You can choose the link or push button style. When clicked, the page posts back and fires a RowCommand server event.
CheckBoxField	A Checkbox is displayed for each row.
CommandField	Defines a Command button (an enhanced version of the ButtonField ) provides support for edit, update and cancel operations.
HyperLinkField	The field is a hyperlink to a different page. An important property is the DataNavigateUrlFields which allows multiple columns to be passed (separated by a comma).
ImageField	Displays an image.
TemplateField	Exactly like the TemplateColumn, It displays user-defined content for each item in the column. Use this column type when you want to create a custom column field. The template can contain any number of data fields combined with literals, images, and other controls.

The following is a code example of the GridView control with the resulting output shown in Figure 6.

<asp:GridView ID="GridView1" Runat="server" 
  DataSourceID="SqlDataSource1" AutoGenerateColumns="False"   
  AllowPaging="True" AllowSorting="True" BorderWidth="1px"
  BackColor="LightGoldenrodYellow" GridLines="None" CellPadding="2"   
  BorderColor="Tan" ForeColor="Black">
  <FooterStyle BackColor="Tan"></FooterStyle>
  <PagerStyle ForeColor="DarkSlateBlue" HorizontalAlign="Center" 
    BackColor="PaleGoldenrod"></PagerStyle>
  <HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
  <PagerSettings Mode="NextPreviousFirstLast"></PagerSettings>
  <AlternatingRowStyle BackColor="PaleGoldenrod" ></AlternatingRowStyle>
  <Columns>
    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
      SortExpression="CompanyName" />
    <asp:BoundField DataField="ContactName" HeaderText="ContactName" 
      SortExpression="ContactName" />
    <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" 
      SortExpression="ContactTitle" />
    <asp:BoundField DataField="OrderDate" HeaderText="OrderDate" 
      SortExpression="OrderDate" />
    <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
      SortExpression="ProductName" />
    <asp:BoundField DataField="Quantity" HeaderText="Quantity" 
      SortExpression="Quantity" />
  </Columns>
  <SelectedRowStyle ForeColor="GhostWhite"  
    BackColor="DarkSlateBlue">
  </SelectedRowStyle>
</asp:GridView>

Figure 6. The GridView in action

The DetailsView Control

The DetailsView server control, similar to the GridView control, allows the developer to easily manage and update individual data records. By default it displays as a vertical layout format and typically is used to display records one at a time. By setting the corresponding Datasource editable property to true you can update the record without any code required. This control is often used in a master/detail scenario where the selected record of the master control determines the DetailsView display record.

Here is an example of the details view control, you can see there are no field items shown, as it is all contained within the DataSource control The main property here to focus on is the DataSourceID where the value is the ID of the Datasource control:

<asp:DetailsView ID="DetailsView1" Runat="server"
  DataSourceID="ObjectDataSource1"
  AllowPaging="False" BackColor="White" BorderWidth="1px"
  BorderColor="#3366CC" BorderStyle="None" CellPadding="4">
  <FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
  <RowStyle ForeColor="#003399" BackColor="White"></RowStyle>
  <PagerStyle ForeColor="#003399" HorizontalAlign="Left" 
    BackColor="#99CCCC"></PagerStyle>
  <HeaderStyle ForeColor="#CCCCFF" Font-Bold="True"
    BackColor="#003399"></HeaderStyle>
  <EditRowStyle ForeColor="#CCFF99" Font-Bold="True" 
  BackColor="#009999"></EditRowStyle>
</asp:DetailsView>

The FormView Control

The form view control is a container to provide a free form layout for viewing and editing data. Used primarily for data entry pages, it can change its mode from being editable to read-only by setting the DefaultMode property. This changes the default view of labels to textboxes. If the Datasource provides more than one record to the FormView control, the control allows you to page through the records individually.

Source data is based on the Datasource control making it very easy to implement.

Data Binding Syntax

I have just described the three new web server controls used to display and maintain data. These controls automatically bind data via a Datasource , but it does not show how we can manually bind fields to these controls.

There are three types of data binding syntax that can be used with any of the previously mentioned web server controls. The three types also include the binding syntax used in .NET1.x :

<%# DataBinder.Eval(Container.DataItem, "<name>", ["<format>"]) %>
<%# Eval("[column-name|property|field]"[, "format"]) %>
<%# Bind("column-name"[, "format"]) %>

You should generally use the Eval() statement as it's the default context for all data-binding expressions. The Bind() method should be considered based on the following scenarios:

Use the Bind method if:

• The Datasource requires parameters and you want to push these values back to the database.
• Any of the following templates EditItemTemplate or InsertItemTemplate (as used in the FormView ) are being used.
• The new web server controls ( DetailsView , FormView or GridView ) are being used and are populated using the DataSourceID which requires parameters to be passed to a method (Select, Insert, Update or Delete).
• Any of the new web server controls ( DetailsView , FormView or GridView ) are being used.

For XML data sources, this is slightly different as it requires you to use either of the following two statements:

XPath() : This returns a value from a single node. The syntax is denoted below:

<%# XPath("xpath-expression"[, "format"]) %>

XPathSelect() : This returns a collection. The syntax is denoted below:

<%# XPathSelect("xpath-expression") %>

The code below demonstrates the use of these XML statements within a FormView control. The FormView control utilizes the XMLDataSource (ds1) that uses the following XML:

<?xml version="1.0" ?>
<employee-list>
  <employee id="1437">
    <name>Mike</name>
    <department>Sales</department>
    <phone-list>
      <phone>3867</phone>
      <phone>773-6482</phone>
      <phone>(278) 555-3678</phone>
      <phone>(643) 555-1101</phone>
    </phone-list>
  </employee>
  <employee id="7290">
…

The XMLDataSource is named Ds1 and bound to the FormView control through the DataSourceID property. The XPath() method grabs a node at a time (Example "Mike") while the XPathSelect() method would return all the phone numbers and display them within the Repeater control using the XPath() method again.

<asp:FormView ID="formview1" runat="server" DataSourceID="ds1" 
  AllowPaging="True">
  <ItemTemplate>
    Name:<asp:Label ID="lbl1" Runat="server" Text='<%# XPath("name") %>'/>
    <br/>
    Address: <asp:Label ID="lbl2Dept" Runat="server" Text='<%#
      XPath("Address") %>' /><br />
      Phone Numbers:<br />
    <asp:Repeater DataSource='<%# XPathSelect("phone-list/phone") %>'
      runat="server">
   <ItemTemplate>
    <asp:Label ID="lblPhone" runat="server" Text='<%# XPath(".") %>' /><br/>
  </ItemTemplate>
  </asp:Repeater>    
  </ItemTemplate>
</asp:FormView>

Limitations of the Solution

One of the problems that the sample code does not address is that the connectionstring (within the web.config file) is saved as plain text. The data source automatically creates this tag and stores it as plain text. To get around this problem, I suggest looking at storing an encrypted connectionstring and decrypt it using the ObjectDataSource control.

The other limitation of the sample is that it doesn't look at inserting, updating or deleting data. This can be addressed either through the data source wizard or through code.

Further Enhancements

The sample code provided with this article is only a barebones example of some of the ways the new web server controls can be used in your own web applications. Other areas that you can consider, but is not part of the sample are the following:

• Inserting, updating and deleting data using the Detailsview and Gridview controls
• Implementing the Formview control

Conclusion

ASP.NET 2.0 has introduced a new set of web server controls that provide developers more control and flexibility over their data. The controls provide many advantages over the previous versions of ASP.NET such as, the limited amount of code required to build complex data access pages, additional new control features and the data architecture that is extensible and designed to grab data from many data sources.

As you can see Microsoft has gone to a great deal of work to reduce the amount of code required to perform these data related tasks. This article mentioned about the different types of data source controls that can be used in web applications, namely the SQLDatasource, ObjectDatasource, AccessDataSource, XMLDatasource and the SitemapDatasource controls.

An example of combining a data source control to a web server control was then presented to help show how the relationship works. Lastly I discussed in detail the new web server controls, with a brief overview of the grid, detail and form view controls.