Click here to Skip to main content
15,867,949 members
Articles / Programming Languages / C#

Full Outer Join of Two DataTables C# Code

Rate me:
Please Sign up or sign in to vote.
4.90/5 (8 votes)
19 Jun 2009GPL32 min read 100.4K   23   11
This article explains how to join two data tables and get the result set into a new data table using C#. Here, this has been done without using Database Access.

Introduction

I have come to realize that ADO.NET does not support SQL statements on their datasets/ data tables. The requirement is to full outer join two data tables, given the primary keys of two data tables.    

This article explains how to join data tables using full outer join and get the result set into a new data table using ADO.NET, C#.

Background

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.

Example full outer join:

SQL
SELECTFROM employee 
       FULL OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID 
Employee.
LastName
Employee.
DepartmentID
Department.
DepartmentName
Department.
DepartmentID
Smith 34 Clerical 34
Jones 33 Engineering 33
Robinson 34 Clerical 34
Jasper NULL NULL NULL
Steinberg 33 Engineering 33
Rafferty 31 Sales 31
NULL NULL Marketing 35

Using the Code

This ‘MyJoinMethod’ below joins the two data tables with Full Outer join and returns a data table with the merged results to expose to the other methods you use in the program or to data bind. As the input the method requires two data tables to merge which I call as ‘LeftTable’ and ‘RightTable’ and primary key columns of both the tables (i.e. ‘LeftPrimaryColumn’ and ‘RightPrimaryColumn’)  

MyJoinMethod’ also uses ‘DataSetToArrayList’ method to return a given data column to array list.

C#
public ArrayList DataSetToArrayList(int ColumnIndex, DataTable dataTable) 
{ 
    ArrayList output = new ArrayList(); 

    foreach (DataRow row in dataTable.Rows) 
        output.Add(row[ColumnIndex]); 

    return output; 
} 

MyJoinMethod’ uses the following order to join the two tables:

  1. Create the empty datatable - ‘dtResult’, which will be filled with the result set.
  2. Add Left data table column names to the ‘dtResult’ datatable.
  3. Add Right data table column names to the ‘dtResult’ datatable excluding the primary key column of the right data table.
  4. Fill left data table data to the ‘dtResult’ looping through each row of the left datatable
  5. Primary key values of the left table stored in array list ‘var’ using the ‘DataSetToArrayList’ method  
  6. Fill the right table data having keys not in the left table.
  7. Fill the right table data, with joined rows looping through the columns in each row.
