DataTable Relational Operators in C# - JOIN Method

This JOIN method is equivalent to the TSQL INNER JOIN expression using equality.

This method returns all columns from both tables.
Once again, column name collision is avoided by appending "_Second" to the columns affected.

There are a total of 3 signatures for this method.
In summary the code works as follows:

Create new empty table
Create a DataSet and add tables.
Get a reference to Join columns
Create a DataRelation
Construct JOIN table columns
Using the DataRelation add rows with matching related rows using array manipulation
Return table

//FJC = First Join Column

//SJC = Second Join Column

public static DataTable Join (DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC)

{

//Create Empty Table

DataTable table = new DataTable("Join");

// Use a DataSet to leverage DataRelation

using(DataSet ds = new DataSet())

{

//Add Copy of Tables

ds.Tables.AddRange(new DataTable[]{First.Copy(),Second.Copy()});

//Identify Joining Columns from First

DataColumn[] parentcolumns = new DataColumn[FJC.Length];

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

{

parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];

}

//Identify Joining Columns from Second

DataColumn[] childcolumns = new DataColumn[SJC.Length];

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

{

childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];

}

//Create DataRelation

DataRelation r = new DataRelation(string.Empty,parentcolumns,childcolumns,false);

ds.Relations.Add(r);

//Create Columns for JOIN table

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

{

table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);

}

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

{

//Beware Duplicates

if(!table.Columns.Contains(Second.Columns[i].ColumnName))

table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);

else

table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);

}

//Loop through First table

table.BeginLoadData();

foreach(DataRow firstrow in ds.Tables[0].Rows)

{

//Get "joined" rows

DataRow[] childrows = firstrow.GetChildRows(r);

if(childrows != null && childrows.Length > 0)

{

object[] parentarray = firstrow.ItemArray;

foreach(DataRow secondrow in childrows)

{

object[] secondarray = secondrow.ItemArray;

object[] joinarray = new object[parentarray.Length+secondarray.Length];

Array.Copy(parentarray,0,joinarray,0,parentarray.Length);

Array.Copy(secondarray,0,joinarray,parentarray.Length,secondarray.Length);

table.LoadDataRow(joinarray,true);

}

}

}

table.EndLoadData();

}

return table;

}

public static DataTable Join (DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)

{

return SQLOps.Join(First, Second, new DataColumn[]{FJC}, new DataColumn[]{SJC});

}

public static DataTable Join (DataTable First, DataTable Second, string FJC, string SJC)

{

return SQLOps.Join(First, Second, new DataColumn[]{First.Columns[FJC]}, new DataColumn[]{First.Columns[SJC]});

}

Print | posted on Tuesday, January 20, 2004 9:26 AM

Feedback

# DataTable and Relational Operators

left by Ashutosh Nilkanth's .NET Blog at 1/20/2004 8:29 PM Gravatar

# re: DataTable Relational Operators in C# - JOIN Method

left by ELmO at 1/28/2004 4:31 AM Gravatar
Interesting.

# re: DataTable Relational Operators in C# - JOIN Method

left by Ben at 4/13/2004 9:49 PM Gravatar
Very neat code, I'm using this to join two DataSets from completely different sources (MS SQL Server and a Windows Indexing Service) and it works exactly as expected.

Thanks David

# Joining DataTables in C#

left by Tangible Thoughts at 6/7/2004 9:04 AM Gravatar

# re: DataTable Relational Operators in C# - JOIN Method

left by Jeff at 7/10/2004 6:39 AM Gravatar
Here is VB version. Sorry, have to follow company's standards.

Public Shared Function Join(ByVal first As DataTable, ByVal second As DataTable, ByVal firstJoinColumn As DataColumn, ByVal secondJoinColumn As DataColumn) As DataTable
Return Join(first, second, New DataColumn() {firstJoinColumn}, New DataColumn() {secondJoinColumn})
End Function

