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)
169 times
Add to favorites
11/23/2017
E-mail Twitter del.icio.us Digg Facebook
Imports System.IO
''' <summary>
''' * Copies the production ms-access database to the test project
''' * Generates a DataTable matching fields/columns in Customers table.
''' * Generates a valid DataRow for testing adding a new customer record.
''' * Generates an invalid DataRow for testing a failed add new customer record
''' </summary>
Public Class TestBaseData
    Private Const databaseName As String = "CustomersDatabase.accdb"

    Public Property UnitTestDatabase() As String
    Private projectDatabase As String

    Public Sub CopyDatabase()
        ' points to the production app runtime folder + database
        projectDatabase = Path.Combine(AppDomain.CurrentDomain.BaseDirectory.UpperFolder(3),
                                       "FrontEnd", "bin", "Debug", databaseName)

        ' location to copy above database too in the unit test project
        UnitTestDatabase = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, databaseName)

        ' must remove an existing copy else a runtime exception will be raised.
        If File.Exists(UnitTestDatabase) Then
            File.Delete(UnitTestDatabase)
        End If

        File.Copy(projectDatabase, UnitTestDatabase)

    End Sub
    ''' <summary>
    ''' Creates a DataTable with columns matching the table Customers in the backend database.
    ''' </summary>
    ''' <returns></returns>
    Private Function CreateCustomerTable() As DataTable
        Dim dt = New DataTable()
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Identifier", .DataType = GetType(Integer)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "CompanyName", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "ContactName", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "ContactTitle", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Address", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "City", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "PostalCode", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Country", .DataType = GetType(String)})

        Return dt
    End Function
    ''' <summary>
    ''' Produces a valid Customer record
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>
    ''' Each field in the backend database customers table has
    ''' constraints that each field is required so don't empty
    ''' out any fields here.
    ''' </remarks>
    Public Function GoodCustomerDataRow_AllValuesPassedToAddNewRecord() As DataRow
        Dim dt = CreateCustomerTable()

        dt.Rows.Add(New Object() _
            {
                Nothing,
                "Karen's auto repair",
                "Karen Payne", "Owner",
                "2398 Adam Circle",
                "Portland",
                "97303",
                "US"
            })

        Return dt.Rows(0)

    End Function
    ''' <summary>
    ''' Create a clone record for a newly added customer record
    ''' </summary>
    ''' <param name="pDataRow"></param>
    ''' <returns></returns>
    Public Function CloneGoodCustomerRecord(ByVal pDataRow As DataRow) As DataRow
        Dim dt = CreateCustomerTable().Clone()
        dt.ImportRow(pDataRow)
        Return dt.Rows(0)

    End Function
    ''' <summary>
    ''' The DataRow sends null for CompanyName and in the backend
    ''' database Customers table CompanyName rule is required thus
    ''' a runtime exception will be thrown and we test for this in
    ''' FailedAddingCustomerRecord_ConstraintViolation unit test.
    ''' </summary>
    ''' <returns></returns>
    Public Function BadCustomerDataRow() As DataRow
        Dim dt = CreateCustomerTable()

        dt.Rows.Add(New Object() {Nothing, Nothing, "Karen Payne", "Owner", "2398 Adam Circle", "Portland", "97303", "US"})

        Return dt.Rows(0)
    End Function
End Class