How to Get the Inserted ID After an INSERT Command in SQL Server?

Retrieving the Newly Inserted ID in SQL Server from C#

This article demonstrates how to insert data into a SQL Server table using C# in an MVC 4 application and subsequently retrieve the ID of the newly added record. We'll explore methods for SQL Server 2005 and later, and for versions prior to 2005.

Method 1: SQL Server 2005 and Later (Using OUTPUT INSERTED.ID)

This approach leverages the OUTPUT clause, a highly efficient method for retrieving the inserted ID directly within the insert statement itself.

<code class="language-csharp">public class MemberBasicData
{
    public int Id { get; set; }
    public string Mem_NA { get; set; }
    public string Mem_Occ { get; set; }     
}

public int CreateNewMember(string Mem_NA, string Mem_Occ)
{
    using (SqlConnection con = new SqlConnection(Config.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic (Mem_Na, Mem_Occ) OUTPUT INSERTED.ID VALUES (@na, @occ)", con))
        {
            cmd.Parameters.AddWithValue("@na", Mem_NA);
            cmd.Parameters.AddWithValue("@occ", Mem_Occ);
            con.Open();
            int newId = (int)cmd.ExecuteScalar();
            con.Close();
            return newId;
        }
    }
}</code>

Method 2: SQL Server Versions Before 2005 (Using SCOPE_IDENTITY())

For older SQL Server versions, the SCOPE_IDENTITY() function is used to retrieve the last identity value generated within the current scope.

<code class="language-csharp">public int CreateNewMember(string Mem_NA, string Mem_Occ)
{
    using (SqlConnection con = new SqlConnection(Config.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic (Mem_Na, Mem_Occ) VALUES (@na, @occ); SELECT SCOPE_IDENTITY();", con))
        {
            cmd.Parameters.AddWithValue("@na", Mem_NA);
            cmd.Parameters.AddWithValue("@occ", Mem_Occ);
            con.Open();
            int newId = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return newId;
        }
    }
}</code>

Both methods achieve the same result: returning the ID of the newly inserted record. The choice depends on your SQL Server version. The OUTPUT clause is generally preferred for its efficiency and clarity in newer versions. Remember to replace "Config.ConnectionString" with your actual connection string.

The above is the detailed content of How to Get the Inserted ID After an INSERT Command in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!