|
Data
Access Layer (DAL) For MS Access Databases - Part 3 |
|
description |
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. |
|
get source code -> |
DalForMSAccessPart3.zip |
|
environment |
Visual
Studio 2005 |
|
language
|
Visual Basic |
|
tags |
DAL, Data Access Layer,
Microsoft Access, Data |
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.
Help
Partial Types
mike mcintyre
http://www.getdotnetcode.com
|