Data Access Layer (DAL) For MS Access Databases

Get Dot Net Code
.NET Resource

Data Access Layer (DAL) For MS Access Databases - Part 4
description	This is the fourth 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 ->	DalForMSAccessPart4.zip
environment	Visual Studio 2005
language	Visual Basic
tags	DAL, Data Access Layer, Microsoft Access, Data, CRUD, Partial Type

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.

Help

DataTable.ColumnChanging Event

Partial Keyword

How to: Extend the Functionality of a Dataset

mike mcintyre http://www.getdotnetcode.com

Your browser does not support inline frames or is currently configured not to display inline frames.