Although much of the content of Web pages comes from fixed text and XHTML and from output produced from user inputs, the majority of information comprising modern Web sites is extracted from external data sources residing on the server—from databases, text files, XML files, and other such data stores. Web page controls and scripts retrieve this external information, process it, and display it through other server controls to create final Web page output. The page is created dynamically, when it is requested, by embedding external information in the output areas reserved for its display.

Databases are often the primary data stores for Web page information. Many of the topics in these tutorials deal with database access and in maintaining the currency of database information. Here, consideration is given to some basic techniques for withdrawing that information for display on a Web page.

Under ASP.NET 2.0, convenient and easy-to-use methods to access database information are introduced. These are in the form of new data source controls. Whereas previously, database access required scripts to link to, open, extract, and iterate records in a database, all of this processing is now encapsulated inside single controls coded declaratively on the page.

The <asp:AccessDataSource> Control

When using a database as the source of Web page content, it is necessary to perform a series of steps to (1) identify the server path to the database, (2) specify the driver software to use when accessing the database, (3) open the database for access by scripts, (4) issue an appropriate SQL statement to retrieve fields of data from specified tables in the database, (5) iterate the returned set of records to display them on the page, and (6) close the database connection when done. Previous to ASP.NET 2.0, these steps required a script to explicitly perform these actions to retrieve information for page display. Now, most of these steps are encapsulated in and can be performed automatically through special data source controls.

Figure 3-9 gives the general format for using the <asp:AccessDataSource> control to link to and return a set of records from a Microsoft Access database. This control is one of several available to work with different database products. The present format shows control properties needed to extract database information for page display. Later formats are introduced for performing database maintenance activities.

<asp:AccessDataSource id="id" Runat="Server"
  DataFile="path"
  SelectCommand="SQL SELECT statement|query name"
  SelectCommandType="Text|StoredProcedure"
  DataSourceMode="DataSet|DataReader"
/>

An id property must be assigned to the control in order to identify the returned recordset to other ASP.NET controls which display the returned records.

The DataFile property gives the directory path to the database. This can be the physical server path beginning with the drive letter, it can be a virtual path relative to the Web page containing the control, or it can be a path relative to the root Web directory.

For the example BooksDB.mdb database used in these tutorials, its physical path is given by the following DataFile property.

DataFile="c:\eCommerce\Databases\BooksDB.mdb"

Alternately, the database can be accessed through a path relative to the Web page containing the control.

DataFile="../Databases/BooksDB.mdb"

A third alternative is to specify a path relative to the root directory (the virtual Web directory) under which the page and database are stored. Thus, in the path

DataFile="~/Databases/BooksDB.mdb"

the character pair "~/" points to the root directory of the application (c:/eCommerce in this example) followed by the subdirectory path to the database. As a general rule, relative path syntax is preferred since it is less likely to need changing when moving applications to different servers or different directory structures.

The SelectCommand property provides two different means to retrieve information from a database. It can supply an SQL SELECT statement to identify the fields to retrieve from specified tables in the database. Any valid SELECT statement can be issued, either for a single table or for joined tables. The property value also can be the name of a stored procedure, in the case of an Access database, the name of a Query. When using a stored procedure, the SelectCommandType="StoredProcedure" property must be coded; it is not required for its default setting SelectCommandType="Text" for an SQL statement.

There are two DataSourceMode settings that can be made. The default DataSet mode stores the extracted recordset in server memory where it is available for further processing beyond its initial assignment to an information display control. This setting is necessary, for instance, when the display control provides sorting and paging options to rearranged and redisplayed a retrieved recordset. The DataReader mode is used when only a single access to the database is needed. This mode is a read-once, forward-only access method to retrieve a recordset for one-time-only display. It is more efficient than the DataSet method to retrieve a single set of records. The DataSourceMode is optional; using the default DataSet mode is adequate for most retrieval situations unless server efficiencies associated with large databases come into play.

Assume, for instance, that a selected set of fields is to be extracted from the Books table of the example BooksDB.mdb database. An appropriately coded <asp:AccessDataSource> control is shown below.

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="~/Databases/BooksDB.mdb"
  SelectCommand="SELECT BookID, BookType, BookTitle, BookPrice FROM Books
                 WHERE BookType = 'Database'
                 ORDER BY BookTitle"/>

An id is assigned so that a display control can point to this data source as the source for its information to display.

The DataFile property gives the path to the database, in this case expressed relative to the root directory of the Web application. Alternate path specifications give the relative path from the page to the database, "../Databases/BooksDB.mdb", or the full physical path to the database, "c:\eCommerce\Databases\BooksDB.mdb".

