Neil Haddley - Developer & Technical Consultant

VB.NET Console SQL Server Unit Tests

Neil Haddley • May 27, 2023

Test driven development

Unit tests are automated tests written and run by software developers to ensure that a section of an application (known as the "unit") meets its design and behaves as intended.

I wanted to add unit tests to a Visual Basic Console App that fetched data from a database using SqlConnection, SqlCommand, SqlDataAdapter and DataTable classes.

In any case it is best practice is for Unit Tests to run independent of a running database server.

I created a new project (in a new solution)

I selected the .NET Framework (old school)

I selected .NET Framework 4.7.2

Main

I added Hello World code

I updated the Console Application to connect to a database and display the contents of a Tasks table

I added a second project to the solution. I selected the Unit Test Project (.NET Framework) template

I accepted the defaults

I added a reference to the Test project

ChatGPT

I asked ChatGPT to generate unit tests for the code.

Write Unit Tests for this code

Imports System.Data.SqlClient

Module Module1

...

To write unit tests for the given code, you can use a testing framework such as NUnit or xUnit. However, since the code primarily consists of database interactions and console output, it may not be feasible to write traditional unit tests for it.

Instead, you can consider refactoring the code to improve testability by separating concerns. One approach could be to move the database-related code into a separate class or method that returns the result DataTable. Then, you can write unit tests specifically for that database-related code.

Here's an example of how you can refactor the code and write unit tests for the database interaction part using NUnit:

...

Database Tests

Refactoring the code with testing in mind

As ChatGPT explained it is hard to create Unit Tests for the database and Console code contained in the Module1.vb.

It does not help that the database access and Console.WriteLine code is mixed together in a single block.

I extracted the WriteDataTable code to create a function that was easier to create Unit Tests for.

I wrote the UnitTestProject2 tests with the help of ChatGPT.

The WriteDataTable function expects to be passed a DataTable.

Running the Unit Tests

UnitTest1.vb

TEXT

1Imports System.Data.SqlClient
2Imports System.Text
3Imports Microsoft.VisualStudio.TestTools.UnitTesting
4
5
6<TestClass()> Public Class DatabaseTests
7    Private ReadOnly connectionString As String = "Data Source=(local);Initial Catalog=ToDo;user id=sa;password=Passw0rd123"
8
9    <TestMethod()>
10    Public Sub GetData_ShouldReturnValidDataTable()
11        ' Arrange
12        Dim database As New DatabaseInteraction(connectionString)
13
14        ' Act
15        Dim result As DataTable = database.GetData()
16
17        ' Assert
18        Assert.IsNotNull(result)
19        Assert.IsTrue(result.Rows.Count > 0)
20    End Sub
21End Class
22
23Public Class DatabaseInteraction
24    Private ReadOnly connectionString As String
25
26    Public Sub New(connectionString As String)
27        Me.connectionString = connectionString
28    End Sub
29
30    Public Function GetData() As DataTable
31        Dim query As String = "SELECT * FROM Items"
32        Dim result As DataTable = Nothing
33
34        Using connection As New SqlConnection(connectionString)
35            connection.Open()
36            Using command As New SqlCommand(query, connection)
37                Using adapter As New SqlDataAdapter(command)
38                    result = New DataTable()
39                    adapter.Fill(result)
40                End Using
41            End Using
42        End Using
43
44        Return result
45    End Function
46End Class

Module1.vb

TEXT

1Imports System.Data.SqlClient
2
3Public Module Module1
4
5    Private ReadOnly connectionString As String = "Data Source=(local);Initial Catalog=ToDo;user id=sa;password=Passw0rd123"
6
7    Sub Main()
8        Console.WriteLine("Tasks")
9
10        Dim query As String = "SELECT * FROM Items"
11        Dim result As DataTable = Nothing
12        Using connection As New SqlConnection(connectionString)
13            connection.Open()
14            Using command As New SqlCommand(query, connection)
15                Using adapter As New SqlDataAdapter(command)
16                    result = New DataTable()
17                    adapter.Fill(result)
18
19                    WriteDataTable(result)
20
21                End Using
22            End Using
23        End Using
24
25        Console.ReadLine()
26    End Sub
27
28    Public Function WriteDataTable(result As DataTable)
29        Console.WriteLine("{0,10}{1,20}{2,7}", "ID", "Description", "Done")
30
31        Dim count As Integer = 0
32        For Each row As DataRow In result.Rows
33            Console.WriteLine("{0,10}{1,20}{2,7}", row("ID"), row("Description"), row("Done"))
34            count = count + 1
35        Next
36
37        Return count
38    End Function
39End Module

UnitTestProject1 - UnitTest1.vb

TEXT

