分别建立二个DataTable，然后建立DataRelation，最后根据关联再产生DataTable，
其栏位有DEPT,LEADER, COSTCENTER, EMPNO, CNAME, EXT。※请分别以表一为主，表二为副产生一个表； 以表二为主，表一为副产生另一个表表一的栏位有 DEPT LEADER COSTCENTER
表二的栏位有 EMPNO CNAME DEPT EXT我的代码 两个表建好了 datarelation也写了 就差循环遍历和显示到浏览器上 楼下放上代码 该怎么写了 大家帮忙
其栏位有DEPT,LEADER, COSTCENTER, EMPNO, CNAME, EXT。※请分别以表一为主，表二为副产生一个表； 以表二为主，表一为副产生另一个表表一的栏位有 DEPT LEADER COSTCENTER
表二的栏位有 EMPNO CNAME DEPT EXT我的代码 两个表建好了 datarelation也写了 就差循环遍历和显示到浏览器上 楼下放上代码 该怎么写了 大家帮忙
public partial class _Default : System.Web.UI.Page
{
private DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt1 = new DataTable("ParentTable");
DataColumn dc;
DataRow dr; dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "DEPT";
dc.AllowDBNull = false;
dt1.Columns.Add(dc); //Create new DataRow objects and add to DataTable
dr = dt1.NewRow();
dr["DEPT"] = "tyu";
dr["LEADER"] = "guj/CTDSP";
dr["COSTCENTER"] = "9001S300";
dt1.Rows.Add(dr); GridView1.DataSource = dt1;
GridView1.DataBind(); //-----Create second table
DataTable dt2 = new DataTable("childTable"); dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = "EMPNO";
dc.AllowDBNull = false;
dt2.Columns.Add(dc); //Create new row
dr = dt2.NewRow();
dr["EMPNO"] = "yui0055";
dr["CNAME"] = "Ajuio Chen";
dr["DEPT"] = "fghj";
dr["EXT"] = "2211";
dt2.Rows.Add(dr); GridView2.DataSource = dt2;
GridView2.DataBind(); DataTable dt3 = new DataTable();
dc = new DataColumn(); }
private System.Data.DataSet dataSet; private void MakeDataRelation()
{
DataColumn parentColumn =
dataSet.Tables["ParentTable"].Columns["DEPT"];
DataColumn childColumn =
dataSet.Tables["ChildTable"].Columns["DEPT"];
DataRelation relation = new
DataRelation("parent2Child", parentColumn, childColumn);
dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
不知道我的datarelation对不对
下面该怎么写了
/// 合并DataTable,模拟SQL中的join语句
/// </summary>
public class DataTableJoiner
{
#region Variables
private DataTable left;
private List<Joiner> _joiners = new List<Joiner>();
#endregion #region Constructor
public DataTableJoiner(DataTable left)
{
this.Left = left;
}
#endregion #region Property
public DataTable Left
{
get { return left; }
set { left = value; }
} public List<Joiner> Joiners
{
get { return _joiners; }
set { _joiners = value; }
}
#endregion #region Public Method
public DataTable Execute()
{
foreach (Joiner joiner in _joiners)
{
switch (joiner.JoinType)
{
case JoinType.Left:
LeftJoin(joiner);
break;
case JoinType.Inner:
InnerJoin(joiner);
break;
case JoinType.Full:
FullJoin(joiner);
break;
}
} return left;
}
public DataTable Join()
{
return Execute();
}
#endregion #region Private Metod
/// <summary>
/// 内连接,实现SQL中的inner join
/// </summary>
/// <param name="on">连接条件</param>
/// <returns></returns>
private void InnerJoin(Joiner joiner)
{
DataTable result = MergeColumns(joiner.Right); foreach (DataRow leftRow in Left.Rows)
{
DataRow[] rightRows = joiner.Right.Select(joiner.On(leftRow)); foreach (DataRow rightRow in rightRows)
{
DataRow mergedRow = result.NewRow(); CopyRow(leftRow, mergedRow);
CopyRow(rightRow, mergedRow); result.Rows.Add(mergedRow);
result.AcceptChanges();
}
} left = result;
} /// <summary>
/// 左连接,实现SQL中的inner join
/// </summary>
/// <param name="on">连接条件</param>
/// <returns></returns>
private void LeftJoin(Joiner joiner)
{
DataTable result = MergeColumns(joiner.Right); foreach (DataRow leftRow in Left.Rows)
{
DataRow[] rightRows = joiner.Right.Select(joiner.On(leftRow));
if (rightRows.Length == 0)
{
DataRow mergedRow = result.NewRow();
CopyRow(leftRow, mergedRow);
result.Rows.Add(mergedRow);
result.AcceptChanges();
}
else
{
foreach (DataRow rightRow in rightRows)
{
DataRow mergedRow = result.NewRow(); CopyRow(leftRow, mergedRow);
CopyRow(rightRow, mergedRow); result.Rows.Add(mergedRow);
result.AcceptChanges();
}
}
} left = result;
} /// <summary>
/// 全连接,实现SQL中的inner join
/// </summary>
/// <returns></returns>
private void FullJoin(Joiner joiner)
{
try
{ DataTable result = MergeColumns(joiner.Right);
List<DataRow> mergedRows = new List<DataRow>();
foreach (DataRow leftRow in Left.Rows)
{
DataRow[] rightRows = joiner.Right.Select(joiner.On(leftRow)); if (rightRows.Length == 0)
{
DataRow mergedRow = result.NewRow();
CopyRow(leftRow, mergedRow);
result.Rows.Add(mergedRow);
result.AcceptChanges();
}
else
{
foreach (DataRow rightRow in rightRows)
{
DataRow mergedRow = result.NewRow(); CopyRow(leftRow, mergedRow);
CopyRow(rightRow, mergedRow); result.Rows.Add(mergedRow);
result.AcceptChanges();
}
mergedRows.AddRange(rightRows);
}
} foreach (DataRow rigtRow in joiner.Right.Rows)
{
if (mergedRows.Contains(rigtRow)) continue; DataRow mergedRow = result.NewRow();
CopyRow(rigtRow, mergedRow);
result.Rows.Add(mergedRow);
result.AcceptChanges();
} left = result;
}
catch(Exception ex)
{
throw new Exception(String.Format("Talbe:{0},Message:{1}",joiner.Right.TableName,ex.Message),ex);
}
} private static void CopyRow(DataRow sourceRow, DataRow targetRow)
{
foreach (DataColumn column in targetRow.Table.Columns)
{
if (sourceRow.Table.Columns.IndexOf(column.ColumnName) < 0) continue;
targetRow[column.ColumnName] = sourceRow[column.ColumnName];
}
} /// <summary>
/// 创建具有left和join指定列的dataTable
/// </summary>
/// <returns></returns>
private DataTable MergeColumns(DataTable right)
{
DataTable result = new DataTable(left.TableName); foreach (DataColumn column in Left.Columns)
{
DataColumn col = new DataColumn(column.ColumnName, column.DataType);
if (column.DataType == typeof(Int32) || column.DataType == typeof(Decimal))
{
col.DefaultValue = 0;
}
result.Columns.Add(col);
} foreach (DataColumn column in right.Columns)
{
if(result.Columns[column.ColumnName] != null) continue;
DataColumn col = new DataColumn(column.ColumnName, column.DataType);
if (column.DataType == typeof(Int32) || column.DataType == typeof(Decimal))
{
col.DefaultValue = 0;
}
result.Columns.Add(col);
} return result;
}
#endregion
} public class Joiner
{
/// <summary>
///
/// </summary>
/// <param name="leftRow"></param>
/// <example>
/// <code>
/// Joiner.JoinDelegate joinDelegate = delegate(DataRow leftRow) {return String.Format("Opara={0}",leftRow["Opara"]); };
/// </code>
/// </example>
/// <returns></returns>
public delegate string JoinDelegate(DataRow leftRow); private DataTable _right; private JoinType _joinType; private JoinDelegate _on; public Joiner(JoinType joinType,DataTable right,JoinDelegate on)
{
On = on;
Right = right;
JoinType = joinType;
} public JoinDelegate On
{
get { return _on; }
set { _on = value; }
} public DataTable Right
{
get { return _right; }
set { _right = value; }
} public JoinType JoinType
{
get { return _joinType; }
set { _joinType = value; }
}
} public enum JoinType
{
Left,
Inner,
Full
}
这是单元测试 [TestFixture]
public class DataTest
{
[Test]
public void InnerJoin()
{
DataTable left = new DataTable();
left.Columns.Add(new DataColumn("id",typeof(Int32)));
DataRow row = left.NewRow();
row["id"] = 1;
left.Rows.Add(row);
left.AcceptChanges(); DataRow row2 = left.NewRow();
row2["id"] = 2;
left.Rows.Add(row2);
left.AcceptChanges(); DataTable right = new DataTable();
right.Columns.Add(new DataColumn("id", typeof(Int32)));
right.Columns.Add(new DataColumn("name", typeof(String))); DataRow rightRow = right.NewRow();
rightRow["id"] = 1;
rightRow["name"] = "2b";
right.Rows.Add(rightRow);
right.AcceptChanges();
//OldDataTableJoiner joiner = new OldDataTableJoiner(left, right);
//DataTable result = joiner.InnerJoin(delegate(DataRow leftRow) { return String.Format("id={0}", leftRow["id"]); });
//result.TableName="result";
//result.WriteXml(Console.Out);
}
[Test]
public void LeftJoin()
{
DataTable left = new DataTable();
left.Columns.Add(new DataColumn("id", typeof(Int32))); DataRow row = left.NewRow();
row["id"] = 1;
left.Rows.Add(row);
left.AcceptChanges(); DataRow row2 = left.NewRow();
row2["id"] = 2;
left.Rows.Add(row2);
left.AcceptChanges(); DataTable right = new DataTable();
right.Columns.Add(new DataColumn("id", typeof(Int32)));
right.Columns.Add(new DataColumn("name", typeof(String))); DataRow rightRow = right.NewRow();
rightRow["id"] = 1;
rightRow["name"] = "2b";
right.Rows.Add(rightRow);
right.AcceptChanges();
//OldDataTableJoiner joiner = new OldDataTableJoiner(left, right);
//DataTable result = joiner.LeftJoin(delegate(DataRow leftRow) { return String.Format("id={0}", leftRow["id"]); });
//result.TableName = "result";
//result.WriteXml(Console.Out);
}
[Test]
public void FullJoin()
{
DataTable left = new DataTable();
left.Columns.Add(new DataColumn("id", typeof(Int32)));
left.Columns.Add(new DataColumn("dateTime", typeof(Int32))); DataRow row = left.NewRow();
row["id"] = 1;
left.Rows.Add(row);
left.AcceptChanges(); DataRow row2 = left.NewRow();
row2["id"] = 2;
left.Rows.Add(row2);
left.AcceptChanges(); DataTable right = new DataTable();
right.Columns.Add(new DataColumn("id", typeof(Int32)));
right.Columns.Add(new DataColumn("name", typeof(String))); DataRow rightRow1 = right.NewRow();
rightRow1["id"] = 1;
rightRow1["name"] = "1b";
right.Rows.Add(rightRow1);
right.AcceptChanges(); DataRow rightRow3 = right.NewRow();
rightRow3["id"] = 3;
rightRow3["name"] = "3b";
right.Rows.Add(rightRow3);
right.AcceptChanges(); Joiner.JoinDelegate joinDelegate =
delegate(DataRow leftRow) { return String.Format("id={0}", leftRow["id"]); }; DataTableJoiner joiner = new DataTableJoiner(left);
joiner.Joiners.Add(new Joiner(JoinType.Full,right,joinDelegate));
DataTable result = joiner.Execute();
result.TableName = "result";
result.WriteXml(Console.Out);
} }