有没有人在单元测试SQL存储过程方面取得任何成功? [英] Has anyone had any success in unit testing SQL stored procedures?

问题描述

我们发现，我们为C#/C ++代码编写的单元测试确实得到了回报. 但是我们在存储过程中仍然有成千上万的业务逻辑，只有在将我们的产品推向大量用户时才会真正受到愤怒的考验.

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.

更糟糕的是，由于在SP之间传递临时表时会降低性能，因此某些存储过程最终会变得很长.这使我们无法重构以简化代码.

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?

我的问题的第二部分是使用linq是否会简化单元测试?

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

我当时想，您不必建立测试数据表，而是可以简单地创建一组测试对象，并在"linq to objects"情况下测试您的linq代码? (我对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)

推荐答案

不久前，我遇到了同样的问题，发现如果我创建了一个简单的抽象数据访问基类，它允许我注入连接和事务，我可以对我的存储程序进行单元测试，以查看它们是否按照我要求他们执行的SQL进行了工作，然后回滚，因此没有测试数据保留在数据库中.

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".

以下是用于数据访问的抽象基类的摘要

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

接下来，您将看到一个使用上述基础的示例数据访问类，以获取产品列表

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

现在，在单元测试中，您还可以继承一个非常简单的基类，该基类可以完成设置/回滚工作，或者可以在每个单元测试的基础上进行保留

下面是我使用的简单测试基类

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

，最后-以下是使用该测试基类的简单测试，该演示显示了如何测试整个CRUD周期以确保所有proc都能完成工作，并且ado.net代码可以左右执行正确映射

我知道这不会测试上面的数据访问示例中使用的"spGetProducts"存储过程，但是您应该会看到这种用于单元测试存储过程的强大功能

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

这篇关于有没有人在单元测试SQL存储过程方面取得任何成功?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！