how to get last inserted id in C#

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString))

                {

                    int newID;

                    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";

                    using (var insertCommand = new SqlCommand(cmd, con))

                    {

                        insertCommand.Parameters.AddWithValue("@Value", "bar");

                        con.Open();

                        newID = (int)insertCommand.ExecuteScalar();

                    }

                }