SQL Server and C#: get last inserted 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 i开发者_StackOverflow中文版t? And how do i get LastID in to a variable? Thanks

OUTPUT clause?

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

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

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

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

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

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.

Full examples on this can be found on 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.

U can try ExecuteScalar for getting the LastID value.

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.

cmd = new SqlCommand("Insert into table values (1,2,3); SELECT SCOPE_IDENTITY()", conn);

lastRecord = cmd.ExecuteScalar().ToString();

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

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