How to Retrieve the ID of a Newly Inserted Record in SQL Server using C#?-Mysql Tutorial-php.cn

How to Retrieve the ID of a Newly Inserted Record in SQL Server using C#?

DDD

Release： 2025-01-17 01:31:10

Original

348 people have browsed it

How to Retrieve the ID of a Newly Inserted Record in SQL Server using C#?

Retrieving Newly Inserted Record IDs in SQL Server with C#

This guide demonstrates how to efficiently retrieve the unique identifier (ID) of a newly inserted record in an SQL Server database using C# within an MVC 4 application. The original code requires modification to achieve this.

Original Code (Inefficient):

<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)",con))
        {
            cmd.Parameters.AddWithValue("@na", Mem_NA);
            cmd.Parameters.AddWithValue("@occ", Mem_Occ);
            con.Open();

            int modified = cmd.ExecuteNonQuery();

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

Copy after login

Improved Code (with ID Retrieval):

The following examples show how to retrieve the newly generated ID. Choose the method appropriate for your SQL Server version:

SQL Server 2005 and later (Recommended):

This method uses the OUTPUT INSERTED.ID clause for optimal performance.

<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>

Copy after login

Older SQL Server Versions (Pre-2005):

This approach uses SCOPE_IDENTITY() which is less efficient but works on older versions.

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

Copy after login

Both revised methods employ ExecuteScalar to directly retrieve the generated ID. The SCOPE_IDENTITY() method should only be used if you are constrained to older SQL Server versions. For SQL Server 2005 and later, the OUTPUT INSERTED.ID method is significantly more efficient and recommended.

Important: Ensure your Mem_Basic table's ID column is defined as an IDENTITY column in your database schema.

The above is the detailed content of How to Retrieve the ID of a Newly Inserted Record in SQL Server using C#?. For more information, please follow other related articles on the PHP Chinese website!