我们发现我们为C#/ C++代码编写的单元测试确实得到了回报.但是,我们仍然在存储过程中拥有数千行业务逻辑,当我们的产品推广到大量用户时,它们才真正得到了真正的测试.
更糟糕的是,这些存储过程中的一些最终会很长,因为在SP之间传递临时表时性能会受到影响.这阻止了我们重构以使代码更简单.
我们已经尝试围绕一些关键存储过程构建单元测试(主要是测试性能),但是发现为这些测试设置测试数据真的很难.例如,我们最终复制测试数据库.除此之外,测试最终对变化非常敏感,甚至是对存储过程的最小变化.或表需要对测试进行大量更改.因此,在由于这些数据库测试间歇性地失败而导致许多构建中断之后,我们只需将它们从构建过程中拉出来.
所以,我的问题的主要部分是:有没有人成功为他们的存储过程编写单元测试？
我的问题的第二部分是使用linq进行单元测试是否更容易？
我想的是,您可以简单地创建一组测试对象,并在"linq to objects"情况下测试您的linq代码,而不是必须设置测试数据表.(我对linq来说是全新的,所以不知道这是否会起作用)
我前一段时间遇到了同样的问题,发现如果我为数据访问创建了一个简单的抽象基类,允许我注入连接和事务,我可以对我的sprocs进行单元测试,看看他们是否在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
_
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
最后 - 以下是使用该测试基类的简单测试,该测试基类显示如何测试整个CRUD周期以确保所有sprocs完成其工作并且您的ado.net代码正确地执行左右映射
我知道这不会测试上面的数据访问示例中使用的"spGetProducts"sproc,但你应该看到这种方法对单元测试sprocs的能力
京公网安备 11010802040832号