Data Access Layer For Microsoft Access Databases

This multi installment article will provide instructions and source code you can use to create a data access layer (DAL) for Microsoft Access databases. Along the way the article will explain when a DAL may be appropriate and some of the roles ADO.NET and Visual Studio data technologies can play in a DAL.

This first installment provides instructions and a downloadable example that can be used to create a basic class library (DLL) that will be extended in future installments to become a data access layer for Microsoft Access databases.

Instructions for Creating a Class Library for a Data Access Layer

A. Create a new Visual Basic Visual Studio 2005 Windows Forms solution.

B. Add a Visual Studio 2005 Class project to the Visual Studio solution.

1. From the File menu select Add -> New Project…

 

2. The ‘Add New Project’ window will open.


3. In the ‘Project types:’ section of the window select “Visual Basic”.


4. In the ‘Templates:’ section of the window select “Class Library”.


5. In the ‘Name:’ text box enter ‘DataService’.


6. Click the ‘OK’ button.


7. A class library project named DataService is added to the solution, ‘Class1’ is created in the library, and ‘Class1’ opens in the code editor.


8. Close ‘Class1’


9. Delete ‘Class1’ from the DataService project.

C. Add a component class to the DataService project.

1.  Right-click the ‘DataService’ project in the ‘Solution Explorer’ panel and select Add -> New Item…


2.  The ‘Add New Item – DataService’ window will open.


3.  In the ‘Templates’ section of the window select ‘Component Class’.


4.  In the ‘Name:’ text box enter ‘DAL’.

 


5.  Click the ‘OK’ button.


6.  A component class named ‘DAL’ is added to the DataService project and the ‘DAL’ component designer opens.


7.  Close the ‘DAL’ component designer.

 

D. Add one or more data sources to the DataService project.

1. Add a data source to the DataService project


a.  Click the DataService project in the Visual Studio solution to make sure it is selected.


b.  From the Visual Studio ‘Data’  menu select ‘Add New Data Source…’


c.  The ‘Data Source Configuration Wizard’ window will open.


d.   In the ‘Where will the application get its data from?’ section of the window click ‘Database’.

 


e.   Click the ‘Next’ button.


f.   The ‘Choose Your Data Connection’ step window of the wizard will open.

 


g.   Click the ‘New Connection’ button.


h.   The ‘Add Connection’ window will open.

 


i.    Make sure the ‘Data Source’ label reads ‘Microsoft Access Database File (OLE DB)’.


(1)    If it doesn’t click the ‘Change…’ button.


(2)    The ‘Change Database’ window will open.


(3)    Double-click ‘Microsoft Access Database’ in the ‘Data source:’ section of the window.


j.    Click the ‘Browse…’ button.


k.    The ‘Select Microsoft Access Database File’ dialog will open. Navigate to an Access Database (.mdb) file.

 


l.     Double-click the file.  You are returned to the ‘Add Connection’ window.


m.   Click the ‘Test Connection’ button.


n.    If the test is successful, click the ‘OK’ button. Otherwise, backup and try again.


o.    You will be returned to the ‘Choose Your Data Connection’ step window of the wizard.


p.    Click the ‘Next…’ button.


q.    A dialog box will open.  The dialog asks if you want to add a copy of the database to the project.

 


r.     Click the ‘Yes’ button.


s.     The ‘Save Connection String to the Application Configuration File’ step window of the wizard opens.

 


t.     Click the ‘Next…’ button.


u.    The ‘Choose Your Database Objects’ step window of the wizard opens.

 


v.    In the “Which database objects do you want in your dataset?’ section use the tree view control to make the tables in the DataSource you wish to included in the dataset.


w.   Click the ‘Finish…’ button.


x.    A copy of the Access database you selected and a DataSet are added to the DataService project.

 


y.   Right-click the database and select ‘Properties’.


z.    The ‘Properties’ panel will open. In the Properties panel set the ‘Copy to Output Directory’ property to ‘Copy if newer’. This will ensure that changes are reflected in the database as you program and debug your application.  If you leave the default setting ‘Copy always’ a fresh copy of the database will be copied to the Bin directory each time you execute the application and you will not see changes in the database made before the current run.

 


NOTE: If your application will use more than one Access database, repeat the process of adding a DataSource for each Access database. Additional databases can be added as your application’s data needs expand. In the example solution two Access database data sources have been added.

 

