前言
接續前一篇, 筆者找到的第2個方案是 Visual Studio + Specflow + Entity Framework .
Specflow 是一個用以與使用者溝通的語言工具, 用以描述使用者的需求案例. 適用於整合測試. 當然, 最好是由 Top Down 的方式, 由 BDD (Behavior-Driven Development) 逐步產生實際運作的程式碼 (Production Code); 但若還是不太習慣, 也是可以先寫實際運作的程式碼, 再寫測試方法 (Test Code).
Entity Framework 是微軟以 ADO.NET 為基礎所發展出來的物件關聯對應 (O/R Mapping) 解決方案, 在本文中, 可以用它來建立及清除測試資料.
不過, 該方案需要開發人員, 除了 T-SQL 外, 還要會寫 C# 的程式碼 .
1.. 需求
Feature: LoanLibrary
In order to let customers knows loan details
As an trader
I want to have an feature to enter query condition, then get results
Scenario: 依據客戶姓名、借款起日、借款迄日 條件，查出顧客的借款明細
Given 查詢條件為
| Name | LoanDateStart | LoanDateEnd |
| Jasper | 2017-01-01 | 2017-12-31 |
And 預計 Clients 資料應有
| BranchId | AccNo | AccNoChk | Name |
| 8880 | 999999 | 6 | Jasper |
And 預計 Loans 資料應有
| BranchId | AccNo | AccNoChk | LoanType | LoanDate | Days | Amt |
| 8880 | 999999 | 6 | 1 | 2016-07-01 | 360 | 10000 |
| 8880 | 999999 | 6 | 1 | 2016-08-01 | 360 | 20000 |
| 8880 | 999999 | 6 | 1 | 2017-07-01 | 180 | 10000 |
| 8880 | 999999 | 6 | 1 | 2017-08-01 | 180 | 20000 |
When 呼叫 Query 方法
Then 查詢結果應為
| BranchId | AccNo | AccNoChk | LoanType | LoanDate | Days | Amt | Name |
| 8880 | 999999 | 6 | 1 | 2017-07-01 | 180 | 10000 | Jasper |
| 8880 | 999999 | 6 | 1 | 2017-08-01 | 180 | 20000 | Jasper |
2.. 相關的 Table 及 Stored Procedure
-- 刪除
DROP PROCEDURE IF EXISTS dbo.usp_GetLoansByName;
DROP TABLE IF EXISTS dbo.Loans;
DROP TABLE IF EXISTS dbo.InterestRates;
DROP TABLE IF EXISTS dbo.Clients;
GO
-- 客戶基本資料檔
CREATE TABLE dbo.Clients
( [BranchId] VARCHAR(04) NOT NULL -- 分公司代號
, [AccNo] VARCHAR(06) NOT NULL -- 客戶帳號流水號
, [AccNoChk] VARCHAR(01) NOT NULL -- 客戶帳號檢查碼
, [Name] NVARCHAR(20) NOT NULL -- 客戶姓名
CONSTRAINT PK_Clients PRIMARY KEY ( [BranchId], [AccNo], [AccNoChk] )
);
GO
-- 客戶借款利率檔
CREATE TABLE dbo.InterestRates
( [LoanType] VARCHAR(1) NOT NULL -- 客戶借款類別 ('1':VIP, '2':Normal, '3':Risk), 此與借款利率有關
, [Desc] NVARCHAR(20) NOT NULL -- 描述
, [Rate] DECIMAL(3,2) NOT NULL -- 利率 (例如: 0.12, 0.10, 0.08)
CONSTRAINT PK_InterestRates PRIMARY KEY ( [LoanType] )
);
GO
-- 客戶借款資料檔
CREATE TABLE dbo.Loans
( [LoanId] INT IDENTITY(1,1) NOT NULL -- 借款序號
, [BranchId] VARCHAR(04) NOT NULL -- 分公司代號
, [AccNo] VARCHAR(06) NOT NULL -- 客戶帳號流水號
, [AccNoChk] VARCHAR(01) NOT NULL -- 客戶帳號檢查碼
, [LoanType] VARCHAR(01) DEFAULT '2' -- 客戶借款類別 ('1':VIP, '2':Normal, '3':Risk), 此與借款利率有關
, [LoanDate] DATE DEFAULT GETDATE() -- 借款日期
, [Days] INT DEFAULT 10 -- 借款天數
, [Amt] INT NOT NULL -- 借款金額
CONSTRAINT PK_Loans PRIMARY KEY ( [LoanId] )
, CONSTRAINT FK_Loans_Clients FOREIGN KEY ( [BranchId], [AccNo], [AccNoChk] ) REFERENCES dbo.Clients ( [BranchId], [AccNo], [AccNoChk] )
, CONSTRAINT FK_Loans_InterestRates FOREIGN KEY ( [LoanType] ) REFERENCES dbo.InterestRates ( [LoanType] )
);
GO
-- 儲存測試資料
INSERT INTO dbo.InterestRates
VALUES ('1', N'VIP', 0.08)
, ('2', N'Normal', 0.10)
, ('3', N'Risk', 0.12)
;
GO
-- 依傳入的姓名, 取出對應的借款明細
CREATE PROCEDURE usp_GetLoansByName
@pi_Name NVARCHAR(20)
AS
BEGIN
SELECT A.*, B.Name
FROM Loans A LEFT OUTER JOIN Clients B
ON ( A.BranchId = B.BranchId AND A.AccNo = B.AccNo AND A.AccNoChk = B.AccNoChk)
WHERE B.Name = @pi_Name;
END;
GO
3.. 撰寫實際運作程式碼 (Test Code)
(1) 新增空白方案
06-LoanSPTestWithSpecflow
(2) 新增類別庫專案
LoanLirary
(3) 建立 Models 資料夾, 以存放由 Database 匯入產生的 Entity Framework Model (Database First)
LoanDBContext
結果
下圖的前3個是 Table 轉換出來的物件, usp_GetLoansByName_Result 是由 Stored Procedure 推論出來的一個複雜模型 (Complex Type), 用以存放 Stored Procedure 的回傳結果
切到模型瀏覽器
檢視或編輯函式匯入對應
(4) 前述是只用 Name 查的部份, 要加上 LoanDate 的條件, 才能符需求, 所以, 再加一個 usp_GetLoansByNameAndLoanDate
-- 依傳入的姓名, 及日期區間, 取出對應的借款明細
CREATE PROCEDURE usp_GetLoansByNameAndLoanDate
@pi_name NVARCHAR(20)
, @pi_date_s DATE = NULL
, @pi_date_e DATE = NULL
AS
BEGIN
DECLARE @ld_date_s DATE = @pi_date_s
, @ld_date_e DATE = @pi_date_e
-- 註: 這裡先不檢查輸入日期的正確性
IF @pi_date_s IS NULL
SELECT @ld_date_s = CAST('17530101' as DATE)
IF @pi_date_e IS NULL
SELECT @ld_date_e = CAST('99991231' as DATE)
SELECT A.*, B.Name
FROM Loans A LEFT OUTER JOIN Clients B
ON ( A.BranchId = B.BranchId AND A.AccNo = B.AccNo AND A.AccNoChk = B.AccNoChk)
WHERE B.Name = @pi_Name
AND A.LoanDate BETWEEN @ld_date_s AND @ld_date_e;
END;
GO
(5) 建立商業邏輯及服務
LoanService.cs
using LoanLirary.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LoanLirary
{
public class LoanService
{
private LoanEntities _db;
public IEnumerable<usp_GetLoansByName_Result> Query(string clientName)
{
using (_db = new LoanEntities())
{
var result = _db.usp_GetLoansByName(clientName);
return result;
}
}
public IEnumerable<usp_GetLoansByNameAndLoanDate_Result> Query2(string clientName, DateTime? startDate, DateTime? endDate)
{
using (_db = new LoanEntities())
{
var result = _db.usp_GetLoansByNameAndLoanDate(clientName, startDate, endDate);
return result;
}
}
}
}
4.. 撰寫測試程式碼 (Test Code)
(1) 加入測試專案
(2) 加入對 Production Code 的專案參考
(3) 加入對 Entity Framework 的 nuget 參考; 留意 app.config 的變化.
(4) 加入對 Specflow 的 nuget 參考. 留意 app.config 的變化.
(5) 修改測試專案的 app.config : 加入 MsTest (for Specflow), 並將 LoanLibray App.config 的連接字串, 複製過來.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468-->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<section name="specFlow" type="TechTalk.SpecFlow.Configuration.ConfigurationSectionHandler, TechTalk.SpecFlow" />
</configSections>
<connectionStrings>
<add name="LoanEntities" connectionString="metadata=res://*/Models.LoanDBContext.csdl|res://*/Models.LoanDBContext.ssdl|res://*/Models.LoanDBContext.msl;provider=System.Data.SqlClient;provider connection string="data source=.\SQL2016;initial catalog=Loan;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="mssqllocaldb" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
<specFlow>
<!-- For additional details on SpecFlow configuration options see http://go.specflow.org/doc-config-->
<unitTestProvider name="MsTest"></unitTestProvider>
</specFlow>
</configuration>
主要在以下2個段落
(6) 在測試專案, 加入 Feature File
LoanLibrary.feature
Feature: LoanLibrary
In order to let customers knows loan details
As an trader
I want to have an feature to enter query condition, then get results
Scenario: 依據客戶姓名、借款起日、借款迄日 條件，查出顧客的借款明細
Given 查詢條件為
| Name | LoanDateStart | LoanDateEnd |
| Jasper | 2017-01-01 | 2017-12-31 |
And 預計 Clients 資料應有
| BranchId | AccNo | AccNoChk | Name |
| 8880 | 999999 | 6 | Jasper |
And 預計 Loans 資料應有
| BranchId | AccNo | AccNoChk | LoanType | LoanDate | Days | Amt |
| 8880 | 999999 | 6 | 1 | 2016-07-01 | 360 | 10000 |
| 8880 | 999999 | 6 | 1 | 2016-08-01 | 360 | 20000 |
| 8880 | 999999 | 6 | 1 | 2017-07-01 | 180 | 10000 |
| 8880 | 999999 | 6 | 1 | 2017-08-01 | 180 | 20000 |
When 呼叫 Query 方法
Then 查詢結果應為
| BranchId | AccNo | AccNoChk | LoanType | LoanDate | Days | Amt | Name |
| 8880 | 999999 | 6 | 1 | 2017-07-01 | 180 | 10000 | Jasper |
| 8880 | 999999 | 6 | 1 | 2017-08-01 | 180 | 20000 | Jasper |
留意一下顏色
(7) 針對 Feature File 定義的各個 Scenario, 產生 Step Definition
要留意檔案的位置, 要與 feature 檔相同資料夾, 比較好維護
產生 Step Definition 後, feature 內容的顏色變了
(8) 實作測試程式 (Step Definition)
A.. 最原始的 Step Definition File
using System;
using TechTalk.SpecFlow;
namespace LoanLibraryTests
{
[Binding]
public class LoanLibrarySteps
{
[Given(@"查詢條件為")]
public void Given查詢條件為(Table table)
{
ScenarioContext.Current.Pending();
}
[Given(@"預計 Clients 資料應有")]
public void Given預計Clients資料應有(Table table)
{
ScenarioContext.Current.Pending();
}
[Given(@"預計 Loans 資料應有")]
public void Given預計Loans資料應有(Table table)
{
ScenarioContext.Current.Pending();
}
[When(@"呼叫 Query 方法")]
public void When呼叫Query方法()
{
ScenarioContext.Current.Pending();
}
[Then(@"查詢結果應為")]
public void Then查詢結果應為(Table table)
{
ScenarioContext.Current.Pending();
}
}
}
B.. 宣告 2 個私有變數, 並解決紅色毛毛蟲底線的問題
private LoanService _target; private LoanEntities _db;
C.. 在 BeforeScenario 初始化 target, 並使用 dbContext 清除測試程式產生的資料
[BeforeScenario]
public void BeforeScenario()
{
this._target = new LoanService();
using (_db = new LoanEntities())
{
_db.Database.ExecuteSqlCommand("Delete [Loans] Where BranchId = '8880' And AccNo = '999999' ");
_db.Database.ExecuteSqlCommand("Delete [Clients] Where BranchId = '8880' And AccNo = '999999' ");
}
}
D.. 在 AfterScenario 使用 dbContext 清除測試程式產生的資料
[AfterScenario]
public void AfterScenario()
{
using (_db = new LoanEntities())
{
_db.Database.ExecuteSqlCommand("Delete [Loans] Where BranchId = '8880' And AccNo = '999999' ");
_db.Database.ExecuteSqlCommand("Delete [Clients] Where BranchId = '8880' And AccNo = '999999' ");
}
}
E.. 修訂 [Given(@"查詢條件為")]
[Given(@"查詢條件為")]
public void Given查詢條件為(Table table)
{
var condition = table.CreateInstance();
ScenarioContext.Current.Set(condition);
}
Tips:
* 建立 QueryConditionDto
public string Name { get; set; }
public DateTime? LoanDateStart { get; set; }
public DateTime? LoanDateEnd { get; set; }
* table.CreateInstance<T>() 可以將 scenario 上的 table 取得單筆的強型別物件
* table.CreateSet<T>() : 可以將 scenario 上的 table 取得多筆的強型別物件
* 上述 2 個方法, 都是 extension method, 要 using TechTalk.SpecFlow.Assist;
F.. 修訂 [Given(@"預計 Clients 資料應有")]
[Given(@"預計 Clients 資料應有")]
public void Given預計Clients資料應有(Table table)
{
var clients = table.CreateSet();
using (_db = new LoanEntities() )
{
_db.Clients.AddRange(clients);
_db.SaveChanges();
}
}
G.. 修訂 [Given(@"預計 Loans 資料應有")]
[Given(@"預計 Loans 資料應有")]
public void Given預計Loans資料應有(Table table)
{
var loans = table.CreateSet();
using (_db = new LoanEntities())
{
_db.Loans.AddRange(loans);
_db.SaveChanges();
}
}
H.. 修訂 [When(@"呼叫 Query 方法")]
把剛剛在 [Given(@"查詢條件為")] 的 condition 變數取出, 並呼叫真正的 target 的方法; 將結果存入 actual 變數
[When(@"呼叫 Query 方法")]
public void When呼叫Query方法()
{
var condition = ScenarioContext.Current.Get<QueryConditionDto>();
// IEnumerable<T> : 要加上命名空間 using System.Collections.Generic;
// var startDate = condition.LoanDateStart.HasValue ? condition.LoanDateStart : DateTime.MinValue;
// var endDate = condition.LoanDateEnd.HasValue ? condition.LoanDateEnd : DateTime.MaxValue;
IEnumerable<usp_GetLoansByNameAndLoanDate_Result> actual = this._target.Query2(condition.Name, condition.LoanDateStart, condition.LoanDateEnd);
ScenarioContext.Current.Set<IEnumerable<usp_GetLoansByNameAndLoanDate_Result>>(actual);
}
I.. 修訂 [Then(@"查詢結果應為")]
a.. 把 When 步驟存入的 actual 取出, 並轉型為 IEnumerable<T>
b.. 把 table 利用 CompareToSet<T> 轉成 IEnumerable<T> 後, 與 actual 作比對
[Then(@"查詢結果應為")]
public void Then查詢結果應為(Table table)
{
var actual = ScenarioContext.Current.Get<IEnumerable<QueryResultDto>>();
table.CompareToSet<QueryResultDto>(actual);
}
5.. 執行測試
(1) 打開測試總管
(2) 執行測試
結果失敗, 按一下 [輸出], 查明細訊息
The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.
原本以為是最後個 STEP 出錯, 結果, 最後發現是服務層的 Query2() 方法, 回傳時, 沒有用 ToList() 把資料由 DB 真的取回.
修訂一下 LoanService.cs
主要是加上 return result.ToList();
using LoanLirary.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LoanLirary
{
public class LoanService
{
private LoanEntities _db;
public IEnumerable<usp_GetLoansByName_Result> Query(string clientName)
{
using (_db = new LoanEntities())
{
var result = _db.usp_GetLoansByName(clientName);
return result.ToList();
}
}
public IEnumerable<usp_GetLoansByNameAndLoanDate_Result> Query2(string clientName, DateTime? startDate, DateTime? endDate)
{
using (_db = new LoanEntities())
{
var result = _db.usp_GetLoansByNameAndLoanDate(clientName, startDate, endDate);
return result.ToList();
}
}
}
}
再測一次, 終於綠燈了
6.. 結論
該方案看來有些複雜, 但卻比較貼近使用者的想法, 適合熟悉 C# 的開發人員, 針對 stored procedure 進行整合測試.
所以筆者想法是, 可以搭配前一篇的 tSQLt, 由 tSQLt 針對 stored procedure 作單元測試; 由 Visual Studio + Specflow + Entity Framework 對 stored procedure 作整合試.
沒有留言:
張貼留言