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

Retrieving the ID of a Newly Inserted SQL Record in C# MVC 4

Inserting data into a SQL Server table using C# MVC 4 requires a mechanism to obtain the ID of the newly added row. While cmd.ExecuteNonQuery() indicates the number of rows affected, it doesn't return the generated ID.

Method for SQL Server 2005 and Later:

The most efficient approach leverages the OUTPUT clause:

<code class="language-csharp">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();

    if (con.State == System.Data.ConnectionState.Open) con.Close();
}</code>

Alternative for Older SQL Server Versions:

For SQL Server versions prior to 2005, use SCOPE_IDENTITY():

<code class="language-csharp">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());

    if (con.State == System.Data.ConnectionState.Open) con.Close();
}</code>

These code snippets demonstrate how to retrieve the newly generated ID after an INSERT operation, ensuring proper data handling in your C# MVC 4 application. Remember to replace Mem_Basic, Mem_Na, and Mem_Occ with your actual table and column names.

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