Hunterpo's IT Vision

歡迎一起分享 C#、T-SQL、ASP.NET 開發心得

2009-08-28

利用 ADO.NET 接收 SQL Server 預存程序的輸出資料

利用 ADO.NET 接收 SQL Server 預存程序的輸出資料

最近同事問我，如果要取得預存程序 (以下用 Proc 簡寫) 的傳回值應該怎麼做，由於以往撰寫的 Proc 大多是回傳資料集，操作方式跟直接下查詢取回資料集沒甚麼兩樣，但對於要取回傳回值 (或輸出參數) 這部分我只知道應該是怎麼做...，有鑑於知之為知之，不知為不知；若要人家知，除非己先知的原則，我決定好好研究一番 (其實是要給公司裡面的人範例，不如就整理成一篇文來發…摸蜆仔兼洗褲…)。

花個點時間研究後，大致上知道做法，難度雖不高，但有些技巧值得分享，在此提供給大家參考看看。Proc 的輸出可以有多種型態，因此利用 ADO.NET 接收時必須跟著因應，整理如下：
  • 資料集 -- 用 SqlDataAdapter 或 SqlDataReader 物件取回，前面說過了，這個跟從資料表或檢視查詢資料作法沒甚麼兩樣。
  • 輸出參數 -- 用 SqlCommand 物件逐一加入 SqlParameters 集合物件，所有輸出參數的 ParameterDirection 屬性必須指定為 Output (或 InputOutput) 型態。
  • 傳回值 -- 做法如同輸出參數，但在 ParameterDirection 屬性必須指定為 ReturnValue 型態。
  • 受影響資料列數 -- 一般做法是用 SqlCommand.ExecuteNonQuery 方法即可取得，但考慮到 Stored Proc 有可能下 SET NOCOUNT ON 陳述式關閉此輸出 (此時 ExecuteNonQuery 方法的回傳值永遠是 -1)，因此可以考慮在 Stored Proc 裡宣告足夠的變數去存放每一段 DML 陳述式執行後的 @@ROWCOUNT，再考慮用輸出參數的方式取回 (若是偏好傳回資料集也是可以)。
  • 警告或 PRINT 陳述式的輸出 -- 在 SQL Server 中，錯誤嚴重性層級 10 以下的視為警告訊息，且不會擲回例外狀況，還有，有時候為了除錯，我們會利用 PRINT 輸出訊息，這類的訊息可透過 SqlConnection 物件的 InfoMessage 事件處理，詳細可參考：ADO.NET - 使用連接事件。
以上條列出比較常用的情況，接下來我會用 Northwind 範例資料庫做示範，不過寫 Code 之前，先建立自訂程序 usp_InsertOrder ，等一下新增訂單時要用： 

	@CustomerID nchar(5),
	@ShipName nvarchar(30),
	@ShipAddress nvarchar(50),
	@ShipCity nvarchar(15),
	@ShipPostalCode varchar(5),
	@ShipCountry nvarchar(15),
	@IdentityID int = 0 OUTPUT,
	@AffectedRows int = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO Orders (
		CustomerID, 
		OrderDate, 
		RequiredDate, 
		ShipName, 
		ShipAddress, 
		ShipCity, 
		ShipPostalCode, 
		ShipCountry
	) 
	VALUES (
		@CustomerID, 
		GETDATE(), 
		GETDATE(), 
		@ShipName, 
		@ShipAddress, 
		@ShipCity, 
		@ShipPostalCode, 
		@ShipCountry
	);
	
	SET @AffectedRows = @@ROWCOUNT;
	SET @IdentityID = @@IDENTITY;
	
	IF (@IdentityID > 0) PRINT N'新增訂單成功';
	ELSE PRINT N'新增訂單失敗';
END

*注意：以上程序為了丟出回傳值、輸出參數、受影響資料列數、訊息，有許多不必要的內容，在實際情況下你很少需要這麼做。

可以撰寫程式碼了，由於要處理 PRINT 訊息，所以先宣告 SqlConnectin 並註冊 InfoMessage 事件處理常式： 

SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString1"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)
{
    // 註冊事件處裡常式
    cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);
}

void cn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    for (int i = 0; i < e.Errors.Count; i++)
    {
        litMsg.Text += String.Format("警告或 PRINT 訊息：{0}。<br />", e.Errors[i].Message);
    }
}

