Declare a variable per thread and manage them in c# Parallel.Foreach

Question

I'm writing a code to iterate through a huge list of objects in order to persist them in DB.

I have a counter wich is increasing with each iteration and when it reaches 500 I persist all in DB (from a StringBuilder).

If I do it sequential I have no problems, but it takes a long time. That's why I want to do it with parallelism.

With parallelism I've seen I cannot have one StringBuilder for all threads, so I need to create one for each thread.

My questions are: How can I create a StringBuilder per thread? And then, How can I persist all StringBuilder objects to DB and empty them all when the static counter reaches 500 loops?

I have the following code:

int counter = 0;

Parallel.ForEach(myList, element =>
{
    lock (balanceLock)
    {
        counter++;
    }

    var sb = new StringBuilder(); //I need one StringBuilder for thread, not for iteration
    
    ...
    
    if (decimal.Remainder(counter, 500) == 0)
        lock (balanceLock)
        {
            persistInDB(sb.toString());
            sb.Clear();
        }
});

Answer 1

Finally I solved it in a sequential way, by putting the records in a table and making a SqlBulkCopy against the DB, following the advices.

DataTable table = getDataTable();

myList.ForEach(element => {
    
    //...
    
    table.Rows.Add(getRow(element));

    if (decimal.Remainder(counter, 500) == 0){
        SqlConnection _db;
        _db.Open();
        
        using (SqlBulkCopy bulk = new SqlBulkCopy(_db)){
            var map1 = new SqlBulkCopyColumnMapping("columnName1", "columnName1");
            var map2 = new SqlBulkCopyColumnMapping("columnName2", "columnName2");
            //...
            
            bulk.ColumnMappings.Add(map1);
            bulk.ColumnMappings.Add(map2);
            //...
            
            bulk.DestinationTableName = "DestinationTableName";
            bulk.WriteToServer(table);

            bulk.Close();
        }
    }
}