1: ///
2: /// 连接两个表
3: ///
4: ///
5: ///
6: ///
7: ///
8: ///
9: public static DataTable JoinTwoTable(DataTable First, DataTable Second, string FJC, string SJC)
10: { 11: return JoinTwoTable(First, Second, new DataColumn[] { First.Columns[FJC] }, new DataColumn[] { First.Columns[SJC] }); 12: }
13:
14:
15: ///
16: /// 连接两个表
17: ///
18: ///
19: ///
20: ///
21: ///
22: ///
23: protected static DataTable JoinTwoTable(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
24: { 25: return JoinTwoTable(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC}); 26: }
27:
28: ///
29: /// 连接两个Table
30: ///
31: ///
32: ///
33: ///
34: ///
35: ///
36: protected static DataTable JoinTwoTable(DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC)
37: { 38:
39: //创建一个新的DataTable
40: DataTable table = new DataTable("Join"); 41:
42: using (DataSet ds = new DataSet())
43: { 44:
45: //把DataTable Copy到DataSet中
46: ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() }); 47:
48: DataColumn[] parentcolumns = new DataColumn[FJC.Length];
49:
50: for (int i = 0; i < parentcolumns.Length; i++)
51: { 52: parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];
53: }
54:
55: DataColumn[] childcolumns = new DataColumn[SJC.Length];
56:
57: for (int i = 0; i < childcolumns.Length; i++)
58: { 59: childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];
60: }
61:
62:
63: //创建关联
64:
65: DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);
66: ds.Relations.Add(r);
67:
68:
69: //为关联表创建列
70: for (int i = 0; i < First.Columns.Count; i++)
71: { 72: table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
73: }
74:
75: for (int i = 0; i < Second.Columns.Count; i++)
76: { 77: //看看有没有重复的列，如果有在第二个DataTable的Column的列明后加_Second
78: if (!table.Columns.Contains(Second.Columns[i].ColumnName))
79: table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);
80: else
81: table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);
82: }
83:
84:
85: table.BeginLoadData();
86:
87: foreach (DataRow firstrow in ds.Tables[0].Rows)
88: { 89: //得到行的数据
90: DataRow[] childrows = firstrow.GetChildRows(r);
91:
92: if (childrows != null && childrows.Length > 0)
93: { 94: object[] parentarray = firstrow.ItemArray;
95:
96: foreach (DataRow secondrow in childrows)
97: { 98: object[] secondarray = secondrow.ItemArray;
99:
100: object[] joinarray = new object[parentarray.Length + secondarray.Length];
101:
102: Array.Copy(parentarray, 0, joinarray, 0, parentarray.Length);
103:
104: Array.Copy(secondarray, 0, joinarray, parentarray.Length, secondarray.Length);
105:
106: table.LoadDataRow(joinarray, true);
107: }
108: }
109: }
110:
111: table.EndLoadData();
112: }
113:
114:
115: return table;
116:
117: }