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 //创建一个新的DataTable 112 DataTable table = new DataTable("Join"); 113 114 // Use a DataSet to leverage DataRelation 115 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的列明后加_Second 145 if (!table.Columns.Contains(dt2.Columns[i].ColumnName)) 146 table.Columns.Add(dt2.Columns[i].ColumnName, dt2.Columns[i].DataType); 147 else 148 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;//Table1 173 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 /// 重载1 189 /// </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 /// 重载2 202 /// </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 }
上面运行的结果：
这是通过网上整理的，适合处理两个服务器上的数据关联。欢迎留言。