1mports System.Data.SqlClient
2Imports System.Text
3Imports Microsoft.VisualStudio.TestTools.UnitTesting
4
5<TestClass()> Public Class UnitTest1
6
7    Private ReadOnly connectionString As String = "Data Source=(local);Initial Catalog=ToDo;user id=sa;password=Passw0rd123"
8
9    Private _dataTable As New DataTable()
10
11    <TestInitialize>
12    Public Sub intialize()
13
14        'reset the test data before each test method is executed
15        _dataTable = New DataTable()
16
17        ' Create the example result DataTable
18        _dataTable.Columns.Add("ID", GetType(Integer))
19        _dataTable.Columns.Add("Description", GetType(String))
20        _dataTable.Columns.Add("Done", GetType(Boolean))
21
22        _dataTable.Rows.Add(1000, "Feed Dog", False)
23
24    End Sub
25
26    <TestMethod>
27    Public Sub TestDataFromDataTable()
28
29        ' Act
30        Dim count = ConsoleApp1.WriteDataTable(_dataTable)
31
32        ' Assert
33        Assert.IsNotNull(_dataTable)
34        Assert.IsTrue(_dataTable.Rows.Count = 1)
35        Assert.IsTrue(count = _dataTable.Rows.Count)
36    End Sub
37
38    <TestMethod>
39    Public Sub TestDataFromDataTableAddItem()
40
41        'Arrange 
42        _dataTable.Rows.Add(1001, "Feed Cat", False)
43
44        ' Act
45        Dim count = ConsoleApp1.WriteDataTable(_dataTable)
46
47        ' Assert
48        Assert.IsNotNull(_dataTable)
49        Assert.IsTrue(_dataTable.Rows.Count = 2)
50        Assert.IsTrue(count = _dataTable.Rows.Count)
51    End Sub
52
53
54    <TestMethod>
55    Public Sub TestDataFromDataTableAddTwoItems()
56
57        'Arrange 
58        _dataTable.Rows.Add(1001, "Feed Snake", False)
59        _dataTable.Rows.Add(1001, "Feed Mouse", False)
60
61        ' Act
62        Dim count = ConsoleApp1.WriteDataTable(_dataTable)
63
64        ' Assert
65        Assert.IsNotNull(_dataTable)
66        Assert.IsTrue(_dataTable.Rows.Count = 3)
67        Assert.IsTrue(count = _dataTable.Rows.Count)
68    End Sub
69
70
71    <TestMethod>
72    Public Sub SelectDataFromDatabaseTest()
73        ' Arrange
74        Dim query As String = "SELECT * FROM Items"
75        Dim result As DataTable = Nothing
76        Dim count As Integer = 0
77
78        ' Act
79        Using connection As New SqlConnection(connectionString)
80            connection.Open()
81            Using command As New SqlCommand(query, connection)
82                Using adapter As New SqlDataAdapter(command)
83                    result = New DataTable()
84                    adapter.Fill(result)
85
86                    count = ConsoleApp1.WriteDataTable(result)
87
88                End Using
89            End Using
90        End Using
91
92        ' Assert
93        Assert.IsNotNull(result)
94        Assert.IsTrue(result.Rows.Count > 0)
95        Assert.IsTrue(count = result.Rows.Count)
96    End Sub
97
98
99End Class

UnitTestProject2 - UnitTest1.vb

TEXT

1Imports System.Data.SqlClient
2Imports System.IO
3Imports ConsoleApp1
4
5<TestClass()> Public Class DatabaseTests
6    Private ReadOnly connectionString As String = "Data Source=(local);Initial Catalog=ToDo;user id=sa;password=Passw0rd123"
7
8    <TestMethod()>
9    Public Sub GetData_ShouldReturnValidDataTable()
10        ' Arrange
11        Dim database As New DatabaseInteraction(connectionString)
12
13        ' Act
14        Dim result As DataTable = database.GetData()
15
16        ' Assert
17        Assert.IsNotNull(result)
18        Assert.IsTrue(result.Rows.Count > 0)
19    End Sub
20End Class
21
22Public Class DatabaseInteraction
23    Private ReadOnly connectionString As String
24
25    Public Sub New(connectionString As String)
26        Me.connectionString = connectionString
27    End Sub
28
29    Public Function GetData() As DataTable
30        Dim query As String = "SELECT * FROM Items"
31        Dim result As DataTable = Nothing
32
33        Using connection As New SqlConnection(connectionString)
34            connection.Open()
35            Using command As New SqlCommand(query, connection)
36                Using adapter As New SqlDataAdapter(command)
37                    result = New DataTable()
38                    adapter.Fill(result)
39                End Using
40            End Using
41        End Using
42
43        Return result
44    End Function
45End Class
46
47
48
49
50<TestClass()> Public Class Module1Tests
51    Private ReadOnly connectionString As String = "Data Source=(local);Initial Catalog=ToDo;user id=sa;password=Passw0rd123"
52
53    <TestMethod>
54    Public Sub WriteDataTable_ShouldWriteCorrectData()
55        ' Arrange
56        Dim result As New DataTable()
57        result.Columns.Add("ID", GetType(Integer))
58        result.Columns.Add("Description", GetType(String))
59        result.Columns.Add("Done", GetType(Boolean))
60        result.Rows.Add(1, "Task 1", True)
61        result.Rows.Add(2, "Task 2", False)
62
63        Dim expectedOutput As String =
64            "        ID         Description   Done" + vbCrLf +
65            "         1              Task 1   True" + vbCrLf +
66            "         2              Task 2  False" + vbCrLf
67
68        ' Act
69        Dim consoleOutput As String = CaptureConsoleOutput(Sub() Module1.WriteDataTable(result))
70
71        Console.WriteLine("Expected")
72        Console.WriteLine("<" + expectedOutput + ">")
73        Console.WriteLine("Test Result")
74        Console.WriteLine("<" + consoleOutput + ">")
75
76        ' Assert
77        Assert.AreEqual(expectedOutput, consoleOutput)
78    End Sub
79
80    ' Helper method to capture console output during testing
81    Private Function CaptureConsoleOutput(action As Action) As String
82        Dim originalOut As TextWriter = Console.Out
83        Dim writer As New StringWriter()
84        Console.SetOut(writer)
85        action.Invoke()
86        Console.SetOut(originalOut)
87        Return writer.ToString()
88    End Function
89End Class