Has anyone had any success in unit testing SQL stored procedures?,
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.
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
_ 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.
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