c#实现join

在网上查了几个例子，用起来，发现错了，是left join，觉的奇怪，一时也看不出哪里不对。

后来想，说不定外国朋友也写一个，中国朋友拿过来搞错了呢。

结果真的是这样，现在把外国朋友的转过来。

 

 

 

http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx

 

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)   //这里就是join，，中国朋友写错了。。nnd。。

{

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]});

}