Build Update Query CommandText in foreach loop for bulksave to DB

IDGO 426 Reputation points
2022-12-24T06:59:50.93+00:00

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.

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,185 questions
Entity Framework 6.0
Entity Framework 6.0
A Microsoft open-source object-database mapper for .NET.
218 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,560 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
6,995 questions
{count} votes

Accepted answer
  1. Karen Payne MVP 29,236 Reputation points Microsoft MVP
    2022-12-25T11:15:09.987+00:00

    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);  
                    }  
                }  
            }  
        }  
      
    }  
    

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 68,106 Reputation points Microsoft MVP
    2022-12-24T13:21:07.307+00:00

    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.


  2. Erland Sommarskog 68,106 Reputation points Microsoft MVP
    2022-12-24T15:47:02.877+00:00

    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.

    No comments