C#
public DataTable myJoinMethod(DataTable LeftTable, DataTable RightTable, 
			String LeftPrimaryColumn, String RightPrimaryColumn) 
{ 
    //first create the datatable columns 
    DataSet mydataSet = new DataSet(); 
    mydataSet.Tables.Add("  "); 
    DataTable myDataTable = mydataSet.Tables[0]; 

    //add left table columns 
    DataColumn[] dcLeftTableColumns = new DataColumn[LeftTable.Columns.Count]; 
    LeftTable.Columns.CopyTo(dcLeftTableColumns, 0); 

    foreach (DataColumn LeftTableColumn in dcLeftTableColumns) 
    { 
        if (!myDataTable.Columns.Contains(LeftTableColumn.ToString())) 
            myDataTable.Columns.Add(LeftTableColumn.ToString()); 
    } 

    //now add right table columns 
    DataColumn[] dcRightTableColumns = new DataColumn[RightTable.Columns.Count]; 
    RightTable.Columns.CopyTo(dcRightTableColumns, 0); 
 
    foreach (DataColumn RightTableColumn in dcRightTableColumns) 
    { 
        if (!myDataTable.Columns.Contains(RightTableColumn.ToString())) 
        { 
            if (RightTableColumn.ToString() != RightPrimaryColumn) 
                myDataTable.Columns.Add(RightTableColumn.ToString()); 
        } 
    } 
 
    //add left-table data to mytable 
    foreach (DataRow LeftTableDataRows in LeftTable.Rows) 
    { 
        myDataTable.ImportRow(LeftTableDataRows); 
    } 

    ArrayList var = new ArrayList(); //this variable holds the id's which have joined 

    ArrayList LeftTableIDs = new ArrayList(); 
    LeftTableIDs = this.DataSetToArrayList(0, LeftTable); 

    //import righttable which having not equal Id's with lefttable 
    foreach (DataRow rightTableDataRows in RightTable.Rows) 
    { 
        if (LeftTableIDs.Contains(rightTableDataRows[0])) 
        { 
            string wherecondition = "[" + myDataTable.Columns[0].ColumnName + "]='" 
					+ rightTableDataRows[0].ToString() + "'"; 
            DataRow[] dr = myDataTable.Select(wherecondition); 
            int iIndex = myDataTable.Rows.IndexOf(dr[0]); 

            foreach (DataColumn dc in RightTable.Columns) 
            { 
                if (dc.Ordinal != 0) 
                    myDataTable.Rows[iIndex][dc.ColumnName.ToString().Trim()] = 
		    rightTableDataRows[dc.ColumnName.ToString().Trim()].ToString(); 
            } 
        } 
        else 
        { 
            int count = myDataTable.Rows.Count; 
            DataRow row = myDataTable.NewRow(); 
            row[0] = rightTableDataRows[0].ToString(); 
            myDataTable.Rows.Add(row); 
            foreach (DataColumn dc in RightTable.Columns) 
            { 
                if (dc.Ordinal != 0) 
                    myDataTable.Rows[count][dc.ColumnName.ToString().Trim()] = 
		    rightTableDataRows[dc.ColumnName.ToString().Trim()].ToString(); 
            } 
        } 
    } 

    return myDataTable; 
} 

History

  • 20th June, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Software Developer
Sri Lanka Sri Lanka
Dakshitha works as a Software Engineer for the Nqual Lanka and joined Nqual in February 2008. Currently he is involved in the developments & enhancements of Web Portals. He has over 4 years of experience working as a Software Engineer for various technologies including C#, Delphi, ASP.net, SQL, XML, CSS, etc. He also have the domain knowledge in the areas of Supply Chain Management, Customer Relationship Management and Human Capital Management. Dakshitha holds a Masters in Information Management from Sri Lanka Institute of Information Technology and also a professional member of British Computer Society.

Comments and Discussions

 
QuestionFull Outer Join of Two DataTables C# Code Pin
Member 1206002022-Jun-17 0:44
Member 1206002022-Jun-17 0:44 
GeneralThanks For Code!!!!! Pin
Sahil-From New-Delhi26-Jun-14 21:22
Sahil-From New-Delhi26-Jun-14 21:22 
QuestionTHANK YOU!!!! Pin
T Pat23-May-14 2:38
T Pat23-May-14 2:38 
QuestionThanks for your code Pin
jizhiunion1-Aug-12 23:35
jizhiunion1-Aug-12 23:35 
GeneralThank YOU Pin
Ratman7027-Jul-12 5:49
Ratman7027-Jul-12 5:49 
GeneralMy vote of 5 Pin
sk sinha28-May-12 0:29
sk sinha28-May-12 0:29 
GeneralFew Suggestions Pin
PrakherSrivastava3-Sep-10 0:09
PrakherSrivastava3-Sep-10 0:09 
GeneralExcellent Pin
smkarten22-Jun-09 23:02
professionalsmkarten22-Jun-09 23:02 
GeneralRe: Excellent Pin
dakshithaw2-Jul-09 1:39
dakshithaw2-Jul-09 1:39 
GeneralRe: Excellent Pin
tusharmody24-Sep-09 7:59
tusharmody24-Sep-09 7:59 
GeneralRe: Excellent Pin
Amith44448-Dec-11 22:17
Amith44448-Dec-11 22:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.