Has Anyone Had Any Success in Unit Testing SQL Stored Procedures

Has anyone had any success in unit testing SQL stored procedures?

I ran into this same issue a while back and found that if I created a simple abstract base class for data access that allowed me to inject a connection and transaction, I could unit test my sprocs to see if they did the work in SQL that I asked them to do and then rollback so none of the test data is left in the db.

This felt better than the usual "run a script to setup my test db, then after the tests run do a cleanup of the junk/test data". This also felt closer to unit testing because these tests could be run alone w/out having a great deal of "everything in the db needs to be 'just so' before I run these tests".

Here is a snippet of the abstract base class used for data access

Public MustInherit Class Repository(Of T As Class)
    Implements IRepository(Of T)

    Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString
    Private mConnection As IDbConnection
    Private mTransaction As IDbTransaction

    Public Sub New()
        mConnection = Nothing
        mTransaction = Nothing
    End Sub

    Public Sub New(ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)
        mConnection = connection
        mTransaction = transaction
    End Sub

    Public MustOverride Function BuildEntity(ByVal cmd As SqlCommand) As List(Of T)

    Public Function ExecuteReader(ByVal Parameter As Parameter) As List(Of T) Implements IRepository(Of T).ExecuteReader
        Dim entityList As List(Of T)
        If Not mConnection Is Nothing Then
            Using cmd As SqlCommand = mConnection.CreateCommand()
                cmd.Transaction = mTransaction
                cmd.CommandType = Parameter.Type
                cmd.CommandText = Parameter.Text
                If Not Parameter.Items Is Nothing Then
                    For Each param As SqlParameter In Parameter.Items
                        cmd.Parameters.Add(param)
                    Next
                End If
                entityList = BuildEntity(cmd)
                If Not entityList Is Nothing Then
                    Return entityList
                End If
            End Using
        Else
            Using conn As SqlConnection = New SqlConnection(mConnectionString)
                Using cmd As SqlCommand = conn.CreateCommand()
                    cmd.CommandType = Parameter.Type
                    cmd.CommandText = Parameter.Text
                    If Not Parameter.Items Is Nothing Then
                        For Each param As SqlParameter In Parameter.Items
                            cmd.Parameters.Add(param)
                        Next
                    End If
                    conn.Open()
                    entityList = BuildEntity(cmd)
                    If Not entityList Is Nothing Then
                        Return entityList
                    End If
                End Using
            End Using
        End If

        Return Nothing
    End Function
End Class

next you will see a sample data access class using the above base to get a list of products

Public Class ProductRepository
    Inherits Repository(Of Product)
    Implements IProductRepository

    Private mCache As IHttpCache

    'This const is what you will use in your app
    Public Sub New(ByVal cache As IHttpCache)
        MyBase.New()
        mCache = cache
    End Sub

    'This const is only used for testing so we can inject a connectin/transaction and have them roll'd back after the test
    Public Sub New(ByVal cache As IHttpCache, ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)
        MyBase.New(connection, transaction)
        mCache = cache
    End Sub

    Public Function GetProducts() As System.Collections.Generic.List(Of Product) Implements IProductRepository.GetProducts
        Dim Parameter As New Parameter()
        Parameter.Type = CommandType.StoredProcedure
        Parameter.Text = "spGetProducts"
        Dim productList As List(Of Product)
        productList = MyBase.ExecuteReader(Parameter)
        Return productList
    End Function

    'This function is used in each class that inherits from the base data access class so we can keep all the boring left-right mapping code in 1 place per object
    Public Overrides Function BuildEntity(ByVal cmd As System.Data.SqlClient.SqlCommand) As System.Collections.Generic.List(Of Product)
        Dim productList As New List(Of Product)
        Using reader As SqlDataReader = cmd.ExecuteReader()
            Dim product As Product
            While reader.Read()
                product = New Product()
                product.ID = reader("ProductID")
                product.SupplierID = reader("SupplierID")
                product.CategoryID = reader("CategoryID")
                product.ProductName = reader("ProductName")
                product.QuantityPerUnit = reader("QuantityPerUnit")
                product.UnitPrice = reader("UnitPrice")
                product.UnitsInStock = reader("UnitsInStock")
                product.UnitsOnOrder = reader("UnitsOnOrder")
                product.ReorderLevel = reader("ReorderLevel")
                productList.Add(product)
            End While
            If productList.Count > 0 Then
                Return productList
            End If
        End Using
        Return Nothing
    End Function
End Class

And now in your unit test you can also inherit from a very simple base class that does your setup / rollback work - or keep this on a per unit test basis

below is the simple testing base class I used

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualStudio.TestTools.UnitTesting

Public MustInherit Class TransactionFixture
    Protected mConnection As IDbConnection
    Protected mTransaction As IDbTransaction
    Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString

    <TestInitialize()> _
    Public Sub CreateConnectionAndBeginTran()
        mConnection = New SqlConnection(mConnectionString)
        mConnection.Open()
        mTransaction = mConnection.BeginTransaction()
    End Sub

    <TestCleanup()> _
    Public Sub RollbackTranAndCloseConnection()
        mTransaction.Rollback()
        mTransaction.Dispose()
        mConnection.Close()
        mConnection.Dispose()
    End Sub
End Class

and finally - the below is a simple test using that test base class that shows how to test the entire CRUD cycle to make sure all the sprocs do their job and that your ado.net code does the left-right mapping correctly

I know this doesn't test the "spGetProducts" sproc used in the above data access sample, but you should see the power behind this approach to unit testing sprocs

Imports SampleApplication.Library
Imports System.Collections.Generic
Imports Microsoft.VisualStudio.TestTools.UnitTesting