That’s it for this installment of the article. In this installment a basic class library (DLL) has been created that can be extended to become a data access layer for Microsoft Access databases. In the next installment the class library will be extended and used for the first time by a Windows Forms application.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.
 
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.

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 Features Run 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.

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

This part of the article explains:

How to create a custom BindingSource class that can be used to bind a DataTable in the DAL to a Windows Forms controls such as a DataGridView.

How to bind a custom BindingSource class to a DataGridView.

How to extend a TableAdapter by adding additional queries to it.

How to modify and/or extend a TableAdapter via its partial class.

Create Custom BindingSource Classes

In Windows Forms 2.0, the BindingSource class was added to the System.Windows.Forms namespace. The BindingSource class simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources. In addition, the BindingSource component can act as a strongly typed data source.

Those who used the DataView class in .NET 1.x will find it worthwhile to compare the similarity of the BindingSource class methods with the DataView class methods. In most cases, the BindingSource class should be preferred over the DataSet class for binding in Windows Forms 2.0 applications.

To bind tables from the DAL object which is declared and instantiated in the sample Windows Forms’ MyApplicationExtensions.vb file, custom BindingSource classes must be added to the sample Windows Forms project. While they could be declared elsewhere, adding them to the MyApplicationExtensions.vb file is convenient and centralizes all custom BindingSource classes in one well known place.

Custom BindingSource classes must be declared out side the Namespace My declaration in the file:

‘ GetDotNetCode MyApplication Extensions

‘ Wrapping it in the My namespace.

‘ This will make Public members available

‘ available in intellisense when

‘ a developer types ‘My.Application.[public members will pop up here.]

Namespace My

 

    Partial Class MyApplication

 

      ……

      ……

 

    End Class

 

End Namespace

 

Add custom binding sources here…

A simple custom BindingSource class can be created by declaring a new class that inherits from the Binding Source class in the System.Windows.Forms namespace and adding only a constructor (‘New’) method to the custom class.  Here is the code that was added to the sample Windows Application’s MyApplicationExtensions.vb file to declare a custom BindingSource class for each of the DataTables in the DAL component.

 

 

#Region “Custom BindingSource Classes”

 

‘ AjaxCustomer Binding Source

Public Class AjaxCustomer

    Inherits System.Windows.Forms.BindingSource

 

    Public Sub New()

        ‘ Must call BindingSource base class’ New method first.

        MyBase.New()

        ‘ Set the DataSource of this BindingSource

        ‘   to be the AjaxDataSevice in the DAL.

        Me.DataSource = My.Application.DAL.AjaxDataService

        ‘ Set the DataMember of this BindingSource

        ‘   to be the Customer table winith the AjaxDataService

        Me.DataMember = My.Application.DAL.AjaxDataService.Customer.ToString

    End Sub

 

End Class

 

‘ AjaxOrders Binding Source

Public Class AjaxOrders

    Inherits System.Windows.Forms.BindingSource

 

    Public Sub New()

        MyBase.New()

        Me.DataSource = My.Application.DAL.AjaxDataService

        Me.DataMember = My.Application.DAL.AjaxDataService.Orders.ToString

    End Sub

 

End Class

 

‘ NorthwindEmployees Binding Source

Public Class NorthwindEmployees

    Inherits System.Windows.Forms.BindingSource

 

    Public Sub New()

        MyBase.New()

        Me.DataSource = My.Application.DAL.NorthwindDataService

        Me.DataMember = My.Application.DAL.NorthwindDataService.Employees.ToString

    End Sub

 

End Class

 

‘ NorthwindProducts Binding Source

Public Class NorthwindProducts

    Inherits System.Windows.Forms.BindingSource

 

    Public Sub New()

        MyBase.New()

        Me.DataSource = My.Application.DAL.NorthwindDataService

        Me.DataMember = My.Application.DAL.NorthwindDataService.Products.ToString

    End Sub

 

End Class

 

#End Region

Bind a Custom DataSource Class to a DataGridView

Next, the custom BindingSource class ‘AjaxCustomer’ will be added to the example Windows Forms project’s data sources.

Select the GetDotNetCodeFreeVisualBasicExample Windows Forms project in the Solution Explorer Panel and then click the ‘Data Sources’ Tab.

The Data Sources panel is shown. Click the Add New Data Source… link in the panel.

The Data Source Configuration Wizard opens. Select the Object icon then click the Next button.

