CODEWITHSUNDEEP
c#sqldatatable
M. Nasir Javaid
M. Nasir Javaid

Reputation: 5990

How to partitioning or splitting DataTable in C#?

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.

Upvotes: 6

Views: 21431

Answers (6)

Yahia
Yahia

Reputation: 70369

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.

Upvotes: 10

Arpan Bhattacharya
Arpan Bhattacharya

Reputation: 1

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;
        }

Upvotes: 0

Rob
Rob

Reputation: 27357

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

Upvotes: 9

Dinesh Jethoe
Dinesh Jethoe

Reputation: 31

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;
    }

Upvotes: 1

vanessa
vanessa

Reputation: 31

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?

Upvotes: 3

DeveloperX
DeveloperX

Reputation: 4683

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

Upvotes: 2

Related Questions