Public Shared Function Join(ByVal first As DataTable, ByVal second As DataTable, ByVal firstJoinColumn As String, ByVal secondJoinColumn As String) As DataTable
Return Join(first, second, New DataColumn() {first.Columns(firstJoinColumn)}, New DataColumn() {second.Columns(secondJoinColumn)})
End Function

Public Shared Function Join(ByVal first As DataTable, ByVal second As DataTable, ByVal firstJoinColumns As DataColumn(), ByVal secondJoinColumns As DataColumn()) As DataTable
' Create Empty Table
Dim oTable As DataTable = New DataTable("Join")

' Use a DataSet to leverage DataRelation
Dim oDataSet As DataSet = New DataSet
With oDataSet
' Add Copy of Tables
.Tables.AddRange(New DataTable() {first.Copy, second.Copy})

' Identify Joining Columns from First
Dim arrParentColumns(firstJoinColumns.Length - 1) As DataColumn
For iCounter As Int32 = 0 To arrParentColumns.Length - 1
arrParentColumns(iCounter) = oDataSet.Tables(0).Columns(firstJoinColumns(iCounter).ColumnName)
Next

' Identify Joining Columns from Second
Dim arrChildColumns(secondJoinColumns.Length - 1) As DataColumn
For iCounter As Int32 = 0 To arrChildColumns.Length - 1
arrChildColumns(iCounter) = oDataSet.Tables(1).Columns(secondJoinColumns(iCounter).ColumnName)
Next

' Create DataRelation
Dim oDataRelation As DataRelation = New DataRelation(String.Empty, arrParentColumns, arrChildColumns, False)
.Relations.Add(oDataRelation)

' Create Columns for JOIN table
For iCounter As Int32 = 0 To first.Columns.Count - 1
oTable.Columns.Add(first.Columns(iCounter).ColumnName, first.Columns(iCounter).DataType)
Next

For iCounter As Int32 = 0 To second.Columns.Count - 1
' Beware Duplicates
If Not oTable.Columns.Contains(second.Columns(iCounter).ColumnName) Then
oTable.Columns.Add(second.Columns(iCounter).ColumnName, second.Columns(iCounter).DataType)
Else
oTable.Columns.Add(second.Columns(iCounter).ColumnName & "_Second", second.Columns(iCounter).DataType)
End If
Next

' Loop through First table
oTable.BeginLoadData()
For Each oFirstTableDataRow As DataRow In oDataSet.Tables(0).Rows
' Get "joined" rows
Dim childRows As DataRow() = oFirstTableDataRow.GetChildRows(oDataRelation)
If Not childRows Is Nothing AndAlso childRows.Length > 0 Then
Dim arrParentArray() As Object = oFirstTableDataRow.ItemArray
For Each oSecondTableDataRow As DataRow In childRows
Dim arrSecondArray() As Object = oSecondTableDataRow.ItemArray
Dim arrJoinArray(arrParentArray.Length + arrSecondArray.Length - 1) As Object
Array.Copy(arrParentArray, 0, arrJoinArray, 0, arrParentArray.Length)
Array.Copy(arrSecondArray, 0, arrJoinArray, arrParentArray.Length, arrSecondArray.Length - 1)
oTable.LoadDataRow(arrJoinArray, True)
Next
End If

Next

oTable.EndLoadData()
End With
Return oTable
End Function

# re: DataTable Relational Operators in C# - JOIN Method

left by Jeff at 7/10/2004 8:06 AM Gravatar
Sorry, found a bug.

Array.Copy(arrSecondArray, 0, arrJoinArray, arrParentArray.Length, arrSecondArray.Length -1)

Should be

Array.Copy(arrSecondArray, 0, arrJoinArray, arrParentArray.Length, arrSecondArray.Length)

# re: DataTable Relational Operators in C# - JOIN Method

left by Kirk Kennison at 8/4/2004 1:58 PM Gravatar
Here is a left outer join, based on the above c# code:

using System;
using System.Data;

