在网上查了几个例子,用起来,发现错了,是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]});
}