Source Code: Data Access Layer For Microsoft Access Databases - Part 2
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. To read part one click here.
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
mike mcintyre http://www.getdotnetcode.com