在 C # 中使用存储过程输出参数

将 SqlServer 存储过程的输出参数返回到 C # 变量时出现问题。我已经阅读了有关这方面的其他帖子，不仅在这里，而且在其他网站，我不能让它的工作。这是我目前拥有的。目前我只是尝试打印返回的值。下面的代码返回空值。我想要返回的是主键。我曾尝试使用 @@IDENTITY和 SCOPE_INDENTITY()(即 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);
}
}
295379 次浏览
小开

Stored Procedure.........

CREATE PROCEDURE usp_InsertContract
@ContractNumber varchar(7)
AS
BEGIN


INSERT into [dbo].[Contracts] (ContractNumber)
VALUES (@ContractNumber)


SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
END

C#

pvCommand.CommandType = CommandType.StoredProcedure;


pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));
object uniqueId;
int id;
try
{
uniqueId = pvCommand.ExecuteScalar();
id = Convert.ToInt32(uniqueId);
}
catch (Exception e)
{
Debug.Print("  Message: {0}", e.Message);
}
}

EDIT: "I still get back a DBNull value....Object cannot be cast from DBNull to other types. I'll take this up again tomorrow. I'm off to my other job,"

I believe the Id column in your SQL Table isn't a identity column.

enter image description here

小开

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();
}

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....

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.

小开

Before changing stored procedure please check what is the output of your current one. In SQL Server Management run following:

DECLARE @NewId int
EXEC    @return_value = [dbo].[usp_InsertContract]
N'Gary',
@NewId OUTPUT
SELECT  @NewId

See what it returns. This may give you some hints of why your out param is not filled.

小开

In your C# code, you are using transaction for the command. Just commit the transaction and after that access your parameter value, you will get the value. Worked for me. :)

小开

I had a similar problem and first closed the connection and then read the parameters and it worked fine. you can use pvConnection.Close(); before read the output parameter 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();
pvConnection.Close();
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);
}
}

提交答案