Data Access Layer (DAL) For MS Access Databases

Data Access Layer (DAL) For MS Access Databases - Part 4

Source Code: Data Access Layer (DAL) For MS Access Databases - Part 4

Part three of this article demonstrated how to call methods in the DAL component - from code in the sample Windows Forms application - to create, update, and delete data in the Access Ajax database.

Part four of the article explains how to centralize CRUD operations (create, retrieve, update, and delete database data) in the DAL component. In addition, part four of the article explains how to use Microsoft.NET 2.0 partial types to modify and extend the behavior of the AjaxDataSet.

Centralize CRUD Methods in the DAL Component

It is good practice to place code that will be used by many parts of a program in one place rather than repeating the code over and over again throughout program code.

For example, if a program has more than one form that will need to perform similar CRUD operations, CRUD code should be centralized in the DAL component. This way, any form needing to perform CRUD operations can call on the DAL to perform the CRUD operations, rather than hosting yet another copy of the CRUD code.

Retrieve operations (Fill methods) and update operations (Update methods) were already implemented in the DAL component in earlier parts of this article.

Now its time to add create operations (AddNew methods) and delete operations (Delete methods) to the DAL component.

To do so, add the code below to the DAL component.

#Region "AddNew Methods"
 
    ' Note: Exception handling code has not yet been added to the AddNewMethods.
 
    ' Ajax Customer AddNew Method
    Public Function AjaxCustomerAddNew() As AjaxDataSet.CustomerRow
        ' 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 = 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 ******
 
        AjaxDataService.Customer.AddCustomerRow(newAjaxCustomerRow)
 
        ' Return the new row in case the calling code needs it.
        Return newAjaxCustomerRow
    End Function
 
    ' Ajax Orders AddNew Method
    Public Function AjaxOrdersAddNew() As AjaxDataSet.OrdersRow
        ' Declare a variable named newAjaxOrdersRow of type OrdersRow.
        Dim newAjaxOrdersRow As DataService.AjaxDataSet.OrdersRow
 
        ' Call the Ajax CustomerTable class's NewOrdersRow method through the DAL.
        ' Assign the new OrdersRow that is returned to the newAjaxOrdersRow variable.
        newAjaxOrdersRow = AjaxDataService.Orders.NewOrdersRow
 
        ' ***** Set the value of the columns in the new OrdersRow. ******
 
        ' Order table's primary key is OrderID, a GUID in string form.
        ' Create a new GUID.
        Dim orderId = New Guid(System.Guid.NewGuid.ToString)
        ' Assign the GUID, as a string, to the OrderID column of the newAjaxOrdersRow.
        newAjaxOrdersRow.CustomerId = orderId.ToString
 
 
        ' .... the value in other columns could be set here but for this demo, are not.
 
        ' ***** Add the new OrdersRow to the OrdersDataTable ******
 
        AjaxDataService.Orders.AddOrdersRow(newAjaxOrdersRow)
 
        ' Return the new row in case the calling code needs it.
        Return newAjaxOrdersRow
    End Function
 
    ' Northwind Employees AddNew Method
    Public Function NorthwindEmployeesAddNew() As NorthwindDataSet.EmployeesRow
 
        Dim newNorthwindEmployeesRow As NorthwindDataSet.EmployeesRow
 
        newNorthwindEmployeesRow = NorthwindDataService.Employees.NewEmployeesRow
 
        ' The primary key in Employees table is AutoNumber; it will be assigned
        ' by the Northwind database when the row table is updated.
 
        ' Default the new Employee's last name to 'New'.
        newNorthwindEmployeesRow.LastName = "New"
 
        ' .... the value in other columns could be set here but for this demo, are not.
 
        NorthwindDataService.Employees.AddEmployeesRow(newNorthwindEmployeesRow)
 
        Return newNorthwindEmployeesRow
    End Function
 
    ' Northwind Products AddNew Method
 
    Public Function NorthwindProductsAddNew() As NorthwindDataSet.ProductsRow
 
        Dim newNorthwindProductsRow As NorthwindDataSet.ProductsRow
 
        newNorthwindProductsRow = NorthwindDataService.Products.NewProductsRow
 
        ' The primary key in Employees table is AutoNumber; it will be assigned
        ' by the Northwind database when the row table is updated.
 
        ' Default the new Product's product name to 'New'.
 
        newNorthwindProductsRow.ProductName = "New"
 
        ' .... the value in other columns could be set here but for this demo, are not.
 
        NorthwindDataService.Products.AddProductsRow(newNorthwindProductsRow)
 
        Return newNorthwindProductsRow
    End Function
 
#End Region
 
#Region "Delete Methods"
 
    ' NOTE: Exception handling code has not yet been added to the Delete methods.
 
    ' Ajax Customer Delete method.
    Public Sub AjaxCustomerDelete(ByVal theRow As AjaxDataSet.CustomerRow)
        theRow.Delete()
    End Sub
 
    ' Ajax Orders Delete Method
    Public Sub AjaxOrdersDelete(ByVal theRow As AjaxDataSet.CustomerRow)
        theRow.Delete()
    End Sub
 
    ' Northwind Employees Delete Method
    Public Sub NorthwindEmployeesDelete(ByVal theRow As AjaxDataSet.CustomerRow)
        theRow.Delete()
    End Sub
 
    ' Northwind Products Delete Method
    Public Sub NorthwindProductsDelete(ByVal theRow As AjaxDataSet.CustomerRow)
        theRow.Delete()
    End Sub
 
