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

Question

We’ve found that the unit tests we’ve written for our C#/C++ code have really paid off. But we still have thousands of lines of business logic in stored procedures, which only really get tested in anger when our product is rolled out to a large number of users.

What makes this worse is that some of these stored procedures end up being very long, because of the performance hit when passing temporary tables between SPs. This has prevented us from refactoring to make the code simpler.

We have made several attempts at building unit tests around some of our key stored procedures (primarily testing the performance), but have found that setting up the test data for these tests is really hard. For example, we end up copying around test databases. In addition to this, the tests end up being really sensitive to change, and even the smallest change to a stored proc. or table requires a large amount of changes to the tests. So after many builds breaking due to these database tests failing intermittently, we’ve just had to pull them out of the build process.

So, the main part of my questions is: has anyone ever successfully written unit tests for their stored procedures?

The second part of my questions is whether unit testing would be/is easier with linq?

I was thinking that rather than having to set up tables of test data, you could simply create a collection of test objects, and test your linq code in a “linq to objects” situation? (I am a totally new to linq so don’t know if this would even work at all)

Answer 1

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 ;)