我在頁面上配置一個按鈕 (btnInsert) ，點選後會利用剛剛建立的自訂程序 usp_InsertOrder 新增 CustomerID 是 DRACD 的訂單 (為了簡化，除了 OrderDate、RequiredDate 以外，其他資料也都直接指定)，然後透過 Northwind 範例資料庫裡原本就有的程序 CustOrdersOrders 查出 DRACD 所有的訂單，並在 GridView1 顯示出來： 

{
    // 按下按鈕新增訂單，並帶出特定使用者所有訂單
    InsertOrder();
    SelectOrders();
}

/// <summary>
/// 新增訂單
/// </summary>
private void InsertOrder()
{
    try
    {
        using (cn)
        {
            using (SqlCommand cmd = new SqlCommand("dbo.usp_InsertOrder", cn))
            {
                // 設定查詢型態為預存程序
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                // 逐一加入輸入參數
                cmd.Parameters.Add("CustomerID", System.Data.SqlDbType.NChar, 5).Value = "DRACD";
                cmd.Parameters.Add("ShipName", System.Data.SqlDbType.NVarChar, 30).Value = "Hunterpo";
                cmd.Parameters.Add("ShipAddress", System.Data.SqlDbType.NVarChar, 50).Value = "NO.12345, Global Village";
                cmd.Parameters.Add("ShipCity", System.Data.SqlDbType.NVarChar, 15).Value = "TAIPEI";
                cmd.Parameters.Add("ShipPostalCode", System.Data.SqlDbType.VarChar, 5).Value = "54321";

                cmd.Parameters.Add("ShipCountry", System.Data.SqlDbType.NVarChar, 15).Value = "TAIWAN, R.O.C.";

                // 宣告參數接收傳回值
                SqlParameter rc = new SqlParameter("RC", System.Data.SqlDbType.Int);
                rc.Direction = System.Data.ParameterDirection.ReturnValue;

                cmd.Parameters.Add(rc);

                // 宣告並加入輸出參數
                SqlParameter identityID = new SqlParameter("IdentityID", System.Data.SqlDbType.Int);
                identityID.Direction = System.Data.ParameterDirection.Output;

                SqlParameter affectedRows = new SqlParameter("AffectedRows", System.Data.SqlDbType.Int);
                affectedRows.Direction = System.Data.ParameterDirection.Output;

                cmd.Parameters.Add(identityID);
                cmd.Parameters.Add(affectedRows);

                cn.Open();

                // 受影響記錄筆數，用以凸顯 SET NOCOUNT ON 陳述式的影響
                int affectedRecords = cmd.ExecuteNonQuery();

                // 丟出最新識別值訊息
                ClientScript.RegisterStartupScript(
                    GetType(),
                    "Notify",
                    String.Format("alert('受影響資料列數：{0} (Output 參數), \\r受影響記錄筆數：{1} (DbCommand.ExecuteNonQuery 回傳值), \\rProc 傳回值：{2} (Return Value), \\r最新 Order ID：{3} (Output 參數)。');",
                        affectedRows.Value,
                        affectedRecords,
                        rc.Value,
                        identityID.Value),
                    true);
            }
        }
    }
    catch (Exception e)
    {
        Response.Write(String.Format("<span style='color: #ff0000;'>發生例外：{0}。</span>", e.Message));
    }
}

/// <summary>
/// 查詢訂單
/// </summary>
private void SelectOrders()
{
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString1"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("dbo.CustOrdersOrders", cn))
        {
            // 設定查詢型態為預存程序
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // 逐一加入輸入參數
            cmd.Parameters.Add("CustomerID", System.Data.SqlDbType.NChar, 5).Value = "DRACD";

            cn.Open();

            using (DataTable dt = new DataTable("Orders"))
            {
                dt.Load(cmd.ExecuteReader());

                if (dt.Rows.Count > 0)
                {
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

執行結果如下圖，一次搞定前面五種狀況：
StoredProcOutput

再強調一次，在 usp_InsertOrder 自訂程序裡，有加 SET NOCOUNT ON 陳述式，所以 SqlCommand.ExecuteNonQuery 方法的回傳值永遠是 -1。

完整程式碼範例可以在此下載 (解壓縮後，用 Visual Studio 以開啟網站方式開啟就可以 Run)：StoredProcOutput.rar

PS. 話說發文前我都會搜尋一下，怕有人發過相同主題，結果還真的有人發過同主題… (其中一篇還是站內的夥伴寫的 XD)，好在詳細看了一下，我這篇有多寫一些些東西，阿不然可能要砍掉重練了…。我把找到的文章一併列出，大家可以互相參考觀摩一下：
參考資料：

系列文章

標籤雲