C#两个DataTable Left join连接

作者: Robin 分类: Asp.net 发布时间: 2009-09-11 13:19

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data;

namespace JoinDataTable
{
public partial class frmMain : Form
{
DataTable table1
= null;
DataTable table2
= null;

public frmMain()
{
InitializeComponent();

table1 = new DataTable();
table1.Columns.Add(
symbol, typeof(string));
table1.Columns.Add(
side, typeof(int));
table1.Columns.Add(
qty, typeof(int));
table1.Rows.Add(
new object[] { HSI, 1, 9000});
table1.Rows.Add(
new object[] { TOPIX, 1, 2000 });
dataGridView1.DataSource
= table1.DefaultView;

table2 = new DataTable();
table2.Columns.Add(
symbol, typeof(string));
table2.Columns.Add(
side, typeof(int));
table2.Columns.Add(
qty, typeof(int));
table2.Rows.Add(
new object[] { HSI, 1, 6000 });
table2.Rows.Add(
new object[] { AEX, 1, 5000 });
dataGridView2.DataSource
= table2.DefaultView;

DataTable t3 = Join(table1, table2,
new DataColumn[] { table1.Columns[0]},
new DataColumn[] { table2.Columns[0]},
true, true);

dataGridView3.DataSource = t3.DefaultView;
}

private DataTable Join(DataTable left, DataTable right,
DataColumn[] leftCols, DataColumn[] rightCols,
bool includeLeftJoin, bool includeRightJoin)
{
DataTable result
= new DataTable(JoinResult);
using (DataSet ds = new DataSet())
{
ds.Tables.AddRange(
new DataTable[] { left.Copy(), right.Copy() });
DataColumn[] leftRelationCols
= new DataColumn[leftCols.Length];
for (int i = 0; i < leftCols.Length; i++)
leftRelationCols[i]
= ds.Tables[0].Columns[leftCols[i].ColumnName];

DataColumn[] rightRelationCols = new DataColumn[rightCols.Length];
for (int i = 0; i < rightCols.Length; i++)
rightRelationCols[i]
= ds.Tables[1].Columns[rightCols[i].ColumnName];

//create result columns
for (int i = 0; i < left.Columns.Count; i++)
result.Columns.Add(left.Columns[i].ColumnName, left.Columns[i].DataType);
for (int i = 0; i < right.Columns.Count; i++)
{
string colName = right.Columns[i].ColumnName;
while (result.Columns.Contains(colName))
colName
+= _2;
result.Columns.Add(colName, right.Columns[i].DataType);
}

//add left join relations
DataRelation drLeftJoin = new DataRelation(rLeft, leftRelationCols, rightRelationCols, false);
ds.Relations.Add(drLeftJoin);

//join
result.BeginLoadData();
foreach (DataRow parentRow in ds.Tables[0].Rows)
{
DataRow[] childrenRowList
= parentRow.GetChildRows(drLeftJoin);
if (childrenRowList != null && childrenRowList.Length > 0)
{
object[] parentArray = parentRow.ItemArray;
foreach (DataRow childRow in childrenRowList)
{
object[] childArray = childRow.ItemArray;
object[] joinArray = new object[parentArray.Length + childArray.Length];
Array.Copy(parentArray,
0, joinArray, 0, parentArray.Length);
Array.Copy(childArray,
0, joinArray, parentArray.Length, childArray.Length);
result.LoadDataRow(joinArray,
true);
}
}
else //left join
{
if (includeLeftJoin)
{
object[] parentArray = parentRow.ItemArray;
object[] joinArray = new object[parentArray.Length];
Array.Copy(parentArray,
0, joinArray, 0, parentArray.Length);
result.LoadDataRow(joinArray,
true);
}
}
}

if (includeRightJoin)
{
//add right join relations
DataRelation drRightJoin = new DataRelation(rRight, rightRelationCols, leftRelationCols, false);
ds.Relations.Add(drRightJoin);

foreach (DataRow parentRow in ds.Tables[1].Rows)
{
DataRow[] childrenRowList
= parentRow.GetChildRows(drRightJoin);
if (childrenRowList == null || childrenRowList.Length == 0)
{
object[] parentArray = parentRow.ItemArray;
object[] joinArray = new object[result.Columns.Count];
Array.Copy(parentArray,
0, joinArray,
joinArray.Length
parentArray.Length, parentArray.Length);
result.LoadDataRow(joinArray,
true);
}
}
}

result.EndLoadData();
}

return result;
}

private void button1_Click(object sender, EventArgs e)
{

}
}
}

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签云