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.IO
Imports BackEnd

<TestClass()> Public Class DataOperationUnitTest
    Inherits TestBaseData
    <TestInitialize>
    Public Sub Init()
        CopyDatabase()
    End Sub
    <TestMethod()> Public Sub ValidateCopyingDatabaseFromProductionFolder()
        Assert.IsTrue(File.Exists(UnitTestDatabase), "Expected the database to exists.")
    End Sub
    <TestMethod()>
    Public Sub ReadCustomers()
        Dim ops = New Operations()
        Dim dtCustomers As DataTable = ops.ReadCustomers
        Assert.IsTrue(dtCustomers.Rows.Count > 0)
    End Sub
    <TestMethod()> Public Sub SuccessfullAdd_CustomerRecord()
        Dim ops = New Operations()
        Dim newIdentifier As Integer = 0
        If ops.AddNewRow(GoodCustomerDataRow_AllValuesPassedToAddNewRecord(), newIdentifier) Then
            Assert.IsTrue(newIdentifier > 0, "Expected the add to be successful")
        End If
    End Sub
    <TestMethod()> Public Sub FailedAddingCustomerRecord_ConstraintViolation()
        Dim ops = New Operations()
        Dim newIdentifier As Integer = 0
        If Not ops.AddNewRow(BadCustomerDataRow(), newIdentifier) Then
            Assert.IsTrue(newIdentifier = 0, "Expected not to find customer")
        End If
    End Sub
    <TestMethod()> Public Sub UpdateCustomerRecord()
        Dim ops = New Operations()
        Dim newIdentifier As Integer = 0

        Dim dataRow = GoodCustomerDataRow_AllValuesPassedToAddNewRecord()

        If ops.AddNewRow(dataRow, newIdentifier) Then
            Dim originalCompanyName = dataRow.Field(Of String)("CompanyName")
            Dim newCompanyName = "Jane's Cafe"

            dataRow.SetField(Of Integer)("Identifier", newIdentifier)
            dataRow.SetField("CompanyName", newCompanyName)

            If ops.UpdateRow(dataRow) Then

                Dim dtCustomers = ops.ReadCustomers()

                Dim customer = dtCustomers _
                    .AsEnumerable() _
                    .Where(Function(row) row.Field(Of Integer)("Identifier") = newIdentifier) _
                    .FirstOrDefault()

                Assert.IsTrue(customer.Field(Of String)("CompanyName") = newCompanyName, "Expected company name to match")

            End If
        End If
    End Sub
    ''' <summary>
    ''' Test deleting a known existing customer record
    ''' </summary>
    <TestMethod()>
    Public Sub DeleteSingleCustomerRecord()

        Dim ops = New Operations()
        Dim newIdentifier As Integer = 0
        Dim dataRow = GoodCustomerDataRow_AllValuesPassedToAddNewRecord()

        If ops.AddNewRow(dataRow, newIdentifier) Then
            If ops.RemoveCustomer(newIdentifier) Then
                Dim dtCustomers = ops.ReadCustomers()

                Dim customer = dtCustomers _
                    .AsEnumerable() _
                    .Where(Function(row) row.Field(Of Integer)("Identifier") = newIdentifier) _
                    .FirstOrDefault()

                Assert.IsNull(customer, "Expected no customer to be returned")

            End If
        End If
    End Sub
    ''' <summary>
    ''' Validate we can find a customer by company name
    ''' </summary>
    <TestMethod()>
    Public Sub FindCustomerByCompanyName()

        Dim ops = New Operations()
        Dim companyName As String = "FISSA Fabrica Inter. Salchichas S.A."
        Dim customer As Customer = ops.FindCustomerByCompanyName(companyName)

        Assert.IsTrue(customer.Identifier > 0, "Expected a customer identifier greater than 0")
        Assert.IsTrue(customer.ContactName = "Diego Roel", "Expected contact name to be 'Diego Roel'")

    End Sub
    ''' <summary>
    ''' This test validates that our FindCustomerByCompanyName truly works
    ''' using an invalid/non-existing company name.
    ''' </summary>
    <TestMethod()>
    Public Sub FailureFindCustomerByCompanyName()

        Dim ops = New Operations()
        Dim companyName As String = "FISSA Inc"
        Dim customer As Customer = ops.FindCustomerByCompanyName(companyName)

        Assert.IsTrue(customer.Identifier = 0, "Expected Identifier to be 0")

    End Sub
End Class