The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

ADO.NET 如何取得 Stored Procedure 的回傳值

📅 2008/12/09 11:02 📁 .NET Framework, ASP.NET, C#

若使用 ADO.NET 連接資料庫並呼叫預儲程序(Stored Procedure)的話，基本上有三種方式可以取得執行後的結果，分別如下：

  1. 在預儲程序中最後一行直接用 SELECT 語法回傳表格資料
  2. 在預儲程序中使用輸出參數(Output Parameter)的方式回傳資料
  3. 在預儲程序中使用 RETURN 語法回傳一個整數型別(Int32)的狀態碼

若是呼叫的預儲程序不需要回傳值，基本上可以用以下程式執行，也是蠻標準的寫法：

SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_MyStoredProcedure", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DataID",SqlDbType.VarChar, 15);                
cmd.Parameters["@DataID"].Value = "XXXXX";

try
{
    db.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}

如果在預儲程序回傳的是一個表格資料型態，就可以將 ExecuteNonQuery() 改成 ExecuteReader() 方法，取回的 SqlDataReader 物件就可以直接針對回傳的資料表進行存取。

若回傳的是一個單一值(Scalar Value)，也可以用 ExecuteScalar() 方法直接取得回傳值。有個比較常見的使用情況是當在預儲程序中新增資料時，要直接回傳新增資料的 ID 值(Last Inserted ID)，你可以直接用 SELECT 語法直接回傳 SCOPE_IDENTITY() 的資料，如下預儲程序範例：

CREATE PROCEDURE sp_AddNewItem
(
    @Name    nvarchar(50)
)
AS

INSERT INTO [dbo].[Category] (Name) VALUES (@Name)

SELECT SCOPE_IDENTITY()

程式碼範例如下：

SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_AddNewItem", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Name",SqlDbType.NVarChar, 50);
cmd.Parameters["@Name"].Value = "Category 1";

try
{
    db.Open();
    int LastInsertedID = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}

如果用輸出參數(Output Parameter)的方式回傳資料，那只要多定義一個參數，並指定 Direction 屬性為 ParameterDirection.Output 即可在執行 ExecuteNonQuery() 方法後得到回傳的資料，如下範例：

SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_MyStoredProcedure", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DataID",SqlDbType.VarChar, 15);                
cmd.Parameters["@DataID"].Value = "XXXXX";

SqlParameter retValParam = cmd.Parameters.Add("@OutputData", SqlDbType.VarChar, 250);
retValParam.Direction = ParameterDirection.Output;

try
{
    db.Open();
    cmd.ExecuteNonQuery();
    Console.Write("取得的輸出資料: " + retValParam.Value);
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}

最後一種，若你的預儲程序僅需要回傳整數型別的資料，可以在預儲程序中直接用 RETURN 語法回傳，而要在 C# 中取得回傳值，用法類似輸出參數(Output Parameter)的方式，只要把要取得的參數 Direction 屬性為 ParameterDirection.ReturnValue 即可，如下範例：

SqlConnection db = new SqlConnection(connStr);

SqlCommand cmd = new SqlCommand("sp_MyStoredProcedure", db);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DataID",SqlDbType.VarChar, 15);                
cmd.Parameters["@DataID"].Value = "XXXXX";

SqlParameter retValParam = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.VarChar, 250);
retValParam.Direction = ParameterDirection.ReturnValue;

try
{
    db.Open();
    cmd.ExecuteNonQuery();
    Console.Write("取得的輸出資料: " + retValParam.Value);
}
catch (Exception ex)
{
    throw ex.GetBaseException();
}
finally
{
    db.Close();
}

備註：所有系統預存程序若執行成功都會回傳 0，若得到非 0 的值表示失敗。（除非文件另有說明）

相關連結

留言評論