有两个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;
}
}
合并时,对查询出来的结果集联接,字段的类型我并不知道,该怎么获取?合并的linq怎么写?还有个问题,由于是left out join,按照我写的方法分别去数据库中查询再合并,结果感觉会不对把?
------解决思路----------------------
你的代码可以,但是是全部取出,在内存中查询的。