Build Update Query CommandText in foreach loop for bulksave to DB

Question

Build Update Query CommandText in foreach loop for bulksave to DB

 foreach (var item in items)  
                        {  
                            
                            item.Locked = IsLocked;  
                            item.ModifiedBy = ModifiedBy;  
                            item.ModifiedDt = DateTime.Today;  
                        };       

item.SplitID = ID; is the Primary Key

From the above foreach loop I need to build a Update Query and later execute in Bulk in One shot.

Below is the example....Something Like this for Update

  var parts = new List<Part>();        
          parts.Add(new Part() { PartName = "crank arm", PartId = 1234 });  
          parts.Add(new Part() { PartName = "chain ring", PartId = 1334 });  
          parts.Add(new Part() { PartName = "regular seat", PartId = 1434 });  
          parts.Add(new Part() { PartName = "banana seat", PartId = 1444 });  
          parts.Add(new Part() { PartName = "cassette", PartId = 1534 });  
          parts.Add(new Part() { PartName = "shift lever", PartId = 1634 });  
          
          var ids = Enumerable.Range(1, parts.Count()).Select(x => "@id1");          
          var DeleteQuery = $"DELETE FROM tablename WHERE TestcolumnID IN ({string.Join(",",ids)})";  
              
         var command = new SqlCommand  
             {  
                 Connection = destinationConnection,  
                 CommandType = CommandType.Text,  
                 CommandText = DeleteQuery,  
                 Transaction = transaction as SqlTransaction  
             };  
              
         var count = 0;  
         parts.ForEach(p => {  
             ++count;  
             command.Parameters.AddWithValue($"@id{count}", p.PartId);  
         });  
      
         await command.ExecuteNonQueryAsync();  

Above is just an example for Reference.

Answer 1

You could use MERGE with just the UPDATE option.

The following comes from the following code sample done with .NET Framework 4.8 and will work with .NET Core also. Here we have four columns to keep things simple.

namespace BulkCopyierLibrary  
{  
    public class Exporter   
    {  
        public static (bool success, Exception exception) UpdateData(DataTable pDataTable)  
        {  
            string connectionString =  
                "Data Source=.\\sqlexpress;Initial Catalog=BulkCopyDatabaseCodeSample;Integrated Security=True";  
            using (var cn = new SqlConnection() { ConnectionString = connectionString })  
            {  
                using (var cmd = new SqlCommand("", cn))  
                {  
                    try  
                    {  
                        cn.Open();  
  
  
                        cmd.CommandText = @"CREATE TABLE #TmpPersonTable(  
                                [Id] [INT],  
                                [FirstName] [TEXT] NULL,  
                                [LastName] [TEXT] NULL,  
                                [Gender] [INT] NULL,[BirthDay] [DATETIME2](7) NULL)";  
  
                        cmd.ExecuteNonQuery();  
  
                        using (var bulkCopy = new SqlBulkCopy(cn))  
                        {  
                            bulkCopy.BulkCopyTimeout = 660;  
                            bulkCopy.DestinationTableName = "#TmpPersonTable";  
                            bulkCopy.WriteToServer(pDataTable);  
                            bulkCopy.Close();  
                        }  
  
                        cmd.CommandTimeout = 300;  
  
                        cmd.CommandText = @"  
                            MERGE INTO dbo.Person AS P  
                            USING dbo.#TmpPersonTable AS S  
                            ON P.Id = S.Id  
                            WHEN MATCHED THEN  
                                UPDATE SET P.FirstName = S.FirstName ,  
                                           P.LastName = S.LastName ,  
                                           P.Gender = S.Gender ,  
                                           P.BirthDay = S.BirthDay;  
                            DROP TABLE #TmpPersonTable";  
  
                        cmd.ExecuteNonQuery();  
  
                    }  
                    catch (Exception ex)  
                    {  
                        return (false, ex);  
                    }  
                    finally  
                    {  
                        cn.Close();  
                    }  
  
                    return (true, null);  
                }  
            }  
        }  
    }  
  
}  

Answer 2

Sample SQL code:

   CREATE TYPE MyData TABLE AS (  
        id           int NOT NULL PRIMARY KEY,  
        ThatCol varchar(23) NOT NULL,  
        ThisCol  datetime    NULL,  
        SomeMoreCol int    NULL  
   )  
   go  
   CREATE PROCEDURE UpdateSomeTbl @data MyData READONLY AS  
       UPDATE YourTabl  
       SET     ThatCol = d.ThatCol,  
               ThisCol  = d.ThisCol  
               SomeMoreCol = d.SomeMoreCol  
      FROM  YourTbl T  
      JOIN    @data d ON T.id = d.id  

Exactly you call it from C# depends on you have the data. If you already have the data in a DataSet (called ds here), it's dirt simple:

   cmd.CommandType = CommandType.StoredProcedure;  
   cmd.CommandText = "dbo.UpdateSomeTbl";  
   cmd.Parameters.Add("@data", SqlDbType.Structured);  
   cmd.Parameters["@data"].Direction = ParameterDirection.Input;  
   cmd.Parameters["@data"].TypeName = "dbo.MyData";  
   cmd.Parameters["@data"].Value = dt;  
   cmd.ExecuteNonQuery();  

If you don't have the data in a DataSet, you would not create one and write the data to it. Rather you would use a List<SqlDataRecord> as that is a more lean data type. It is also possible to pass an IEnuremable and stream the data to the database. This is very useful if you are for instance reading the data from a file.

Answer 3

DB On-Prem but webapp in Azure. From the above example can you help what should be done for Performance

So there is a distance between application and database. Why does that matter? Because for each call you make to the database, the application has to wait for response. And the data cannot travel faster than the speed of light. So the longer the distance, the slower it will go. (To that comes fixed overhead in firewalls and whatnots.)

So this is why you should loop and send one piece of data at the time, but you should sent all at once. As for how, I have already given you an outline.

If you want more details and examples, I have an article on my web site: Using Table-Valued Parameters in SQL Server and .NET.

I think it is a much better idea that you read and learn the method than I or someone else spoonfeed you solution for this particular problem. Because this will not be the last time you encounter this situation.