.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  
                                     };  
            }