如何根据2个datatable做关联查询,将结果保存到另一个datatable

Question

程序中得到两个表的内容,已经放入
dsDataSet1.Tables["tb1"] dsDataSet1.Tables["tb2"]
我需要做这两表的关联查询
SELECT tb1.ID ,tb1.f2 ,tb2.f2
FROM tb1  JOIN  tb2 ON tb1.ID=tb2.ID
WHERE tb1.f2='A'
如何做类似的查询,并将结果放入tb3中,可以使用SQL语句吗
如
tb1:   
  ID     f2  
  1       A   
  2       B   
  3       C   
 tb2:   
  ID     f2  
  1       a   
  2       b   
  4       d 想得到结果 
tb3: 1   A   a
 ID
 

Answer 1

用你的SQL语句不就直接生成了吗，然后将 它放到数据集datatable中

Answer 2

                               //第一个 table   第二个table     第一个table的
        public static DataTable Join(DataTable First, DataTable Second, 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[] { First.Copy(), Second.Copy() });                DataColumn[] parentcolumns = new DataColumn[FJC.Length];                for (int i = 0; i < parentcolumns.Length; i++)
                {
                    parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];
                }                DataColumn[] childcolumns = new DataColumn[SJC.Length];                for (int i = 0; i < childcolumns.Length; i++)
                {                    childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];                }
                //创建关联                DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);                ds.Relations.Add(r);
                //为关联表创建列                for (int i = 0; i < First.Columns.Count; i++)
                {                    table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);                }                for (int i = 0; i < Second.Columns.Count; i++)
                {                    //看看有没有重复的列，如果有在第二个DataTable的Column的列明后加_Second                    if (!table.Columns.Contains(Second.Columns[i].ColumnName))                        table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);                    else                        table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);                }
                table.BeginLoadData();                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);                        }                    }                }                table.EndLoadData();            }            return table;
        }        public static DataTable Join(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
        {            return Join(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC });        }        public static DataTable Join(DataTable First, DataTable Second, string FJC, string SJC)
        {            return Join(First, Second, new DataColumn[] { First.Columns[FJC] }, new DataColumn[] { First.Columns[SJC] });        }