不久前，我遇到了同样的问题，发现如果我为数据访问创建了一个简单的抽象基类，该类允许我注入连接和事务，则可以对存储过程进行单元测试，以查看它们是否在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
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 @R_506_2419@es 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 @R_506_2419@es 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 @R_506_2419@ 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 @R_506_2419@ 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
我知道这是一个很长的示例，但是它有助于为数据访问工作提供一个可重用的类，并为我的测试提供另一个可重用的类，因此我不必一遍又一遍地进行设置/拆卸工作；）
602392714
清零编程群