How to Retrieve the Inserted ID After an SQL INSERT Command in C# MVC 4?

Retrieving the Inserted ID After an SQL INSERT in C# MVC 4

This article demonstrates how to insert data using C# in an MVC 4 application and retrieve the ID of the newly inserted record. While ExecuteNonQuery indicates the number of rows affected, obtaining the inserted ID requires a different approach.

The most straightforward method utilizes ExecuteScalar. However, the result needs to be explicitly cast to an integer:

int modified = (int)cmd.ExecuteScalar();

After successful execution, modified will hold the newly generated ID.

For SQL Server 2005 and later, leveraging the OUTPUT keyword within the INSERT statement provides a more efficient solution:

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 modified = (int)cmd.ExecuteScalar();

    if (con.State == System.Data.ConnectionState.Open) con.Close();

    return modified;
}

For older SQL Server versions, the SCOPE_IDENTITY() function offers a comparable alternative:

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 modified = Convert.ToInt32(cmd.ExecuteScalar());

    if (con.State == System.Data.ConnectionState.Open)
        con.Close();

    return modified;
}

These methods ensure you accurately retrieve the ID of the inserted record, regardless of your SQL Server version. Remember to handle potential exceptions appropriately in a production environment.

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