Putting the VS 2005-Generated Data Access Layer Into Perspective : Page 3

Importing Data Sources in Windows Forms Applications
To add data to your Windows application, you start by dropping a BindingSource component onto your form. Next, you set the DataSource property of the component. There are a number of feasible data sources, including arrays, collections, and lists of custom types. Lists are typically extended collections that implement any of the following interfaces: IBindingList, ITypedList, or IListSource. Note that popular ADO.NET container classes such as DataSet and DataTable fall in the last category, as they implement the IListSource interface.

In the beginning, you have no data source in the application and have to create one. When you select the DataSource property in the BindingSource component, you face a window like that in Figure 3. Click on the link at the bottom of the popup window and start the wizard to add a new data source to the current project. Here's where you start having some fun.

Figure 3: Adding a new data source to the project.

Politely enough, Visual Studio 2005 asks where the application intends to get data from. There are three possible scenarios: database, external Web service, or custom object. If you opt for a Web service, Visual Studio 2005 opens the Add Web Reference dialog box and lets you choose either a local or remote Web service and creates the corresponding proxy class. It is then up to you to complete binding to retrieve data and bind it to controls. Similarly, you can choose a custom object inside any of the assemblies referenced by the project.

Authors of the custom set of objects or Web service are responsible for designing and implementing the object model using any approach and pattern they prefer and reckon effective. When you opt for the database option, Visual Studio 2005 generates a lot of code for you. Typically, you add a DataSet component; that is, a set of related tables described through an XSD file. The DataSet component represents an in-memory DataSet object with one or more data tables. So far, the behavior of Visual Studio 2005 is not that much different from Visual Studio 2003 where typed dataset classes were created from declarative XSD files.

Visual Studio 2005 does more though. In particular, it introduces the concept of a table adapter.

Table Adapters
A table adapter provides support for communication between an application and its database. To exemplify, a table adapter connects to a database and executes commands; any returned data is stored to a DataTable object for further processing. You can also use a table adapter to send updates back to the database. A table adapter is an instance of a tool-generated class that acts as an adapter. A table adapter is just a specialization of the adapter class defined in .NET managed providers. Simply put, it is a wrapper object acting as the adapter for a particular table. Interestingly enough, there's no base class for table adapters.

This code snippet shows the typical signature of a table adapter class.

   Partial Public Class CustomersTableAdapter
      Inherits System.ComponentModel.Component
      ...
   End Class

Internally, a table adapter class incorporates a SqlCommand, SqlConnection, and SqlDataAdapter object. In Visual Basic .NET, the data adapter is decorated with the WithEvents modifier to catch events. Table 2 lists the internal properties that make up a table adapter.

Table 2. Internal members of a table adapter class.

Member	Modifier	Description
Adapter	Private ReadOnly	References the data adapter used for communicating with the corresponding database table.
Connection	Friend	References the connection object used for communicating with the corresponding database table.
CommandCollection	Protected ReadOnly	Defines a collection of command objects that all together describe the behavior of table adapter. To extend the tasks that can be accomplished through the adapter, you add a new command to this collection.
ClearBeforeFill	Public	Indicates whether the table should be emptied before filling. True by default.

The internal data adapter is initialized in the InitAdapter method. The method is called from within the get accessor of the Adapter property.

   Private ReadOnly Property Adapter() _
      As System.Data.SqlClient.SqlDataAdapter
      Get
         If (Me._adapter Is Nothing) Then
            Me.InitAdapter
         End If
         Return Me._adapter
      End Get
   End Property

In this context, the adapter is a helper object used to drive standard CRUD (Create, Read, Update, Delete) operations on the underlying table. The adapter defines default T-SQL statements for Insert, Delete, and Update commands. It doesn't include a Select command. Aimed at populating the data table with data, the adapter implements its Select functionality through a pair of public methods on the table adapter named Fill and GetData.

Any data operation requires a physical connection to the data source. Listing 1 shows the internal implementation of the Connection property. The connection string is stored in the application's configuration file and can be edited at any time without touching and recompiling the base code. When you set a new connection, the information is automatically passed along to all command objects in the table adapter.

As you'll see later on, the table adapter class is a well-known element in a popular design pattern for data access. Through the CommandCollection property, it lists all the actions one can execute on the table. In a word—it defines the "behavior" of the table.

   Protected ReadOnly Property CommandCollection() _ 
      As System.Data.SqlClient.SqlCommand()
      Get
         If (Me._commandCollection Is Nothing) Then
            Me.InitCommandCollection
         End If
         Return Me._commandCollection
      End Get
   End Property

By default, the CommandCollection contains just one command—the T-SQL command for the Select operation. The following code shows the initialization of the collection.

   Private Sub InitCommandCollection()
     _commandCollection = New SqlCommand(0) {}
      _commandCollection(0) = New SqlCommand
      _commandCollection(0).Connection = Me.Connection
      _commandCollection(0).CommandText = _
         "SELECT * FROM Customers"
      _commandCollection(0).CommandType = _
         CommandType.Text
   End Sub

The default command object in the collection is wrapped by the Fill and GetData methods (see Listing 2), where Fill populates the data table with the results of the command and GetData returns a new DataTable object with the same data. All public methods of the table adapter class are listed in Table 3.

Table 3: Methods of a table adapter class.

Method	Description
Fill	Populates the data table associated with this table adapter with the results returned by the default command in the command collection.
GetData	Returns a newly created DataTable object filled with the data obtained from the execution of the default command in the command collection.
Delete	Executes the DELETE command associated with the internal data adapter
Insert	Executes the INSERT command associated with the internal data adapter
Update	Executes the UPDATE command associated with the internal data adapter. The method features quite a few additional overloads.

Listing 3 shows the signatures of the Delete, Insert and Update methods. As you can see, they are auto-generated based on a selected table and a selected number of columns. The source code for each table adapter, and parent DataSet component class, is located in the designer file of the DataSet component class. For example, if you have a northwind.xsd dataset file, the table adapter code is located in a file named northwind.designer.vb. You can edit signatures and commands for each statement and, for example, replace standard T-SQL with stored procedures running inside transactions.

Finally, it is worth noting that the Update method has a few overloads, as shown below:

   Public Overloads Overridable Function Update( _
      ByVal dataTable As CustomersDataTable) _
   As Integer
   
   Public Overloads Overridable Function Update( _
      ByVal dataSet As northwndDataSet) _
   As Integer
   
   Public Overloads Overridable Function Update( _
      ByVal dataRow As DataRow) _
   As Integer
   
   Public Overloads Overridable Function Update( _
      ByVal dataRows() As DataRow) _
   As Integer

All these overloads map to one of the standard overloads of the Update method (batch update trigger) on the SqlDataAdapter class. All the methods are implemented as thin wrappers around the corresponding SqlDataAdapter method. Here's an example:

   Public Overloads Overridable Function Update( _
      ByVal dataRows() As DataRow) As Integer
      Return Me.Adapter.Update(dataRows)
   End Function

Previous Page

Next Page