This is the second part of a tutorial article which demonstrates how to create a data access layer (DAL) with .NET 2.0, ADO.NET 2.0, Visual Studio 2005, and Visual Basic 2005.

A basic data access layer component (DAL) for Microsoft Access databases was created in part one.

In part two the DAL component will be extended and then used for the first time by a Windows Forms application.

Before the article discusses how to extended the DAL component it will first discuss the DataSets created in part one of the article.

Understanding the DataService Project DataSets

In part one, two DataSets were added to the DataService project; the AjaxDataSet and the NorthwindDataSet. A DataSet was added each time a DataSource was added to the DataService project. One DataSource was added to the DataService project for each Microsoft Access database the DataService DAL component will access.

Part of the process of adding a DataSource to a project is selecting one or more tables from a database. For example, in part one the Ajax database Customer and Orders tables were selected.

Selecting the Customer table from the Ajax database automatically added a Customer DataTable to the Ajax DataSet.  Selecting the Orders table from the Ajax database automatically added an Orders DataTable to the Ajax Dataset.  In the Solution Explorer double-click the AjaxDataSet in the DataService project to see the two DataTables that were created.

At the same time as the DataTables were automatically added to the DataSet, default TableAdapters were created and associated with the DataTables.

TableAdapters

A TableAdapter named 'CustomerTableAdapter' was added and associated with the Customer DataTable. A TableAdapter named 'OrdersTableAdapter' was added and associated with the Orders DataTable.

TableAdapters provide communication between an application and a database. A TableAdapter connects to a database, executes queries or stored procedures, and either returns a new data table populated with the returned data or fills an existing DataTable with the returned data. TableAdapters are also used to send updated data from your application back to the database.

Users of previous versions of Visual Studio can think of a TableAdapter as a DataAdapter with a built-in connection object and the ability to contain multiple queries. Each query added to a TableAdapter is exposed as a public method that is simply called like any other method or function on an object.


TableAdapter Queries

When the AjaxDataSet and the NorthwindDataSet were created in part one of this article, an initial query was automatically added to every TableAdapter. For example, an initial query was automatically added to the CustomerTableAdapter and the OrdersTableAdapter in the AjaxDataSet:

 

The initial query in a TableAdapter defines the schema of the TableAdapter's associated DataTable. For example, the Fill query in the CustomerTableAdapter defines the schema for the Customer DataTable.

Additional queries can be added to a TableAdapter, for example to return different values, update the database, and insert new rows in the database.

Extend the DAL Component

The example Windows Forms application will access application data via the DataService DAL component.

The DataService DAL component will utilize the AjaxDataSet, the NorthwindDataSet, and any DataSets added later to communicate with Microsoft Access databases.

The DataService DAL component must be extended with code so that it can talk to both the Windows Forms application and the application databases.

Follow the instructions below to extend the DataService DAL component.

Instructions

A. Add a public property with a private backing field to the DAL component for each DataSet it will utilize.

Right-click the DAL component in the DataService project and select 'View Code'.

Declare a public property and a private backing field variable for the AjaxDataSet and the NorthwindDataSet.

An example of the private backing field variables can be seend in the 'Field Variables' region of the DAL source code:

 

#Region "Field Variables"

 

    ' Declare a field variable named m_AjaxDataSet of type AjaxDataSet.t

    Private m_AjaxDataService As AjaxDataSet

 

    ' Declare a variable named m_NorthwindDataService of type NorthwindDataSet.

    Dim m_NorthwindDataService As NorthwindDataSet

 

#End Region

An example of the public properties can be seen in the 'Properties' region of the DAL source code.

 

#Region "Properties"

 

    ' AjaxDataService Property

    Public Property AjaxDataService() As AjaxDataSet

        Get

            If Me.m_AjaxDataService Is Nothing Then

                Me.m_AjaxDataService = New AjaxDataSet

            End If

            Return m_AjaxDataService

        End Get

        Set(ByVal value As AjaxDataSet)

            m_AjaxDataService = value

        End Set

    End Property

 

    ' NorthwindDataService Property

    Public Property NorthwindDataService() As NorthwindDataSet

        Get

            If Me.m_AjaxDataService Is Nothing Then

                Me.m_NorthwindDataService = New NorthwindDataSet

            End If

            Return m_NorthwindDataService

        End Get

        Set(ByVal value As NorthwindDataSet)

            m_NorthwindDataService = value

        End Set

    End Property

