How to split data table into multiple tables with adding 1 minute delay

Question

I know there are a lot of questions and solutions related to it. But I am not getting them. I have a data table of records 8000 and it will increase in the future. I want to split it into 1000 records per table and then want to add one minute delay. Below is my code

DataTable dt = util.getReading();

if (dt != null && dt.Rows.Count > 0)
    {
        totalRec = dt.Rows.Count;
        string ReqEnvPath = System.Configuration.ConfigurationManager.AppSettings["ReadEnvPath"].ToString();
        XElement SoapReqEnv = XElement.Load(ReqEnvPath);

        foreach (DataRow dr in dt.Rows)
        {
            //string uniqueID = dr["UNIQ_KEY"].ToString();
            string uniqueID = dr["uniqueid"].ToString();
            string meterNo = dr["msn"].ToString();
            //APPLICATION_NO, REF_NO, XMETER_NO, METER_SERIAL_NO, KWH_METER_STATUS
            string timestamp = DateTime.UtcNow.ToString("o");
            StringBuilder sbArg0 = new StringBuilder();
            try
            {
                sbArg0.AppendFormat(@"<?xml version=""1.0"" encoding=""UTF-8"" ?>          " + SoapReqEnv.ToString(), uniqueID, startTS, endTS, timestamp, this.HEXURL, this.HEXUID, this.HEXPWD);
                Guid currentGuid = Guid.NewGuid();    


                obj.getResponseAsync(sbArg0.ToString(), currentGuid + "$" + uniqueID);
                obj.getResponseCompleted += this.myHandler;

                string delayMS = System.Configuration.ConfigurationManager.AppSettings["DelayMS"].ToString();
                ushort delay = 1000;
                ushort.TryParse(delayMS, out delay);
                System.Threading.Thread.Sleep(delay);



            }
            catch (Exception ex)
            {
                error += "Error for UniqID:" + uniqueID + "Desc:" + ex.Message + "\n";
            }
            finally
            {
                //System.Threading.Thread.CurrentThread.Join();
            }
        }
    }

How can I split the data table with 1000 rows each while adding 1 minute delay time so the next 1000 records can be taken after a 1-minute delay?

Update 1

For a testing point of view, I have taken 20 records and by following this solution I have split the data table into 5.

private List<DataTable> SplitTable(DataTable originalTable, int batchSize)
{
    List<DataTable> tables = new List<DataTable>();
    int i = 0;
    int j = 1;
    DataTable newDt = originalTable.Clone();
    newDt.TableName = "Table_" + j;
    newDt.Clear();
    foreach (DataRow row in originalTable.Rows)
    {
        DataRow newRow = newDt.NewRow();
        newRow.ItemArray = row.ItemArray;
        newDt.Rows.Add(newRow);
        i++;
        if (i == batchSize)
        {
            tables.Add(newDt);
            j++;
            newDt = originalTable.Clone();
            newDt.TableName = "Table_" + j;
            newDt.Clear();
            i = 0;
        }
    }
    return tables;
}  

public string LoadAMIReadings()
{
    string[] arr = new string[] 
    {"37030298060","28373341367200U","002997004330","002997004330",
     "37010674330","28371551110400U","002997006388","002997006388",
     "37030315632","28373131369800U","002998000563","002998000563",
     "37010681112","28374211369900U","002998000938","002998000938",
     "37010682305","28374331368400U","002998000351","002998000351",
     "37010682312","28374331369600U","002998000995","002998000995",
     "37030297517","28373321004010U","002998003915","002998003915",
     "37010674134","28371550292110U","002997006486","002997006486",
     "37030295965","28373150875200U","002997004697","002997004697",
     "37010678805","28372720047060U","002997002511","002997002511",
     "37010675029","28372230024431U","002999000385","002999000385",
     "37030299221","28373430506600U","002997000337","002997000337",
     "37030299227","28373430507200U","002997000382","002997000382",
     "37030297870","28373340570200U","002997001558","002997001558",
     "37010679004","28372730053742U","002997001334","002997001334",
     "37010719967","28372810024580U","002997006816","002997006816",
     "37010720185","28374120091810U","002998003930","002998003930",
     "37010720008","28372810036450U","002997006911","002997006911",
     "37010680399","20374131467200U","002998002734","002998002734",
     "37030296089","28373151133100U","002997002578","002997002578"};

    DataTable dt = new DataTable();
    dt.Columns.Add("Application_No", typeof(string));
    dt.Columns.Add("REF_NO", typeof(string));
    dt.Columns.Add("METER_SERIAL_NO", typeof(string));
    dt.Columns.Add("XMETER_NO", typeof(string));

    dt.FillDataTable(arr);
if (dt != null && dt.Rows.Count > 0)
    {
        totalRec = dt.Rows.Count;
        string ReqEnvPath = System.Configuration.ConfigurationManager.AppSettings["ReadEnvPath"].ToString();
        XElement SoapReqEnv = XElement.Load(ReqEnvPath);
        List<DataTable> splitdt = SplitTable(dt, 5);

        foreach (DataRow dr in dt.Rows) // here I want to pass splitdt but it gives me error
        {

            //string uniqueID = dr["UNIQ_KEY"].ToString();
            string uniqueID = dr["Application_No"].ToString();
            string meterNo = dr["METER_SERIAL_NO"].ToString();
            //APPLICATION_NO, REF_NO, XMETER_NO, METER_SERIAL_NO, KWH_METER_STATUS
            string timestamp = DateTime.UtcNow.ToString("o");
            StringBuilder sbArg0 = new StringBuilder();
            try
            {
                sbArg0.AppendFormat(@"<?xml version=""1.0"" encoding=""UTF-8"" ?>          " + SoapReqEnv.ToString(), uniqueID, startTS, endTS, timestamp, this.HEXURL, this.HEXUID, this.HEXPWD);
                Guid currentGuid = Guid.NewGuid();    


                obj.getResponseAsync(sbArg0.ToString(), currentGuid + "$" + uniqueID);
                obj.getResponseCompleted += this.myHandler;

                string delayMS = System.Configuration.ConfigurationManager.AppSettings["DelayMS"].ToString();
                ushort delay = 1000;
                ushort.TryParse(delayMS, out delay);
                System.Threading.Thread.Sleep(delay);


            }
            catch (Exception ex)
            {
                error += "Error for UniqID:" + uniqueID + "Desc:" + ex.Message + "\n";
            }
            finally
            {
                //System.Threading.Thread.CurrentThread.Join();
            }
        }
    }

}