<TestClass()> _
Public Class ProductRepositoryUnitTest
    Inherits TransactionFixture

    Private mRepository As ProductRepository

    <TestMethod()> _
    Public Sub Should-Insert-Update-And-Delete-Product()
        mRepository = New ProductRepository(New HttpCache(), mConnection, mTransaction)
        '** Create a test product to manipulate throughout **'
        Dim Product As New Product()
        Product.ProductName = "TestProduct"
        Product.SupplierID = 1
        Product.CategoryID = 2
        Product.QuantityPerUnit = "10 boxes of stuff"
        Product.UnitPrice = 14.95
        Product.UnitsInStock = 22
        Product.UnitsOnOrder = 19
        Product.ReorderLevel = 12
        '** Insert the new product object into SQL using your insert sproc **'
        mRepository.InsertProduct(Product)
        '** Select the product object that was just inserted and verify it does exist **'
        '** Using your GetProductById sproc **'
        Dim Product2 As Product = mRepository.GetProduct(Product.ID)
        Assert.AreEqual("TestProduct", Product2.ProductName)
        Assert.AreEqual(1, Product2.SupplierID)
        Assert.AreEqual(2, Product2.CategoryID)
        Assert.AreEqual("10 boxes of stuff", Product2.QuantityPerUnit)
        Assert.AreEqual(14.95, Product2.UnitPrice)
        Assert.AreEqual(22, Product2.UnitsInStock)
        Assert.AreEqual(19, Product2.UnitsOnOrder)
        Assert.AreEqual(12, Product2.ReorderLevel)
        '** Update the product object **'
        Product2.ProductName = "UpdatedTestProduct"
        Product2.SupplierID = 2
        Product2.CategoryID = 1
        Product2.QuantityPerUnit = "a box of stuff"
        Product2.UnitPrice = 16.95
        Product2.UnitsInStock = 10
        Product2.UnitsOnOrder = 20
        Product2.ReorderLevel = 8
        mRepository.UpdateProduct(Product2) '**using your update sproc
        '** Select the product object that was just updated to verify it completed **'
        Dim Product3 As Product = mRepository.GetProduct(Product2.ID)
        Assert.AreEqual("UpdatedTestProduct", Product2.ProductName)
        Assert.AreEqual(2, Product2.SupplierID)
        Assert.AreEqual(1, Product2.CategoryID)
        Assert.AreEqual("a box of stuff", Product2.QuantityPerUnit)
        Assert.AreEqual(16.95, Product2.UnitPrice)
        Assert.AreEqual(10, Product2.UnitsInStock)
        Assert.AreEqual(20, Product2.UnitsOnOrder)
        Assert.AreEqual(8, Product2.ReorderLevel)
        '** Delete the product and verify it does not exist **'
        mRepository.DeleteProduct(Product3.ID)
        '** The above will use your delete product by id sproc **'
        Dim Product4 As Product = mRepository.GetProduct(Product3.ID)
        Assert.AreEqual(Nothing, Product4)
    End Sub

End Class

I know this is a long example, but it helped to have a reusable class for the data access work, and yet another reusable class for my testing so I didn't have to do the setup/teardown work over and over again ;)

Unit Testing and Stored Procedures

The approach I use for this is to encapsulate the the logic layers from the calling of stored procedures behind another method or class. Then you can test the database layer logic separate from testing of the application logic. This way you can create separate unit tests for the client side application logic and integration tests for the server side (database) application logic. Given a piece of code that utilizes a stored procedure call as I have below:

class foo:

    prop1 = 5

    def method1(listOfData):
        for item in listOfData:
            dbobj.callprocedure('someprocedure',item+prop1)

It can be refactored to encapsulate the call to the remote system to it's own method:

class foo:
    prop1 = 5
    def method1(listOfData):
        for item in listOfData:
            someprocedure(item+prop1)

    def someprocedure(value):
        dbobj.callprocedure('someprocedure',value)

Now when you write your unit tests, mock out the someprocedure() class method so that it does not actually make the database call. Then create a separate set of integration tests which require a configured database which calls the actual version of someprocedure() and then verifies that the database is in the correct state.

Anyone had any luck integrating SQL unit tests to run with CI build tool?

You don't say what tools you use for writing you SQL unit tests. If you're using Steven Feuerstein's utplsql tool you should read this artcle on Continuous Integration with Oracle PL/SQL, utPLSQL and Hudson. And even if you're not it might provide some useful insights.

Anyone had any luck integrating SQL unit tests to run with CI build tool?

You don't say what tools you use for writing you SQL unit tests. If you're using Steven Feuerstein's utplsql tool you should read this artcle on Continuous Integration with Oracle PL/SQL, utPLSQL and Hudson. And even if you're not it might provide some useful insights.

Unit-Testing Databases

There's no real way to unit test a database other than asserting that the tables exist, contain the expected columns, and have the appropriate constraints. But that's usually not really worth doing.

You don't typically unit test the database. You usually involve the database in integration tests.

You typically use your favourite automated unit testing framework to perform integration tests, which is why some people get confused, but they don't follow the same rules. You are allowed to involve the concrete implementation of many of your classes (because they've been unit tested). You are testing how your concrete classes interact with each other and with the database.

How do you make the unit test sleep for a period of time within the database pro version of visual studio?

Having unit tests that are slow is always bad. Having unit tests depending on each other (don't know if that is the case) is also bad.

So I strongly suggest you try designing your code so you can test without the delays. But if you really want delays WaitFor might be the solution (assuming SQL server).

---

Related Topics