T-SQL:返回新插入的身份到C# [英] T-SQL: returning the new INSERT identity to C#
问题描述
我使用一个存储过程把值到SQL Server。该过程将一个ID添加到被添加的行。我需要得到这个ID回到我的代码。
目前我能得到我看到在Visual Studio中的输出窗口输出标识,但似乎无法捕捉它在我的代码。这里是PROC的摘要版本:
SQL
CREATE PROCEDURE dbo.DoSomething
(
@ VAR1 INT = NULL,
@ VAR2 INT = NULL,
@ VAR3日期= NULL
)
AS
BEGIN
INSERT INTO atable
(
翻,
varb,
VARC
)
值
(
@ VAR1,
@ VAR2,
@ VAR3
)
返回SCOPE_IDENTITY()
端
C#:
INT结果= 0;
///创建命令
的SqlCommand CMD =新的SqlCommand(DoSomething的this.OpenSqlConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(@ VAR1,thing.value1);
cmd.Parameters.AddWithValue(@ VAR2,thing.value2);
cmd.Parameters.AddWithValue(@ VAR3,thing.value3);
///将数据发送到DB
结果=(INT)cmd.ExecuteScalar();
所以我得到一个错误:对象引用不设置到对象的实例当它到达的(INT)cmd.ExecuteScalar()。
任何想法?
...
选择SCOPE_IDENTITY()
返回值实际上是回来作为一个特殊的输出参数。 SELECT给出的ExecuteScalar结果集。
您可以使用的输出条款,而不是一个单独的选择太多:
...
AS
BEGIN
INSERT INTO atable
(
翻,
varb,
VARC
)
输出INSERTED.IDCol
值
(
@ VAR1,
@ VAR2,
@ VAR3
)
端
GO
这将为多行工作过。
i'm putting values into SQL Server using a Stored Procedure. The Procedure will add an ID to the row that is added. I need to get this ID back to my code.
Currently I can get the I see the output id in the OUTPUT window of Visual Studio, but can't seem to capture it in my code. Here is a summarized version of the proc:
SQL:
CREATE PROCEDURE dbo.DoSomething
(
@var1 INT = NULL,
@var2 INT = NULL,
@var3 DATE = NULL
)
AS
BEGIN
INSERT INTO atable
(
vara,
varb,
varc
)
VALUES
(
@var1,
@var2,
@var3
)
RETURN SCOPE_IDENTITY()
END
C#:
int result = 0;
/// create command
SqlCommand cmd = new SqlCommand("DoSomething", this.OpenSqlConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@var1", thing.value1);
cmd.Parameters.AddWithValue("@var2", thing.value2);
cmd.Parameters.AddWithValue("@var3", thing.value3);
/// send data to db
result = (int)cmd.ExecuteScalar();
So I'm getting an error: Object reference not set to an instance of an object. when it gets to the (int)cmd.ExecuteScalar().
Any ideas?
...
SELECT SCOPE_IDENTITY()
The RETURN value actually comes back as an special OUTPUT parameter. SELECT gives a result set for ExecuteScalar.
You can use the OUTPUT clause instead of a separate SELECT too:
...
AS
BEGIN
INSERT INTO atable
(
vara,
varb,
varc
)
OUTPUT INSERTED.IDCol
VALUES
(
@var1,
@var2,
@var3
)
END
GO
This will work for multiple rows too.
这篇关于T-SQL:返回新插入的身份到C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!