The ‘Select the Object You Wish to Bind to’ panel of the Data Source Configuration Wizard opens.

Click the + in front of DalForMicrosoftAjaxDataBases and the the + to the left of GetDotNetCode.

The custom BindingSource classes are listed.

Double click the AjaxCustomer object.

The Add Object Data Source panel of the Data Source Configuration Wizard opens.

The AjaxCustomer class is shown as the object that will be added to the example Windows Forms project’s data sources.

Click the Finish button.

The Data Sources panel now lists AjaxCustomer.

Now that the AjaxCustomer data source has been added to the example Windows Forms project it can be bound to a Windows Forms control on any form in the project.

Open the project’s ExampleOneForm in design view. Click the DataGridView on the form to select it.

In the Properties panel, click the drop down arrow of the DataSource property.

A data source navigation dialog will pop up.

Drill down into ‘Other Data Sources’ until you can see the AjaxCustomer data source. Click AjaxCustomer to bind it to the DataGridView.

The DataGridView is now bound to the Ajax Customer table in the application’s DAL, via AjaxCustomer (the custom BindingSource class).

Now that the DataGridView is bound via the custom AjaxCustomer binding source class, code used in an earlier part of the article should be removed.

Open the code behind the DemoOneForm.

Remove the code marked in yellow below from the code behind DemoOneForm.

Modify the  DeleteButton_Click method so that it uses the form’s AjaxCustomerBindingSource component:

 

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.AjaxCustomerBindingSource.Current, _

    DataRowView).Row, DataService.AjaxDataSet.CustomerRow))

End Sub

This is a good time to test all the changes made to the example Windows Forms project.Run the application and test DemoOneForm.

Extend a TableAdapter by Adding Queries to It

A TableAdapter can be extend by adding one or more queries to it.

Open the AjaxDataSet.xsd file in the DataService project.

Right-click CustomerTableAdaper under the Customer table in the DataSet designer and select ‘Add Query’.

The TableAdapter Query Configuration Wizard opens. Click the Next> button.

The Choose a Query type panel of the wizard opens. Click the Next> button.

The Specify a SQL SELECT Statement panel of the wizard opens. Click the Query Builder… button.

The Query Builder opens. Set the filter on the Name column to: =?

Press the Enter key. A WHERE clause is added to the SELECT statement.

Setting the filter on one or more columns to =? creates a parameterized SELECT statement.

In code that uses the query, values will be passed to the query which will replace the ? in a filter with the value before the query is executed.

Click the OK button.

The Specify a SQL SELECT Statement now shows the WHERE clause just created.

Click the Next> button.

The Choose Methods to Generate panel of the TableAdapter Configuration Wizards is shown.

Name the Fill a DataTable method:  FillByCustomerName

Name the Return a DataTable method: GetDataByCustomerName

Click the Next> button.

The Wizard Results panel of the wizard is shown.

Click the Finish button.

The new query is now a part of the CustomerTableAdapter.

In the DataService project, add the code below to the DAL component code’s ‘Fill Methods’ region.

‘ Ajax Customer Fill By Customer Name Method

Public Sub AjaxOrdersFillByCustomerName(ByVal customerName As String)

    Dim customerTA As New AjaxDataSetTableAdapters.CustomerTableAdapter

    ‘ Call the CustomerTableAdapter’s FillByCustomerName method

    ‘   passing in the name of the customer.

    customerTA.FillByCustomerName(Me.AjaxDataService.Customer, customerName)

End Sub

The new query is now accessible through the DAL component.

Try Out the New Query

Open the DemoFormOne in the example Windows Forms Application in the form designer.

Add a button named FindByCustomerNameButton to the form.

Open the code behind the DemoFormOne and add the method below:

 

