首页 > 代码库 > 两个DataTable关联查询（inner join、left join）C#代码
两个DataTable关联查询（inner join、left join）C#代码
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Data; 5 6 namespace ConsoleApplication1 7 { 8 /// <summary> 9 /// 在返回的table处大断点查看结果 10 /// </summary> 11 class Program 12 { 13 static void Main(string[] args) 14 { 15 CreatTable(); 16 } 17 18 /// <summary> 19 /// 创建两个测试表 20 /// </summary> 21 public static void CreatTable() 22 { 23 //定义数据结构 24 DataTable Ks = new DataTable("客商"); //左表or主表 25 DataColumn dcID = new DataColumn("ID", System.Type.GetType("System.Int32")); 26 DataColumn dcNa = new DataColumn("客商名称", System.Type.GetType("System.String")); 27 Ks.Columns.Add(dcID); 28 Ks.Columns.Add(dcNa); 29 30 DataTable Dj = new DataTable("订单");//右表or子表 31 DataColumn dcID2 = new DataColumn("ID", System.Type.GetType("System.Int32")); 32 DataColumn dcNu = new DataColumn("订单数量", System.Type.GetType("System.Int32")); 33 Dj.Columns.Add(dcID2); 34 Dj.Columns.Add(dcNu); 35 36 //左表数据 37 DataRow KsDt = Ks.NewRow(); 38 KsDt["ID"] = 11; 39 KsDt["客商名称"] = "张三"; 40 Ks.Rows.Add(KsDt); 41 42 KsDt = Ks.NewRow(); 43 KsDt["ID"] = 12; 44 KsDt["客商名称"] = "李四"; 45 Ks.Rows.Add(KsDt); 46 47 KsDt = Ks.NewRow(); 48 KsDt["ID"] = 13; 49 KsDt["客商名称"] = "王武"; 50 Ks.Rows.Add(KsDt); 51 52 KsDt = Ks.NewRow(); 53 KsDt["ID"] = 14; 54 KsDt["客商名称"] = "赵柳"; 55 Ks.Rows.Add(KsDt); 56 57 58 //右表数据 59 KsDt = Dj.NewRow(); 60 KsDt["ID"] = 11; 61 KsDt["订单数量"] = 10; 62 Dj.Rows.Add(KsDt); 63 64 //KsDt = Dj.NewRow(); 65 //KsDt["ID"] = 11; 66 //KsDt["订单数量"] = 12; 67 //Dj.Rows.Add(KsDt); 68 69 //KsDt = Dj.NewRow(); 70 //KsDt["ID"] = 12; 71 //KsDt["订单数量"] = 16; 72 //Dj.Rows.Add(KsDt); 73 74 75 //KsDt = Dj.NewRow(); 76 //KsDt["ID"] = 13; 77 //KsDt["订单数量"] = 30; 78 //Dj.Rows.Add(KsDt); 79 80 KsDt = Dj.NewRow(); 81 KsDt["ID"] = 14; 82 KsDt["订单数量"] = 40; 83 Dj.Rows.Add(KsDt); 84 85 KsDt = Dj.NewRow(); 86 KsDt["ID"] = 15; 87 KsDt["订单数量"] = 1250; 88 Dj.Rows.Add(KsDt); 89 90 /*********************调用******************************************/ 91 // 92 Join(Ks, Dj, new DataColumn[] { Ks.Columns["ID"] }, new DataColumn[] { Dj.Columns["ID"] }); 93 //重载1 94 Join(Ks, Dj, Ks.Columns["ID"], Dj.Columns["ID"]); 95 //重载2 96 Join(Ks, Dj, "ID", "ID"); 97 98 /*********************调用结束**************************************/ 99 }100 101 /// <summary>102 /// 建立两内存表的链接103 /// </summary>104 /// <param name="dt1">左表（主表）</param>105 /// <param name="dt2">右表</param>106 /// <param name="FJC">左表中关联的字段名（字符串）</param>107 /// <param name="SJC">右表中关联的字段名（字符串）</param>108 /// <returns></returns>109 public static DataTable Join(DataTable dt1, DataTable dt2, DataColumn[] FJC, DataColumn[] SJC)110 {111 //创建一个新的DataTable112 DataTable table = new DataTable("Join");113 114 // Use a DataSet to leverage DataRelation115 using (DataSet ds = new DataSet())116 {117 //把DataTable Copy到DataSet中118 ds.Tables.AddRange(new DataTable[] { dt1.Copy(), dt2.Copy() });119 120 DataColumn[] First_columns = new DataColumn[FJC.Length];121 for (int i = 0; i < First_columns.Length; i++)122 {123 First_columns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];124 }125 126 DataColumn[] Second_columns = new DataColumn[SJC.Length];127 for (int i = 0; i < Second_columns.Length; i++)128 {129 Second_columns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];130 }131 132 //创建关联133 DataRelation r = new DataRelation(string.Empty, First_columns, Second_columns, false);134 ds.Relations.Add(r);135 136 //为关联表创建列137 for (int i = 0; i < dt1.Columns.Count; i++)138 {139 table.Columns.Add(dt1.Columns[i].ColumnName, dt1.Columns[i].DataType);140 }141 142 for (int i = 0; i < dt2.Columns.Count; i++)143 {144 //看看有没有重复的列，如果有在第二个DataTable的Column的列明后加_Second145 if (!table.Columns.Contains(dt2.Columns[i].ColumnName))146 table.Columns.Add(dt2.Columns[i].ColumnName, dt2.Columns[i].DataType);147 else148 table.Columns.Add(dt2.Columns[i].ColumnName + "_Second", dt2.Columns[i].DataType);149 }150 151 table.BeginLoadData();152 int itable2Colomns = ds.Tables[1].Rows[0].ItemArray.Length;153 foreach (DataRow firstrow in ds.Tables[0].Rows)154 {155 //得到行的数据156 DataRow[] childrows = firstrow.GetChildRows(r);//第二个表关联的行157 if (childrows != null && childrows.Length > 0)158 {159 object[] parentarray = firstrow.ItemArray;160 foreach (DataRow secondrow in childrows)161 {162 object[] secondarray = secondrow.ItemArray;163 object[] joinarray = new object[parentarray.Length + secondarray.Length];164 Array.Copy(parentarray, 0, joinarray, 0, parentarray.Length);165 Array.Copy(secondarray, 0, joinarray, parentarray.Length, secondarray.Length);166 table.LoadDataRow(joinarray, true);167 }168 169 }170 else//如果有外连接(Left Join)添加这部分代码171 {172 object[] table1array = firstrow.ItemArray;//Table1173 object[] table2array = new object[itable2Colomns];174 object[] joinarray = new object[table1array.Length + itable2Colomns];175 Array.Copy(table1array, 0, joinarray, 0, table1array.Length);176 Array.Copy(table2array, 0, joinarray, table1array.Length, itable2Colomns);177 table.LoadDataRow(joinarray, true);178 DataColumn[] dc = new DataColumn[2];179 dc[0] = new DataColumn("");180 }181 }182 table.EndLoadData();183 }184 return table;//***在此处打断点，程序运行后点击查看即可观察到结果185 }186 187 /// <summary>188 /// 重载1189 /// </summary>190 /// <param name="dt1"></param>191 /// <param name="dt2"></param>192 /// <param name="FJC"></param>193 /// <param name="SJC"></param>194 /// <returns></returns>195 public static DataTable Join(DataTable dt1, DataTable dt2, DataColumn FJC, DataColumn SJC)196 {197 return Join(dt1, dt2, new DataColumn[] { FJC }, new DataColumn[] { SJC });198 }199 200 /// <summary>201 /// 重载2202 /// </summary>203 /// <param name="dt1"></param>204 /// <param name="dt2"></param>205 /// <param name="FJC"></param>206 /// <param name="SJC"></param>207 /// <returns></returns>208 public static DataTable Join(DataTable dt1, DataTable dt2, string FJC, string SJC)209 {210 return Join(dt1, dt2, new DataColumn[] { dt1.Columns[FJC] }, new DataColumn[] { dt1.Columns[SJC] });211 }212 213 }214 }
上面运行的结果：
这是通过网上整理的，适合处理两个服务器上的数据关联。欢迎留言。
两个DataTable关联查询（inner join、left join）C#代码
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。