本文主要介绍.NET(C#)中,使用ADO.NET或Entify Framework方式一次查询多个表并且返回多个表的结果的方法代码。

1、使用ADO.NET查询多个表

1)无关联多表查询

var connectionString = @"Provider=sqloledb; Data Source=Aron1; Initial Catalog=cjavapy; User Id=sa; Password=asdasd; ";
var commandText = "SELECT * FROM Table1; SELECT * FROM Table2;";
var ds = new DataSet();
using (var da = new SqlDataAdapter(commandText, connectionString))
{
da.Fill(ds);
}

2)多表关联查询

var connectionString = @"Provider=sqloledb; Data Source=Aron1; Initial Catalog=cjavapy; User Id=sa; Password=asdasd; ";
var commandText = "SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name";
var ds = new DataSet();
using (var da = new SqlDataAdapter(commandText, connectionString))
{
da.Fill(ds);
}

2、使用Entify Framework查询多个表

1)普通多表查询

List<Table1> list1;
List<Table2> list2;
using (var cn = new SqlConnection(@"Connection String"))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Table1; SELECT * FROM Table2";
var reader = cmd.ExecuteReader();
using (var db = new YourDbContext())
{
var context = ((IObjectContextAdapter)db).ObjectContext;
list1 = context.Translate<Table1>(reader).ToList();
reader.NextResult();
list2 = context.Translate<Table2>(reader).ToList();
}
}
}

2)多表关联查询

using (DBEntities db=new DBEntities())  
{
List<Employee> employees = db.Employees.ToList();
List<Department> departments = db.Departments.ToList();
List<Incentive> incentives = db.Incentives.ToList();
var employeeRecord = from e in employees
join d in departments on e.Department_Id equals d.DepartmentId into table1
from d in table1.ToList()
join i in incentives on e.Incentive_Id equals i.IncentiveId into table2
from i in table2.ToList()
select new ViewModel
{
employee=e,
department=d,
incentive=i
};
}

推荐文档