Private Sub FindByCustomerNameButton_Click(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles FindByCustomerNameButton.Click

    ‘ Call the AjaxCustomerFillByCustomerName method passing in the name of a Customer.

    ‘ Only the customer(s) having the name passed in will be selected and shown in the DataGridView.

    My.Application.DAL.AjaxCustomerFillByCustomerName(“York Fish and Chips”)

End Sub

Note: If you do not have the ‘York Fish and Chips’ customer in your Ajax Customer table change the code above to pass in the name of a customer from your Ajax Customer table.

Test the application. Run it, open the DemoOneForm and click the Find By Customer Name button.

Modify a TableAdapter’s Partial Class to Extend It’s Features

TableAdapters are auto generated by Visual Studio as partial classes. You can modify or extend the behavior of a TableAdapter by adding your own partial class for the TableAdapter.

Code that adds a new method to the AjaxDataSetTableAdapters partial CustomerTableAdapter class has been added to the code behind the AjaxDataSet. Open the AjaxDataSet.xsd, right-click on the design surface, then select ‘View Code’ to view the code behind the AjaxDataSet. The code is also shown below:

 

‘ TableAdapters for a DataSet are located in their own namespace.

Namespace AjaxDataSetTableAdapters

 

    Partial Class CustomerTableAdapter

        ‘ CustomFill method added to the CustomerTableAdapter class.

        ‘ Pass in a DataSet, the name of a DataTable, and a query string to fill

        ‘   the DataTable.

        Public Sub CustomFill(ByVal theDataSet As DataSet, ByVal theDataTable As String, ByVal selectCommandText As String)

            ‘ Create a new OleDb.OleDbCommand using query string passed in and

            ‘   the CustomerTableAdapter’s connection;

            ‘   assign the new OleDbCommand to the CustomerTableAdapter’s SelectComman.

            Me.Adapter.SelectCommand = New OleDb.OleDbCommand(selectCommandText, Me.Connection)

            ‘ Fill the DataTable.

            Me.Adapter.Fill(theDataSet, theDataTable)

        End Sub

    End Class

 

End Namespace

To better understand how to use TableAdapter partial classes, read the MSDN article ‘How to: Extend the Functionality of TableAdapters’. A link is provided at the end of this article. In the DataService project, add the code below to the DAL component code’s ‘Fill Methods’ region.

 

‘ Ajax Customer Custom Fill Method

Public Sub AjaxCustomerCustomFill(ByVal selectQueryString As String)

    Dim customerTA As New AjaxDataSetTableAdapters.CustomerTableAdapter

    customerTA.CustomFill(Me.AjaxDataService, Me.AjaxDataService.Customer.ToString, selectQueryString)

End Sub

Try The CustomFill Method

Open the DemOneForm in the example Windows Forms project in design view, and add a button named CustomFill  to it.

Open the code behind the form and add the method below to it.

 

Private Sub CustomFillButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomFill.Click

    ‘ Call the DAL’s AjaxCustomerCustomFill method, passing in a query string.

    My.Application.DAL.AjaxCustomerCustomFill(“SELECT * FROM Customer WHERE Name LIKE ‘Bill%'”)

End Sub

Note: If you do not have a customer in your Ajax Customer table with a name that starts with ‘Bill’,  change the code above to pass part of the name of a customer from your Ajax Customer table. Test the application. Run it, open the DemoOneForm and click the Custom Fill button.

The next installment of this article will provide a architecture diagram of the DAL component and the example Windows Forms project.

Part five of this article demonstrated how to 1) create a custom BindingSource class; 2) bind a custom BindingSource class to a DataGridView; 3) extend a TableAdapter by adding queries to it; and 4) modify and/or extend a TableAdapter via its partial class.

Part six provides demonstrates how to use a BindingNavigator class with the custom AjaxCustomer BindingSource created in part five and provides a diagram of the major components of the DataService project to-date and the Windows Forms project that consumes services from the DataService.

Windows Forms Example Screen Shot

Use a BindingNavigator with the Custom AjaxCustomer BindingSource

A new form, the ExampleTwoForm, has been added to the Windows Forms example application to demonstrate how use a BindingNavigator control with the custom AjaxCustomer BindingSource that was created in an earlier part of this article.

You can use the .NET Windows Forms BindingNavigator control to create a standardized means for users to search and change data on a Windows Form. You frequently use BindingNavigator with the BindingSource component to enable users to move through data records on a form and interact with the records. To learn more about the BindingNavigator control click -> BindingNavigator Control Overview

Here are the steps taken to add a BindingNavigator to ExampleTwoForm to get basic navigation features for the Ajax Customer records:

A new form, the ExampleTwoForm, was added to the source code.

A new menu item to show ExampleTwoForm was added to the MainForm’s Examples menu.

ExampleTwoForm was opened in the Visual Studio form designer.

In the Data Sources panel of Visual Studio, the drop down menu next to the AjaxCustomer data source was used to select ‘Details’.

