loop through N records from a datatable fill another datatable with the n records value

Question

Good Day Everyone,

I have a datatable with 3k records, I want to loop through the datatable and on every 1000 records i want add the 1k record to another data table and the next 1000 add the second 1k to the datatable again and so on. So I just want to loop through my Datatable getting 1K records at a time.

what i have tried:

using (OleDbConnection myConnection = new OleDbConnection(Form1.constring))
        {
            myConnection.Open();
            string query = "";

            int count = 0;




            OleDbCommand cmdDataBase = new OleDbCommand("SELECT [DATA ENTRY].*,      BANKLIST.BANKLISTNAME, BANKLIST.CBNCODE FROM [DATA ENTRY]  ", myConnection);

            OleDbDataAdapter sda = new OleDbDataAdapter();
            sda.SelectCommand = cmdDataBase;
            //  cmdDataBase.Parameters.AddWithValue("@DateStart", cbParameters.startDateValue.ToString());
            //   cmdDataBase.Parameters.AddWithValue("@dateEnd", cbParameters.EndDateValue.ToString());
            cmdDataBase.ExecuteNonQuery();
            myConnection.Close();



            DataTable SDS = new DataTable();


            sda.Fill(SDS);
            

            var q = SDS.Rows.Cast<DataRow>().Select((r, i) => new { r, n = i / 1000 }).GroupBy(p => p.n);
            int GRP = 1;
            //To iterate the group
            foreach (var g in q)
            {
                dsMandate SDS2 = new dsMandate();
                SDS2.EXCELDETAIL.Clear();
                GRP++;
                //To iterate the rows in the group

                foreach (DataRow r in g.Select(p => p.r))
                {                        
                    DataRow nr = SDS2.EXCELDETAIL.NewRow();
                    nr["ID"] = r["ID"];
                    nr["GROUP"] = GRP;
                    nr["BENEFICIARY NAME"] = r["BENEFICIARY NAME"];
                    nr["BANK NAME"] = r["BANK NAME"];
                    nr["ACNO"] = r["ACNO"];
                    nr["BVN"] = r["BVN"];
                    nr["NET PAYMENT"] = r["NET PAYMENT"];
                    nr["SENDER NAME"] = r["SENDER NAME"];
                    nr["NARATION"] = r["NARATION"];
                    nr["DIGIT"] = r["DIGIT"];
                    nr["BANKCODE"] = r["BANKCODE"];
                    nr["BANKLISTNAME"] = r["BANKLISTNAME"];
                    nr["CBNCODE"] = r["CBNCODE"];
                    // nr["GROUP1"] = r["GROUP1"];
                    nr["SUBHEAD"] = r["SUBHEAD"];
                    SDS2.EXCELDETAIL.Rows.Add(nr);
                }

                PrintDialog printDialog = new PrintDialog();
                CrystalDecisions.CrystalReports.Engine.ReportDocument myReport = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
                myReport.Load(Application.StartupPath + "\\Reports\\mandateToExcel.rpt");
                myReport.SetDataSource(SDS);

                myReport.SetParameterValue("bankRegName", bankName.ToString());
                myReport.SetParameterValue("bankRegAcctNo", BankCodeAcctNo.ToString());
                myReport.SetParameterValue("mandateNo", txtMandate.Text.ToString());
                myReport.SetParameterValue("date", dtpDate.Value.ToString("dd-MM-yyyy"));


                ExportOptions exportOption;

                DiskFileDestinationOptions diskOps = new DiskFileDestinationOptions();
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Excel |*.xls";

                var result = sfd.ShowDialog();

                if (result == DialogResult.OK && !string.IsNullOrWhiteSpace(sfd.FileName))
                {
                    diskOps.DiskFileName = sfd.FileName;



                    exportOption = myReport.ExportOptions;
                    {
                        exportOption.ExportDestinationType = ExportDestinationType.DiskFile;
                        exportOption.ExportFormatType = ExportFormatType.ExcelWorkbook;
                        exportOption.ExportDestinationOptions = diskOps;
                        exportOption.ExportFormatOptions = new ExcelFormatOptions();
                    }
                    myReport.Export();
                    MessageBox.Show("Successfull", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else if (result == DialogResult.Cancel || result == DialogResult.Abort)
                {
                    MessageBox.Show("Not Successfull!", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

            }

The issue i have with the code, is it return all the 3k data instead instead of 1k page.

Answer 1

So, loop though a table, and on every 1000th record, copy that one reocrd to another table?

This would and should work (you don't mention your 2nd table name).

        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
        {
            string strSQL = "SELECT * FROM [DATA ENTRY]";
            using (OleDbCommand cmdSQL = new OleDbCommand(strSQL, conn))
            {
                conn.Open();
                DataTable rstData = new DataTable();
                rstData.Load(cmdSQL.ExecuteReader());

                int i = 0;
                foreach (DataRow OneRow in rstData.Rows)
                {
                    i++;
                    if ((i % 1000)== 0)
                    {
                        // 1000th row, copy to other table
                        strSQL = "INSERT INTO My2ndTable " +
                            "SELECT [DATA ENTRY].* WHERE [DATA ENTRY].ID = " + OneRow["ID"];
                        cmdSQL.CommandText = strSQL;
                        cmdSQL.ExecuteNonQuery();
                    }
                }
            }
        }