c# - 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 + "
";
            }
            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 + "
";
            }
            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

Answer 1

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 = 1;
        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.