#End Region

Modify DemoOneForm's code in the example Windows application so it will use the create and delete methods that were just added to the DAL. Replace the AddNewButton_Click and DeleteButton_Click methods in DemoOneForm's code with the code below.

Private Sub AddNewButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewButton.Click
    ' Call the DAL's AjaxCustomerNew method to add a new row to the 
    '   DAL's AjaxDataSet Customer DataTable.
    ' The new row will not be added to the Ajax database until the AjaxCustomerUpdate method
    '   in the DAL is called; until then it only exists in computer memory.
    My.Application.DAL.AjaxCustomerAddNew()
End Sub
 
Private Sub DeleteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteButton.Click
    ' Call the DAL's AjaxCustomerDelete method passing it the row currently selected in this form's
    ' customerDataSource object.
    ' This will mark the row as deleted. 
    ' The row will not be permanently deleted until the AjaxCustomerUpdate
    '   method in the DAL is called.
    My.Application.DAL.AjaxCustomerDelete(CType(CType(Me.customerDataSource.Current, DataRowView).Row, DataService.AjaxDataSet.CustomerRow))
End Sub

Run the example Windows Forms application and test DemoOneForm.

Modify the Behavior of the AjaxDataSet With Partial Types

An introduction to partial types was included in part three of this article. Now partial types will be used to modify the behavior of the AjaxDataSet.

In the DataService project right-click the 'AjaxDataSet.xsd' file and select 'View Code'.

A partial type, in this case a partial class, will be shown in the code editor.

When you right-clicked the AjaxDataSet.xsd file and chose 'View Code', a new file was added under the AjaxDataSet.xsd file in the Project Explorer panel. Click the + sign to the left of AjaxDataSet.xsd to see this file. It is named 'AjaxDataSet.vb'. It is code from this file you are now viewing in the code editor.

Recall that part three of this article asked you view the classes in the AjaxDataSet.designer.vb file. The code in the AjaxDataSet.designer.vb was auto generated by Visual Studio when you created the AjaxDataSet and the code is maintained by Visual Studio. Do not make changes to this code.

The AjaxDataSet.vb file was created by you. It is your place to modify and extend the code Visual Studio created in the AjaxDataSet.vb.designer file.

Add Validation Features to the AjaxDataSet

The .NET System.Data DataTable class raises an event named 'ColumnChanging' when a value of a DataColumn in one of its DataRows is changing.

The CustomerDataTable class in the AjaxDataSet.designer.vb code inherits from the System.Data DataTable class. This means that when the value of a DataColumn in one of the CustomerDataTable's DataRows is changing, the ColumnChanging event will be raised.

A method can be added to AjaxDataSet.vb to handle the CustomerDataTable's ColumnChanging event. Within the method, code can be added to validate a proposed change to the value of a DataColumn in a DataRow of the CustomerDataTable.

Next, code will be added to the AjaxDataSet.vb file that will perform a validation check when the data in the CityColumn of a CustomerDataTable CustomerDataRow is changing. If validation fails, the proposed changed will not be applied to the CityDataColumn in the CustomerDataRow. Also, an event will be raised to publish the validation failure so that other classes, such as a Windows Form, can handle the event and use it to communicate the validation failure to the application user.

Instructions

Double-click the AjaxDataSet.xsd file in the Visual Studio Solution Explorer panel to open the AjaxDataSet in the DataSet designer.

Double-click the City column in the Customer table to create the CustomerDataTable_ColumnChanging event handler method.