namespace MyNameSpace
{
/// <summary>
/// Summary description for SQLOps.
/// </summary>
public class SQLOps
{
public SQLOps()
{
}

//FJC = First Join Column

//SJC = Second Join Column

public static DataTable Join (DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC)
{
//Create Empty Table
DataTable table = new DataTable("Join");

// Use a DataSet to leverage DataRelation
using(DataSet ds = new DataSet())
{
//Add Copy of Tables
ds.Tables.AddRange(new DataTable[]{First.Copy(),Second.Copy()});

//Identify Joining Columns from First
DataColumn[] parentcolumns = new DataColumn[FJC.Length];

for(int i = 0; i < parentcolumns.Length; i++)
{
parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];
}

//Identify Joining Columns from Second
DataColumn[] childcolumns = new DataColumn[SJC.Length];

for(int i = 0; i < childcolumns.Length; i++)
{
childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];
}

//Create DataRelation
DataRelation r = new DataRelation(string.Empty,parentcolumns,childcolumns,false);
ds.Relations.Add(r);

//Create Columns for JOIN table
for(int i = 0; i < First.Columns.Count; i++)
{
table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
}

for(int i = 0; i < Second.Columns.Count; i++)
{
//Beware Duplicates
if(!table.Columns.Contains(Second.Columns[i].ColumnName))
table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);
else
table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);
}


//Loop through First table
table.BeginLoadData();

foreach(DataRow firstrow in ds.Tables[0].Rows)
{
//Get "joined" rows
DataRow[] childrows = firstrow.GetChildRows(r);
if(childrows != null && childrows.Length > 0)
{
object[] parentarray = firstrow.ItemArray;
foreach(DataRow secondrow in childrows)
{
object[] secondarray = secondrow.ItemArray;
object[] joinarray = new object[parentarray.Length+secondarray.Length];
Array.Copy(parentarray,0,joinarray,0,parentarray.Length);
Array.Copy(secondarray,0,joinarray,parentarray.Length,secondarray.Length);
table.LoadDataRow(joinarray,true);
}
}
else
{
object[] parentarray = firstrow.ItemArray;
object[] joinarray = new object[parentarray.Length];
Array.Copy(parentarray,0,joinarray,0,parentarray.Length);
table.LoadDataRow(joinarray,true);
}
}
table.EndLoadData();
}

return table;
}

public static DataTable Join (DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
{
return SQLOps.Join(First, Second, new DataColumn[]{FJC}, new DataColumn[]{SJC});
}

public static DataTable Join (DataTable First, DataTable Second, string FJC, string SJC)
{
return SQLOps.Join(First, Second, new DataColumn[]{First.Columns[FJC]}, new DataColumn[]{First.Columns[SJC]});
}
}
}

# Works Fantasticly

left by Keen Bean at 11/11/2004 10:31 AM Gravatar
This works like a dream and has stream-lined my code incredibly... thanking you!

# DataTable Relational Operators in C# - JOIN Method

left by Little Tidbits of Random Knowled at 1/27/2005 12:02 PM Gravatar

# re: DataTable Relational Operators in C# - JOIN Method

left by Jitendra Singh at 7/25/2005 8:17 PM Gravatar

Thanks a lot, this code is really good.

# re: DataTable Relational Operators in C# - JOIN Method

left by Mike at 8/7/2005 5:54 AM Gravatar
Could someone post an example of the method call. I tried passing in the column names and the column objects and it doesn't work for me.

Thanks Mike

# re: DataTable Relational Operators in C# - JOIN Method

left by Sam at 9/10/2005 5:52 AM Gravatar
Works really well. Has saved me much time. Thank you so much.

# re: DataTable Relational Operators in C# - JOIN Method

left by Ritesh Ramesh at 11/8/2005 3:56 AM Gravatar
Im nottoo sure but looks like the following code

public static DataTable Join (DataTable First, DataTable Second, string FJC, string SJC)
{
return SQLOps.Join(First, Second, new DataColumn[]{First.Columns[FJC]}, new DataColumn[]{First.Columns[SJC]});
}

Should have been

public static DataTable Join (DataTable First, DataTable Second, string FJC, string SJC)
{
return SQLOps.Join(First, Second, new DataColumn[]{First.Columns[FJC]}, new DataColumn[]{Second.Columns[SJC]});
}

