插入记录后如何从SQL Server获取Identity值

I add a record in my database with an identity value. I want to get the identity value after inserting. I don't want to do that by stored procedure.

我在数据库中添加了一个带有标识值的记录。我希望在插入后获得标识值。我不想通过存储过程来做到这一点。

This is my code:

这是我的代码：

SQLString = " INSERT INTO myTable ";
SQLString += " (Cal1, Cal2, Cal3, Cal4) ";
SQLString += " VALUES(N'{0}',N'{1}',N'{2}',N'{3}') ";
SQLString = string.Format(SQLString, Val1, Val2, Val3, Val4);
SQLString += " Declare @_IdentityCode INT SET @_IdentityCode = @@IDENTITY RETURN @_IdentityCode";

int result;

public int DoCommand2(string sql)
{
        con = new SqlConnection();
        cmd = new SqlCommand();
        da = new SqlDataAdapter();
        cmd.Connection = con;
        da.SelectCommand = cmd;

        string cs = GlobalConstants.SqlConnectionString;
        con.ConnectionString = cs;

        cmd.CommandText = sql;
        int i = cmd.ExecuteNonQuery();
        return i;
}

but I get this error:

但我得到这个错误：

A RETURN statement with a return value cannot be used in this context.

带有返回值的RETURN语句不能在此上下文中使用。

3 个解决方案

#1

Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:

附加SELECT SCOPE_IDENTITY（）;到您正常的INSERT语句：

Replace the last concatenation with:

将最后一个连接替换为：

SQLString += "; SELECT SCOPE_IDENTITY();"

Then to retrieve the ID:

然后检索ID：

int ID = Convert.ToInt32(command.ExecuteScalar());

#2

Take a good look at the OUTPUT clause. You can output your inserted ID from the INSERT statement. There are examples in the link I posted.

仔细看看OUTPUT子句。您可以从INSERT语句输出插入的ID。我发布的链接中有一些示例。

#3

... or just run

......或者只是跑步

select @@identity

#1