Source Code: Data Access Layer (DAL) For MS Access Databases - Part 3
This is the third part of a tutorial article demonstrating how to create a data access layer (DAL) with .NET 2.0, ADO.NET 2.0, Visual Studio 2005, and Visual Basic 2005.
Click these links to view previous posts in this series: Part 1 Part 2
In part two the DataService project's DAL component was extended and then used for the fist time by a Windows Forms application.
Part three of the article will demonstrate how to use the the DAL component - from code in the sample Windows Forms application - to create, update, and delete data in the Access Ajax database.
When the AjaxDataSet and NorthwindDataSet were created in the DataService project in part one of this article, code to create, retrieve, update, and delete (CRUD) data in the application's Access databases was auto generated in the code behind the two DataSets.
Take some time now to familiarize yourself with the auto generated code behind the AjaxDataSet.
DataSet Designer Code
Open the DAL solutions in Visual Studio.
Click the 'Show All Files' button in the 'Solution Explorer' panel. Open up the 'Solution Explorer' view until you can see all the files beneath the AjaxDataSet and the NorthwindDataSet.
The code that was auto generated when the DataSets were created can be recognized by its '.Designer.vb' file suffix. For example, the 'AjaxDataSet.Designer.vb' file contains the auto generated code for the AjaxDataSet.
Double-click the 'AjaxDataSet.Designer.vb' file. WARNING: Do not change this code. If you do, your changes will eventually be overwritten by the designer.
Examine the auto generated Ajax DataSet code. The designer code defines the DataSet which provides access to data through a strongly typed metaphor. Tables and columns that are part of the DataSet can be accessed using user-friendly names and strongly typed variables.
A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet. Additionally, a typed DataSet provides strongly typed methods, events, and properties. This means you can access tables and columns by name, instead of using collection-based methods. Aside from the improved readability of the code, a typed DataSet also allows the Visual Studio .NET code editor to automatically complete lines as you type (Intellisense).
Additionally, the strongly typed DataSet provides access to values as the correct type at compile time. With a strongly typed DataSet, type mismatch errors are caught when the code is compiled rather than at run time.
Spend some time examining the Ajax DataSet code that opened when you double-clicked the 'AjaxDataSet.Designer.vb' file.
Find the CustomerTable class which begins with the line that reads:
Partial
Public Class CustomerDataTable
This class defines many members for working with the Customer table:
Notice that the CustomerDataTable class encapsulates a private backing field and a public property for each of the columns in the Ajax database's Customer table, for example the CustomerID column:
Private columnCustomerId As System.Data.DataColumn
Public ReadOnly Property CustomerIdColumn() As System.Data.DataColumn
Get
Return Me.columnCustomerId
End Get
End Property
Notice that the CustomerDataTable class defines events:
Public Event CustomerRowChanging As CustomerRowChangeEventHandler
Public Event CustomerRowChanged As CustomerRowChangeEventHandler
Public Event CustomerRowDeleting As CustomerRowChangeEventHandler
Public Event CustomerRowDeleted As CustomerRowChangeEventHandler
Notice that the CustomerDataTable class defines methods:
Public Overloads Sub AddCustomerRow(ByVal row As CustomerRow)
Public Overloads Function AddCustomerRow(ByVal CustomerId As String, ByVal Name As String, ByVal StreetAddress1 As String, ByVal StreetAddress2 As String, ByVal City As String, ByVal State As String, ByVal Zip As String, ByVal Status As String, ByVal LastOrderDate As Date) As CustomerRow
Public Function FindByCustomerId(ByVal CustomerId As String) As CustomerRow
Public Overridable Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
Public Overrides Function Clone() As System.Data.DataTable
Protected Overrides Function CreateInstance() As System.Data.DataTable
Friend Sub InitVars()
Private Sub InitClass()
Public Function NewCustomerRow() As CustomerRow
Protected Overrides Function NewRowFromBuilder(ByVal builder As System.Data.DataRowBuilder) As System.Data.DataRow
Protected Overrides Function GetRowType() As System.Type
Protected Overrides Sub OnRowChanged(ByVal e As System.Data.DataRowChangeEventArgs)
Protected Overrides Sub OnRowChanging(ByVal e As System.Data.DataRowChangeEventArgs)
Protected Overrides Sub OnRowDeleted(ByVal e As
Protected Overrides Sub OnRowDeleting(ByVal e As System.Data.DataRowChangeEventArgs)
Public Sub RemoveCustomerRow(ByVal row As CustomerRow)
Using a strongly-typed DataSet's members through your DAL component your application can perform basic CRUD operations.
Extend the DataService Project's DAL Component
Open the code behind the DAL component in the DataService project.
Add the code below to the DAL class.
#Region "Update Methods"
' Ajax Customer Update Method
Public Sub AjaxCustomerUpdate()
Dim customerTA As New AjaxDataSetTableAdapters.CustomerTableAdapter
customerTA.Update(Me.AjaxDataService.Customer)
End Sub
' Ajax Orders Update Method
Public Sub AjaxOrdersUpdate()
Dim ordersTA As New AjaxDataSetTableAdapters.OrdersTableAdapter
ordersTA.Update(Me.AjaxDataService.Orders)
End Sub
' Northwind Employees Update Method
Public Sub NorthwindEmployeesUpdate()
Dim employeesTA As New NorthwindDataSetTableAdapters.EmployeesTableAdapter
employeesTA.Update(Me.NorthwindDataService.Employees)
End Sub
' Northwind Products Update Method
Public Sub NorthwindeProductsUpdate()
Dim productsTA As New NorthwindDataSetTableAdapters.ProductsTableAdapter
productsTA.Update(Me.NorthwindDataService.Products)
End Sub
#End Region
You will call the update methods in the DAL to push changes made to the DataTables in the tow DataSets to the two Access databases.
Perform CRUD Operations Through the DataService Project's DAL Component
Open the designer view of the 'ExampleOneForm' form in the sample Windows Forms application.
Add a new button named 'AddButton'.
Add a new button named 'DeleteButton'
Add a new button name 'UpdateButton'
Open the code behind the 'DemoOneForm' and add the following code:
Private Sub AddNewButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewButton.Click
' Declare a variable named newAjaxCustomerRow of type CustomerRow.
Dim newAjaxCustomerRow As DataService.AjaxDataSet.CustomerRow
' Call the Ajax CustomerTable class's NewCustomerRow method through the DAL.
' Assign the new CustomerRow that is returned to the newAjaxCustomerRow variable.
newAjaxCustomerRow = My.Application.DAL.AjaxDataService.Customer.NewCustomerRow
' ***** Set the value of the columns in the new CustomerRow. ******
' Customer table's primary key is CustomerID, a GUID in string form.
' Create a new GUID.
Dim customerId = New Guid(System.Guid.NewGuid.ToString)
' Assign the GUID, as a string, to the CustomerID column of the newAjaxCustomerRow.
newAjaxCustomerRow.CustomerId = customerId.ToString
' Default the value in the Customer Name colum to 'New Customer'.
newAjaxCustomerRow.Name = "New Customer"
' .... the value in other columns could be set here but for this demo, are not.
' ***** Add the new CustomerRow to the CustomerDataTable ******
My.Application.DAL.AjaxDataService.Customer.AddCustomerRow(newAjaxCustomerRow)
' At this point the new row is held in the AjaxDataSet CustomerDataTable in memory.
' To see how the row could be saved to the Ajax database, see the UpdateButton_Click
' Sub in this form.
End Sub
Private Sub DeleteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteButton.Click
' Declare a variable named newAjaxCustomerRow of type CustomerRow.
Dim currentCustomerRow As DataService.AjaxDataSet.CustomerRow
' Use customerDataSource object's Current method to get the CustomerRow currently selected in the DataGridView.
' An object is returned.
' Cast the object as a DataRowView object.
' Cast the ojecte assigned to the Row property of the DataRowView object as a CustomerRow.
currentCustomerRow = CType(CType(Me.customerDataSource.Current, DataRowView).Row, DataService.AjaxDataSet.CustomerRow)
' Call the CustomerRow's Delete method.
currentCustomerRow.Delete()
End Sub
Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
' Call the DAL's AjaxCustomerUpdate to push changes made to the CustomerDataTable in memory
' to the Ajax database's Customer table.
My.Application.DAL.AjaxCustomerUpdate()
End Sub
Test
1. Run the sample Windows Forms application.
2. From the menu select Examples -> Example One.
3. Click the Fill button.
4. Click the Add New button. A new row will be added to the DataGridView.
5. Select any row in the DataGridView.
6. Click the Delete button. The row will be deleted.
7. Modify the data in one or more of the rows in the DataGridView.
8. Click the Update button. This will push the changes you made to the Ajax database's Customer table.
9. Shut down the application.
10. Restart the application. From the menu select Examples -> Example One.
11. Click the Fill button. The changes you made should be reflected in the data loaded into the DataGridView when you clicked the Fill button.
Partial Types
In the next part of this article, partial types will be used to modify the behavior of the AjaxDataSet. Below is a short introduction to partial types. You may want to experiment with partial types before tackling the next part of this article.
New in .NET 2.0, partial types provide a way to divide the definition of a class or structure among several declarations with the Partial keyword.
Partial-type statements allow a class, or a structure, to be defined across multiple source files. When a multi-source-file program is compiled, all of the source files are processed together as if all the source files were concatenated into one file before being processed.
You can use as many partial declarations as desired, in as many different source files as desired. However, all the declarations must be in the same assembly and the same namespace.
Designers, such as the DataSet designer, use partial-class definitions to separate generated code from user-authored code in separate source files. Another example is the Windows Form Designer that defines partial classes for controls such as Form.
You should not modify the generated code in these controls. Instead, create a partial class with the same name as the partial class generated by the designer where you can modify and extend the designer class.
mike mcintyre http://www.getdotnetcode.com