实现DataTable的Inner Join(C#)--转

 

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