AjaxDataSet.vb will open in the code editor. Visual Studio has added stub code you can modify to provide validation for the City column of the Customer table.

    1 Partial Class AjaxDataSet
    2 
    3     Partial Class CustomerDataTable
    4 
    5         Private Sub CustomerDataTable_ColumnChanging(ByVal sender As System.Object, _
    6         ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanging
    7             If (e.Column.ColumnName = Me.CityColumn.ColumnName) Then
    8                 'Add user code here
    9             End If
   10 
   11         End Sub
   12 
   13     End Class
   14 
   15 End Class

Visual Studio has added a Partial Class named CustomerDataTable within the AjaxDataSet partial class (line 3 above). Within the CustomerDataTable partial class statement you can modify and extend the CustomerDataTable partial class Visual Studio generated in the AjaxDataSet.designer.vb file.

Visual Studio has added a method 'stub' named CustomerDataTable_ColumnChanging (line 5 above) that can handle a ColumnChanging event raised by the CustomerDataTable.

In the Visual Studio source code provided with this article, in the DataService project, the AjaxDataSet.vb file has been modified to include all the code below. If you are following along with your own Visual Studio solution you can copy the code from the example source code and use it to replace the code in your AjaxDataSet.vb file. Comments in the code explain how the validation process for the City column was implemented.

    1 ' Define a delegate named InvalidDataHandler.
    2 Public Delegate Sub InvalidDataHandler(ByVal e As InvalidDataArgs)
    3 
    4 ' Declare a class named InvalidDataArgs.
    5 ' This class can be used to create an InvalidDataArgs
    6 ' object that can be passed to client code when
    7 ' an event of type InvalidDataHandler is raised.
    8 Public Class InvalidDataArgs
    9 
   10     ' ValidationMessage Backing Field and Property
   11     Private m_ValidationMessage As String
   12     Public Property ValidationMessage() As String
   13         Get
   14             Return m_ValidationMessage
   15         End Get
   16         Set(ByVal value As String)
   17             m_ValidationMessage = value
   18         End Set
   19     End Property
   20 
   21     ' DataTableName Backing Field and Property
   22     Private m_DataTableName As String
   23 
   24     Public Property DataTableName() As String
   25         Get
   26             Return m_DataTableName
   27         End Get
   28         Set(ByVal value As String)
   29             m_DataTableName = value
   30         End Set
   31     End Property
   32 
   33     ' DataColumnName Backing Field and Property
   34     Private m_DataColumnName As String
   35 
   36     Public Property DataColumnName() As String
   37         Get
   38             Return m_DataColumnName
   39         End Get
   40         Set(ByVal value As String)
   41             m_DataColumnName = value
   42         End Set
   43     End Property
   44 
   45     ' Class constructor.
   46     Public Sub New(ByVal dataTableName As String, _
   47                    ByVal dataColumnName As String, _
   48                    ByVal validationMessage As String)
   49         Me.DataTableName = dataTableName
   50         Me.DataColumnName = dataColumnName
   51         Me.ValidationMessage = validationMessage
   52     End Sub
   53 
   54 End Class
   55 
   56 Partial Class AjaxDataSet
   57 
   58     Partial Class CustomerDataTable
   59 
   60         ' Declare an event named CustomerInvalidData of type InvalidDataHander (a delegate).
   61         Public Event CustomerInvalidData As InvalidDataHandler
   62 
   63         Private Sub CustomerDataTable_ColumnChanging(ByVal sender As System.Object, _
   64             ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanging
   65 
   66             ' If the column being changed is the City column...
   67             If (e.Column.ColumnName = Me.CityColumn.ColumnName) Then
   68 
   69                 ' If the proposed value for the City column is Nothing or 
   70                 '    less than one character.
   71                 If e.ProposedValue Is Nothing OrElse _
   72                    CType(e.ProposedValue, String).Length < 1 Then
   73 
   74                     ' Because the ProposedValue is invalid, revert to the 
   75                     '    current City column value.
   76                     e.ProposedValue = e.Row.Item("City")
   77 
   78                     ' Declare a variable named theInvalidDataArgs of type InvalidDataArgs.
   79                     ' Call the InvalidDataArgs class' New method passing it the data
   80                     '    that is to be assigned to the new InvalidDataArgs object's 
   81                     '    properties.
   82                     Dim theInvalidDataArgs As New InvalidDataArgs("Customer", "City", _
   83                     "City must be more than 1 character long.")
   84 
   85                     ' Raise a CustomerInvalidData event, passing it theInvalidDataArgs object.
   86                     RaiseEvent CustomerInvalidData(theInvalidDataArgs)
   87 
   88                 End If
   89 
   90             End If
   91 
   92         End Sub
   93 
   94     End Class
   95 
   96 End Class
Use the AjaxDataSet Validation Features in the Windows Forms Example Project
Open Windows Forms project's DemoOneForm. Add the method below to the DemoOneForm class' code.
    ' InvalidDataHander Method
    '   This is the handler for InvalidData events
    '   raised by this application's Dal AjaxDataService Customer table.
    Private Sub InvalidDataHandler(ByVal e As DataService.InvalidDataArgs)
        ' Message the user.
        MessageBox.Show("The data entered in the " & e.DataColumnName & " column of the " & e.DataTableName & " table was invalid." & e.ValidationMessage)
    End Sub
Modify the form's DemoOneForm_Load method, adding the AddHandler code shown below.

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
 
    ' Register this form's InvalidDataHandler method as a handler for CustomerInvalidData events
    '    raised by this application's Dal AjaxDataService Customer table.
    AddHandler My.Application.DAL.AjaxDataService.Customer.CustomerInvalidData, AddressOf InvalidDataHandler
End Sub
Test the AjaxDataSet Validation FeaturesRun the example Windows Forms application and open the DemoOneForm. Click the Fill button.
In any row in the DataGridView, clear the City column and press the tab key.
A validation failure message will be displayed:
The data in the City column will revert to the value that was in the column before you tried to change it.

Help

DataTable.ColumnChanging Event

Partial Keyword

How to: Extend the Functionality of a Dataset

mike mcintyre http://www.getdotnetcode.com

posted on Friday, December 01, 2006 7:39 AM

mike mcintyre's

Archives

Topics

Source Code