Vishwamohan

Welcome to Vishwa's blog - Technology, Spirituality and More...

Data Transfer Objects (DTO) - Data Access Layer

19. October 2007 Vishwa .NET (0)

When you separate your business layer from data access layer in two different assembly, you come across a situation as which data transfer method will best fit you. In this example, I went through various Data Transfer Objects(DTO) which are currently available in .NET such as XML, DataSet, DataTable, ArrayList and Custom Data Transfer Object . One DTO generally maps to one Business Object. Each option has its pros and cons, so depending on project one should choose as what best fits the need. In the following code I have tried to use all the four possibilities.

Please be aware of the fact that XML is the most flexible but slowest in performance among all. On the other side Custom Objects are least flexible, take extra time to develop but are best performing among all. Using DataSet  and DataTable comes in the middle as they are easy to develop and relatively better in performance with some overhead but relationships between data tables can be easily made and also it has inbuilt capability for sorting, filtering etc.  Custom Objects has minimum overhead, best performance, no boxing or un-boxing but you have to write your own code for adding all additional flexibility which comes by default with DataSet. In my personal opinion, if you are going to develop small to medium size project and quick delivery is the first priority, going with DataSet will be the best option, but you are going to build an Enterprise Application with thousands of concurrent user, then consider developing Custom Objects. I had an opportunity to attend recent Microsoft Seminar on VS 2008, in which they talked about upcoming ADO.NET Entity Framework , which looks like a promising future for designing and developing Custom Entities quickly and easily along with LINQ capability - most likely it will be available with VS 2008 release.  To undertstand the details on pros and cons on business entity represented in various ways, you can check out the following link -  http://msdn2.microsoft.com/en-us/library/ms978496.aspx . Following is a simplified example to implement the same. ArrayList object falls in between DataSet and Custom Object, since it is an array, so it performs faster but due to boxing and unboxing it comes on 2nd in the performance race.
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.
For SQL script and stored procedures you can check out my previous post Developing 3 Tier Application in .NET 2.0 – Part 1 . I am using exactly same objects for this example too.
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
 
' Author : Vishwa@VishwaMohan.Com
' 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
 
 
' Author : Vishwa@VishwaMohan.Com
' 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.
Tags :

Add comment

Loading