Most people have probably join on similar column names hence may not have realised the difference. But this is easily fixed.

RR

# re: DataTable Relational Operators in C# - JOIN Method

left by Chad Hughes at 12/3/2005 3:03 AM Gravatar
SQLOps, Is this from a .NET class?

# DataTable JOIN in C#

left by Hard Rotten Milk at 2/2/2006 12:38 PM Gravatar

# re: DataTable Relational Operators in C# - JOIN Method

left by Rajeev Ranjan at 4/22/2006 3:36 AM Gravatar
Chad, from Kirk's example you can make out that its a user defined class.
Mike, the method call would be simply something like:
System.Data.DataTable table1 = new System.Data.DataTable("JoinTable1");
System.Data.DataTable table2 = new System.Data.DataTable("JoinTable2");
System.Data.DataTable dt = Join(table1,table2, "Id", "Id");

And before calling the method you'll populate the tables. ;)

# re: DataTable Relational Operators in C# - JOIN Method

left by Michael Rivera at 6/23/2006 7:52 AM Gravatar
Nice work.
However, I am unable to get the Left Outer Join example to work.

# re: DataTable Relational Operators in C# - JOIN Method

left by Michael Rivera at 6/23/2006 8:02 AM Gravatar
I figured out my problem with the left outer join. It was developer error. Disregard previous message

# re: DataTable Relational Operators in C# - JOIN Method

left by Ingo at 9/19/2006 9:23 PM Gravatar
Thank you for the nice work. Saved me much time.

# re: DataTable Relational Operators in C# - JOIN Method

left by Alan Heywood at 9/22/2006 3:32 PM Gravatar
Thanks for this, what a time saver.

# re: DataTable Relational Operators in C# - JOIN Method

left by iggydarsa at 3/8/2007 8:51 AM Gravatar
SAVED TIME< THANKS!!!

I think I found a bug tho...

Instead of;
public static DataTable Join (DataTable First, DataTable Second, string FJC, string SJC)
{
return SQLOps.Join(First, Second, new DataColumn[]{First.Columns[FJC]}, new DataColumn[]{First.Columns[SJC]});
}


it should be;

public static DataTable Join (DataTable First, DataTable Second, string FJC, string SJC)
{
return SQLOps.Join(First, Second, new DataColumn[]{First.Columns[FJC]}, new DataColumn[]{Second.Columns[SJC]});
}


If I'm wrong please let me know.

# re: DataTable Relational Operators in C# - JOIN Method

left by Steve Stokes at 6/8/2007 11:15 PM Gravatar
Thank you SO MUCH for this code, it has helped us EMMENSLY!!! You saved my job dude! thanks!!!!!!!

# re: DataTable Relational Operators in C# - JOIN Method

left by Kris at 6/29/2007 7:31 PM Gravatar
Worked great for my needs...I had to add support for deleted rows, the code will try to put them in the resulting join.

I just checked for row state = deleted after this statement and again for the child rows:

DataRow[] childrows = firstrow.GetChildRows(r);

Thanks!

# re: DataTable Relational Operators in C# - JOIN Method

left by Yibing at 7/7/2007 4:49 AM Gravatar
This is very nice and exactly what I want to do. However, I got an error on the following line:

//Create DataRelation
DataRelation r = new DataRelation(string.Empty,parentcolumns,childcolumns,false);
ds.Relations.Add(r);

complaining about the column types are different between parentcolumns and childcolumns.

Basically I loaded one Datatable for SQL server and another one from Oracle. One in SQL is using "int" and one in Oracle is using "Number". How could I improve it so that this program can run as long as the values match?

Thanks!

Yibing

# re: DataTable Relational Operators in C# - JOIN Method

left by Raman Sachin Omar at 9/14/2007 3:46 PM Gravatar
Very Fine Job.It's was very time saving for me

# There is an article in MSDN

left by Sergey Radkevich at 10/25/2007 11:11 PM Gravatar
http://support.microsoft.com/kb/326080

