none
How to join two DataTables using C# (no Database Access)

    Question

  •  

    How can we join two tables and get the resultset of this into a new table using C#.

    Here, everything must be done without Database Access.

     

    Take two DataTables and join them and get this result set into a new table.

    Reply me !!

     

     

    • Moved by Val MazurModerator Friday, January 04, 2013 2:22 PM (From:ADO.NET Managed Providers)
    Saturday, June 30, 2007 12:02 PM

Answers

  •  

    I have found out the way to get the join of the tables using the GetParentRow() method in combination with a "Relation" in the DataSet.

     

    Here is the code:

     

    DataTable dt1 = new DataTable("Table1");

    DataTable dt2 = new DataTable("Table2");

    DataSet ds = new DataSet("DataSet");

     

    dt1.Columns.Add("Eno", typeof(Int32));

    dt1.Columns.Add("Ename", typeof(String));

    dt1.Columns.Add("Salary", typeof(Double));

    dt1.Columns.Add("Deptno", typeof(Int32));

    dt1.PrimaryKey = new DataColumn[] { dt1.Columns["Eno"] };

     

    dt2.Columns.Add("Deptno", typeof(Int32));

    dt2.Columns.Add("Dname", typeof(String));

    dt2.PrimaryKey = new DataColumn[] { dt2.Columns["Deptno"] };

     

    ds.Tables.Add(dt1);

    ds.Tables.Add(dt2);

     

    // Loading data into dt1, dt2:

    object[] o1 ={ 1, "dvs.kiran kumar", 50000.50, 10 };

    object[] o2 ={ 2, "Raj", 4000.50, 20 };

    object[] o3 ={ 3, "Gary", 10000.50, 10 };

     

    object[] c1 ={ 10, "MFG" };

    object[] c2 ={ 20, "EAS" };

    object[] c3 ={ 30, "E&U" };

    object[] c4 ={ 40, "PES" };

     

    dt2.Rows.Add(c1);

    dt2.Rows.Add(c2);

    dt2.Rows.Add(c3);

    dt2.Rows.Add(c4);

     

    dt1.Rows.Add(o1);

    dt1.Rows.Add(o2);

    dt1.Rows.Add(o3);

     

    DataRelation drel = new DataRelation("EquiJoin", dt2.Columns["Deptno"], dt1.Columns["Deptno"]);

    ds.Relations.Add(drel);

     

    DataTable jt = new DataTable("Joinedtable");

    jt.Columns.Add("Eno", typeof(Int32));

    jt.Columns.Add("Ename", typeof(String));

    jt.Columns.Add("Salary", typeof(Double));

    jt.Columns.Add("Deptno", typeof(Int32));

    jt.Columns.Add("Dname", typeof(String));

    ds.Tables.Add(jt);

    foreach (DataRow dr in ds.Tables["Table1"].Rows)

    {

           DataRow parent = dr.GetParentRow("EquiJoin");

           DataRow current = jt.NewRow();

           // Just add all the columns' data in "dr" to the New table.

           for (int i = 0; i < ds.Tables["Table1"].Columns.Count; i++)

           {

                 current[ i ] = dr[ i ];

           }

           // Add the column that is not present in the child, which is present in the parent.

           current["Dname"] = parent["Dname"];

           jt.Rows.Add(current);

    }

    dataGridView1.DataSource = ds.Tables["Joinedtable"];

     

    Finally display the Joined table in the DataGridView.

    Thursday, July 05, 2007 9:16 AM