#End Region

B. Add public methods that will used by client code to fill the DataTables in the DAL component's AjaxDataSet and NorthwindDataSet objects.

Right-click the DAL component in the DataService project and select 'View Code'.

Declare a public method to fill the DAL's AjaxDataSet Customer DataTable and Orders DataTable.

Declare a public method to fill the DAL's NorthwindDataSet Employees DataTable and Products DataTable.

An example of the fill methods can be seen in the 'Methods' region of the DAL source code:

 

#Region "Methods"

 

    ' Ajax Customer Fill Method

    Public Sub AjaxCustomerFill()

        Dim customerTA As New AjaxDataSetTableAdapters.CustomerTableAdapter

        customerTA.Fill(Me.AjaxDataService.Customer)

    End Sub

 

    ' Ajax Orders Fill Method

    Public Sub AjaxOrdersFill()

        Dim ordersTA As New AjaxDataSetTableAdapters.OrdersTableAdapter

        ordersTA.Fill(Me.AjaxDataService.Orders)

    End Sub

 

    ' Northwind Employees Fill Method

    Public Sub NorthwindEmployeesFill()

        Dim employeesTA As New NorthwindDataSetTableAdapters.EmployeesTableAdapter

        employeesTA.Fill(Me.NorthwindDataService.Employees)

    End Sub

 

    Public Sub NorthwindProductsFill()

        Dim productsTA As New NorthwindDataSetTableAdapters.ProductsTableAdapter

        productsTA.Fill(Me.NorthwindDataService.Products)

    End Sub

 

#End Region

Use the DAL Component in the Windows Forms Application

Follow the instructions below to use the DAL component in the Windows Forms application.

Instructions

A. Build the Visual Studio solution.

B. Add a DataService project reference to the the Windows Forms application.

C. Add a private backing field and a public property for a DataService DAL object to the Windows Forms application.

Add a private backing field and public property for a DAL object to a class in the Windows Forms application.

An example of the backing field and public property for a DAL object can be seen in the 'MyApplicationExtensions' class in this article's source code.

 

' Private backing field variable and public property for

' a DataService DAL object.

Private m_DAL As DataService.DAL

Public Property DAL() As DataService.DAL

    Get

        If Me.m_DAL Is Nothing Then

            Me.m_DAL = New DataService.DAL

        End If

        Return Me.m_DAL

    End Get

    Set(ByVal value As DataService.DAL)

        Me.m_DAL = value

    End Set

End Property

D. Use the DAL object in a Windows Form.

 

Add a Button control named 'FillButton' and a DataGridView control named 'CustomersDataGridView' to the ExampleOneForm in the Windows Forms project.

 

Add the code below to the code behind the ExampleOneForm in the Windows Forms project:

 

' Declare a variable named customerBindingSource of type BindingSource.

' Call the BindingSource class' 'New' constructor passing it

'   the application DAL object's AjaxDataService DataSet as a data source and

'   the string 'Customer' to identify the Customer DataTable as the data member that will be the binding source.

' Assign the resulting BindingSource object to the customerDataSource variable.

Private customerDataSource As New BindingSource(My.Application.DAL.AjaxDataService, "Customer")

 

Private Sub DemoOneForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    ' Make this form a MDI child of MainForm.

    Me.MdiParent = My.Forms.MainForm

    ' Assign this class' customerBindingSource to this form's customerDataGridView DataSource property.

    Me.CustomersDataGridView.DataSource = Me.customerDataSource

End Sub

 

 

Private Sub FillButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillButton.Click

    ' Call this application's DAL object's AjaxCustomerFill method to fill the Customer DataTable in the

    ' DAL's AjaxDataSet object.

    My.Application.DAL.AjaxCustomerFill()

End Sub

 

E. Add a copy of the Northwind and Ajax .mdb files to the Bin folder in the Windows Forms application.

 

F. Run the application.  From the menu select Examples -> Example One.  Click the Fill button.

 

Click the link above to download Visual Basic source code in a Visual Studio 2005 solution which demonstrates how to use Visual Studio to extend the DAL component created in part one of this article and use it the first time by a Windows Forms application.

 

Help

 

TableAdapter Overview

How to: Create TableAdapters