How to divide DataTable to multiple DataTables by row index-CodePudding

I have one DataTable that i need to split into multiple DataTables, with the same structure, by rows. The way i need to split the tables is: if i have a table with 40 rows, each individual new table can have a maximum of 17 rows. So it should be first DataTable with rows 1-17, second from 18-34 and third from 35 to 40. Finally, i would add the mentioned tables to the DataSet.

I tried creating copies of the tables and deleting rows by index, but that didn't work.

CodePudding user response：

you can use table.AsEnumerable() and use Skip(startRowIndex) for start index of rows and take(size) for size of each table...

var t1 = table.AsEnumerable().Skip(0).Take(17).CopyToDataTable();
var t2 = table.AsEnumerable().Skip(17).Take(17).CopyToDataTable();
...

CodePudding user response：

A reusable way that handles cases like empty tables or tables that contain less rows than the split-count is this method:

public static IEnumerable<DataTable> SplitTable(DataTable table, int splitCount)
{
    if (table.Rows.Count <= splitCount) 
    {
        yield return table.Copy(); // always create a new table
        yield break;
    }

    for (int i = 0; i   splitCount <= table.Rows.Count; i  = splitCount)
    {
        yield return CreateCloneTable(table, i, splitCount);
    }

    int remaining = table.Rows.Count % splitCount;
    if (remaining > 0)
    {
        yield return CreateCloneTable(table, table.Rows.Count - remaining, splitCount);
    }
}

private static DataTable CreateCloneTable(DataTable mainTable, int startIndex, int length)
{
    DataTable tClone = mainTable.Clone(); // empty but same schema
    for (int r = startIndex; r < Math.Min(mainTable.Rows.Count, startIndex   length); r  )
    {
        tClone.ImportRow(mainTable.Rows[r]);
    }

    return tClone;
}

Here is your case with 40 rows and 17 split-count:

DataTable table = new DataTable();
table.Columns.Add();
for (int i = 1; i <= 40; i  ) table.Rows.Add(i.ToString());
DataSet ds = new DataSet();
ds.Tables.AddRange(SplitTable(table, 17).ToArray());

Demo: https://dotnetfiddle.net/8XUBjH

CodePudding user response：

you can create a DataSet and add multiple tables to it in the batch size of 17 in your case.

DataSet dataSet = new DataSet();
int batchSize = 17;

DataTable currentDT = originalTable.Clone();

for (int i = 0; i < originalTable.Rows.Count; i  )
{
    currentDT.Rows.Add(originalTable.Rows[i].ItemArray);

    // add to DatSet
    if (currentDT.Rows.Count == batchSize)
    {
        dataSet.Tables.Add(currentDT);
        currentDT = originalTable.Clone();
    }
}

// Add any remaining rows to the DataSet
if (currentDT.Rows.Count > 0)
{
    dataSet.Tables.Add(currentDT);
}