The purpose of code-behind development is to encapsulate the business logic behind an application within separate, stand-alone software components. The goal is to divide an application into different layers, or tiers—a business logic layer and a user interface layer. Doing so promotes creation of processing components that can be shared among multiple pages, and it becomes easier to maintain the components separately than if coded multiple times on multiple pages.
A second way to encapsulate page components within different layers is to separate "data access" from the user interface. In previous applications this has been accomplished with code-behind files in which data access is part of the processing logic. ASP.NET 2.0 introduces more direct ways to create a data access layer.
In many of the applications you have seen so far, a Web page combines a data source control (such as an AccessDataSource) with an information display control (such as a GridView).
<asp:AccessDataSource id="BookSource" Runat="Server" DataFile="../Databases/BooksDB.mdb" SelectCommand="SELECT * FROM Books"/> <asp:GridView id="BookGrid" Runat="Server"/> DataSourceId="BookSource"/>
In this example, a data access method is contained on the page, no different in fact from coding a script on the page to connect to the database, issue an SQL command, and assign the resulting DataReader to the GridView. The only difference is that the script is encapsulated within the AccessDataSource control rather than appearing separately in the script portion of the page.
As you have also seen, this same data access method has been employed on numerous pages throughout these tutorials, each time using the same AccessDataSource to retrieve the same set of database records. Consider what would happen if the design of the database changed. Now, a different set of records may need to be retrieved from the redesigned database, forcing a need to track down and change all instances of the AccessDataSource. Or, consider the same situation arising if the path to the database changes. The point is, the data access method is coded along side the control that displays its returned recordset. A better solution is to uncouple data access from its display. As in the case with code-behind scripts, a separate level, or tier, of data access makes it easier to share data access components and to manage changes wrought by changing database designs and locations.
The <asp:ObjectDataSource> Control
Under ASP.NET 2.0, a new <asp:ObjectDataSource> control permits you to remove the mechanics of data access to a separate software component, and to link to that component through the ObjectDataSource control. The control is, in effect, a "logical" description of data access whose "physical" implementation details are hidden inside a code-behind data-access component. An information display control such as a GridView binds to the ObjectDataSource, which solicits behind-the-scenes data access by the data-access component. A general format for coding an ObjectDataSource is given in Figure 11-9.
<asp:ObjectDataSource id="id" Runat="Server" TypeName="class" SelectMethod="method" />
An id is required for reference by the control that links to the data source. TypeName gives the name of a Visual Basic class containing the data access component; SelectMethod gives the name of the data access method contained in the class, and which returns the recordset bound to the display control.
Creating a Data Access Class
A Visual Basic data access class is a stand-alone component that retrieves and returns a set of records from a database. This work is performed by a data-access method coded as part of the class. In the following listing, a BookInfo class is defined. It contains a Get_Books() method (function) that retrieves a set of records from the Books table of the BooksDB.mdb database and returns the DataReader to any ObjectDataSource that calls it.
Imports System.Data.OleDb Public Class BookInfo Function Get_Books() Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "SELECT * FROM Books ORDER BY BookID" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() Return DBReader DBReader.Close() DBConnection.Close() End Function End Class
The app_Code Directory
Like a code-behind file, the Visual Basic class is saved with the extension .vb. In this example, the file is saved as BookInfo.vb. In order to make the class accessible to all pages that call it, it must be saved in the app_Code directory located in the root directory of the application. In the current example, the app_Code directory appears inside the c:\eCommerce root directory.
Binding to a Data Access Class
Once a data access class has been placed inside the app_Code directory, it is called by an ObjectDataSource control for binding its output to a display control coded on the page. In the following example, a GridView appears on a page. The GridView is linked to an ObjectDataSource through its DataSourceID. The ObjectDataSource points to the BookInfo class contained in the BookInfo.vb file through its TypeName attribute, and it calls the class's Get_Books() method through its SelectMethod attribute.
<h3>Data Access with an ObjectDataSource</h3> <asp:ObjectDataSource id="BookSource" Runat="Server" TypeName="BookInfo" SelectMethod="Get_Books"/> <asp:GridView id="GridView" Runat="Server" AutoGenerateColumns="False" DataSourceID="BookSource"> <Columns> <asp:BoundField HeaderText="Book ID" DataField="BookID"/> <asp:BoundField HeaderText="Title" DataField="BookTitle"/> <asp:BoundField HeaderText="Price" DataField="BookPrice"/> </Columns> </asp:GridView>
As a result, the Get_Books() method returns the retrieved DataReader (DBReader) to the ObjectDataSource, which binds the recordset to the GridView.
Example
You should notice identical coding between this BookInfo.vb data-access class and the code-behind DBPage.vb class used in the previous tutorial. You should also notice that the page using the data-access class does not require inheriting the class nor a src pointer to the location of the class. All links between the Web page and the data-access class are given in the TypeName and SelectMethod attributes of the ObjectDataSource. An ObjectDataSource is simply easier to use than a code-behind file for supplying database information to a server control.
In the current example, the ObjectDataSource returns all records and all fields from the Books table. Display of selected fields from the returned recordset is governed by the BoundFields defined in the GridView. An alternative design for the BookInfo class is to create different methods for different returned recordsets. Then, different pages can call these different methods to return different recordsets.
Passing Parameters to a Data Access Class
An ObjectDataSource can be supplied with control parameters to restrict access to selected records. In the following example, a DropDownList permits the user to select the types of books to display.
The DropDownList is itself populated by an ObjectDataSource that calls a new method added to the previous BookInfo class. Coding for the DropDownList and for the new Get_Types() method is shown below.
<asp:ObjectDataSource id="TypeSource" Runat="Server" TypeName="BookInfo" SelectMethod="Get_Types"/> <asp:DropDownList id="TypeList" Runat="Server" AutoPostBack="True" DataSourceID="TypeSource" DataTextField="BookType" DataValueField="BookType"/>
Imports System.Data.OleDb Public Class BookInfo Function Get_Types() Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "SELECT DISTINCT BookType FROM Books ORDER BY BookType" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() Return DBReader DBReader.Close() DBConnection.Close() End Function ... End Class
Code for the GridView is identical to the previous example. Its ObjectDataSource, though, now includes a ControlParameter identifying the SelectedValue from the DropDownList as a parameter named BookType that is passed to the Get_Books() method of the BookInfo class.
<asp:ObjectDataSource id="BookSource" Runat="Server" TypeName="BookInfo" SelectMethod="Get_Books"> <SelectParameters> <asp:ControlParameter Name="BookType" ControlID="TypeList" PropertyName="SelectedValue"/> </SelectParameters> </asp:ObjectDataSource> <asp:GridView id="BookGrid" Runat="Server" AutoGenerateColumns="False" DataSourceId="BookSource"> <Columns> <asp:BoundField HeaderText="Book ID" DataField="BookID"/> <asp:BoundField HeaderText="Title" DataField="BookTitle"/> <asp:BoundField HeaderText="Price" DataField="BookPrice"/> </Columns> </asp:GridView>
The Get_Books() method of the BookInfo class is revised to accept the passed BookType value identified in the ObjectDataSource's ControlParameter. This value is associated with the parameter @BookType in a WHERE clause added to the revised SELECT statement.
Imports System.Data.OleDb Public Class BookInfo Function Get_Types() Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "SELECT DISTINCT BookType FROM Books ORDER BY BookType" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() Return DBReader DBReader.Close() DBConnection.Close() End Function Function Get_Books (BookType as String) Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "SELECT * FROM Books WHERE BookType = @BookType ORDER BY BookID" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@BookType", BookType) DBReader = DBCommand.ExecuteReader() Return DBReader DBReader.Close() DBConnection.Close() End Function End Class
The revised SELECT statement includes a reference to parameter @BookType, a placeholder for a value that restricts selection of records to those with this value in the BookType field. There is a need, then, to associate this parameter with an actual value.
The Command Object's Parameters Collection
A value is associated with a parameter appearing in a Command object (DBCommand in this example) by adding a parameter name and value to the Command object's Parameters collection. A general format for this collections' AddWithValue() method is shown in Figure 11-12.
CommandObject.Parameters.AddWithValue("@parameter", value)
The name of the parameter, @parameter, appearing in the Command object is associated with a value. In the current example, this value comes from the BookType argument passed to the Get_Books() method when it is called by the ObjectDataSource. Therefore, the following code appears in the script:
DBCommand.Parameters.AddWithValue("@BookType", BookType)
The @BookType parameter appearing in the SELECT statement is replaced by the BookType value passed to the Get_Books() method. The selected records are then returned in the DataReader passed back to the ObjectDataSource and bound to the GridView.