The Will Will Web

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

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

若使用 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 的值表示失敗。(除非文件另有說明)

相關連結