The AjaxCustomer data source was drug from the Data Sources panel to ExampleTwoForm’s design surface.  Visual Studio automatically added: 1)a BindingSource component; 2) a BindingNavigator component; 3) a BindingNavigator control; and 4) labels and controls for editing Customer records.

The BindingNavigator control’s AddNewItem and DeleteItem properties were set to (none):

The AddNewItem property was set to (none) to prevent the BindingNavigator from automatically adding a new customer row when it’s add new button (the button with the + sign) is clicked. Instead our code will handle the click and call the DAL’s AjaxCustomerAddNew method to add a new customer row.

The DeleteItem property was set to (none) to prevent the BindingNavigator from automatically deleting the current customer when it’s delete button (the button with the X) is clicked. Instead our code will handle the click and call the DAL’s AjaxCustomerDelete method to delete the current customer row.

Code was added to ExampleTwoForm to load all customers when the form is shown and to wire up the BindingNavigator control’s Add New, Delete, and Save buttons.

 

Imports System.Data

Public Class ExampleTwoForm

 

    Private Sub ExampleTwoForm_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

    End Sub

 

    ‘ This code is called the BindingNavigator control’s New button is clicked.

    ‘ Note: To prevent the BindingNavigator from automatically adding a new row

    ‘  when the New button is clicked, you must set the BindingNavigator’s AddNewItem

    ‘  property to ‘None’.

    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click

        ‘ Call the DAL’s AjaxCustomerAddNew method.

        My.Application.DAL.AjaxCustomerAddNew()

    End Sub

 

    ‘ This code activates the BindingNavigator’s Save button when any column

    ‘ of the Customer record is modified.

    Private Sub CustomerRecordModified(ByVal sender As Object, ByVal e As System.EventArgs) Handles _

    CityTextBox.Validated, LastOrderDateDateTimePicker.Validated, NameTextBox.Validated, _

    StateTextBox.Validated, StatusTextBox.Validated, StreetAddress1TextBox.Validated, _

    StreetAddress2TextBox.Validated, ZipTextBox.Validated

        ‘ If somethings been modified let the user save it.

        Me.AjaxCustomerBindingNavigatorSaveItem.Enabled = True

    End Sub

 

    ‘ This code is called the BindingNavigator control’s Delete button is clicked.

    ‘ Note: To prevent the BindingNavigator from automatically deleting the current row

    ‘  when the Delete button is clicked, you must set the BindingNavigator’s DeleteItem

    ‘  property to ‘None’.

    Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click

 

        ‘ Cast the AjaxCustomerBindingSource’s current object to

        ‘   an AjaxDataSet CustomerRow.

        Dim currentCustomerRow As DataService.AjaxDataSet.CustomerRow

        currentCustomerRow = CType(CType(Me.AjaxCustomerBindingSource.Current, DataRowView).Row, DataService.AjaxDataSet.CustomerRow)

 

        ‘ Verify the user want’s to delete the current Customer record.

        If MessageBox.Show(“Really delete “ & currentCustomerRow.Name & “‘s customer record?”, “Delete Customer Record”, MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.OK Then

            ‘ Call the DAL’s AjaxCustomerDelete method.

            My.Application.DAL.AjaxCustomerDelete(currentCustomerRow)

            ‘ Enable BindingNavigator’s Save button.

            Me.AjaxCustomerBindingNavigatorSaveItem.Enabled = True

        Else

            ‘ Do nothing.

        End If

    End Sub

 

    ‘ This code is called when the BindingNavigator control’s Save button is clicked.

    Private Sub AjaxCustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles AjaxCustomerBindingNavigatorSaveItem.Click

 

        Me.AjaxCustomerBindingSource.EndEdit()

 

        ‘ Use the DAL’s AjaxCustomerUpdate method to save the Customer records (update the database).

        My.Application.DAL.AjaxCustomerUpdate()

 

        ‘ Disable the BindingNavigator control’s Save button.

        Me.AjaxCustomerBindingNavigatorSaveItem.Enabled = False

 

    End Sub

End Class

Diagram

The diagram below presents a birds eye view of the major components of the DataService project and the Windows Forms project that references it.

When the DataService project is compiled the DataService.dll file is created. When the GetDotNetCodeFreeVisualBasicExample project gets compiled the GetDotNetCodeFreeVisualBasicExample.exe is created.


 
 

~ bởi duriangroup on Tháng Sáu 1, 2007.

 
%d bloggers like this: