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

Retrieving Auto-Generated IDs After SQL INSERT in C#

When adding data to a SQL Server table via C#, obtaining the automatically generated ID of the new entry is a common requirement. This tutorial demonstrates two methods: using the OUTPUT clause (SQL Server 2005 and later) and the SCOPE_IDENTITY() function (older versions).

Method 1: OUTPUT Clause (SQL Server 2005 and later)

The OUTPUT clause provides a direct way to specify the column containing the ID:

<code class="language-csharp">using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) OUTPUT INSERTED.ID VALUES(@na,@occ)", con))</code>

Here, INSERTED.ID in the OUTPUT clause returns the ID of the newly inserted row.

Method 2: SCOPE_IDENTITY() Function (Older SQL Server Versions)

For older SQL Server versions, SCOPE_IDENTITY() retrieves the ID of the most recently inserted row within the current scope:

<code class="language-csharp">using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) VALUES(@na,@occ); SELECT SCOPE_IDENTITY();", con))</code>

This query inserts the data and then uses SCOPE_IDENTITY() in a separate SELECT statement to get the ID.

Accessing the ID

In both methods, the generated ID is accessed using ExecuteScalar() and type casting:

<code class="language-csharp">int newId = (int)cmd.ExecuteScalar();</code>

This technique ensures efficient retrieval of the newly inserted record's ID, offering compatibility across different SQL Server versions.

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