如何在 C# 中成功從 SQL Server 預存程序擷取輸出參數？

在 C# 中使用預存程序輸出參數

問題

將 SQL Server 預存程序的輸出參數傳回 C# 變數可能具有挑戰性。儘管遵循了各種建議，許多開發人員仍報告出現空值或其他問題。

預存程序

考慮以下預存程序 usp_InsertContract，它旨在將合約編號插入資料庫並將主鍵作為輸出參數傳回：

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),
    @NewId int OUTPUT
AS
BEGIN
    INSERT INTO [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber);
    Select @NewId = Id From [dbo].[Contracts] where ContractNumber = @ContractNumber;
END

開啟資料庫

使用以下程式碼開啟資料庫連線：

pvConnectionString = "Server = Desktop-PC\SQLEXPRESS; Database = PVDatabase; User ID = sa;
    PASSWORD = *******; Trusted_Connection = True;";

try
{
    pvConnection = new SqlConnection(pvConnectionString);
    pvConnection.Open();
}
catch (Exception e)
{
    databaseError = true;
}

執行指令

使用以下程式碼執行預存程序：

pvCommand = new SqlCommand("usp_InsertContract", pvConnection);

pvCommand.Transaction = pvTransaction;
pvCommand.CommandType = CommandType.StoredProcedure;

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));

SqlParameter pvNewId = new SqlParameter();
pvNewId.ParameterName = "@NewId";
pvNewId.DbType = DbType.Int32;
pvNewId.Direction = ParameterDirection.Output;
pvCommand.Parameters.Add(pvNewId);

try
{
    sqlRows = pvCommand.ExecuteNonQuery();

    if (sqlRows > 0)
        Debug.Print("New Id Inserted =  ", 
            pvCommand.Parameters["@NewId"].Value.ToString()); 
}
catch (Exception e)
{
    Debug.Print("Insert Exception Type: {0}", e.GetType());
    Debug.Print("  Message: {0}", e.Message);
}

解

使用 SCOPE_IDENTITY() 檢索主鍵，對預存程序進行少量修改，即可解決空值問題：

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),
    @NewId int OUTPUT
AS
BEGIN
    INSERT INTO [dbo].[Contracts] (ContractNumber)
    VALUES (@ContractNumber);
    SELECT @NewId = SCOPE_IDENTITY();
END

要在 C# 中使用此修改後的預存程序，請實作以下程式碼：

using(SqlConnection conn = new SqlConnection(pvConnectionString))
using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7).Value = contractNumber;
    cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
    conn.Open();
    cmd.ExecuteNonQuery();
    int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
    conn.Close();
}

此程式碼成功檢索並列印從預存程序傳回的主鍵。

以上是如何在 C# 中成功從 SQL Server 預存程序擷取輸出參數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！