1

Can anybody help me rewrite the code below so that I don't have to loop around the update command? In T-SQL, I would do:

UPDATE MyTable 
SET MyCol = 'MyVal' 
WHERE key IN (SELECT someKey FROM someOtherTable)

but I can't seem to get my head around this in C#. The someOtherTable is in memory if that is significant.

Thanks,

Palle

public static void UpdateDb()
{
    var myKeys = someOtherTable.AsEnumerable()
                               .Select(r => r.Field<int>("id")).ToList();

    const string updateTable = "UPDATE myTable SET myCol = 'myVal' WHERE id = @id";

    using (SqlConnection sqlConnection = new SqlConnection(SqlConnectionString))
    {
        sqlConnection.Open();

        foreach (int myId in myKeys)
        {
            SqlCommand cmd = new SqlCommand(updateTable, sqlConnection);
            cmd.Parameters.AddWithValue("@id", myId);

            cmd.ExecuteNonQuery());
        }

        sqlConnection.Close();
    }
}
pnowack
  • 101
  • 6
  • The only way to do it is with dynamic sql and join. but you probably don't want to do that because it can be hit by injection attacks. – Hogan Dec 16 '20 at 21:25
  • I'd rather not do that, Hogan. But actually, having edited my question to be a bit more precise (thanks - I meant avoiding repeating the update - not looping in general), I think "the other guy"'s answer was useable, but I didn't catch it before it was deleted :-) – pnowack Dec 16 '20 at 21:41
  • It's not clear why you don't just still run it in SQL? What's the need to get C# involved? – ADyson Dec 16 '20 at 21:44
  • I keep looking at what you have and do not see anything obvious at first. An extra ) after ExecuteNonQuery. Perhaps a datatype issue with myCol and an implicit int conversion? Do you get an error when running this? What is an example SQL call made in the cmd? – Jesse Dec 16 '20 at 21:50
  • @ADyson: it's a small part of bigger deal with timing issues, file i/o and stuff... And it just bugs me, that I can't figure it out :-) – pnowack Dec 16 '20 at 21:52
  • @Jesse: it works alright. It just feels wrong to do a table update 50, 500, 5000 times if it can be done in a single update. – pnowack Dec 16 '20 at 21:54
  • Oh gotcha :) yes, so maybe a table valued parameter and a stored procedure? I agree with you but I also think that is how C# does its thing. Or bulk insert the table from the app memory and update once it is in SQL Server? – Jesse Dec 16 '20 at 21:57
  • Yes, hmm...that's two other options, but I'm trying move my business rules / logic out of the SQL server, not the other way :-) Of course, if I could use EF or similar, the problem would vanish, but it's not "my" table... Anyway, thanks for all you input. I need to get some shut-eye... – pnowack Dec 16 '20 at 22:01
  • "if it can be done in a single update"...it can, using the original T-SQL code you posted above. Not sure what you mean about file I/O or timing...that shouldn't affect a SQL query especially...if you have an issue with that query, it would help to be more specific about the problem, rather than just moving straight onto workarounds. You can't really get around the loop / multiple-update problem if you attack it from the C# perspective, because C# (unlike SQL) doesn't deal in set-based operations. – ADyson Dec 16 '20 at 22:55
  • (I mean, you _could_ reconstruct a single UPDATE statement by looping over myKeys to generate a massive IN clause (appropriately parameterised of course), or use a table-valued parameter. But you basically just end up with a lot of effort and extra processing (and extra I/O between database and application) to create almost the same query you had in the first part of your post. I just cannot see any purpose at all to trying to do this outside SQL...again, if the original query has issues, try to fix those instead. – ADyson Dec 16 '20 at 23:00
  • Use Dapper - https://stackoverflow.com/a/8388333/34092 if less than 2100 values in the IN clause. If more than that, use a table valued parameter. – mjwills Dec 16 '20 at 23:22
  • 1
    UPDATE MyTable SET MyCol =@MyValue FROM MyTable INNER JOIN someOtherTable ON (someOtherTable.id=MyTable.key) – Shehab Dec 17 '20 at 08:44

0 Answers0