The SelectCommand property gives the SQL SELECT statement needed to retrieve four data fields from records in the Books table. Only those records with a BookType field value of 'Database' are selected, and all records are returned in sorted order by the BookTitle field. When the Web page is opened, this data source control immediately connects to the database and retrieves the recordset, making it available for display on the page.

The <asp:SqlDataSource> Control

The AccessDataSource is a special case of the general <asp:SqlDataSource> control used to access a variety of database products. An SqlDataSource requires ProviderName and ConnectionString properties to make a connection to a database.

<asp:SqlDataSource id="id" Runat="Server"
  ProviderName="provider name"
  ConnectionString="Provider=provider;
                    Data Source=path"
  SelectCommand="SQL SELECT statement|query name"
  SelectCommandType="Text|StoredProcedure"
/>

The ProviderName is "System.Data.OleDb" when connecting to an Access database. The ConnectionString includes Provider and Data Source parameters separated by a semicolon. For an Access database, the Provider is "Microsoft.Jet.OLEDB.4.0" and the Data Source gives the physical path to the database. The following SqlDataSource retrieves the same recordset as the previous AccessDataSource.

<asp:SqlDataSource id="BookSource" Runat="Server"
  ProviderName="System.Data.OleDb"
  ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; 
                    Data Source=c:\eCommerce\Databases\BooksDB.mdb"
  SelectCommand="SELECT BookID, BookType, BookTitle, BookPrice FROM Books
                 WHERE BookType = 'Database'
                 ORDER BY BookTitle"/>

Using Connection Strings

For both the AccessDataSource and SqlDataSource controls, the coded path to a database gives its current location, through either its relative path or physical path. Further, across the multiple pages of a Web site, this path is coded for every data source control appearing on all pages. This presents no particular problem unless, that is, the location of the database, or perhaps its name, is changed. Then it is necessary to track down and change these path references everywhere they occur, a possibly daunting task leading to oversights and errors.

Creating a Database Connection String

Under ASP.NET, it is possible to code the path to a database one time only; then, if its path or name changes, a coding change can be made to this single occurence for automatic propogation across all path references on all pages. This single occurence of a path specification appears in the web.config file, used previously to set up debugging preferences. In this case, a special <connectionStrings> section is added to the file where database connection information is coded.

The following rewrite of the previous web.config file has entries that can be used for both the DataFile attribute of an AccessDataSource and the ConnectionString attribute of an SqlDataSource, both of which include the path to the example BooksDB.mdb database.

<!-- Web.Config Configuration File -->
<configuration>

  <system.web>
    <customErrors mode="Off"/>
  </system.web>

  <connectionStrings>
  <add 
    name="AccessBooksConnection" 
    connectionString="c:\eCommerce\Databases\BooksDB.mdb" 
  />
  <add 
    name="SqlBooksConnection" 
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0; 
                      Data Source=c:\eCommerce\Databases\BooksDB.mdb" 
  />
  </connectionStrings>

</configuration>

A <connectionStrings> specification is made through an <add/> entry. A programmer-supplied name is given for the entry, and a connectionString gives the connection information.

For an AccessDataSource's DataFile attribute—here named AccessBooksConnection—the only required information is the physical path to the database. For an SqlDataSource—here named SqlBooksConnection—the connection string includes both a Provider and Data Source string, the latter giving the physical path to the database.

Using a Database Connection String

After a connection string entry is made in the web.config file, it can be used in a data source control through reference to its name. In the following listing, an AccessDataSource and an SqlDataSource point to the previous entries.

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="<%$ connectionStrings: AccessBooksConnection %>"
  SelectCommand="SELECT BookID, BookType, BookTitle, BookPrice FROM Books
                 WHERE BookType = 'Database'
                 ORDER BY BookTitle"
/>

<asp:SqlDataSource id="BookSource" Runat="Server" 
  ProviderName="System.Data.OleDb"
  ConnectionString="<%$ connectionStrings: SqlBooksConnection %>"
  SelectCommand="SELECT BookID, BookType, BookTitle, BookPrice FROM Books
                 WHERE BookType = 'Database'
                 ORDER BY BookTitle"
/>

Note the format of the references. They must be enclosed inside <%$ and %> symbols. The connectionStrings: parameter refers to the <connectionStrings> section of the web.config file; AccessBooksConnection and SqlBooksConnection refer to the names associated with the connection strings coded there.

Now, any time a database changes location or has its name changed, this change needs to be recoded only one time in the <connectionStrings> section of the web.config file. Thereafter, the change is automatically reflected in all occurences of a data source control on all pages of a Web site.

For purposes of this tutorial, a <connectionStrings> entry in the web.config file is not assumed. All connection strings are coded as physical or relative paths in the data source controls. This practice simply reinforces code learning; in a production environment, centralizing connection-string coding is certainly preferred.

Binding to a Display Control