# re: DataTable Relational Operators in C# - JOIN Method

left by Tigris at 1/18/2008 9:36 AM Gravatar
Yes iggydarsa, I agree with you... that is a bug. I know this post is kind of aged, but I only found it yesterday and it is very helpful to me. Thank you!!

# re: DataTable Relational Operators in C# - JOIN Method

left by Pal at 2/18/2008 6:47 AM Gravatar
Hey David/Kirk:

Thanks a BIG BUNCH for the c# equivalent for the Left join in C#. It worked like a charm!!!!!!!!!!!!!! This saved me tons and tons of work and pain. If not for this code, I would have been going around and around and wasting time.

Thanks again!!!

-Pal

# re: DataTable Relational Operators in C# - JOIN Method

left by Harris at 3/12/2008 2:18 AM Gravatar
What would the full outer join look like. I am having trouble converting it. Thanks.

# re: DataTable Relational Operators in C# - JOIN Method

left by Pablo Alejandro Pérez Acosta at 5/9/2008 9:14 AM Gravatar
Very Thanks for your code, It worked smoothly on my app...

# re: DataTable Relational Operators in C# - JOIN Method

left by pako at 9/25/2008 7:41 PM Gravatar
thx man

# re: DataTable Relational Operators in C# - JOIN Method

left by Sergey D at 11/20/2008 8:52 AM Gravatar
It works great. Thank you.

Just one thing. I'm using Russian locale, so temporary DataSet:

using(DataSet ds = new DataSet())

is created with locale ru-Ru. Due to this, relation r couldn't be added. So I had to set locale as English manually. After that all worked fine.

# re: DataTable Relational Operators in C# - JOIN Method

left by Amar at 1/30/2009 9:14 PM Gravatar
Thanks for the listing. Works fine for my requirement.

# re: DataTable Relational Operators in C# - JOIN Method

left by Maw at 3/9/2009 11:23 AM Gravatar
Hi

I know how to join two tables using one field.

Dim dtJoin As DataTable = _Join.Join(dt1, dt2, "id", "id")

What about if you want to join based on 2 or 3 fields.
e.g
select * from tbl1 INNER JOIN tbl2
ON tbl1.id=tbl2.id and tbl1.fname=tbl2.fname
and tbl1.lname = tbl2.lname?

Lastly how do you use datacolumn paramater in the code?

Thank you.

# re: DataTable Relational Operators in C# - JOIN Method

left by Quinton at 6/18/2009 5:23 PM Gravatar
Hi - Great piece of code - thank you so much! One small request - Please can you provide me with code (VB) for a LEFT JOIN, if possible. Thank you.

# re: DataTable Relational Operators in C# - JOIN Method

left by Manduca at 8/25/2009 10:45 PM Gravatar
There is also a kb artcile from MS on
HOW TO: Implement a DataSet JOIN helper class
http://support.microsoft.com/kb/326080/en-us

I use this helper class and it works quite good for me.

# re: DataTable Relational Operators in C# - JOIN Method

left by Indika at 8/27/2009 10:07 PM Gravatar
Please tell me how can i find the class "SQLOps"...........
Need your help....

# re: DataTable Relational Operators in C# - JOIN Method

left by Pablo at 11/25/2009 10:30 AM Gravatar
The same question, there is an object called "SQLOps", what type is that instance? or is this the Namespace? bye.. excelent work...

# re: DataTable Relational Operators in C# - JOIN Method

left by Vincent Rainardi at 12/24/2009 9:39 PM Gravatar
wow great piece of code. Works like a charm. Thank you David.

# re: DataTable Relational Operators in C# - JOIN Method

left by john at 2/14/2010 3:58 AM Gravatar
Hi..
Code's very good..I need help to execute ths..Wat's that SQLOps?..how to resolve that in my code..

# re: DataTable Relational Operators in C# - JOIN Method

left by shoobie at 5/19/2010 12:17 AM Gravatar
Kirk, this was a frickin life saver!

^SQLOps is what he is calling his class....
Comments have been closed on this topic.