在 C# 中使用存储过程输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10905782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using stored procedure output parameters in C#
提问by Gary
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 @@IDENTITYand SCOPE_INDENTITY()(i.e. SET @NewId = SCOPE_IDENTITY()).
我在将 Sql Server 存储过程中的输出参数返回到 C# 变量时遇到问题。我已经阅读了与此相关的其他帖子，不仅在这里，而且在其他网站上，但我无法让它工作。这是我目前拥有的。目前我只是想打印返回的值。以下代码返回空值。我试图返回的是主键。我试过使用@@IDENTITY和SCOPE_INDENTITY()（即SET @NewId = SCOPE_IDENTITY()）。
Stored Procedure:
存储过程：
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
Opening the database:
打开数据库：
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;
}
Executing the command:
执行命令：
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);
}
}
回答by Jeremy Thompson
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#
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,"
编辑：“我仍然得到一个 DBNull 值......对象不能从 DBNull 转换为其他类型。我明天再考虑这个问题。我要去我的另一份工作了，”
I believe the Id column in your SQL Table isn't a identity column.
我相信您的 SQL 表中的 Id 列不是标识列。
回答by marc_s
I slightly modified your stored procedure (to use SCOPE_IDENTITY) and it looks like this:
我稍微修改了你的存储过程（使用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
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....
这是否也适用于您的环境？我不能说为什么你的原始代码不起作用 - 但是当我在这里，VS2010 和 SQL Server 2008 R2 这样做时，它只是完美地工作......
If you don't get back a value - then I suspect your table Contractsmight not really have a column with the IDENTITYproperty on it.
如果您没有取回值 - 那么我怀疑您的表Contracts可能没有真正包含该IDENTITY属性的列。
回答by TarasB
Before changing stored procedure please check what is the output of your current one. In SQL Server Management run following:
在更改存储过程之前，请检查当前存储过程的输出是什么。在 SQL Server 管理中运行以下命令：
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.
看看它返回什么。这可能会给您一些提示，说明为什么您的 out 参数未填写。
回答by Nitin
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. :)
在您的 C# 代码中，您将事务用于命令。只需提交事务，然后访问您的参数值，您将获得该值。为我工作。:)