There are numerous ways to display information extracted from a database. The easiest way to display recordsets, however, is through several information display controls available through ASP.NET. One of the handiest and easy-to-use of these controls is the <asp:GridView> control introduced earlier. The way in which it binds to a data source control is similar to most other display controls.

A GridView in its default configuration automatically displays the rows and columns of a recordset returned from a data source control. It even uses the field names in a database table as column headings for its table display. A GridView binds to a data source by giving the id of the data source in its DataSourceID property. To bind to the AccessDataSource described above (id="BookSource"), GridView coding is as simple as that shown below.

<asp:GridView DataSourceID="BookSource" Runat="Server"/>

A display control binds to a data source control during the page-load process. Therefore, the display control is already populated with returned information when the page opens. The resulting display is shown below.

You are likely to want more control over the aesthetics of the display than what is provided by the default GridView, although the default view is sufficient to test database connections. In later tutorials you learn the formatting tricks for the GridView.

Data Binding with Script

A Web site can be data driven in a second way in addition to reporting contents of external data sources. It can respond to user requests. Controls can be placed on the page to solicit user preferences about what information to display and how to display it. In order to demonstrate the ease with which users can become active in selecting page content, the following rewrite of the previous application provides six buttons for selecting different table displays, each of which is produced dynamically in response to the choice.

In this case, a script is needed to respond to user clicks on the buttons. As shown in the listing below, subprogram Display_Type is called to dynamically create an appropriate SQL SELECT statement to select all records of the chosen type. This SELECT statement is dynamically assigned to the AccessDataSource to return these records for display in a GridView.

<SCRIPT Runat="Server">

Sub Show_Type (Src As Object, Args As EventArgs)

  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice FROM Books " & _
              "WHERE BookType = '" & Src.Text & "'"
  BookSource.SelectCommand = SQLString

End Sub

</SCRIPT>

<form Runat="Server">

<h3>Select a book type:</h3>

<asp:Button Text="Database" Width="80px" OnClick="Show_Type" Runat="Server"/>
<asp:Button Text="Graphics" Width="80px" OnClick="Show_Type" Runat="Server"/>
<asp:Button Text="Hardware" Width="80px" OnClick="Show_Type" Runat="Server"/>
<br/>
<asp:Button Text="Systems"  Width="80px" OnClick="Show_Type" Runat="Server"/>
<asp:Button Text="Software" Width="80px" OnClick="Show_Type" Runat="Server"/>
<asp:Button Text="Web"      Width="80px" OnClick="Show_Type" Runat="Server"/>

<asp:AccessDataSource id="BookSource" Runat="Server" 
  DataFile="../Databases/BooksDB.mdb"/>

<asp:GridView DataSourceID="BookSource" Runat="Server"/>

</form>

The AccessDataSource includes the required DataFile property to point to the database. However, it does not include a SelectCommand to retrieve a set of records for display. In this case, records are chosen for display by clicking buttons, and the SelectCommand is composed in the associated script.

User choices are effected by assigning the six BookType field values in the database as the Text properties of the buttons. The buttons' labels, then, match the book-type values found in the database. Recall that when a button calls a subprogram, it identifies itself through the first item in the argument list, through argument Src in the example script. This button's Text property is given by the reference Src.Text, which translates as one of the BookType values in the database. Therefore, this Src.Text property can be plugged into a SELECT statement to retrieve records of this type.

Assume, for instance, the button labeled "Graphics" is clicked. Therefore, the subprogram reference to Src.Text produces the value "Graphics". Since a SELECT statement is composed with the following declaration and assignment,

SQLString = "SELECT BookID, BookType, BookTitle, BookPrice FROM Books " & _
            "WHERE BookType = '" & Src.Text & "'"

when Src.Text ("Graphics") is concatenated inside this string the following statement is produced:

SELECT BookID, BookType, BookTitle, BookPrice FROM Books 
WHERE BookType = 'Graphics'

Now it is a matter of assigning this statement to the SelectCommand property of the AccessDataSource. This assignment is done programmatically with the statement,

BookSource.SelectCommand = SQLString

The SelectCommand property of the control with id="BookSource" (the AccessDataSource) is assigned the SELECT statement stored in variable SQLString. Immediately upon this assignment, the newly composed SQL command is issued, and the AccessDataSource returns this set of records from the database. Its binding with the GridView automatically produces a new table display.

The above examples just scratch the surface of Web-based data access. Throughout these tutorials additional server controls demonstrate how to retrieve data sources and display their content to produce dynamic information for changing user needs. Working with databases is an exercise in using the SQL language to compose SELECT, INSERT, UPDATE, DELETE, and other SQL commands to carry out database processing. It is assumed you have basic facility with this language. If you need a review of SQL syntax, check the appendix to these tutorials.