#眉標=VS2010 Ultimate、SQL Server、TDD
#副標=Visual Studio 2010資料庫建構(4)
#大標=對資料庫進行單元測試
#作者=文／圖 胡百敬




=====程式1	<box>===========
CREATE TABLE #t (Products NVARCHAR(40),UnitPrice MONEY)
---測試預存程序執行的結果是否符合需求
INSERT #t EXEC dbo.[Ten Most Expensive Products]
IF @@rowcount<>10
　RAISERROR('[Ten Most Expensive Products] 傳回非 10 筆的紀錄數',16,1) 
ELSE
BEGIN
　DECLARE @Price MONEY,@Count INT
　SELECT @Price=MIN(UnitPrice) FROM #t
　SELECT @Count=COUNT(*) FROM Products WHERE UnitPrice > @Price
　IF @Count <> 9  -- 假設沒有價格相同的產品
　RAISERROR('[Ten Most Expensive Products] 傳回紀錄並非是最高的前 10 筆',16,1) 
END
DROP TABLE #t
====<end>==============





=====程式2	<BOX>=============
CREATE TABLE [dbo].[tblRowCount]
(
　PK INT IDENTITY(1,1) PRIMARY KEY, 
　TableName nvarchar(30) NOT NULL UNIQUE,
　RowsCount INT NOT NULL
);
=====<end>==============





=====程式3	<BOX>=============
INSERT tblRowCount VALUES('tblTestRename',0)
CREATE TRIGGER [trgInsertDelete]
ON [dbo].[tblTestRename]
FOR INSERT,DELETE
AS 
BEGIN
　SET NOCOUNT ON
　UPDATE tblRowCount SET RowsCount=RowsCount + (SELECT COUNT(*) FROM Inserted)
　WHERE TableName='tblTestRename'
　UPDATE tblRowCount SET RowsCount=RowsCount - (SELECT COUNT(*) FROM Deleted)
　WHERE TableName='tblTestRename'
END
=====<end>==============





=====程式4	<BOX>=============
UPDATE tblRowCount SET RowsCount=(SELECT COUNT(*) FROM tblTestRename) 
INSERT tblTestRename(CustomerID,BeginDate,DueDate) SELECT CustomerID,GetDate(),GetDate()+1 FROM Customers
SELECT * FROM tblRowCOunt
SELECT * FROM tblTestRename
CREATE FUNCTION [dbo].[GetOneYearDue]
(
　@CustID nvarchar(5)
)
RETURNS TABLE AS RETURN 
(
  SELECT * FROM tblTestRename 
  WHERE CustomerID LIKE @CustID 
  AND (DueDate BETWEEN GetDate() AND DateAdd(yy,1,GetDate()))
)
=====<end>==============






=====程式5	<反灰>=============
<TestMethod()> _
Public Sub dbo_trgInsertDeleteTest()
  Dim testActions As DatabaseTestActions = Me.dbo_trgInsertDeleteTestData
'執行測試前指令碼
  System.Diagnostics.Trace.WriteLineIf((Not (testActions.PretestAction) Is Nothing), "正在執行 測試前 指令碼...")
  Dim pretestResults() As ExecutionResult = TestService.Execute(Me.PrivilegedContext, Me.PrivilegedContext, testActions.PretestAction)
'執行測試指令碼
  System.Diagnostics.Trace.WriteLineIf((Not (testActions.TestAction) Is Nothing), "正在執行 測試 指令碼...")
  Dim testResults() As ExecutionResult = TestService.Execute(Me.ExecutionContext, Me.PrivilegedContext, testActions.TestAction)
'執行測試後指令碼
  System.Diagnostics.Trace.WriteLineIf((Not (testActions.PosttestAction) Is Nothing), "正在執行 測試後 指令碼...")
  Dim posttestResults() As ExecutionResult = TestService.Execute(Me.PrivilegedContext, Me.PrivilegedContext, testActions.PosttestAction)
End Sub

<TestMethod()> _
Public Sub dbo_spInsertTestTest()
  Dim testActions As DatabaseTestActions = Me.dbo_spInsertTestTestData
'執行測試前指令碼
  System.Diagnostics.Trace.WriteLineIf((Not (testActions.PretestAction) Is Nothing), "正在執行 測試前 指令碼...")
  Dim pretestResults() As ExecutionResult = TestService.Execute(Me.PrivilegedContext, Me.PrivilegedContext, testActions.PretestAction)
'執行測試指令碼
  System.Diagnostics.Trace.WriteLineIf((Not (testActions.TestAction) Is Nothing), "正在執行 測試 指令碼...")
  Dim testResults() As ExecutionResult = TestService.Execute(Me.ExecutionContext, Me.PrivilegedContext, testActions.TestAction)
'執行測試後指令碼
  System.Diagnostics.Trace.WriteLineIf((Not (testActions.PosttestAction) Is Nothing), "正在執行 測試後 指令碼...")
  Dim posttestResults() As ExecutionResult = TestService.Execute(Me.PrivilegedContext, Me.PrivilegedContext, testActions.PosttestAction)
End Sub
=====<end>==============





=====程式6	<反灰>=============
DECLARE @CustID AS NVARCHAR (5);
SELECT @CustID = NULL;
SELECT *
FROM   [dbo].[GetOneYearDue] (@CustID);
=====<end>==============




=====<反灰>=============
IF @@ROWCOUNT = 0
	RAISERROR('GetOneYearDue alfki 受影響的紀錄筆數應該大於 0',16,1)
====<end>==============





=====<反灰>=============
select * from dbo.[Products by Category]
where CategoryName='Beverages'
IF @@ROWCOUNT < 11
  RAISERROR('Beverages 應該有 11 種以上的產品',16,0)
====<end>==============