程序里取得2个表分别为：Table1，Table2
table1如下:
id
3
2
table2如下：
id mname mno ano aname
2 aa 399 399 aa
3 aa 399 399 aa
4 ss 400 400 ss
5 ss 400 399 aa
6 ew 345 345 ew
现在想根据id把table2中的mname，mno，aname列包括对应的值插入到table1中
C#怎么写
table1如下:
id
3
2
table2如下：
id mname mno ano aname
2 aa 399 399 aa
3 aa 399 399 aa
4 ss 400 400 ss
5 ss 400 399 aa
6 ew 345 345 ew
现在想根据id把table2中的mname，mno，aname列包括对应的值插入到table1中
C#怎么写
dt1.Columns.Add("id");
dt1.Columns.Add("mname");
dt1.Columns.Add("mno");
dt1.Columns.Add("aname"); dt1.Rows.Add("2", "", "", "");
dt1.Rows.Add("3", "", "", ""); DataTable dt2 = new DataTable();
dt2.Columns.Add("id");
dt2.Columns.Add("mname");
dt2.Columns.Add("mno");
dt2.Columns.Add("ano");
dt2.Columns.Add("aname");
dt2.Rows.Add("2", "aa", "21", "aa", "aa");
dt2.Rows.Add("3", "bb", "31", "bb", "bb");
dt2.Rows.Add("4", "cc", "41", "cc", "cc");
dt2.Rows.Add("5", "aa", "21", "aa", "aa");
dt2.Rows.Add("6", "bb", "31", "bb", "bb");
dt2.Rows.Add("7", "cc", "41", "cc", "cc");
Stopwatch sw = new Stopwatch();
sw.Start();
Dictionary<string, DataRow> dict = new Dictionary<string, DataRow>();
foreach(DataRow aa in dt1.Rows)
dict[aa["id"] as string] = null; foreach (DataRow aa in dt2.Rows)
{
string key = aa["id"] as string;
if(dict.ContainsKey(key))
{
dict[key] = aa;
}
}
dt1.Clear();
foreach(DataRow aa in dict.Values)
dt1.Rows.Add(aa["id"], aa["mname"], aa["mno"], aa["aname"]);
sw.Stop();
Console.WriteLine(sw.ElapsedMilliseconds);
/// <summary>
/// 合并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)
{
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>
/// <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>
/// <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 (IsNumType(column))
{
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 (IsNumType(column))
{
col.DefaultValue = 0;
}
result.Columns.Add(col);
} return result;
} private static bool IsNumType(DataColumn column)
{
return column.DataType == typeof(Int32)
|| column.DataType == typeof(Decimal)
|| column.DataType == typeof(Int16)
|| column.DataType == typeof(Int64)
|| column.DataType == typeof(Single)
|| column.DataType == typeof(Double)
|| column.DataType == typeof(Byte) ;
}
#endregion #region Public Static Method
public static DataTable LeftJoin(Joiner.JoinDelegate joinDelegate,DataTable left, params DataTable[] joinTBs)
{
return Join(JoinType.Left, joinDelegate, left, joinTBs);
} public static DataTable InnerJoin(Joiner.JoinDelegate joinDelegate, DataTable left, params DataTable[] joinTBs)
{
return Join(JoinType.Left, joinDelegate, left, joinTBs);
} public static DataTable FullJoin(Joiner.JoinDelegate joinDelegate, DataTable left, params DataTable[] joinTBs)
{
return Join(JoinType.Left, joinDelegate, left, joinTBs);
} public static DataTable Join(JoinType joinType,Joiner.JoinDelegate joinDelegate,DataTable left, params DataTable[] joinTBs)
{
DataTableJoiner tableJoiner = new DataTableJoiner(left); foreach (DataTable rightTb in joinTBs)
{
tableJoiner.Joiners.Add(new Joiner(joinType, rightTb, joinDelegate));
}
return tableJoiner.Execute();
}
#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
}
使用举例 [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(); DataTable result = DataTableJoiner.LeftJoin(delegate(DataRow leftRow) { return String.Format("id={0}", leftRow["id"]); }, left,
right);
result.TableName = "result";
result.WriteXml(Console.Out);
}
DataTable t1 = new DataTable("t1");
DataTable t2 = new DataTable("t2");
DataTable t3 = new DataTable("t3"); t1.Columns.Add("id", typeof(int));
t1.Rows.Add(2);
t1.Rows.Add(3); t2.Columns.Add("id", typeof(int));
t2.Columns.Add("mname", typeof(string));
t2.Columns.Add("mno", typeof(string));
t2.Columns.Add("ano", typeof(string));
t2.Columns.Add("aname", typeof(string)); t2.Rows.Add(2, "aa", "399", "399", "aa");
t2.Rows.Add(3, "aa", "399", "399", "aa");
t2.Rows.Add(4, "ss", "400", "400", "ss");
t2.Rows.Add(5, "ss", "400", "399", "aa");
t2.Rows.Add(6, "ew", "345", "345", "ew"); t3 = t2.Clone();
foreach (DataRow dr in t1.Rows)
{
foreach(DataRow dr2 in t2.Rows)
{
if(int.Equals(dr2["id"],dr["id"]))
{
t3.Rows.Add(dr2["id"], dr2["mname"], dr2["mno"], dr2["ano"], dr2["aname"]);
}
}
}
看看啊是你要的效果？
然后table1克隆table2的表结构
然后放进去~~
dt1.Columns.Add("ID",typeof(int));
dt1.Columns.Add("Name",typeof(string));
dt1.PrimaryKey=new DataColumn[] { dt1.Columns[0] }; DataTable dt2=new DataTable();
dt2.Columns.Add("ID",typeof(int));
dt2.Columns.Add("MC",typeof(string));DataTable dt3= dt1.Copy();
dt3.Merge(dt2);
DataTable curDt = dt2.Copy(); //curDt存放合并后的值
if(!curDt.Columns.Cotains("B")) curDt.Columns.Add("B"); for(int r = 0; r < dt2.Rows.Count; r++)
{
DataRow dr = dt2.Rows[r];
string aValue = dr["A"].ToString().ToUpper();
foreach(DataRow tDr in dt1.Rows)
{
string tValue = tDr["A"].ToString().ToUpper();
if(tValue == aValue)
{
curDt.Rows[r]["B"] = tDr["B"];
break;
}
}
}
ds.Relation.Add 设置父子表