parameterizing all JOIN data in a large UPDATE operation

Question

I have an app that makes a bunch of updates to objects hydrated with data from an SQL Server table and then writes the updates objects' data back to the DB in one query. I'm trying to convert this into a parameterized query so that I don't have to do manual escaping, conversions, etc.

Here's the most straightforward example query:

UPDATE TestTable
SET [Status] = DataToUpdate.[Status], City = DataToUpdate.City
FROM TestTable
JOIN
(
    VALUES --this is the data to parameterize
    (1, 0, 'A City'),
    (2, 0, 'Another City')
) AS DataToUpdate(Id, [Status], City) 
ON DataToUpdate.Id = TestTable.Id

I've also played around with using OPENXML to do this, but I'm still forced to write a bunch of escaping code when adding the values to the query. Any ideas on how to make this more elegant? I am open to ADO.NET/T-SQL solutions or platform-agnostic solutions.

One thought I had (but I don't really like how dynamic this is) is to dynamically create parameters and then add them to an ADO.NET SqlConnection, e.g.

for(int i = 0; i < data.Length; i++)
{
    string paramPrefix = string.Format("@Item{0}", i);
    valuesString.AppendFormat("{0}({1}Status)", Environment.NewLine, paramPrefix);
    var statusParam = new SqlParameter(
        string.Format("{0}Status", paramPrefix),
        System.Data.SqlDbType.Int) 
        { Value = data[i].Status };

    command.Parameters.Add(statusParam);
}

ShinyCy · Answer 1 · 2015-07-29T15:58:36.430

I'm not exactly sure how you store your application data (and I don't have enough rep points to post comments) so I will ASSUME that the records are held in an object CityAndStatus which is comprised of int Id, string Status, string City held in a List<CityAndStatus> called data. That way you can deal with each record one at a time. I made Status a string so you can convert it to an int in your application.
With those assumptions:

I would create a stored procedure https://msdn.microsoft.com/en-us/library/ms345415.aspx in SQL Server that updates your table one record at at time.

CREATE PROCEDURE updateCityData (
    @Id INT
    ,@Status INT
    ,@City VARCHAR(50)
    )
AS
BEGIN TRAN

UPDATE TestTable
SET [Status] = @Status
    ,City = @City
WHERE Id = @Id

COMMIT

RETURN
GO

Then I would call the stored procedure https://support.microsoft.com/en-us/kb/310070 from your ADO.NET application inside a foreach loop that goes through each record that you need to update.

   SqlConnection cn = new SqlConnection(connectionString);
   cn.Open();    
   foreach (CityAndStatus item in data)
   {
    SqlCommand cmd = new SqlCommand("updateCityData",cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Id", item.Id);
    cmd.Parameters.AddWithValue("@Status", Convert.ToInt32(item.Status));
    cmd.Parameters.AddWithValue("@City", item.User);
    cmd.ExecuteNonQuery();
    cmd.Dispose();
   }
   cn.Close();

After that you should be good. The one thing left that might stand in your way is SQL Server makes web application users grant permission to execute stored procedures. So in SQL Server you may have to do something like this to allow your application to fire the stored proc.

GRANT EXECUTE
    ON updateCityData 
    TO whateverRoleYouHaveGivenPermissionToExecuteStoredProcedures

Good Luck

Your assumptions are accurate. The main difference here is that I am calling one query as opposed to one query per record. So this would work and I'm considering making it easier on myself by doing this, but I need to make sure I can take the performance hit by doing this. — moarboilerplate, Jul 29 '15 at 15:54
It is generally more intensive to push DataTable into SQL Server and deal with it as a set than to deal with each record one at at time. But if you go here http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure it might be closer to what you are looking for. — ShinyCy, Jul 29 '15 at 16:04

parameterizing all JOIN data in a large UPDATE operation

1 Answers1