有人在对SQL存储过程进行单元测试方面取得了成功吗？

Question

我们发现我们为C# / C++代码编写的单元测试确实很有用。但是，我们仍有数千行业务逻辑存储过程，只有在我们的产品面向大量用户时才真正进行愤怒测试。

更糟糕的是，由于将临时表传递给存储过程时的性能损失，其中一些存储过程最终变得非常冗长。这阻止了我们进行重构以使代码更简单。

我们已经尝试过多次围绕一些关键存储过程构建单元测试（主要是测试性能），但发现为这些测试设置测试数据真的很难。例如，我们最终会复制测试数据库。除此之外，测试结果对更改非常敏感，即使对存储过程或表进行最小的更改，也需要大量更改来更新测试。因此，在许多构建过程中由于这些数据库测试间歇性失败而导致构建失败后，我们不得不将其从构建流程中删除。

因此，我问题的主要部分是：是否有人曾成功地为其存储过程编写单元测试？

我的问题的第二部分是：使用linq进行单元测试是否更容易/更容易？

我想，与其必须设置测试数据表，不如直接创建测试对象集合，并在“linq to objects”情况下测试您的linq代码？（我完全不了解linq，因此不知道这是否有效）

- John Sibly

Answer 1

我曾经遇到过同样的问题，后来发现如果我创建一个简单的数据访问抽象基类，允许我注入连接和事务，那么我就可以对我的存储过程进行单元测试，以查看它们是否按照我要求在SQL中完成了工作，然后回滚，使得测试数据不会留在数据库中。

这比通常的“运行脚本设置我的测试数据库，然后在测试运行后清理垃圾/测试数据”更好。这也更接近单元测试，因为这些测试可以单独运行，而不需要在运行这些测试之前将数据库中的“所有内容都变得恰到好处”。

以下是用于数据访问的抽象基类代码片段：

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周期，以确保所有存储过程都能正常工作，并且您的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

我知道这个例子很长，但是它有助于创建一个可重用的数据访问类和另一个可重用的测试类，这样我就不必一遍又一遍地进行设置/拆卸工作了 ;)

- Toran Billups

Answer 2

你尝试过 DBUnit 吗？它专门用于单元测试你的数据库，只需操作数据库，而不需要编写 C# 代码。

- Jon Limjap

Answer 3

如果你考虑到单元测试所促进的代码类型：小而高度内聚，低耦合的例程，那么你应该能够看到至少部分问题所在。在我这个愤世嫉俗的世界里，存储过程是关系型数据库领域长期试图说服你将业务处理转移到数据库中的一部分，当你考虑到服务器许可证成本往往与处理器数量等相关因素时，这是有道理的。你在数据库中运行的越多，他们就会从你身上赚取更多的收益。但我认为你实际上更关心性能，这并不是单元测试的专属领域。单元测试应该是相对原子化的，并且旨在检查行为而不是性能。在这种情况下，你几乎肯定需要生产级别的负载才能检查查询计划。我认为你需要一个不同类别的测试环境。我建议使用生产的副本作为最简单的方法，假设安全性不是问题。然后对于每个候选版本，你都可以从先前的版本开始，使用发布流程进行迁移（这将作为副作用对其进行良好的测试），并运行你的时间测量。就像这样。

- Mike Woodhouse

Answer 4

测试存储过程的关键在于编写一个脚本，用预先计划好的数据填充空数据库，以在调用存储过程时产生一致的行为。

我必须强烈支持存储过程，并将业务逻辑放在我（和大多数DBA）认为它应该存在的地方——数据库中。

我知道作为软件工程师，我们想要美丽的重构代码，用我们喜欢的语言编写，包含所有重要的逻辑，但高容量系统的性能现实和数据完整性的关键性要求我们做出一些妥协。 SQL代码可能很丑陋、重复且难以测试，但如果没有对查询设计完全的控制，我无法想象调整数据库的困难。

我经常被迫完全重新设计查询，包括对数据模型的更改，以使事物在可接受的时间内运行。 有了存储过程，我可以确保更改对调用者是透明的，因为存储过程提供了如此优秀的封装。

- Eric Z Beard

---

