0

I have a simple update statement which updates locations where it is a certain Location. I am currently making a small desktop app where When I press a button, it will update and then display the records that were updated. How can I return the Ids of the records that were updated and then display those records in C#. 

 SqlCommand cmd = new SqlCommand(" update conset set location='LA' where deal in (select deal from dealset where location='LA') and locationid = 'NY'", con);
                int rowsAffected = cmd.ExecuteNonQuery();

This however, only tells me the number of records that were updated not which ids were updated so I can query to display them. Help?

For example if 3 records were updated with the IDs: 1102, 1105, 111

Then it should display their Number based on the Id. However, I am unsure how I can do that. How would I loop through the updated results.

daniele3004
  • 13,072
  • 12
  • 67
  • 75
user2684009
  • 147
  • 1
  • 4
  • 19
  • 1
    Read up on the `OUTPUT` clause. It's not simple to use though and I'm not certain how difficult it is to get results from SqlCommand. – AaronLS Oct 08 '14 at 15:15
  • But what if for some reason that particular record does not get updated. The display will show it .. NO? – user2684009 Oct 08 '14 at 15:16
  • 1
    @MatthewMartin: wouldn't it be a race condition? I'd also use `OUTPUT`: http://stackoverflow.com/a/1610530/284240 – Tim Schmelter Oct 08 '14 at 15:16
  • @AaronLS The output clause did not work for me. I think it also locks those records. – user2684009 Oct 08 '14 at 15:17
  • 1
    @TimSchmelter Yes, would need both SELECT+UPDATE in a transaction if you wanted to be certain. – AaronLS Oct 08 '14 at 15:17
  • How can I do that? IF so How would I loop through the select statement to display those records? Sorry I'm a bit lost – user2684009 Oct 08 '14 at 15:19
  • Do you want to use a stored proc? Its much simpler in a stored proc using the OUTPUT clause. If yes I'll send you code snippets using the OUTPUT clause as specified by AaronLS – daveBM Oct 08 '14 at 15:21
  • @DaveBM wouldn't the storedprocedure need to be stored on the database. I'm trying to avoid storing any new stored procs on the database, as it has to go through bi-yearly upgrades which will wipe out any custom stored procs. – user2684009 Oct 08 '14 at 15:24
  • @user2684009 you going to be sacrificing a lot of performance improvements by not using stored procs. Why would a db upgrade wipe out a stored proc and not your tables? – daveBM Oct 08 '14 at 15:27
  • @MatthewMartin If they aren't run in a transaction there is the potential that your select and update will affect different records. Yes the select blocks, but once it is finished there is a tiny window of opportunity for a different client to insert/update records which [do not]meet the criteria of the update, and thus [not]get updated. This seems like it would be impossible because the time between the select and update would be tiny, but if you've ever been bitten by this bug, you will appreciate the importance of using a transaction here. – AaronLS Oct 08 '14 at 15:28
  • @DaveBM it is a vendor supported database. I don't exactly know why but it does. – user2684009 Oct 08 '14 at 15:31
  • @DaveBM what if it was a transaction, which selected the records that needed updating went through each record that was updated and then stored it into another tempt table, from which I can select and then simply delete the table? I'm so confused. – user2684009 Oct 08 '14 at 15:33
  • 1
    @AaronLS: have a look at [my answer](http://stackoverflow.com/a/26260856/284240), i've tested it, it should work. Note that `IdColumn` is the column that you want to retrieve. – Tim Schmelter Oct 08 '14 at 15:43
  • @daveBM: Stored procedures rarely offer a significant speed benefit over ad-hoc SQL any longer. – Moby Disk Oct 08 '14 at 15:47

1 Answers1

4

I'd use an OUTPUT clause and loop the DataReader:

string sql = @"
UPDATE c 
SET    c.location = 'LA' 
OUTPUT INSERTED.IdColumn
FROM conset c
WHERE  deal IN (SELECT deal 
                FROM   dealset 
                WHERE  location = 'LA') 
AND c.locationid = 'NY';";

List<int> updatedIDs = new List<int>();
using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
using (var cmd = new SqlCommand(sql, con))
{
    con.Open();
    using (var rd = cmd.ExecuteReader())
    {
        while (rd.Read())
        {
            updatedIDs.Add(rd.GetInt32(0));
        }
    }
}
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I am trying to modify this include two items Id, and DealName> But would this require a nested list. How would I add two items to the list?? – user2684009 Oct 08 '14 at 17:32
  • @user: you can use whatever you like, a Dictionary or a List> or a custom class with these properties(f.e. Conset) which instances you add to a List. – Tim Schmelter Oct 08 '14 at 17:51