How to Get the Inserted ID After an SQL Insert Using C# in MVC 4?

Retrieving the Newly Inserted ID Following an SQL Insert Operation in C# (MVC 4)

This article demonstrates how to insert data into a SQL Server table using C# within an MVC 4 application and simultaneously obtain the ID of the newly inserted record.

Code Example: Inserting Data and Retrieving the ID

The following code snippet illustrates how to achieve this using two different approaches:

Method 1: Using SCOPE_IDENTITY() (Suitable for SQL Server 2005 and later)

<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) 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();
            return newId;
        }
    }
}</code>

Method 2: Using the OUTPUT Clause (SQL Server 2005 and later)

This method is generally preferred for its clarity and efficiency.

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

    return newId;
}</code>

Both methods achieve the same result: inserting data and returning the newly generated ID. The OUTPUT clause is generally considered more efficient and readable. 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 SQL Insert Using C# in MVC 4?. For more information, please follow other related articles on the PHP Chinese website!