你在用你的回答来逗乐，是吧？“业务逻辑”这个词被过度使用了吗？那么你会如何对你的逻辑进行单元测试，使用集成或更高层次的测试？我在保持存储过程简单并在代码中进行逻辑提升方面取得了更大的成功。我曾经有过可怕的经历，因为要确保所有业务逻辑都留在数据库中而使用了荒谬地嵌套的存储过程和函数。不适合我，谢谢。 - brumScouse

Answer 5

好问题。

我有类似的问题，我选择了最简单的方法（至少对我来说是这样）。

还有很多其他的解决方案，其他人也提到了许多。其中许多更好/更纯粹/更适合其他人。

我已经在使用Testdriven.NET/MbUnit测试我的C#代码，所以我只需为每个项目添加测试来调用该应用程序使用的存储过程。

我知道，我知道。听起来很糟糕，但我需要开始进行一些测试，并从那里开始。这种方法意味着虽然我的覆盖率低，但我同时测试了一些存储过程和将要调用它们的代码。这有一定的逻辑。

- AJ

Answer 6

我假设您想在MSSQL中进行单元测试。查看DBUnit，它在支持MSSQL方面存在一些限制。例如，它不支持NVarChar。这里有一些真实用户及其在使用DBUnit时遇到的问题。

- RedWolves

Answer 7

我和原始帖子的作者处于完全相同的情况中。这涉及到性能与可测试性之间的权衡。我的偏见是倾向于可测试性（让它工作，让它正确，让它快），这意味着将业务逻辑保持在数据库之外。数据库不仅缺乏像Java语言中那样的测试框架、代码分解结构和代码分析导航工具，而且高度分解的数据库代码也很慢（高度分解的Java代码则不然）。

但是，我认识到数据库集处理的强大性。当使用得当时，SQL可以用非常少的代码实现一些非常强大的功能。因此，我可以接受一些基于集合的逻辑存在于数据库中，即使我仍然会尽力对其进行单元测试。

在相关问题上，似乎非常长和过程化的数据库代码通常是其他问题的症状，我认为这种代码可以转换为可测试的代码而不会产生性能损失。理论上，这种代码通常代表周期性处理大量数据的批处理过程。如果将这些批处理过程转换为更小的实时业务逻辑块，只要输入数据发生变化，该逻辑就可以在中间层运行（可以进行测试），而不会产生性能损失（因为实时以小块完成工作）。作为副作用，这也消除了批处理错误处理的长时间反馈循环。当然，这种方法并不适用于所有情况，但在某些情况下可能有效。此外，如果您的系统中有大量这种无法测试的批处理数据库代码，则拯救之路可能会漫长而艰难。你的情况可能会有所不同。

- thvo42

Answer 8

但我感觉你更关注性能，这并不是单元测试的核心。单元测试应该比较原子化，旨在检查行为而非性能。如果是这种情况，您几乎肯定需要生产级负载来检查查询计划。 我认为这里有两个非常不同的测试领域：性能和存储过程的实际逻辑。 我曾经给出过测试数据库性能的例子，幸运的是，我们已经达到了足够好的性能水平。我完全同意，在数据库中放置所有业务逻辑的情况很糟糕，但这是我们继承自大多数开发人员加入公司之前的遗留问题。 但是，我们现在正在采用Web服务模型来开发新功能，并且尽可能避免使用存储过程，将逻辑保留在C＃代码中并向数据库发送SQL命令（虽然Linq现在是首选方法）。仍然有一些使用现有SP的情况，这就是我考虑回顾性地对它们进行单元测试的原因。

- John Sibly

Answer 9

你也可以尝试使用 Visual Studio 数据库专业版，它主要关注变更管理，但也提供生成测试数据和单元测试的工具。

不过它非常昂贵。

- Craig

Answer 10

我进行简单的单元测试。如果我懒得做，测试就只是一些有效调用，可能会有问题的参数值。

/*

--setup
Declare @foo int Set @foo = (Select top 1 foo from mytable)

--test
execute wish_I_had_more_Tests @foo

--look at rowcounts/look for errors
If @@rowcount=1 Print 'Ok!' Else Print 'Nokay!'

--Teardown
Delete from mytable where foo = @foo
*/
create procedure wish_I_had_more_Tests
as
select....

- MatthewMartin