有没有人在单元测试SQL存储过程方面取得任何成功？

秦才

2023-03-14

问题内容：

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

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

我们已经尝试过围绕一些关键存储过程构建单元测试（主要是测试性能），但是发现为这些测试设置测试数据确实很困难。例如，我们最终围绕测试数据库进行复制。除此之外，测试最终对更改甚至是对存储过程的最小更改都非常敏感。或表格需要对测试进行大量更改。因此，在由于这些数据库测试间歇性失败而导致许多构建中断之后，我们只需要将它们退出构建过程即可。

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

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

我当时在想，不必建立测试数据表，而是可以简单地创建一个测试对象集合，并在“对象对对象的需求”情况下测试您的linq代码？（我对linq完全陌生，所以不知道这是否还能奏效）

问题答案：

不久前，我遇到了同样的问题，发现如果我为数据访问创建了一个简单的抽象基类，该类允许我注入连接和事务，则可以对存储过程进行单元测试，以查看它们是否在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周期以确保所有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

我知道这是一个很长的示例，但是它有助于为数据访问工作提供一个可重用的类，并为我的测试提供另一个可重用的类，因此我不必一遍又一遍地进行设置/拆卸工作；）

有没有人在单元测试SQL存储过程方面取得任何成功？

相关阅读

相关文章

相关问答

相关工具

相关文档