Click here to Skip to main content
Click here to Skip to main content

Full Outer Join of Two DataTables C# Code

, 19 Jun 2009
Rate this:
Please Sign up or sign in to vote.
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:

SELECT * 
FROM 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.

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.
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)

About the Author

dakshithaw
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

 
GeneralThanks For Code!!!!! PinmemberSahil-From-New-Delhi26-Jun-14 21:22 
QuestionTHANK YOU!!!! PinprofessionalT Pat23-May-14 2:38 
QuestionThanks for your code Pinmemberjizhiunion1-Aug-12 23:35 
GeneralThank YOU PinmemberRatman7027-Jul-12 5:49 
GeneralMy vote of 5 PinmemberSanjay K. Sinha28-May-12 0:29 
GeneralFew Suggestions PinmemberPrakherSrivastava3-Sep-10 0:09 
GeneralExcellent PinmemberSMKarten22-Jun-09 23:02 
Thanks.
 
Just what I was trying to do using a different approach.
 
Steve
GeneralRe: Excellent Pinmemberdakshithaw2-Jul-09 1:39 
GeneralRe: Excellent Pinmembertusharmody24-Sep-09 7:59 
GeneralRe: Excellent PinmemberAmith44448-Dec-11 22:17 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 20 Jun 2009
Article Copyright 2009 by dakshithaw
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid