This example will implement various data transfer objects such as XML, Dataset, DataTable, ArrayList and Custom (Data Transfer) Object. Custom Object for Data Transfer refers to a class primarily made for Set and Get operation with mapping field of database table. This layer will be compiled as a separate assembly so Data Access can be truly separated from Business Object Layer. However, Business Object Layer will be referring this assembly to call these methods. DataTable represents one table, if you wish to get data from multiple table and its relationship, this option will not work.
The main benefit of this approach is if you need to make any changes to Data Access Layer or Business Layer, you will be touching only that layer unless you are changing the signature of existing method. Thus, it helps you to separate your Data Access Layer from Business Layer for maintainability and reusability prospective.
Data Access Layer only deals with Data Normalization, where as in Business Layer, Business Object deals with Behavior Normalization.Thus, the data access layer will return the result in a relational fashion where as Business Object will apply the logic to make it behave as it is needed by business rules.
In this example, I am considering possibility of two data sources – XML or SQL Server. You can choose DataProvider by setting a value SQL or XML in web.config. If you are using SQL Server, provide the connection string, if XML then provide XMLDataFilePath in web.config. If you do not configure these values, the data access layer will choose XML as data provider by default and your C:\ drive for the location of Customer.XML data file.
Following are the steps
1. Created a Class Library Project in Visual Studio 2005 using language VB.NET
a. Project Name: Vishwa.Example.Data
2. Added following four class files
a. CustomerDTO.vb – Custom Data Transfer Object Class
b. SQLProvider.vb – A Class containing SQL Server CRUD operations
c. XMLProvider.vb – A Class containing XML file CRUD operations
d. DataAccess.vb – A Class which acts as a factory and main interface for Business Layer through a Property which determines the corresponding Data Provider based on config setting.
Note: You must be thinking, too many classes for just CRUD operations for such a small table. But in order to keep things logically separated and clean and to implement different types of data transfer objects using different data provider model, it makes sense to create it. So here we go with code.
Web.Config
<appSettings>
<add key="DataProvider" value="SQL"/> <!-- You can choose either SQL or XML as Data Provider, In case of SQL, you must have a valid connection string defined -->
<add key="XMLDataFilePath" value="C:\Temp"/> <!--Path for XML Data File if DataProvider is XML-->
</appSettings>
<connectionStrings>
<add name="ExampleDB" connectionString="Data Source=localhost;Database=Example;User ID=<UserID>;Password=<Password>;" providerName="System.Data.SqlClient"/>
</connectionStrings>
CustomerDTO.VB
Option Strict On
Option Explicit On
''' Design Pattern: Data Transfer Objects.
''' </summary>
''' <remarks>
''' Data Transfer Objects transfers data between processes, but without behavior, thus no properties or methods needed.
''' </remarks>
Namespace Example.Data
<Serializable()> _
Public Class CustomerDTO
Public CustID As Integer = 0
Public CustName As String = String.Empty
Public CustDOB As DateTime = DateTime.MinValue
Public CustAddress As String = String.Empty
Public DateCreated As DateTime = DateTime.Now
Public DateModified As DateTime = DateTime.Now
#Region "Constructor"
Public Sub New()
End Sub
#End Region
End Class
End Namespace
DataAccess.vb: The following code is also a simplest example of Factory Pattern, however it can be modified for Abstract Factory pattern for best uses.
Option Strict On
Option Explicit On
Imports System.Web.Configuration
' Design Pattern : Factory
' Purpose: Providers Appropriate Provider based on Data Provider Type
Namespace Example.Data
Public MustInherit Class DataAccess
#Region "Constructor"
Public Sub New()
End Sub
#End Region
Private Shared _provider As DataAccess= Nothing
Public Shared ReadOnly Property DataProvider() As DataAccess
Get
If DataProviderType = "SQL" Then
Return New SQLProvider
Else
Return New XMLProvider
End If
End Get
End Property
#Region "Customer Methods"
Public MustOverride Function InsertCustomer(ByVal custRecord As DTOCustomer) As Integer
Public MustOverride Function UpdateCustomer(ByVal custRecord As DTOCustomer) As Boolean
Public MustOverride Function DeleteCustomer(ByVal custRecord As DTOCustomer) As Boolean
Public MustOverride Function GetCustomer(ByVal custID As Int32) As DTOCustomer
Public MustOverride Function GetCustomers() As List(Of DTOCustomer)
Public MustOverride Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
ByVal custAddress As String) As Integer
Public MustOverride Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
Public MustOverride Function DeleteCustomer(ByVal custID As Integer) As Boolean
Public MustOverride Function GetCustomerAsXMLString(ByVal custID As Int32) As String
Public MustOverride Function GetCustomersAsXMLString() As String
Public MustOverride Function GetCustomerAsDataSet(ByVal custID As Int32) As DataSet
Public MustOverride Function GetCustomersAsDataSet() As DataSet
Public MustOverride Function GetCustomerAsDataTable(ByVal custID As Integer) As DataTable
Public MustOverride Function GetCustomersAsDataTable() As DataTable
Public MustOverride Function GetCustomerAsArrayList(ByVal custID As Int32) As ArrayList
Public MustOverride Function GetCustomersAsArrayList() As ArrayList
#End Region
#Region "Common Property"
Protected Shared ReadOnly Property ConnectionString() As String
Get
Try
Return WebConfigurationManager.ConnectionStrings.Item("ExampleDB").ConnectionString
Catch exc As Exception
Return ""
End Try
End Get
End Property
Protected Shared ReadOnly Property XMLDataFile() As String
Get
Try
Return WebConfigurationManager.AppSettings.Item("XMLDataFilePath") & "\Customer.xml"
Catch exc As Exception
Return "C:\Customer.xml"
End Try
End Get
End Property
Protected Shared ReadOnly Property DataProviderType() As String
Get
Try
Return WebConfigurationManager.AppSettings.Item("DataProvider").ToUpper
Catch exc As Exception
Return "XML"
End Try
End Get
End Property
#End Region
End Class
End Namespace
SQLProvider.VB
Option Strict On
Option Explicit On
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Web.Configuration
' Purpose: SQL Server Data Provider
Namespace Example.Data
Friend Class SQLProvider
Inherits DataAccess
#Region "Constructor"
Public Sub New()
End Sub
#End Region
#Region "CRUD Operation Using Various Data Trasfer Objects"
#Region "Custom Data Transfer Object"
Public Overrides Function GetCustomer(ByVal custID As Int32) As DTOCustomer
Dim record As New DTOCustomer
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Connection.Open()
End With
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
If reader.Read Then
If Not reader.IsDBNull(custIDOrdinal) Then record.CustID = reader.GetInt32(custIDOrdinal)
If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)
If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)
If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)
If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)
If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)
End If
End Using
End Using
End Using
Return record
End Function
Public Overrides Function GetCustomers() As List(Of DTOCustomer)
Dim records As New List(Of DTOCustomer)
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
With command
.CommandType = CommandType.StoredProcedure
.Connection.Open()
End With
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
Do While reader.Read
Dim record As New DTOCustomer
If Not reader.IsDBNull(custIDOrdinal) Then record.CustID = reader.GetInt32(custIDOrdinal)
If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)
If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)
If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)
If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)
If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)
records.Add(record)
Loop
End Using
End Using
End Using
Return records
End Function
Public Overrides Function InsertCustomer(ByVal custRecord As DTOCustomer) As Integer
Return InsertCustomer(custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
End Function
Public Overrides Function UpdateCustomer(ByVal custRecord As DTOCustomer) As Boolean
Return UpdateCustomer(custRecord.CustID, custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
End Function
Public Overrides Function DeleteCustomer(ByVal custRecord As DTOCustomer) As Boolean
Return DeleteCustomer(custRecord.CustID)
End Function
#End Region
#Region "Insert/Update/Delete Methods"
Public Overrides Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
ByVal custAddress As String) As Integer
Dim returnValue As Integer = 0
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
.Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
.Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
.Connection.Open()
.ExecuteNonQuery()
returnValue = CInt(command.Parameters("@ReturnID").Value)
End With
End Using
End Using
Return returnValue
End Function
Public Overrides Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
Dim returnValue As Integer = 0
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
.Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
.Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
.Connection.Open()
.ExecuteNonQuery()
returnValue = CInt(command.Parameters("@ReturnID").Value)
End With
End Using
End Using
Return (returnValue > 0)
End Function
Public Overrides Function DeleteCustomer(ByVal custID As Integer) As Boolean
Dim returnValue As Integer = 0
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Connection.Open()
.ExecuteNonQuery()
returnValue = CInt(command.Parameters("@ReturnID").Value)
End With
End Using
End Using
Return (returnValue > 0)
End Function
#End Region
#Region "Implementing Two Methods with Other Data Trasfer Objects"
#Region "XML String"
Public Overrides Function GetCustomerAsXMLString(ByVal custID As Int32) As String
Return GetCustomerAsDataSet(custID).GetXml
End Function
Public Overrides Function GetCustomersAsXMLString() As String
Return GetCustomersAsDataSet().GetXml
End Function
#End Region
#Region "DataSet Object"
Public Overrides Function GetCustomerAsDataSet(ByVal custID As Int32) As DataSet
Dim resultDataSet As New DataSet("Customers")
Using connection As New SqlConnection(ConnectionString)
Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)
With dataAdapter
.SelectCommand.CommandType = CommandType.StoredProcedure
.SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Fill(resultDataSet, "Customer")
End With
End Using
End Using
Return resultDataSet
End Function
Public Overrides Function GetCustomersAsDataSet() As DataSet
Dim resultDataSet As New DataSet("Customers")
Using connection As New SqlConnection(ConnectionString)
Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomers", connection)
With dataAdapter
.SelectCommand.CommandType = CommandType.StoredProcedure
.Fill(resultDataSet, "Customer")
End With
End Using
End Using
Return resultDataSet
End Function
#End Region
#Region "DataTable Object"
Public Overrides Function GetCustomerAsDataTable(ByVal custID As Integer) As DataTable
Dim recordDataTable As New DataTable("Customer")
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
command.Connection.Open()
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
recordDataTable.Load(reader)
End Using
End Using
End Using
Return recordDataTable
End Function
Public Overrides Function GetCustomersAsDataTable() As DataTable
Dim recordDataTable As New DataTable("Customers")
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
command.CommandType = CommandType.StoredProcedure
command.Connection.Open()
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
recordDataTable.Load(reader)
End Using
End Using
End Using
Return recordDataTable
End Function
#End Region
#Region "ArrayList Object"
Public Overrides Function GetCustomerAsArrayList(ByVal custID As Int32) As ArrayList
Dim arrCustomer As New ArrayList()
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Connection.Open()
End With
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
If reader.Read Then
arrCustomer.Add(reader.Item(custIDOrdinal))
arrCustomer.Add(reader.Item(custNameOrdinal))
arrCustomer.Add(reader.Item(custDOBOrdinal))
arrCustomer.Add(reader.Item(custAddressOrdinal))
arrCustomer.Add(reader.Item(custDateCreatedOrdinal))
arrCustomer.Add(reader.Item(custDateModifiedOrdinal))
End If
End Using
End Using
End Using
arrCustomer.TrimToSize()
Return arrCustomer
End Function
Public Overrides Function GetCustomersAsArrayList() As ArrayList
Dim arrCustomers As New ArrayList()
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
With command
.CommandType = CommandType.StoredProcedure
.Connection.Open()
End With
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
Do While reader.Read
Dim arrCustomer As New ArrayList
arrCustomer.Add(reader.Item(custIDOrdinal))
arrCustomer.Add(reader.Item(custNameOrdinal))
arrCustomer.Add(reader.Item(custDOBOrdinal))
arrCustomer.Add(reader.Item(custAddressOrdinal))
arrCustomer.Add(reader.Item(custDateCreatedOrdinal))
arrCustomer.Add(reader.Item(custDateModifiedOrdinal))
arrCustomer.TrimToSize()
arrCustomers.Add(arrCustomer)
Loop
End Using
End Using
End Using
arrCustomers.TrimToSize()
Return arrCustomers
End Function
#End Region
#End Region
#End Region
End Class
End Namespace
XMLProvider.vb
Option Strict On
Option Explicit On
Imports System.Data
Imports System.IO
Imports System.Xml
' Author : Vishwa
' Purpose: XML Data Provider
Namespace Example.Data
Friend Class XMLProvider
Inherits DataAccess
Public Sub New()
End Sub
#Region "CRUD Operation Using Various Data Trasfer Objects"
#Region "Custom Data Transfer Object"
Public Overrides Function GetCustomer(ByVal ID As Integer) As DTOCustomer
Dim custDataSet As DataSet = GetCustomersAsDataSet()
Dim custRecord As DTOCustomer = Nothing
If custDataSet.Tables.Count > 0 Then
Dim dataRow As DataRow()
dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & ID.ToString & "'")
'Assumption : there will be only one record for given ID
If dataRow.Length > 0 Then
custRecord = New DTOCustomer
custRecord.CustID = CInt(dataRow(0).Item("Cust_ID"))
custRecord.CustName = dataRow(0).Item("Cust_Name").ToString
custRecord.CustDOB = CDate(dataRow(0).Item("Cust_DOB"))
custRecord.CustAddress = dataRow(0).Item("Cust_Address").ToString
custRecord.DateCreated = CDate(dataRow(0).Item("Date_Created"))
custRecord.DateModified = CDate(dataRow(0).Item("Date_Modified"))
End If
'--Alternative Way to Search a Customer in DataSet
'Dim custDataView As DataView = custDataSet.Tables(0).DefaultView
'custDataView.RowFilter = "ID=" & ID.ToString
'If custDataView.Count > 0 Then
' custRecord = New Customer
' custRecord.CustID = CInt(custDataView.Item(0).Item("ID"))
' custRecord.CustName = custDataView.Item(0).Item("Name").ToString
' custRecord.CustDOB = CDate(custDataView.Item(0).Item("DOB"))
' custRecord.CustAddress = custDataView.Item(0).Item("Address").ToString
' custRecord.DateCreated = CDate(custDataView.Item(0).Item("DateCreated"))
' custRecord.DateModified = CDate(custDataView.Item(0).Item("DateModified"))
'End If
End If
Return custRecord
End Function
Public Overrides Function GetCustomers() As List(Of DTOCustomer)
Dim custDataSet As New DataSet("Customers")
Dim custRecord As DTOCustomer = Nothing
Dim customers As New List(Of DTOCustomer)
If File.Exists(XMLDataFile) Then
custDataSet.ReadXml(XMLDataFile)
For i As Integer = 0 To custDataSet.Tables(0).Rows.Count - 1
Dim dataRow As DataRow = custDataSet.Tables(0).Rows(i)
custRecord = New DTOCustomer
custRecord.CustID = CInt(dataRow.Item("Cust_ID"))
custRecord.CustName = dataRow.Item("Cust_Name").ToString
custRecord.CustDOB = CDate(dataRow.Item("Cust_DOB"))
custRecord.CustAddress = dataRow.Item("Cust_Address").ToString
custRecord.DateCreated = CDate(dataRow.Item("Date_Created"))
custRecord.DateModified = CDate(dataRow.Item("Date_Modified"))
customers.Add(custRecord)
Next
End If
Return customers
End Function
Public Overrides Function InsertCustomer(ByVal custRecord As DTOCustomer) As Integer
Return InsertCustomer(custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
End Function
Public Overrides Function UpdateCustomer(ByVal custRecord As DTOCustomer) As Boolean
Return UpdateCustomer(custRecord.CustID, custRecord.CustName, custRecord.CustDOB, custRecord.CustAddress)
End Function
Public Overrides Function DeleteCustomer(ByVal custRecord As DTOCustomer) As Boolean
Return DeleteCustomer(custRecord.CustID)
End Function
#End Region
#Region "Insert/Update/Delete Methods"
Public Overrides Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
ByVal custAddress As String) As Integer
Dim custDataSet As New DataSet("Customers")
Dim returnValue As Integer = 0
Dim custTable As DataTable = Nothing
Dim row As DataRow
If File.Exists(XMLDataFile) Then
custDataSet.ReadXml(XMLDataFile)
If custDataSet.Tables.Count > 0 Then custTable = custDataSet.Tables(0)
Else
custTable = GetCustomerTable()
custDataSet.Tables.Add(custTable)
End If
row = custTable.NewRow()
returnValue = GetNextUniqueID()
row.Item("Cust_ID") = returnValue 'CustID
row.Item("Cust_Name") = custName
row.Item("Cust_Address") = custAddress
row.Item("Cust_DOB") = XmlConvert.ToString(custDOB, XmlDateTimeSerializationMode.Local)
row.Item("Date_Created") = XmlConvert.ToString(Now, XmlDateTimeSerializationMode.Local)
row.Item("Date_Modified") = XmlConvert.ToString(Now, XmlDateTimeSerializationMode.Local)
custDataSet.Tables(0).Rows.Add(row)
custDataSet.WriteXml(XMLDataFile)
Return returnValue
End Function
Public Overrides Function UpdateCustomer(ByVal custID As Integer, ByVal custName As String, _
ByVal custDOB As DateTime, ByVal custAddress As String) As Boolean
Dim custDataSet As DataSet = GetCustomersAsDataSet()
Dim returnValue As Boolean = False
Dim custTable As DataTable = Nothing
If custDataSet.Tables.Count > 0 Then
Dim dataRow As DataRow()
dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & custID.ToString & "'")
'Assumption : there will be only one record for given ID
If dataRow.Length > 0 Then
dataRow(0).Item("Cust_Name") = custName
dataRow(0).Item("Cust_Address") = custAddress
dataRow(0).Item("Cust_DOB") = XmlConvert.ToString(custDOB, XmlDateTimeSerializationMode.Local)
dataRow(0).Item("Date_Modified") = XmlConvert.ToString(Now, XmlDateTimeSerializationMode.Local)
returnValue = True
End If
custDataSet.WriteXml(XMLDataFile)
End If
Return returnValue
End Function
Public Overrides Function DeleteCustomer(ByVal ID As Integer) As Boolean
Dim custDataSet As DataSet = GetCustomersAsDataSet()
Dim returnValue As Boolean = False
Dim custTable As DataTable = Nothing
If custDataSet.Tables.Count > 0 Then
Dim dataRow As DataRow()
dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & ID.ToString & "'")
'Assumption : there will be only one record for given ID
If dataRow.Length > 0 Then
dataRow(0).Delete()
returnValue = True
End If
custDataSet.WriteXml(XMLDataFile)
End If
Return returnValue
End Function
#End Region
#Region "Implementing Two Methods with Other Data Trasfer Objects"
#Region "XML String"
Public Overrides Function GetCustomerAsXMLString(ByVal custID As Int32) As String
Return GetCustomerAsDataSet(custID).GetXml
End Function
Public Overrides Function GetCustomersAsXMLString() As String
Return GetCustomersAsDataSet().GetXml
End Function
#End Region
#Region "DataSet Object"
Public Overrides Function GetCustomerAsDataSet(ByVal custID As Int32) As DataSet
Dim resultDataSet As New DataSet("Customers")
Dim custDataSet As DataSet = GetCustomersAsDataSet()
Dim resultDataTable As DataTable = GetCustomerTable()
If custDataSet.Tables.Count > 0 Then
Dim dataRow As DataRow()
dataRow = custDataSet.Tables("Customer").Select("Cust_ID = '" & custID.ToString & "'")
If dataRow.Length > 0 Then
resultDataTable.Rows.Add(dataRow(0))
resultDataTable.AcceptChanges()
resultDataSet.Tables.Add(resultDataTable)
End If
End If
Return resultDataSet
End Function
Public Overrides Function GetCustomersAsDataSet() As DataSet
Dim resultDataSet As New DataSet("Customers")
If File.Exists(XMLDataFile) Then resultDataSet.ReadXml(XMLDataFile)
Return resultDataSet
End Function
#End Region
#Region "DataTable Object"
Public Overrides Function GetCustomerAsDataTable(ByVal custID As Integer) As DataTable
Dim recordDataTable As New DataTable("Customer")
recordDataTable = GetCustomerAsDataSet(custID).Tables(0)
Return recordDataTable
End Function
Public Overrides Function GetCustomersAsDataTable() As DataTable
Dim recordDataTable As New DataTable("Customer")
recordDataTable = GetCustomersAsDataSet().Tables(0)
Return recordDataTable
End Function
#End Region
#Region "ArrayList Object"
Public Overrides Function GetCustomerAsArrayList(ByVal custID As Int32) As ArrayList
Dim arrCustomer As New ArrayList()
Dim custDataSet As DataSet = GetCustomersAsDataSet()
If custDataSet.Tables.Count > 0 Then
Dim row As DataRow()
row = custDataSet.Tables("Customer").Select("Cust_ID = '" & custID.ToString & "'")
'Assumption : there will be only one record for given ID
If row.Length > 0 Then
arrCustomer.Add(row(0).Item("Cust_ID"))
arrCustomer.Add(row(0).Item("Cust_Name"))
arrCustomer.Add(row(0).Item("Cust_DOB"))
arrCustomer.Add(row(0).Item("Cust_Address"))
arrCustomer.Add(row(0).Item("Date_Created"))
arrCustomer.Add(row(0).Item("Date_Modified"))
End If
End If
arrCustomer.TrimToSize()
Return arrCustomer
End Function
Public Overrides Function GetCustomersAsArrayList() As ArrayList
Dim arrCustomers As New ArrayList()
Dim custDataSet As DataSet = GetCustomersAsDataSet()
If custDataSet.Tables.Count > 0 Then
For Each row As DataRow In custDataSet.Tables(0).Rows
Dim arrCustomer As New ArrayList
arrCustomer.Add(row.Item("Cust_ID"))
arrCustomer.Add(row.Item("Cust_Name"))
arrCustomer.Add(row.Item("Cust_DOB"))
arrCustomer.Add(row.Item("Cust_Address"))
arrCustomer.Add(row.Item("Date_Created"))
arrCustomer.Add(row.Item("Date_Modified"))
arrCustomer.TrimToSize()
arrCustomers.Add(arrCustomer)
Next
End If
arrCustomers.TrimToSize()
Return arrCustomers
End Function
#End Region
#Region "Private Shared Methods"
Private Shared Function GetCustomerTable() As DataTable
Dim customersTable As DataTable = New DataTable("Customer")
customersTable.Columns.Add("Cust_ID", GetType(System.Int32))
customersTable.Columns.Add("Cust_Name", GetType(System.String))
customersTable.Columns.Add("Cust_DOB", GetType(System.DateTime))
customersTable.Columns.Add("Cust_Address", GetType(System.String))
customersTable.Columns.Add("Date_Created", GetType(System.DateTime))
customersTable.Columns.Add("Date_Modified", GetType(System.DateTime))
Return customersTable
End Function
Private Shared Function GetNextUniqueID() As Integer
Dim uniqueID As Guid = Guid.NewGuid()
Dim bArr As Byte() = uniqueID.ToByteArray()
Dim autoNum As Integer = BitConverter.ToInt32(bArr, 0)
If autoNum < 0 Then autoNum = autoNum * -1
Return autoNum
End Function
#End Region
#End Region
#End Region
End Class
End Namespace
Compiled Assembly Name: Vishwa.Example.Data.dll
In next post Data Transfer Objects (DTO) – Business Layer, I will be referring this assembly to consume these methods.