Reputation: 180
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;
try
{
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@ID",id);
cmd.Parameters.AddWithValue("@fname",fname);
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;
}
finally
{
con.Close();
}
but I don't know how to get that updated ID value in C#.
Upvotes: 2
Views: 4268
Reputation: 2979
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:
SELECT * INTO Test FROM (VALUES (1, 'ABC'),(2,'DEF'),(3,'ABC')) A(Id, Val)
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))
{
con.Open();
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"]);
}
}
}
Prints
1 XYZ
3 XYZ
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.
Upvotes: 4