Unit testing data operations with Access database in VB.NET

DescriptionThis code sample will provide a method to unit test backend database operations, adding records, updating records and removal of records using ms-access database table.I’ve been on various developer forums where developers have issues with their data operations and in

 
 
 
 
 
(0)
172 times
Add to favorites
11/23/2017
E-mail Twitter del.icio.us Digg Facebook
Imports System.Data.OleDb
Imports System.IO

Public Class Operations

    Private Builder As OleDbConnectionStringBuilder = New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0", .DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CustomersDatabase.accdb")}

    Private mExceptiom As Exception
    Public ReadOnly Property Exception() As Exception
        Get
            Return mExceptiom
        End Get
    End Property
    ''' <summary>
    ''' Read all customer into a DataTable
    ''' </summary>
    ''' <returns></returns>
    Public Function ReadCustomers() As DataTable
        Dim dt = New DataTable()

        Try
            Using cn As OleDbConnection = New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

                    cmd.CommandText = "SELECT Identifier,CompanyName, ContactName, ContactTitle, [Address], City,PostalCode, Country FROM  Customers"

                    cn.Open()

                    dt.Load(cmd.ExecuteReader())
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                End Using
            End Using
        Catch ex As Exception
            mExceptiom = ex
        End Try

        Return dt

    End Function
    Public Function FindCustomerByCompanyName(ByVal pCompanyName As String) As Customer
        Dim singleCustomer As New Customer

        Try
            Using cn As OleDbConnection = New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

                    cmd.CommandText = "SELECT Identifier, ContactName, ContactTitle, Phone FROM Customers WHERE CompanyName = @CompanyName"
                    cmd.Parameters.AddWithValue("@CompanyName", pCompanyName)
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
                    If reader.HasRows Then
                        reader.Read()
                        singleCustomer.Identifier = reader.GetInt32(0)
                        singleCustomer.CompanyName = pCompanyName
                        singleCustomer.ContactName = reader.GetString(1)
                        singleCustomer.ContactTitle = reader.GetString(2)
                        singleCustomer.Phone = reader.GetString(3)
                    End If

                End Using
            End Using
        Catch ex As Exception
            mExceptiom = ex
        End Try

        Return singleCustomer

    End Function
    ''' <summary>
    ''' Remove a single customer by primary key
    ''' </summary>
    ''' <param name="pIdentifier"></param>
    ''' <returns></returns>
    Public Function RemoveCustomer(ByVal pIdentifier As Integer) As Boolean
        Dim Success As Boolean = True

        Try
            Using cn As OleDbConnection = New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

                    cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"

                    cmd.Parameters.AddWithValue("@Identifier", pIdentifier)

                    cn.Open()

                    Dim Affected As Integer = cmd.ExecuteNonQuery()

                    If Affected = 1 Then
                        Success = True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch oex As OleDbException
            Success = False
            mExceptiom = oex
        Catch ex As Exception
            Success = False
            mExceptiom = ex
        End Try

        Return Success

    End Function
    ''' <summary>
    ''' Update a single customer by passing a DataRow, we use the primary
    ''' key to locate the record and do the update using existing field values
    ''' in the DataRow passed in. Others might have a parameter for each field 
    ''' rather than passing a data row or perhaps an instance of a concrete class.
    ''' </summary>
    ''' <param name="pRow"></param>
    ''' <returns></returns>
    Public Function UpdateRow(ByVal pRow As DataRow) As Boolean
        Dim Success As Boolean = True

        Try
            Using cn As OleDbConnection = New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

                    cmd.CommandText = "UPDATE Customers SET CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, " & "[Address] = @Address, City = @City,PostalCode = @PostalCode, Country = @Country  WHERE Identifier = @Identifier"


                    cmd.Parameters.AddWithValue("@CompanyName", pRow.Field(Of String)("CompanyName"))
                    cmd.Parameters.AddWithValue("@ContactName", pRow.Field(Of String)("ContactName"))
                    cmd.Parameters.AddWithValue("@ContactTitle", pRow.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@Address", pRow.Field(Of String)("Address"))
                    cmd.Parameters.AddWithValue("@City", pRow.Field(Of String)("City"))
                    cmd.Parameters.AddWithValue("@PostalCode", pRow.Field(Of String)("PostalCode"))
                    cmd.Parameters.AddWithValue("@Country", pRow.Field(Of String)("Country"))
                    cmd.Parameters.AddWithValue("@Identifier", pRow.Field(Of Integer)("Identifier"))

                    cn.Open()

                    Dim Affected As Integer = cmd.ExecuteNonQuery()
                End Using
            End Using
        Catch oex As OleDbException
            Success = False
            mExceptiom = oex
        Catch ex As Exception
            Success = False
            mExceptiom = ex
        End Try

        Return Success
    End Function
    ''' <summary>
    ''' Add a new record using a DataRow.
    ''' pIdentifier will be set if the INSERT was successful, otherwise in the
    ''' caller it will be 0.
    ''' </summary>
    ''' <param name="pRow"></param>
    ''' <param name="pIdentifier"></param>
    ''' <returns></returns>
    Public Function AddNewRow(ByVal pRow As DataRow, ByRef pIdentifier As Integer) As Boolean
        Dim Success As Boolean = True

        Try
            Using cn As OleDbConnection = New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

                    cmd.CommandText = "INSERT INTO Customers (CompanyName,ContactName,ContactTitle,[Address],City,PostalCode,Country) " & "VALUES (@CompanyName,@ContactName,@ContactTitle,@Address,@City,@PostalCode,@Country)"


                    cmd.Parameters.AddWithValue("@CompanyName", pRow.Field(Of String)("CompanyName"))
                    cmd.Parameters.AddWithValue("@ContactName", pRow.Field(Of String)("ContactName"))
                    cmd.Parameters.AddWithValue("@ContactTitle", pRow.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@Address", pRow.Field(Of String)("Address"))
                    cmd.Parameters.AddWithValue("@City", pRow.Field(Of String)("City"))
                    cmd.Parameters.AddWithValue("@PostalCode", pRow.Field(Of String)("PostalCode"))
                    cmd.Parameters.AddWithValue("@Country", pRow.Field(Of String)("Country"))

                    cn.Open()

                    Dim Affected As Integer = cmd.ExecuteNonQuery()

                    If Affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        pIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch oex As OleDbException
            Success = False
            mExceptiom = oex
        Catch ex As Exception
            Success = False
            mExceptiom = ex
        End Try

        Return Success
    End Function

End Class