How to partitioning or splitting DataTable in C#?

Question

I want to split DataTable so that I can upload its chunks from one place to other.

For example

pick first 100 rows.
pick next 100 rows.
pick next 100 rows and so on ...

Is there any way to do it just like cursor in Database? I do not like to use loops etc. for counting rows.

Use linq select part of record see this link In Stack Over flow [Split a collection into n parts with LINQ?][1] [1]: http://stackoverflow.com/questions/438188/split-a-collection-into-n-parts-with-linq — DeveloperX, Nov 15 '11 at 06:19
possible duplicate of [Split List into Sublists with LINQ](http://stackoverflow.com/questions/419019/split-list-into-sublists-with-linq) — nawfal, Feb 18 '13 at 11:28

score 10 · Accepted Answer · edited May 31 '18 at 05:37

10

YourDataTable.Select() gives you an array of Data

What about linq?

Fro example YourDataTable.Select (x => x).Take (100).ToEnumerable() gives you the first 100 DataRows and YourDataTable.Select (x => x).Skip(100).Take (100).ToEnumerable() for the next 100.

edited May 31 '18 at 05:37

NASSER

5,900
7
38
57

answered Nov 15 '11 at 06:16

Yahia

69,653
9
115
144

2

use Skip() like `.Skip(100).Take(100)` – V4Vendetta Nov 15 '11 at 06:20
5

@Yahia, Getting an error as "Cannot convert lambda expression to type 'string' because it is not a delegate type". Looks like a lambda expresssion cannot be used in DataTable.Select() method. – Dimuthu Oct 30 '13 at 12:48
You are assuming it is always 100? – SearchForKnowledge Jul 07 '15 at 13:05

Rob · Answer 2 · 2018-01-06T16:03:14.893

9

Try this:

public static class DataExtensions
{
    public static IEnumerable<IEnumerable<DataRow>> Partition(this DataTable dataTable, int partitionSize)
    {
        var numRows = Math.Ceiling((double)dataTable.Rows.Count);
        for(var i = 0; i < numRows / partitionSize; i++)
        {
            yield return Partition(dataTable, i * partitionSize, i * partitionSize + partitionSize);
        }
    }
    private static IEnumerable<DataRow> Partition(DataTable dataTable, int index, int endIndex)
    {
        for(var i = index; i < endIndex && i < dataTable.Rows.Count; i++)
        {
            yield return dataTable.Rows[i];
        }
    }
}
var partitions = dataTable.Partition(100);

Doing:

dataTable.Skip(0).Take(100);
dataTable.Skip(100).Take(100);
dataTable.Skip(200).Take(100);
dataTable.Skip(300).Take(100);

Will iterate 0 times, and take 100 on the first execution. Then iterate through 100 rows, take 100, then iterate 200 rows, then take 100, etc.

The above will do a lazy fetch and only hit each row once

edited Jan 06 '18 at 16:03

answered Nov 15 '11 at 06:31

Rob

26,989
16
82
98

This is the best answer for performance, using multiple linq calls as mentioned in the text above will result in additional unnecessary iterations through the entire data set. This answer avoids that problem. – Mr.Brownstone Jun 09 '16 at 14:26
How do i convert this to vb.net? – Smith Apr 10 '17 at 02:11
How might I go about implementing `foreach (DataRow row in dataTable.Skip(300).Take(100))` ? – Bill Roberts Oct 23 '18 at 21:55
Until now I didn't know i needed that extension :D – Jan 25 '22 at 15:54

vanessa · Answer 3 · 2012-10-10T16:22:46.157

3

This is a simply way to do that:

public DataSet test(DataSet ds, int max)
    {
        int i = 0;
        int j = 1;
        DataSet newDs = new DataSet();
        DataTable newDt = ds.Tables[0].Clone();
        newDt.TableName = "Table_" + j;
        newDt.Clear();
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            DataRow newRow = newDt.NewRow();
            newRow.ItemArray = row.ItemArray;

            newDt.Rows.Add(newRow);
            i++;
            if (i == max)
            {
                newDs.Tables.Add(newDt);
                j++;                    
                newDt = ds.Tables[0].Clone();
                newDt.TableName = "Table_" + j;
                newDt.Clear();
                i = 0;
            }
        }
        return newDs;
    }

