Full Outer Join of Two DataTables C# Code

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 

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

• 20^th June, 2009: Initial post