2013-3-18 两个DataTable在内存中连接查询代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

/*********************************************************
 * CLR：4.0.30319.296
 * 开发人员：XING
 * 描述说明：
 * 
 * 在内存中将两个表做inner join\left join 关联查询
 * *******************************************************/

namespace WindowsFormsApplication1
{
    /// <remarks>
    /// <list type="table">
    /// <listheader><term>更改日期(作者)</term><description>描述</description></listheader>
    /// <item><term> </term><description> </description></item>
    /// </list>
    /// <code>
    /// 
    /// </code>
    /// </remarks>
    public class SQLOps
    {
        public SQLOps()
        {
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="First"></param>
        /// <param name="Second"></param>
        /// <param name="FJC">关联列</param>
        /// <param name="SJC">关联列</param>
        /// <returns></returns>
        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[] First_columns = new DataColumn[FJC.Length];

                for (int i = 0; i < First_columns.Length; i++)
                {

                    First_columns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];

                }

                DataColumn[] Second_columns = new DataColumn[SJC.Length];

                for (int i = 0; i < Second_columns.Length; i++)
                {

                    Second_columns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];

                }


                //创建关联

                DataRelation r = new DataRelation(string.Empty, First_columns, Second_columns, 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();
                int itable2Colomns = ds.Tables[1].Rows[0].ItemArray.Length;
                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);

                        }

                    }
                    else//如果有外连接(Left Join)添加这部分代码
                    {
                        object[] table1array = firstrow.ItemArray;//Table1
                        object[] table2array = new object[itable2Colomns];
                        object[] joinarray = new object[table1array.Length + itable2Colomns];
                        Array.Copy(table1array, 0, joinarray, 0, table1array.Length);
                        Array.Copy(table2array, 0, joinarray, table1array.Length, itable2Colomns);
                        table.LoadDataRow(joinarray, true);
                        DataColumn[] dc = new DataColumn[2];
                        dc[0] = new DataColumn("");
                    }
                }

                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] });

        }


    }
}