LL Janneh LL Janneh - 1 year ago 78
SQL Question

how to get the ID of the record that is update in C#

I am able to get a sql query that can return the ID of a record that is update in the Database; that ID is needed to update other records.

public int updateCases(int id,string fname)
string query="update bio_data set firstName=@fname OUTPUT INSERTED.ID where id=@ID";
int IDValue = 0;
SqlCommand cmd = new SqlCommand(query, con);

IDValue = Convert.ToInt32(cmd.ExecuteNonQuery()); // this is incorrect because it always return one
if (IDValue <= 0)
return 0;
return IDValue;
catch (Exception ex)
error.Text = "An Error Occur while update a case Bio_data!!" + ex.Message;
error.ForeColor = Color.Red;
return 0;

but I don't know how to get that updated ID value in C#
Can someone please help me; thanks in advanced!!

Answer Source

I think you're really asking how do you retrieve the resultset returned by an INSERT or UPDATE with an OUTPUT clause. You use ExecuteReader instead of ExecuteNonQuery

Here's simplified example:

Create a test table

SELECT * INTO Test FROM (VALUES (1, 'ABC'),(2,'DEF'),(3,'ABC')) A(Id, Val)

C# code

Update where Val is 'ABC' returning the rows updated and print them (excuse the suspect error handling etc)

var conString = ...;
var query = "UPDATE TEST SET Val ='XYZ' OUTPUT INSERTED.* WHERE Val = @Val";

using (SqlConnection con = new SqlConnection(conString))

    using (SqlCommand cmd = new SqlCommand(query, con))
        cmd.Parameters.AddWithValue("@Val", "ABC");

        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                Console.WriteLine(reader["id"] + " " + reader["Val"]);



And SELECT * FROM Test now shows

Id          Val
----------- ----
1           XYZ
2           DEF
3           XYZ

Hope this helps. You should be aware that this approach can have issues with triggers and raising/handling of SQL errors.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download