Split datatable into multiple tables

Question

5.00/5 (1 vote)

See more:

I have a Datatable like this

Id Name ParentId
-------- -------- --------
1 AA 0
2 BB 1
3 CC2 1
4 DD 1
5 EE 0
6 FF 5
7 GG 5
8 HH 0
9 II 8

I want to split the datatable Like this

Id Name ParentId
-------- -------- --------
2 BB 1
3 CC2 1
4 DD 1

Id Name ParentId
-------- -------- --------
6 FF 5
7 GG 5

Id Name ParentId
-------- -------- --------
9 II 8

the spliting is based on the rows BETWEEN the parentId ='0'

Help me pls..
Thank you

Posted 22-Apr-13 23:55pm

kanamala subin

Updated 6-Sep-16 5:28am

Add a Solution

Comments

Rockstar_ 23-Apr-13 6:00am

Run query on datatable based on parentid it will automatically filter the datatable .

Rockstar_ 23-Apr-13 6:00am

And do whatever u want with the data...

kanamala subin 23-Apr-13 6:02am

In between each ParentId=0 I need the rows

Rockstar_ 23-Apr-13 6:04am

Not understand?

kanamala subin 23-Apr-13 6:10am

Actually Inbetween the '0' values watever i added values are temporary data...

After selecting the rows between the ParentId=0 I want to give the ParentId based on conditions for that rows

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

njammy · Answer 1 · 2016-09-06T05:28:00

Please excuse the large content but I wanted to give you a complete example with the most efficient way to get the result you wish.

(I just used web forms sample page with page_load as a way to create this logic for you to easily test and adjust).

C#

public static class MockData
    {
        public static DataTable GetMockData()
        {
            var dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]{
                new DataColumn("Id",typeof(Int32)),
                new DataColumn("Name",typeof(String)),
                new DataColumn("ParentId",typeof(Int32))
            });

            dt.Rows.Add(new object[] { 1, "AA", 0 });
            dt.Rows.Add(new object[] { 2, "BB", 1 });
            dt.Rows.Add(new object[] { 3, "CC", 1 });
            dt.Rows.Add(new object[] { 4, "DD", 1 });
            dt.Rows.Add(new object[] { 5, "EE", 0 });
            dt.Rows.Add(new object[] { 6, "FF", 5 });
            dt.Rows.Add(new object[] { 7, "GG", 5 });
            dt.Rows.Add(new object[] { 8, "HH", 0 });
            dt.Rows.Add(new object[] { 9, "II", 8 });

            return dt;
        }
    }

    public partial class DataTableSplit : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var dts = MockData.GetMockData().AsEnumerable().GroupBy(x => x.Field<int>("ParentId"));

            DataSet ds = new DataSet();
            foreach(IGrouping<int,DataRow> dataGroup in dts)
            {
                var groupRows = dataGroup.ToList(); // Convert goruping item to data rows.
                DataTable dt = MockData.GetMockData().Clone(); // Copy the structure of the source table to new table.
                ds.Tables.Add(dt);
                dt.TableName = dataGroup.Key.ToString(); // Set the table name as parent id to make it easier to locate them.
                groupRows.ToList().ForEach(x => dt.Rows.Add(x.ItemArray)); // Add the group rows to parent id specific table.
            }

            foreach(DataTable dt in ds.Tables)
            {
                Debug.WriteLine("Table name: " + dt.TableName);
                foreach(DataRow dr in dt.Rows)
                {
                    Debug.WriteLine(dr.ItemArray.ToList().Aggregate((current, next)=>current + "," + next));
                }
            }
        }
    }

The output is as follows:
Table name: 0
1,AA,0
5,EE,0
8,HH,0
Table name: 1
2,BB,1
3,CC,1
4,DD,1
Table name: 5
6,FF,5
7,GG,5
Table name: 8
9,II,8

StianSandberg · Answer 2 · 2013-04-23T00:09:00

You can query your datatable like this:

C#

var tableWhereParentIdIsOne = MyDataTable.AsEnumerable().Where(r => r.Field<int>("ParentId") == 1).CopyToDataTable();</int>

You can query your datatable using multiple where statements, sorting etc.

C#

DataTable tbl = new DataTable("Data").AsEnumerable()
    .Where(r => r.Field<int>("ParentId") == 1) // ParentId == 1
    .Where(r => r.Field<int>("Id") > 3) // Id > 3
    .Where(r => r.Field<string>("Name").Contains("L")) // Name contains L
    .OrderBy(r => r.Field<int>("Id")) // Order by Id
    .CopyToDataTable();

CPallini · Answer 3 · 2013-04-23T00:13:00

Solution 3

It looks a pretty simple task:
Create a new output table.
Read all the the input table, row by row. For each row:

if the third field is 0 then create a new table (and discard the read row).
if the third field is NOT 0, insert the read row into the current output table.

Posted 23-Apr-13 0:13am

CPallini

Sant Osha · Answer 4 · 2013-04-23T00:08:00

string sql = "";
sql = "select * from table where parentId =0 ";
sql = sql + " select * from table where parentId =1 ";
sql = sql+ " select * from table where parentId =5 ";
sql = sql+ " select * from table where parentId =8 ";

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
da.Fill(ds);

//this dataset will have 4 datatables

RelicV · Answer 5 · 2013-04-23T00:36:00

Hi kanamala subin429,

I assume that you need to split the data table into multiple tables. Here, you neded to take an data table array.

This array has the advantage in the above scenario:
- If the input data table has to be divided into unknown number of multiple tables, we can set the array number dynamically.

Lets consider your table as dtInptut

C#

// Create an variable to hold the datatable count
int iCount = 0;

/* Get the count of the number of rows that have ParentID as 0. Please get it 
** from DataView and use the RowFilter property to fetch only ParentId = 0 rows 
** and get the count of number of rows into the dtCount variable 
*/

// Create a DataTable array
DataTable[] dtResult = new DataTable[dtCOunt]

// After setting the iCount, do set this value for loop purpose
int iVal = iCount;

for(int i=0; i < dtInput.Rows.Count; i++)
{
    for(int j=0; j < dtInput.Columns.Count; j++)
    {
        // When ParentID = 0, decrease the iCount value
        if(Convert.ToInt32(dtInput[0]["ParentId"]) == 0)
        {
            iCount = iCount - 1;            
        }
        // When ParenID != 0, enter the value into dtResult data table
        else
        {
            dtResult[iVal - iCount][j] = dtInput[i][j];
        }
    }
}

I've not compliled/tested the above code, just wrote on the solution box. So, there might be some issues/bugs. Please do your part if any error comes up and use debugger. We're here to just provide you a logic or a helping hand, not more than that.

Please dont mind if you feel like I'm harsh in the above paragraph and dont expect a spoon feeding everytime though.. :)

Thank you,
Vamsi

almsaad20010 · Answer 6 · 2016-09-06T04:41:00

C#

Function splitDataTable(ByVal tbl As DataTable) As DataTable()
       Dim tableCount = Math.Ceiling(tbl.Rows.Count / Val(txtCount.Text))

       Dim Divisor = tbl.Rows.Count / tableCount
       Dim tables = tbl.AsEnumerable().Select(Function(r, i) New With {.row = r, .index = i}).GroupBy(Function(x) Math.Floor(x.index / Divisor)).Select(Function(g) g.Select(Function(x) x.row).CopyToDataTable())
       Return tables.ToArray
   End Function

Split datatable into multiple tables

6 solutions

Solution 6

Solution 2

Solution 3

Solution 1

Solution 4

Solution 5

Add your solution here

Preview 0