Linq合并不同站点的结果集怎么操作？

柠檬片-味道 2015-05-06 08:21:46

有两个Oracle数据库，分别存储student表和class表，要联接两张表查询，如select s.name,c.name from student s left outer join class c on s.classid=c.id where s.hobby like '%篮球%' and c.department like '%计算机%';现在把这条sql分解成两条，分表到对应的数据库去执行然后合并：



string sql1 = "select classid,name from student where hobby like '%篮球%'";

string sql2 = "select id,name from class where department like '%计算机%'";

DataSet dataset = new DataSet();

Dictionary<string, string[]> dict = new Dictionary<string, string[]>();

dict.Add("student", new string[] { "Data Source=172.168.1.1/ORCL;User ID=user1;Password=123", sql1 });

dict.Add("class", new string[] { "Data Source=172.168.1.2/ORCL;User ID=user2;Password=123", sql2 });

var tables = (from x in dict.AsParallel() select Query(x)).ToList();

Dictionary<string, DataTable> dict1 = new Dictionary<string, DataTable>();

for (int i = 0; i < tables.Count; i++)

{

    dict1.Add(tables[i].TableName, tables[i]);

}

var result = from s in dict1["student"].AsEnumerable()

             join c in dict1["class"].AsEnumerable()

             on s["classid"] equals c["id"]

             select new

             {

                 studentname = s["name"],

                 classname = c["name"]

             };



static DataTable Query(KeyValuePair<string, string[]> dict)

{

    string host = dict.Value[0];

    string sql = dict.Value[1];

    using (OracleConnection conn = new OracleConnection(host))

    {

        OracleDataAdapter da = new OracleDataAdapter(sql, conn);

        conn.Open();

        DataSet ds = new DataSet();

        da.Fill(ds);

        DataTable table = ds.Tables[0];

        table.TableName = dict.Key;

        return table;

    }

}