如何用ADO.NET实现数据库的JOIN算法? 你的意思不是太明确,你是说join算法用程序设计出来,还是别的什么意思?其实数据库里面的join 是一种里集合论的 典型应用罢了;它是迪卡尔积得子集! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我想先循环DataTable1的每行取出条件值,作为DataTable2的筛选条件,用DataTable的Select方法查找DataTable中符合的记录再合并 for (each DataRow row in DataTable2.Rows){ DataRow row1 = DataTable1.Select("ID = " + DataRow.Item[2] )[0]; //... if (row1 == null) {} else {}} public enum JoinType { InnerJoin, // 内联接 LeftJoin, // (外)左联接 RightJoin, // (外)右联接 FullJoin // (外)全联接 }; public static DataTable JoinTable(DataTable dt1, DataTable dt2, DataColumn[] KeyColumn1, DataColumn[] KeyColumn2, JoinType jt)// dt1 第一个DataTable(可能来自不同的DataSet)// dt2 第二个DataTable(可能来自不同的DataSet)// KeyColumn1 dt1的关联列(可能是多个DataColumn)// KeyColumn2 dt2的关联列(可能是多个DataColumn,但与KeyColumn1的数量相同且对应的每个列类型相容)// jt 联接类型,见上可以用DataView.FindRows(), DataTable.Select()或DataRelation不知哪种方式效率高些要求完整源码,分不够可以加. 写好了帮我看看有没有问题程序如下: public static DataTable JoinTable(DataTable dt1, DataTable dt2, DataColumn[] KeyColumns1, DataColumn[] KeyColumns2, JoinType jt) { #region JoinTable #region Check Parameters int tc = 0; if (dt1 != null) tc += 1; if (dt2 != null) tc += 2; switch (tc) { case 0: return null; case 1: return dt1; case 2: return dt2; } #endregion #region Get Parent/Child DataTable ParentTable = null, ChildTable = null; DataColumn[] ParentKeys = null, ChildKeys = null; switch (jt) { case JoinType.RightJoin: ParentTable = dt2; ParentKeys = KeyColumns2; ChildTable = dt1; ChildKeys = KeyColumns1; break; default: ParentTable = dt1; ParentKeys = KeyColumns1; ChildTable = dt2; ChildKeys = KeyColumns2; break; } #endregion #region Add Return Table Columns DataTable rt = ParentTable.Clone(); foreach (DataColumn dc in ChildTable.Columns) { if (!ParentTable.Columns.Contains(dc.ColumnName)) { DataColumn NewColumn = rt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression); NewColumn.AllowDBNull = dc.AllowDBNull; NewColumn.AutoIncrement = dc.AutoIncrement; NewColumn.DefaultValue = dc.DefaultValue; NewColumn.Unique = dc.Unique; NewColumn.ColumnMapping = dc.ColumnMapping; NewColumn.Caption = dc.Caption; } } StringBuilder OrderBy = new StringBuilder(null, 50); foreach (DataColumn dc in ParentKeys) OrderBy.Append("," + dc.ColumnName); if (OrderBy.Length > 0) OrderBy.Remove(0, 1); #endregion #region Add Column Values of Parent and Child Table DataView dv = ChildTable.DefaultView; dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ParentTable.Rows) { ArrayList KeyColumnValues = new ArrayList(); foreach (DataColumn dc in ParentKeys) KeyColumnValues.Add(dr[dc]); int RelationRowIndex = dv.Find(KeyColumnValues.ToArray(typeof(object)) as object[]); if (RelationRowIndex == -1) { if (jt != JoinType.InnerJoin) rt.ImportRow(dr); } else { DataRow NewRow = rt.NewRow(); foreach (DataColumn dc in ParentTable.Columns) NewRow[dc.ColumnName] = dr[dc.ColumnName]; foreach (DataColumn dc in ChildTable.Columns) if (!ParentTable.Columns.Contains(dc.ColumnName)) NewRow[dc.ColumnName] = dv[RelationRowIndex][dc.ColumnName]; rt.Rows.Add(NewRow); } } #endregion #region Add Column Values of Child Table if (jt == JoinType.FullJoin) { dv = ParentTable.DefaultView; dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ChildTable.Rows) { ArrayList KeyColumnValues = new ArrayList(); foreach (DataColumn dc in ChildKeys) KeyColumnValues.Add(dr[dc]); int RelationRowIndex = dv.Find(KeyColumnValues.ToArray(typeof(object)) as object[]); if (RelationRowIndex == -1) rt.ImportRow(dr); } } #endregion rt.AcceptChanges(); return rt; #endregion } 又改了一下: public static DataTable JoinTable(DataTable dt1, DataTable dt2, DataColumn[] KeyColumns1, DataColumn[] KeyColumns2, JoinType jt) { #region JoinTable #region Check Parameters int tc = 0; if (dt1 != null) tc += 1; if (dt2 != null) tc += 2; switch (tc) { case 0: return null; case 1: return dt1; case 2: return dt2; } if (KeyColumns1.Length != KeyColumns2.Length) throw new Exception("Number of key columns is not matched."); #endregion #region Get Parent/Child DataTable ParentTable = null, ChildTable = null; DataColumn[] ParentKeys = null, ChildKeys = null; switch (jt) { case JoinType.RightJoin: ParentTable = dt2; ParentKeys = KeyColumns2; ChildTable = dt1; ChildKeys = KeyColumns1; break; default: ParentTable = dt1; ParentKeys = KeyColumns1; ChildTable = dt2; ChildKeys = KeyColumns2; break; } #endregion #region Add Return Table Columns DataTable rt = ParentTable.Clone(); foreach (DataColumn dc in ChildTable.Columns) { if (!ParentTable.Columns.Contains(dc.ColumnName)) { DataColumn NewColumn = rt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression); NewColumn.AllowDBNull = dc.AllowDBNull; NewColumn.AutoIncrement = dc.AutoIncrement; NewColumn.DefaultValue = dc.DefaultValue; NewColumn.Unique = dc.Unique; NewColumn.ColumnMapping = dc.ColumnMapping; NewColumn.Caption = dc.Caption; } } StringBuilder OrderBy = new StringBuilder(null, 50); foreach (DataColumn dc in ParentKeys) OrderBy.Append("," + dc.ColumnName); if (OrderBy.Length > 0) OrderBy.Remove(0, 1); #endregion #region Add Column Values of Parent and Child Table DataView dv = ChildTable.DefaultView; dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ParentTable.Rows) { ArrayList KeyColumnValues = new ArrayList(); foreach (DataColumn dc in ParentKeys) KeyColumnValues.Add(dr[dc]); DataRowView[] RelationRows = dv.FindRows(KeyColumnValues.ToArray(typeof(object)) as object[]); if (RelationRows.Length == 0) { if (jt != JoinType.InnerJoin) rt.ImportRow(dr); } else { foreach (DataRowView drvs in RelationRows) { DataRow NewRow = rt.NewRow(); foreach (DataColumn dc in ParentTable.Columns) NewRow[dc.ColumnName] = dr[dc.ColumnName]; foreach (DataColumn dc in ChildTable.Columns) if (!ParentTable.Columns.Contains(dc.ColumnName)) NewRow[dc.ColumnName] = drvs[dc.ColumnName]; rt.Rows.Add(NewRow); } } } #endregion #region Add Column Values of Child Table if (jt == JoinType.FullJoin) { dv = ParentTable.DefaultView; dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ChildTable.Rows) { ArrayList KeyColumnValues = new ArrayList(); foreach (DataColumn dc in ChildKeys) KeyColumnValues.Add(dr[dc]); int RelationRowIndex = dv.Find(KeyColumnValues.ToArray(typeof(object)) as object[]); if (RelationRowIndex == -1) rt.ImportRow(dr); } } #endregion rt.AcceptChanges(); return rt; #endregion }生成的表中如果dt2没有与dt1关联的列,其相应列全为DBNull 倾注所有分,求解答这段关于GPIO的程序 复合控件添加事件响应 C#类构造函数重载问题 各位,请帮忙一下,小女子在此谢了!看不到设计界面,只看到了代码! CSV文件格式问题 一道题目的算法,求解!!! 成都的朋友来看看 如果用自定义ScrollBar控件控制TreeView滚动 恳求中大型 项目源码!大哥帮忙啊 ! .net UpdatePanel获取Request.Files? 有没有比较空闲的人 ----“中国铁路中国造 坚决反对日本介入”网络大签名 在js文件中如何计算2个日期值之间相差的天数?
{
DataRow row1 = DataTable1.Select("ID = " + DataRow.Item[2] )[0];
//...
if (row1 == null)
{}
else
{}
}
{
InnerJoin, // 内联接
LeftJoin, // (外)左联接
RightJoin, // (外)右联接
FullJoin // (外)全联接
}; public static DataTable JoinTable(DataTable dt1, DataTable dt2, DataColumn[] KeyColumn1, DataColumn[] KeyColumn2, JoinType jt)// dt1 第一个DataTable(可能来自不同的DataSet)
// dt2 第二个DataTable(可能来自不同的DataSet)
// KeyColumn1 dt1的关联列(可能是多个DataColumn)
// KeyColumn2 dt2的关联列(可能是多个DataColumn,但与KeyColumn1的数量相同且对应的每个列类型相容)
// jt 联接类型,见上
可以用DataView.FindRows(), DataTable.Select()或DataRelation不知哪种方式效率高些要求完整源码,分不够可以加.
{
#region JoinTable
#region Check Parameters
int tc = 0;
if (dt1 != null)
tc += 1; if (dt2 != null)
tc += 2; switch (tc)
{
case 0:
return null;
case 1:
return dt1;
case 2:
return dt2;
}
#endregion
#region Get Parent/Child
DataTable ParentTable = null, ChildTable = null;
DataColumn[] ParentKeys = null, ChildKeys = null;
switch (jt)
{
case JoinType.RightJoin:
ParentTable = dt2;
ParentKeys = KeyColumns2;
ChildTable = dt1;
ChildKeys = KeyColumns1;
break;
default:
ParentTable = dt1;
ParentKeys = KeyColumns1;
ChildTable = dt2;
ChildKeys = KeyColumns2;
break;
}
#endregion
#region Add Return Table Columns
DataTable rt = ParentTable.Clone(); foreach (DataColumn dc in ChildTable.Columns)
{
if (!ParentTable.Columns.Contains(dc.ColumnName))
{
DataColumn NewColumn = rt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
NewColumn.AllowDBNull = dc.AllowDBNull;
NewColumn.AutoIncrement = dc.AutoIncrement;
NewColumn.DefaultValue = dc.DefaultValue;
NewColumn.Unique = dc.Unique;
NewColumn.ColumnMapping = dc.ColumnMapping;
NewColumn.Caption = dc.Caption;
}
} StringBuilder OrderBy = new StringBuilder(null, 50);
foreach (DataColumn dc in ParentKeys)
OrderBy.Append("," + dc.ColumnName); if (OrderBy.Length > 0)
OrderBy.Remove(0, 1);
#endregion
#region Add Column Values of Parent and Child Table
DataView dv = ChildTable.DefaultView;
dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ParentTable.Rows)
{
ArrayList KeyColumnValues = new ArrayList();
foreach (DataColumn dc in ParentKeys)
KeyColumnValues.Add(dr[dc]); int RelationRowIndex = dv.Find(KeyColumnValues.ToArray(typeof(object)) as object[]);
if (RelationRowIndex == -1)
{
if (jt != JoinType.InnerJoin)
rt.ImportRow(dr);
}
else
{
DataRow NewRow = rt.NewRow();
foreach (DataColumn dc in ParentTable.Columns)
NewRow[dc.ColumnName] = dr[dc.ColumnName]; foreach (DataColumn dc in ChildTable.Columns)
if (!ParentTable.Columns.Contains(dc.ColumnName))
NewRow[dc.ColumnName] = dv[RelationRowIndex][dc.ColumnName]; rt.Rows.Add(NewRow);
}
}
#endregion
#region Add Column Values of Child Table
if (jt == JoinType.FullJoin)
{
dv = ParentTable.DefaultView;
dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ChildTable.Rows)
{
ArrayList KeyColumnValues = new ArrayList();
foreach (DataColumn dc in ChildKeys)
KeyColumnValues.Add(dr[dc]); int RelationRowIndex = dv.Find(KeyColumnValues.ToArray(typeof(object)) as object[]);
if (RelationRowIndex == -1)
rt.ImportRow(dr);
}
}
#endregion
rt.AcceptChanges();
return rt;
#endregion
}
{
#region JoinTable
#region Check Parameters
int tc = 0;
if (dt1 != null)
tc += 1; if (dt2 != null)
tc += 2; switch (tc)
{
case 0:
return null;
case 1:
return dt1;
case 2:
return dt2;
} if (KeyColumns1.Length != KeyColumns2.Length)
throw new Exception("Number of key columns is not matched.");
#endregion
#region Get Parent/Child
DataTable ParentTable = null, ChildTable = null;
DataColumn[] ParentKeys = null, ChildKeys = null;
switch (jt)
{
case JoinType.RightJoin:
ParentTable = dt2;
ParentKeys = KeyColumns2;
ChildTable = dt1;
ChildKeys = KeyColumns1;
break;
default:
ParentTable = dt1;
ParentKeys = KeyColumns1;
ChildTable = dt2;
ChildKeys = KeyColumns2;
break;
}
#endregion
#region Add Return Table Columns
DataTable rt = ParentTable.Clone(); foreach (DataColumn dc in ChildTable.Columns)
{
if (!ParentTable.Columns.Contains(dc.ColumnName))
{
DataColumn NewColumn = rt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
NewColumn.AllowDBNull = dc.AllowDBNull;
NewColumn.AutoIncrement = dc.AutoIncrement;
NewColumn.DefaultValue = dc.DefaultValue;
NewColumn.Unique = dc.Unique;
NewColumn.ColumnMapping = dc.ColumnMapping;
NewColumn.Caption = dc.Caption;
}
} StringBuilder OrderBy = new StringBuilder(null, 50);
foreach (DataColumn dc in ParentKeys)
OrderBy.Append("," + dc.ColumnName); if (OrderBy.Length > 0)
OrderBy.Remove(0, 1);
#endregion
#region Add Column Values of Parent and Child Table
DataView dv = ChildTable.DefaultView;
dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ParentTable.Rows)
{
ArrayList KeyColumnValues = new ArrayList();
foreach (DataColumn dc in ParentKeys)
KeyColumnValues.Add(dr[dc]); DataRowView[] RelationRows = dv.FindRows(KeyColumnValues.ToArray(typeof(object)) as object[]);
if (RelationRows.Length == 0)
{
if (jt != JoinType.InnerJoin)
rt.ImportRow(dr);
}
else
{
foreach (DataRowView drvs in RelationRows)
{
DataRow NewRow = rt.NewRow();
foreach (DataColumn dc in ParentTable.Columns)
NewRow[dc.ColumnName] = dr[dc.ColumnName]; foreach (DataColumn dc in ChildTable.Columns)
if (!ParentTable.Columns.Contains(dc.ColumnName))
NewRow[dc.ColumnName] = drvs[dc.ColumnName]; rt.Rows.Add(NewRow);
}
}
}
#endregion
#region Add Column Values of Child Table
if (jt == JoinType.FullJoin)
{
dv = ParentTable.DefaultView;
dv.Sort = OrderBy.ToString(); foreach (DataRow dr in ChildTable.Rows)
{
ArrayList KeyColumnValues = new ArrayList();
foreach (DataColumn dc in ChildKeys)
KeyColumnValues.Add(dr[dc]); int RelationRowIndex = dv.Find(KeyColumnValues.ToArray(typeof(object)) as object[]);
if (RelationRowIndex == -1)
rt.ImportRow(dr);
}
}
#endregion
rt.AcceptChanges();
return rt;
#endregion
}生成的表中如果dt2没有与dt1关联的列,其相应列全为DBNull