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

Question :

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

Answer :

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)


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.

Read More  Find out which colours are in use when using the MFC Feature pack in Office 2007 style

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

Read More  Cross platform file-access tracking

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         _      Public Sub CreateConnectionAndBeginTran()          mConnection = New SqlConnection(mConnectionString)          mConnection.Open()          mTransaction = mConnection.BeginTransaction()      End Sub         _      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     _  Public Class ProductRepositoryUnitTest      Inherits TransactionFixture        Private mRepository As ProductRepository         _      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 😉

That’s the answer Has anyone had any success in unit testing SQL stored procedures?, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you