在 C# 中使用存储过程输出参数 [英] Using stored procedure output parameters in C#

问题描述

我在将 Sql Server 存储过程中的输出参数返回到 C# 变量时遇到问题.我已经阅读了与此相关的其他帖子，不仅在这里，而且在其他网站上，但我无法让它工作.这是我目前拥有的.目前我只是想打印返回的值.以下代码返回空值.我试图返回的是主键.我曾尝试使用 @@IDENTITY 和 SCOPE_INDENTITY()(即 SET @NewId = SCOPE_IDENTITY()).

I am having a problem returning an output parameter from a Sql Server stored procedure into a C# variable. I have read the other posts concerning this, not only here but on other sites, and I cannot get it to work. Here is what I currently have. Currently I am just trying to print the value that comes back. The following code returns a null value. What I an trying to return is the primary key. I have tried using @@IDENTITY and SCOPE_INDENTITY() (i.e. SET @NewId = SCOPE_IDENTITY()).

存储过程:

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)，它看起来像这样:

I slightly modified your stored procedure (to use SCOPE_IDENTITY) and it looks like this:

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

    SELECT @NewId = SCOPE_IDENTITY()
END

我试过了，效果很好(使用修改后的存储过程):

I tried this and it works just fine (with that modified stored procedure):

// define connection and command, in using blocks to ensure disposal
using(SqlConnection conn = new SqlConnection(pvConnectionString ))
using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
        
    // set up the parameters
    cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7);
    cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;

    // set parameter values
    cmd.Parameters["@ContractNumber"].Value = contractNumber;

    // open connection and execute stored procedure
    conn.Open();
    cmd.ExecuteNonQuery();

    // read output value from @NewId
    int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
    conn.Close();
}

这是否也适用于您的环境?我不能说为什么你的原始代码不起作用 - 但是当我在这里，VS2010 和 SQL Server 2008 R2 这样做时，它完美地工作......

Does this work in your environment, too? I can't say why your original code won't work - but when I do this here, VS2010 and SQL Server 2008 R2, it just works flawlessly....

如果您没有取回值 - 那么我怀疑您的表 Contracts 可能没有真正包含 IDENTITY 属性的列.

If you don't get back a value - then I suspect your table Contracts might not really have a column with the IDENTITY property on it.

这篇关于在 C# 中使用存储过程输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！