Can you try?

edited Oct 10 '12 at 16:22

answered Oct 10 '12 at 16:03

vanessa

31
2

Before you return newDs, you'll need to add the last newDt! – Tony Jul 16 '15 at 05:26
This code has a major flaw: It only adds the created DataTable to the tables List once i == max, this means that the last DataTable wont be added unless the table size is divisible by chunk size. To remedy that move/add the newDs.Tables.Add(newDt); to immediately after the two table creations – Blob31 Sep 22 '20 at 10:42

score 2 · Answer 4 · edited May 23 '17 at 12:02

2

Use linq select part of record this link in stack overflow can be helpful Split a collection into n parts with LINQ?

edited May 23 '17 at 12:02

Community

1
1

answered Nov 15 '11 at 06:20

DeveloperX

4,633
17
22

score 1 · Answer 5 · edited Oct 03 '14 at 21:13

Check: splitting a large datatable into smaller batches from c-sharpcorner.com

internal static List<datatable> SplitTable(DataTable originalTable, int batchSize)
    {
        List<datatable> tables = new List<datatable>();

        DataTable new_table = new DataTable();
        new_table = originalTable.Clone();
        int j = 0;
        int k = 0;

        if (originalTable.Rows.Count &lt;= batchSize)
        {
            new_table.TableName = "Table_" + k;
            new_table = originalTable.Copy();
            tables.Add(new_table.Copy());
        }
        else
        {
            for (int i = 0; i &lt; originalTable.Rows.Count; i++)
            {
                new_table.NewRow();
                new_table.ImportRow(originalTable.Rows[i]);
                if ((i + 1) == originalTable.Rows.Count)
                {
                    new_table.TableName = "Table_" + k;
                    tables.Add(new_table.Copy());
                    new_table.Rows.Clear();
                    k++;
                }
                else if (++j == batchSize)
                {
                    new_table.TableName = "Table_" + k;
                    tables.Add(new_table.Copy());
                    new_table.Rows.Clear();
                    k++;
                    j = 0;
                }
            }
        }

        return tables;
    }

score 0 · Answer 6 · answered May 30 '18 at 12:25

Improving on  @vanessa

public DataSet SplitDataTable(DataTable tableData, int max)
        {
            int i = 0;
            int j = 1;
            int countOfRows = tableData.Rows.Count;
            DataSet newDs = new DataSet();
            DataTable newDt = tableData.Clone();
            newDt.TableName = tableData.TableName+"_" + j;
            newDt.Clear();
            foreach (DataRow row in tableData.Rows)
            {
                DataRow newRow = newDt.NewRow();
                newRow.ItemArray = row.ItemArray;

                newDt.Rows.Add(newRow);
                i++;

                countOfRows--;

                if (i == max )
                {
                    newDs.Tables.Add(newDt);
                    j++;
                    newDt = tableData.Clone();
                    newDt.TableName = tableData.TableName + "_" + j;
                    newDt.Clear();
                    i = 0;
                }

                if (countOfRows == 0 && i < max)
                {
                    newDs.Tables.Add(newDt);
                    j++;
                    newDt = tableData.Clone();
                    newDt.TableName = tableData.TableName + "_" + j;
                    newDt.Clear();
                    i = 0;
                }
            }
            return newDs;
        }

Welcome to Stack Overflow! Please don't just throw your source code here. Be nice and try to give a nice description to your answer, so that others will like it and upvote it. See: [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) — sɐunıɔןɐqɐp, May 30 '18 at 12:47

How to partitioning or splitting DataTable in C#?

6 Answers6