如何用SqlConnection类的InfoMessage事件来显示Stored Procedure的PRINT讯息。
（華版）
我想很多程序员都会面对一个问题。。。就是如何调试（Debug）Stored Procedure；以下是我在研究ADO.NET时所得到的心得，在此跟大家分享以下。
只需在VB.NET或C#代码里，添加SqlConnect_InfoMessage事件，然后就从SqlInfoMessageEventArgs读取所有的PRINT/ERROR讯息就可以了 。
所返回的讯息种类：
Source = The class that return the information.
Error = T-SQL/Database error number.
Server = SQL error serverity (please refer to MSDN for detail information).
Line = The line number of the T-SQL that cause the output.
Message = Output/Error message.
Private Sub cn_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs) Handles cn.InfoMessage
'声明变量
Dim se As SqlError
With ListBox1
'显示讯息
.Items.Add("+ Message: " & e.Message)
.Items.Add(" + Source: " & e.Source)
'检查SqlErrorCollection是否空的
If e.Errors.Count > 0 Then
'读取每一个SqlError对象讯息
For Each se In e.Errors
.Items.Add(" ? Error: " & se.Number)
.Items.Add(" ? Level: " & se.Server)
.Items.Add(" ? Line: " & se.LineNumber)
.Items.Add(" ? Message: " & se.Message)
.Items.Add("**************************")
Next
End If
'选最后一个记录
.SelectedIndex = .Items.Count - 1
End With
End Sub
以下是Stored Procedure的代码，
CREATE PROCEDURE CustOrdersOrdersEx
(
@CustomerID nchar(5),
@Count int output
)
AS
-- DEBUG MESSAGE
PRINT 'DEBUG START'
PRINT '@CustomerID = ' + @CustomerID
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
-- DEBUG MESSAGE
PRINT @@ROWCOUNT
PRINT 'DEBUG END'
SET @Count = @@ROWCOUNT
SET QUOTED_IDENTIFIER OFF
GO
