SQL Server和C＃：获取最后插入的id

public static void CreateSocialGroup(string FBUID)
{
    string query = "INSERT INTO SocialGroup (created_by_fbuid) VALUES (@FBUID); SELECT @@IDENTITY AS LastID";

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@FBUID", FBUID);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

Is this the right way to do it? And how do i get LastID in to a variable? Thanks

这是正确的方法吗？我如何将LastID输入变量？谢谢

8 个解决方案

#1

OUTPUT clause?

OUTPUT子句？

string query = "INSERT INTO SocialGroup (created_by_fbuid) 
                OUTPUT INSERTED.IDCol  --use real column here
                VALUES (@FBUID)";
...
int lastId = (int)command.ExecuteScalar();

#2

You can use ExecuteScalar to get the last value from a Sqlcommand.

您可以使用ExecuteScalar从Sqlcommand获取最后一个值。

The scope_identity() function is safer than @@identity.

scope_identity（）函数比@@ identity更安全。

#3

If your server supports the OUTPUT clause you could try it with this one:

如果您的服务器支持OUTPUT子句，您可以尝试使用此子句：

public static void CreateSocialGroup(string FBUID)
{
    string query = "INSERT INTO SocialGroup (created_by_fbuid) OUTPUT INSERTED.IDENTITYCOL VALUES (@FBUID)";

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@FBUID", FBUID);

        connection.Open();
        var _id = command.ExecuteScalar();
    }
}

#4

Personally, I would re-write your code to use Parameters. You could either use an InputOutput parameter or an Output Parameter. However, using a Return Value in your SQL would also work.

就个人而言，我会重新编写您的代码以使用参数。您可以使用InputOutput参数或输出参数。但是，在SQL中使用返回值也可以。

Full examples on this can be found on MSDN.

有关这方面的完整示例可以在MSDN上找到。

I would also use Scope_Identity() rather than @@Identity this will ensure that you will reveice the ID that relates to the current transaction. Details on Scope_Identity can be found here.

我也会使用Scope_Identity（）而不是@@ Identity，这将确保您将显示与当前事务相关的ID。有关Scope_Identity的详细信息，请访问此处。

#5

U can try ExecuteScalar for getting the LastID value.

您可以尝试使用ExecuteScalar获取LastID值。

#6

I'd recommend to use a stored procedure to do this. You can give it an OUTPUT parameter which you can use to return the id value back to your app.

我建议使用存储过程来执行此操作。您可以给它一个OUTPUT参数，您可以使用该参数将id值返回给您的应用程序。

#7

-3

Use Stored Procedure only for the queries and use SCOPE_IDENTITY to get max value.

仅对查询使用存储过程，并使用SCOPE_IDENTITY获取最大值。

#8

-16

SqlCommand command = new SqlCommand("select max(id) from SocialGroup ", connection);
int lastId = (int)command.ExecuteScalar();

#1