#眉標=VS2010 Ultimate、SQL Server、T-SQL
#副標=Visual Studio 2010資料庫建構(5)
#大標=對資料庫進行負載測試
#作者=文／圖 胡百敬


=====程式1	<box>===========

waitfor delay '00:00:01'

select * from (values(1,1,'a')
,(2,2,'b')
,(3,3,'c')
,(4,4,'d')
)t(c1,c2,c3)

select * from (values(1,1)
,(2,2)
)t(c1,c2)

--丟出嚴重等級10以上的錯誤，否則只認定為訊息，不算錯誤
--raiserror('執行結果不合預期',16,1)  
====<end>==============




=====<反灰>===========

select * from (values(1,'1')
,(2,'2')
)t(c1,c2)

====<end>==============





=====	<box>===========
SELECT 'SELECT * FROM [' + Name + '] 
GO' FROM sys.objects where type IN ('u','v')

====<end>==============




=====<反灰>===========
Imports System.Data.SqlClient

====<end>==============





=====程式2	<box>===========
Dim conn As New SqlConnection()

<DataSource("System.Data.SqlClient", "Data Source=.;Initial Catalog=Northwind;Integrated Security=True", "tbTrace", DataAccessMethod.Sequential)> <TestMethod()>
Public Sub TestMethod1()

    '測試目標資料庫的連接，因為用多條 thread 執行負載測試
    '會造成 connection 超過 max pool 的錯誤，
    '故增大 Max Pool Size 到 300
    conn.ConnectionString = _
        "Data Source=byroni7;Initial Catalog=northwind;Integrated Security=SSPI;Max Pool Size=300"

    If IsDBNull(TestContext.DataRow("TextData")) Then Exit Sub

    '模擬使用者應用程式存取資料庫，結果儘量不要拿到前端，
    '否則壓力測試程式自己掛了。但這種測法沒有壓到網路
    Dim strSQL As String = "select * into #tmp from (" & _
        Me.TestContext.DataRow("TextData").ToString() & ") a;select @@rowcount"
    Dim cmd As New SqlCommand(strSQL, conn)

    Try
        conn.Open()
        Dim iRes As Long = cmd.ExecuteScalar()
        '驗證測試執行結果
        Assert.AreEqual(TestContext.DataRow("RowCounts"), iRes)
    Catch ex As SqlException
        System.Diagnostics.Debug.WriteLine(ex.Message & vbCrLf & strSQL)
        '錯誤依然要觸發，讓測試失敗
        Throw (ex)
    Finally
        conn.Close()
    End Try
End Sub

====<end>==============




=====<box>===========
<DataSource("System.Data.SqlClient", "Data Source=.;Initial Catalog=Northwind;Integrated Security=True", "tbTrace", DataAccessMethod.Sequential)>

====<end>==============