All replies

  • If the tables have the same schema then possibly the dataset or datatable's Merge method will do what you need.

     

    Glenn

    Sunday, July 01, 2007 8:01 PM
  •  

    The tables are not of same schema.

     

    I want to create a huge cross joined table from these two tables..

    Monday, July 02, 2007 4:21 AM
  •  

    I have found out the way to get the join of the tables using the GetParentRow() method in combination with a "Relation" in the DataSet.

     

    Here is the code:

     

    DataTable dt1 = new DataTable("Table1");

    DataTable dt2 = new DataTable("Table2");

    DataSet ds = new DataSet("DataSet");

     

    dt1.Columns.Add("Eno", typeof(Int32));

    dt1.Columns.Add("Ename", typeof(String));

    dt1.Columns.Add("Salary", typeof(Double));

    dt1.Columns.Add("Deptno", typeof(Int32));

    dt1.PrimaryKey = new DataColumn[] { dt1.Columns["Eno"] };

     

    dt2.Columns.Add("Deptno", typeof(Int32));

    dt2.Columns.Add("Dname", typeof(String));

    dt2.PrimaryKey = new DataColumn[] { dt2.Columns["Deptno"] };

     

    ds.Tables.Add(dt1);

    ds.Tables.Add(dt2);

     

    // Loading data into dt1, dt2:

    object[] o1 ={ 1, "dvs.kiran kumar", 50000.50, 10 };

    object[] o2 ={ 2, "Raj", 4000.50, 20 };

    object[] o3 ={ 3, "Gary", 10000.50, 10 };

     

    object[] c1 ={ 10, "MFG" };

    object[] c2 ={ 20, "EAS" };

    object[] c3 ={ 30, "E&U" };

    object[] c4 ={ 40, "PES" };

     

    dt2.Rows.Add(c1);

    dt2.Rows.Add(c2);

    dt2.Rows.Add(c3);

    dt2.Rows.Add(c4);

     

    dt1.Rows.Add(o1);

    dt1.Rows.Add(o2);

    dt1.Rows.Add(o3);

     

    DataRelation drel = new DataRelation("EquiJoin", dt2.Columns["Deptno"], dt1.Columns["Deptno"]);

    ds.Relations.Add(drel);

     

    DataTable jt = new DataTable("Joinedtable");

    jt.Columns.Add("Eno", typeof(Int32));

    jt.Columns.Add("Ename", typeof(String));

    jt.Columns.Add("Salary", typeof(Double));

    jt.Columns.Add("Deptno", typeof(Int32));

    jt.Columns.Add("Dname", typeof(String));

    ds.Tables.Add(jt);

    foreach (DataRow dr in ds.Tables["Table1"].Rows)

    {

           DataRow parent = dr.GetParentRow("EquiJoin");

           DataRow current = jt.NewRow();

           // Just add all the columns' data in "dr" to the New table.

           for (int i = 0; i < ds.Tables["Table1"].Columns.Count; i++)

           {

                 current[ i ] = dr[ i ];

           }

           // Add the column that is not present in the child, which is present in the parent.

           current["Dname"] = parent["Dname"];

           jt.Rows.Add(current);

    }

    dataGridView1.DataSource = ds.Tables["Joinedtable"];

     

    Finally display the Joined table in the DataGridView.

    Thursday, July 05, 2007 9:16 AM
  • The code look's fine but its better to do that procss in storeprocedure, because in the above code you will be creating more number of object which you should avoid. But in store procedure you will be passing the parameter and you will get the result which satisfies your condition.



    SELECT

    TAB1.COL1,TAB1.COL2,TAB2.COL3
    FROM
    TAB1 inner join TAB2
    on TAB1.COL1=TAB2.COL1
    Wednesday, September 12, 2007 12:38 PM
  • There is a better way described in MSDN "HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET". You can find at http://support.microsoft.com/kb/326080/en-us

     

    Regards!

    Monday, September 24, 2007 7:46 PM
  • Use

    dataTable.Merge(datatable1);
    Saturday, September 05, 2009 10:30 AM
  • Try this. It worked for me. But both DataTable must have same data schema

     

    DataTable dt = dba.SelectParticular("Angelique_TransMaster T,Angelique_Group G,Angelique_Subitem SI,Angelique_Item I,Angelique_TransDetails TD,Angelique_Unit U", "T.PartyID,T.BillDate,G.GroupName,SI.SubItemName,I.ItemName,I.IID,'' as PID,TD.Quantity,TD.Rate,U.UnitName,TD.Amount,TD.BilledQty,'0.00' as BilledAmt", "T.TranID=TD.TranID and TD.SubIID=SI.SubIID and SI.GID=G.GID and TD.SubIID=SI.SubIID and TD.IID=I.IID and I.UID=U.UID and TD.BillingStatus=0 and TD.Activity=2");

     

    DataTable dt1 = dba.SelectParticular("Angelique_TransMaster T,Angelique_Product P,Angelique_TransDetails TD,Angelique_Unit U", "T.PartyID,T.BillDate,'NA' as GroupName,'NA' as SubItemName, P.ProductName as ItemName,0 as IID, P.PID,TD.Quantity,TD.Rate,U.UnitName,TD.Amount,TD.BilledQty,'0.00' as BilledAmt", "T.TranID=TD.TranID and TD.PID=P.PID and P.UID=U.UID and TD.BillingStatus=0 and TD.Activity=2");

     

     

    if (dt.Rows.Count > 0)

    {

     

    foreach (DataRow drow in dt1.Rows)

    {

    dt.ImportRow(drow);

    }

    ViewState[

    "Data"] = dt;

    dt.Constraints.Add(

    "PKIID", dt.Columns["IID"], true);

    SearchFromViewState();

    }

    Wednesday, June 23, 2010 7:31 AM
  • I've tried this but get the System.NullReferenceException: Object reference not set ... error from the line referencing the parent row column.  Below is my relevant code.

    //relate invoices and planholder tables by invoicenumber
    DataRelation drIP = new DataRelation("ds1", ds1.Tables["PLANHOLDER"].Columns["INVOICENUMBER"], ds1.Tables["INVOICES"].Columns["INVOICENUMBER"],false);
    ds1.Relations.Add(drIP);
    //create datatable joining invoices and planholder
    DataTable jt = new DataTable("Joinedtable");
    jt.Columns.Add("INVOICENUMBER", typeof(String));
    jt.Columns.Add("VENDORNAME", typeof(String));
    ds1.Tables.Add(jt);
     
     foreach (DataRow dr in ds1.Tables["INVOICES"].Rows)
     {
            DataRow parent = dr.GetParentRow("drIP");
            DataRow current = jt.NewRow();
           for (int i = 0; i < ds1.Tables["INVOICES"].Columns.Count; i++)
           {
                if (dr[i] != null)
                {
                 current[ i ] = dr[ i ];
                 }
          }
         current["VENDORNAME"] = parent["VENDORNAME"].ToString();  //this line throws the error
         jt.Rows.Add(current);     
     }

    Thursday, June 24, 2010 5:12 PM
  • Nevermind, I figured it ou.  I had to remove the quotes around the datarelation name:

    DataRow parent = dr.GetParentRow(drIP);
    Friday, June 25, 2010 1:02 PM
  •  

    I have found out the way to get the join of the tables using the GetParentRow() method in combination with a "Relation" in the DataSet.

     

    Here is the code:

     

    DataTable dt1 = new DataTable ( "Table1" );

    DataTable dt2 = new DataTable ( "Table2" );

    DataSet ds = new DataSet ( "DataSet" );

     

    dt1.Columns.Add( "Eno" , typeof ( Int32 ));

    dt1.Columns.Add( "Ename" , typeof ( String ));

    dt1.Columns.Add( "Salary" , typeof ( Double ));

    dt1.Columns.Add( "Deptno" , typeof ( Int32 ));

    dt1.PrimaryKey = new DataColumn [] { dt1.Columns[ "Eno" ] };

     

    dt2.Columns.Add( "Deptno" , typeof ( Int32 ));

    dt2.Columns.Add( "Dname" , typeof ( String ));

    dt2.PrimaryKey = new DataColumn [] { dt2.Columns[ "Deptno" ] };

     

    ds.Tables.Add(dt1);

    ds.Tables.Add(dt2);

     

    // Loading data into dt1, dt2:

    object [] o1 ={ 1, "dvs.kiran kumar" , 50000.50, 10 };

    object [] o2 ={ 2, "Raj" , 4000.50, 20 };

    object [] o3 ={ 3, "Gary" , 10000.50, 10 };

     

    object [] c1 ={ 10, "MFG" };

    object [] c2 ={ 20, "EAS" };

    object [] c3 ={ 30, "E&U" };

    object [] c4 ={ 40, "PES" };

     

    dt2.Rows.Add(c1);

    dt2.Rows.Add(c2);

    dt2.Rows.Add(c3);

    dt2.Rows.Add(c4);

     

    dt1.Rows.Add(o1);

    dt1.Rows.Add(o2);

    dt1.Rows.Add(o3);

     

    DataRelation drel = new DataRelation ( "EquiJoin" , dt2.Columns[ "Deptno" ], dt1.Columns[ "Deptno" ]);

    ds.Relations.Add(drel);

     

    DataTable jt = new DataTable ( "Joinedtable" );

    jt.Columns.Add( "Eno" , typeof ( Int32 ));

    jt.Columns.Add( "Ename" , typeof ( String ));

    jt.Columns.Add( "Salary" , typeof ( Double ));

    jt.Columns.Add( "Deptno" , typeof ( Int32 ));

    jt.Columns.Add( "Dname" , typeof ( String ));

    ds.Tables.Add(jt);

    foreach ( DataRow dr in ds.Tables[ "Table1" ].Rows)

    {

           DataRow parent = dr.GetParentRow( "EquiJoin" );

           DataRow current = jt.NewRow();

           // Just add all the columns' data in "dr" to the New table.

           for ( int i = 0; i < ds.Tables[ "Table1" ].Columns.Count; i++)

           {

                 current[ i ] = dr[ i ];

           }

           // Add the column that is not present in the child, which is present in the parent.

           current[ "Dname" ] = parent[ "Dname" ];

           jt.Rows.Add(current);

    }

    dataGridView1.DataSource = ds.Tables[ "Joinedtable" ];

     

    Finally display the Joined table in the DataGridView.

    Thank you,

    This code really help me


    Regards Satheesh Kumar
    Friday, December 17, 2010 9:31 AM
  •  

    I have found out the way to get the join of the tables using the GetParentRow() method in combination with a "Relation" in the DataSet.

     

    Here is the code:

     

    DataTable dt1 = new DataTable ( "Table1" );

    DataTable dt2 = new DataTable ( "Table2" );

    DataSet ds = new DataSet ( "DataSet" );

     

    dt1.Columns.Add( "Eno" , typeof ( Int32 ));

    dt1.Columns.Add( "Ename" , typeof ( String ));

    dt1.Columns.Add( "Salary" , typeof ( Double ));

    dt1.Columns.Add( "Deptno" , typeof ( Int32 ));

    dt1.PrimaryKey = new DataColumn [] { dt1.Columns[ "Eno" ] };

     

    dt2.Columns.Add( "Deptno" , typeof ( Int32 ));

    dt2.Columns.Add( "Dname" , typeof ( String ));

    dt2.PrimaryKey = new DataColumn [] { dt2.Columns[ "Deptno" ] };

     

    ds.Tables.Add(dt1);

    ds.Tables.Add(dt2);

     

    // Loading data into dt1, dt2:

    object [] o1 ={ 1, "dvs.kiran kumar" , 50000.50, 10 };

    object [] o2 ={ 2, "Raj" , 4000.50, 20 };

    object [] o3 ={ 3, "Gary" , 10000.50, 10 };

     

    object [] c1 ={ 10, "MFG" };

    object [] c2 ={ 20, "EAS" };

    object [] c3 ={ 30, "E&U" };

    object [] c4 ={ 40, "PES" };

     

    dt2.Rows.Add(c1);

    dt2.Rows.Add(c2);

    dt2.Rows.Add(c3);

    dt2.Rows.Add(c4);

     

    dt1.Rows.Add(o1);

    dt1.Rows.Add(o2);

    dt1.Rows.Add(o3);

     

    DataRelation drel = new DataRelation ( "EquiJoin" , dt2.Columns[ "Deptno" ], dt1.Columns[ "Deptno" ]);

    ds.Relations.Add(drel);

     

    DataTable jt = new DataTable ( "Joinedtable" );

    jt.Columns.Add( "Eno" , typeof ( Int32 ));

    jt.Columns.Add( "Ename" , typeof ( String ));

    jt.Columns.Add( "Salary" , typeof ( Double ));

    jt.Columns.Add( "Deptno" , typeof ( Int32 ));

    jt.Columns.Add( "Dname" , typeof ( String ));

    ds.Tables.Add(jt);

    foreach ( DataRow dr in ds.Tables[ "Table1" ].Rows)

    {

           DataRow parent = dr.GetParentRow( "EquiJoin" );

           DataRow current = jt.NewRow();

           // Just add all the columns' data in "dr" to the New table.

           for ( int i = 0; i < ds.Tables[ "Table1" ].Columns.Count; i++)

           {

                 current[ i ] = dr[ i ];

           }

           // Add the column that is not present in the child, which is present in the parent.

           current[ "Dname" ] = parent[ "Dname" ];

           jt.Rows.Add(current);

    }

    dataGridView1.DataSource = ds.Tables[ "Joinedtable" ];

     

    Finally display the Joined table in the DataGridView.

    Thank you,

    This code really help me


    Regards Satheesh Kumar

    Hello, there another bit way for this using Linq:

    			DataTable dt1 = new DataTable("Table1");
    
    			DataTable dt2 = new DataTable("Table2");
    
    
    			dt1.Columns.Add("Eno", typeof(Int32));
    			dt1.Columns.Add("Ename", typeof(String));
    			dt1.Columns.Add("Salary", typeof(Double));
    			dt1.Columns.Add("Deptno", typeof(Int32));
    			dt1.PrimaryKey = new DataColumn[] { dt1.Columns["Eno"] };
    
    
    
    			dt2.Columns.Add("Deptno", typeof(Int32));
    			dt2.Columns.Add("Dname", typeof(String));
    			dt2.PrimaryKey = new DataColumn[] { dt2.Columns["Deptno"] };
    
    
    
    
    
    			// Loading data into dt1, dt2:
    
    			object[] o1 = { 1, "dvs.kiran kumar", 50000.50, 10 };
    			object[] o2 = { 2, "Raj", 4000.50, 20 };
    			object[] o3 = { 3, "Gary", 10000.50, 10 };
    
    
    
    			object[] c1 = { 10, "MFG" };
    			object[] c2 = { 20, "EAS" };
    			object[] c3 = { 30, "E&U" };
    			object[] c4 = { 40, "PES" };
    
    
    
    			dt2.Rows.Add(c1);
    			dt2.Rows.Add(c2);
    			dt2.Rows.Add(c3);
    			dt2.Rows.Add(c4);
    
    
    
    			dt1.Rows.Add(o1);
    			dt1.Rows.Add(o2);
    			dt1.Rows.Add(o3);
    
    			var tablesJoinend = from t1 in dt1.Rows.Cast<DataRow>()
    								join t2 in dt2.Rows.Cast<DataRow>() on t1["yourcolumn"] equals t2["yourcolumn"]
    								select t1;
    
    			dataGridView1.DataSource = tablesJoinend.CopyToDataTable();
    
    

     


    Se for útil marque como tal, se for a resposta, marque-a também. Procure ajudar a quem mais precisa
    Sunday, December 26, 2010 7:24 PM
  • hi, i  suffered from this proplem but i descoverd a solution for i tried to use merg method but i did not understand the way to use it

    so i discoverd this functions to get what i need:

    note i have to merg two tables items and  users then the result table be merged with categories then the result with etp tables :

    those functions works for only tow tables but you can use the m for the result one

     

     

     

    public DataTable merge(DataTable fatherTable, DataTable sonTable, string fatherColumnName, string sonColumnName)

    {

     

     

    DataTable result=getSchemedTable(fatherTable,sonTable);

     

     

    string STR;

     

     

    for (int i = 0; i < fatherTable.Rows.Count; i++)

    {

     

     

    DataRow FatherRow = fatherTable.Rows[i];

    STR = FatherRow[fatherColumnName].ToString();

     

     

    for (int j = 0; j < sonTable.Rows.Count; j++)

    {

     

     

    DataRow sonRow = sonTable.Rows[j];

     

     

    if (STR == sonTable.Rows[j][sonColumnName].ToString())

    {

     

     

    DataRow RROW=result.NewRow();

     

     

    //for (int t = 0; t < fatherTable.Rows.Count; t++)

     

     

    //{

    result.Rows.Add( compinTwoRows(FatherRow, sonRow, RROW,fatherTable,sonTable));

     

     

    //}

     

    }

     

    }

     

     

    //result.Columns.Remove(sonColumnName);

     

     

    //result.Columns.Remove(fatherColumnName);

    }

     

     

    return result;

    }

     

     

     

    private DataTable getSchemedTable(DataTable main,DataTable branch)

    {

     

     

    DataTable result = new DataTable();

     

     

    for (int i = 0; i < main.Columns.Count; i++)

    {

    result.Columns.Add(main.Columns[i].ColumnName);

    }

     

     

    for (int j = 0; j < branch.Columns.Count; j++)

    {

    result.Columns.Add(branch.Columns[j].ColumnName);

    }

     

     

    return result;

    }

     

     

    private DataRow compinTwoRows(DataRow mainRow, DataRow sonRow, DataRow RRow,DataTable Father,DataTable son)

    {

     

     

    string mainColumnName;

     

     

     

    for (int i = 0; i < mainRow.ItemArray.Length; i++)

    {

    mainColumnName = Father.Columns[i].ToString();

    RRow[mainColumnName] = mainRow[mainColumnName];

    }

     

     

    for (int j = 0; j < sonRow.ItemArray.Length; j++)

    {

    mainColumnName = son.Columns[j].ToString();

    RRow[mainColumnName] = sonRow[mainColumnName];

    }

     

     

    return RRow;

    }

    Wednesday, January 12, 2011 1:33 PM
  • Hi Recardo, U r a star, my code works fine I tried the first code the one you replied too and it never displayed the data and I tried your comment, it worked perfect. Thank you
    Tuesday, October 11, 2011 1:46 PM
  • This may help you Dynamic Load of Devexpress Grid

    // Get Data from Database Stored Procedure (Note: I user SubSonic for my Datalayer Creation)

    public

    static DataTable GetRegionPlantsDetails(int RegionID)

    {

     

    StoredProcedure procedure = SPs.ScorecardGetRegionPlants(RegionID);

     

    DataSet ds = procedure.GetDataSet();

     

    DataTable dt = ds.Tables[0];

     

    return dt;

    }

    //Load DataTable to Gridview

    

    int

     

    RegionID = Convert.ToInt32(hdfRegionID.Value.ToString());
    int Region2ID = Convert

    .ToInt32(hdfRegion2ID.Value.ToString());

     

    //Create DataTable

     

    DataTable dt = new DataTable

    ();

    dt.Columns.Add(

    "ID", typeof(int

    ));

    dt.Columns.Add(

    "LocationName", typeof(string

    ));

     

    //Region DataTable

     

    //Get DataTable

     

    DataTable dt2 = ScorecardAnalyticesHandler

    .GetRegionPlantsDetails(RegionID);

     

    if

    (dt2.Rows.Count > 0)

    {

     

    //Insert Datatable Rows

     

    for (int

    i = 0; i < dt2.Rows.Count; i++)

    {

     

    int ID = Convert.ToInt32(dt2.Rows[i]["ID"

    ].ToString());

     

    string LocationName = dt2.Rows[i]["LocationName"

    ].ToString();

    dt.Rows.Add(ID, LocationName);

    }

    }

     

    //Region 2 DataTable

     

    //Get DataTable

     

    DataTable dt3 = ScorecardAnalyticesHandler

    .GetRegionPlantsDetails(Region2ID);

     

    if

    (dt3.Rows.Count > 0)

    {

     

    //Insert Datatable Rows

     

    for (int

    i = 0; i < dt3.Rows.Count; i++)

    {

     

    int ID = Convert.ToInt32(dt3.Rows[i]["ID"

    ].ToString());

     

    string LocationName = dt3.Rows[i]["LocationName"

    ].ToString();

    dt.Rows.Add(ID, LocationName);

    }

    }

    gridviewlocation.DataSource = dt;

    gridviewlocation.KeyFieldName =

    "ID"

    ;

    gridviewlocation.DataBind();

    • Proposed as answer by Rodney Dunn Wednesday, November 09, 2011 4:36 PM
    Wednesday, November 09, 2011 4:33 PM