Now I have 4 tables of each 5 rows in it. I want to take data from the 1st table process it then waits for 1-minute and then get the 2nd split table and so on.

I am trying to pass splitdt into foreach(DataRow in splitdt.Rows) but it is giving me an error.

How can I achieve it?

Any help would be highly appreciated

So am I right, you want to handle 1000 rows and make 1 minute delay and continue to the next 1000 rows? As I know, you cannot change (remove/insert rows) the datatable content within the for each loop. — Leon, Apr 15 '19 at 11:34
You want to split a List into small chunk a List>. Like https://stackoverflow.com/questions/419019/split-list-into-sublists-with-linq?noredirect=1&lq=1. — xdtTransform, Apr 15 '19 at 11:43
The 1 minute delay is a bit unclear why .. Perhaps you could just process the sub list every minut instead of stoping a thread for 1minute. A simple timer that tick every X time, take the first sub list in the list work on it deleted it . — xdtTransform, Apr 15 '19 at 11:44
It's unclear if you had issue on spliting or waiting. As there is at least 5 way to split and 3 way to wait that's a lot of possible answer. Note that if you splitthe question in 2 part we have two clean duplicate. — xdtTransform, Apr 15 '19 at 11:55
The best way of doing this is to query the Event database every minutes and get results between a Start Time and an End Time. So you can setup and Timer Event that triggers every 10 seconds and then when the minutes changes query the Event database for the previous one minute. I wouldn't limits the records to 1000 but instead all the records for 1 minute timespan. I would make the xml output filename contain the time so it will be easier to extract the data. — jdweng, Apr 15 '19 at 12:30
@Leon yes I want to process 1st 1000 rows and when they are processed, I want to have a 1-minute delay and then get next 1000 rows and so on — Moeez, Apr 16 '19 at 03:15
@xdtTransform there is a requirment that way. That why I want to have a delay — Moeez, Apr 16 '19 at 03:16

donatasj87 · Answer 1 · 2021-12-28T15:06:34.440

I hope this is what you want to achieve:

using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;

...

    public void Test()
    {
        DataTable bigDataTable = GetBigDataTable();
        var splitedTables = new List<DataTable>();
        var smallTable = new DataTable();
        int page = 0;
        int pageSize = 1000;
        List<DataRow> results;
        while (true)
        {
            results = bigDataTable.AsEnumerable().Skip(pageSize * page++).Take(pageSize).ToList();

            if (!results.Any())
                break;

            smallTable = results.CopyToDataTable();
            splitedTables.Add(smallTable);

            Thread.Sleep(1000 * 60 * 1);
        } while (results.Any());
    }

For this to work you need to add reference to System.Data.DataSetExtensions.

This is also similar question in SO.

This worked for me, except I just had to change the page variable's initial value to 0, otherwise it'd skip the first rows and not add them as the first table in the list. Otherwise, PERFECT! Thank you! — Westley Bennett, Dec 27 '21 at 22:22

How to split data table into multiple tables with adding 1 minute delay

1 Answers1

Linked