【转】【C#】两个DataTable关联查询（inner join、left join）C#代码
using System; using System.Collections.Generic; using System.Text; using System.Data; namespace ConsoleApplication1 { /// <summary> /// 在返回的table处大断点查看结果 /// </summary> class Program { static void Main(string[] args) { CreatTable(); } /// <summary> /// 创建两个测试表 /// </summary> public static void CreatTable() { //定义数据结构 DataTable Ks = new DataTable("客商"); //左表or主表 DataColumn dcID = new DataColumn("ID", System.Type.GetType("System.Int32")); DataColumn dcNa = new DataColumn("客商名称", System.Type.GetType("System.String")); Ks.Columns.Add(dcID); Ks.Columns.Add(dcNa); DataTable Dj = new DataTable("订单");//右表or子表 DataColumn dcID2 = new DataColumn("ID", System.Type.GetType("System.Int32")); DataColumn dcNu = new DataColumn("订单数量", System.Type.GetType("System.Int32")); Dj.Columns.Add(dcID2); Dj.Columns.Add(dcNu); //左表数据 DataRow KsDt = Ks.NewRow(); KsDt["ID"] = 11; KsDt["客商名称"] = "张三"; Ks.Rows.Add(KsDt); KsDt = Ks.NewRow(); KsDt["ID"] = 12; KsDt["客商名称"] = "李四"; Ks.Rows.Add(KsDt); KsDt = Ks.NewRow(); KsDt["ID"] = 13; KsDt["客商名称"] = "王武"; Ks.Rows.Add(KsDt); KsDt = Ks.NewRow(); KsDt["ID"] = 14; KsDt["客商名称"] = "赵柳"; Ks.Rows.Add(KsDt); //右表数据 KsDt = Dj.NewRow(); KsDt["ID"] = 11; KsDt["订单数量"] = 10; Dj.Rows.Add(KsDt); //KsDt = Dj.NewRow(); //KsDt["ID"] = 11; //KsDt["订单数量"] = 12; //Dj.Rows.Add(KsDt); //KsDt = Dj.NewRow(); //KsDt["ID"] = 12; //KsDt["订单数量"] = 16; //Dj.Rows.Add(KsDt); //KsDt = Dj.NewRow(); //KsDt["ID"] = 13; //KsDt["订单数量"] = 30; //Dj.Rows.Add(KsDt); KsDt = Dj.NewRow(); KsDt["ID"] = 14; KsDt["订单数量"] = 40; Dj.Rows.Add(KsDt); KsDt = Dj.NewRow(); KsDt["ID"] = 15; KsDt["订单数量"] = 1250; Dj.Rows.Add(KsDt); /*********************调用******************************************/ // Join(Ks, Dj, new DataColumn[] { Ks.Columns["ID"] }, new DataColumn[] { Dj.Columns["ID"] }); //重载1 Join(Ks, Dj, Ks.Columns["ID"], Dj.Columns["ID"]); //重载2 Join(Ks, Dj, "ID", "ID"); /*********************调用结束**************************************/ } /// <summary> /// 建立两内存表的链接 /// </summary> /// <param name="dt1">左表（主表）</param> /// <param name="dt2">右表</param> /// <param name="FJC">左表中关联的字段名（字符串）</param> /// <param name="SJC">右表中关联的字段名（字符串）</param> /// <returns></returns> public static DataTable Join(DataTable dt1, DataTable dt2, DataColumn[] FJC, DataColumn[] SJC) { //创建一个新的DataTable DataTable table = new DataTable("Join"); // Use a DataSet to leverage DataRelation using (DataSet ds = new DataSet()) { //把DataTable Copy到DataSet中 ds.Tables.AddRange(new DataTable[] { dt1.Copy(), dt2.Copy() }); DataColumn[] First_columns = new DataColumn[FJC.Length]; for (int i = 0; i < First_columns.Length; i++) { First_columns[i] = ds.Tables[0].Columns[FJC[i].ColumnName]; } DataColumn[] Second_columns = new DataColumn[SJC.Length]; for (int i = 0; i < Second_columns.Length; i++) { Second_columns[i] = ds.Tables[1].Columns[SJC[i].ColumnName]; } //创建关联 DataRelation r = new DataRelation(string.Empty, First_columns, Second_columns, false); ds.Relations.Add(r); //为关联表创建列 for (int i = 0; i < dt1.Columns.Count; i++) { table.Columns.Add(dt1.Columns[i].ColumnName, dt1.Columns[i].DataType); } for (int i = 0; i < dt2.Columns.Count; i++) { //看看有没有重复的列，如果有在第二个DataTable的Column的列明后加_Second if (!table.Columns.Contains(dt2.Columns[i].ColumnName)) table.Columns.Add(dt2.Columns[i].ColumnName, dt2.Columns[i].DataType); else table.Columns.Add(dt2.Columns[i].ColumnName + "_Second", dt2.Columns[i].DataType); } table.BeginLoadData(); int itable2Colomns = ds.Tables[1].Rows[0].ItemArray.Length; foreach (DataRow firstrow in ds.Tables[0].Rows) { //得到行的数据 DataRow[] childrows = firstrow.GetChildRows(r);//第二个表关联的行 if (childrows != null && childrows.Length > 0) { 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); } } else//如果有外连接(Left Join)添加这部分代码 { object[] table1array = firstrow.ItemArray;//Table1 object[] table2array = new object[itable2Colomns]; object[] joinarray = new object[table1array.Length + itable2Colomns]; Array.Copy(table1array, 0, joinarray, 0, table1array.Length); Array.Copy(table2array, 0, joinarray, table1array.Length, itable2Colomns); table.LoadDataRow(joinarray, true); DataColumn[] dc = new DataColumn[2]; dc[0] = new DataColumn(""); } } table.EndLoadData(); } return table;//***在此处打断点，程序运行后点击查看即可观察到结果 } /// <summary> /// 重载1 /// </summary> /// <param name="dt1"></param> /// <param name="dt2"></param> /// <param name="FJC"></param> /// <param name="SJC"></param> /// <returns></returns> public static DataTable Join(DataTable dt1, DataTable dt2, DataColumn FJC, DataColumn SJC) { return Join(dt1, dt2, new DataColumn[] { FJC }, new DataColumn[] { SJC }); } /// <summary> /// 重载2 /// </summary> /// <param name="dt1"></param> /// <param name="dt2"></param> /// <param name="FJC"></param> /// <param name="SJC"></param> /// <returns></returns> public static DataTable Join(DataTable dt1, DataTable dt2, string FJC, string SJC) { return Join(dt1, dt2, new DataColumn[] { dt1.Columns[FJC] }, new DataColumn[] { dt1.Columns[SJC] }); } } }
上面运行的结果：
作者：大可 • Duke
