1

I have around 25k records in datatable. I already have update query written by previous developer which I can't change. What I am trying to do is as follows:

  1. Take 1000 records at a time from datatable, records can vary from 1 to 25k.
  2. Update query which is in string, replace IN('values here') clause of that with these 1000 records and fire query against database.

Now, I know there are effecient ways to do it, like bulk insert by use of array binding , but I can't change present coding pattern due to restrictions. What I have tried to do:

 if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {

                    reviewitemsend =reviewitemsend + dr["ItemID"].ToString()+ ',';
                    //If record count is 1000 , execute against database. 
                }

            }

Now above approach is taking me nowwhere and am like struck. So another better aproach which I am thinking is below :

int TotalRecords = dt.rows.count;
If (TotalRecords <1000 && TotalRecords >0 )
 //Update existing query with this records by placing them in IN cluse and execute
else
 {
        intLoopCounter = TotalRecords/1000; //Manage for extra records, as        counter will be whole number, so i will check modulus division also, if that is 0, means no need for extra counter, if that is non zero, intLoopCounter increment by 1
for(int i= 0;i < intLoopCounter; i++)
 {
    //Take thousand records at a time, unless last counter has less than 1000 records and execute against database
}

}

Also, note update query is below : 

 string UpdateStatement = @" UPDATE Table
 SET column1=<STATUS>,                                                       
 column2= '<NOTES>',
 changed_by = '<CHANGEDBY>',                                        
 status= NULL,   
 WHERE ID IN  (<IDS>)";

In above update query, IDS are already replaced with all 25K record ID's, which will be shown to end user like that, internally only I have to execute it as separate chunks, So within IN() cluase I need to insert 1k records at a time

Ubaid Ashraf
  • 1,049
  • 5
  • 15
  • 43

1 Answers1

2

You can split your Datatable using this linq method:

private static List<List<DataRow>> SplitDataTable(DataTable table, int pageSize)
{
    return
    table.AsEnumerable()
          .Select((row, index) => new { Row = row,  Index = index, })
          .GroupBy(x => x.Index / pageSize)
          .Select(x => x.Select(v => v.Row).ToList())
          .ToList();
}

Then run the database query on each chunk:

foreach(List<DataRow> chuck in SplitDataTable(dt, 1000))
{
    foreach(DataRow row in chuck)
    {
        // prepare data from row
    }

    // execute against database
}

Tip: you can modify the split query to prepare your data directly inside of it (by replacing the x.Select(v => v.Row) part, instead of looping twice on that huge DataTable.

Zein Makki
  • 29,485
  • 6
  • 52
  • 63