//返回的数据类型为DataTable,DataTable 比较灵活,
//如果有更新操作,字段不不定的,比较合适。而不是list<string>
public DataTable GetProjectMessage(int projectSupervisionUnit)
{
projectSupervisionUnit = 10001;//上海市卫生管理局,这里我是写的一个死数据用来测试
//这里注意,之前我把foreach包裹using,报了一个bug,
//自己没有仔细去想,using会创建数据库连接,
//如果多次查询多次连接数据库,肯定是不对的。被人看到,笑掉大牙了。
using (EF.MSISEntities context = new EF.MSISEntities())
{
//根据当前的监管单位,查到监管单位下所属的项目,得到项目ids
var item = context.Project_SupervisionUnit.Where(o => o.SupervisionUnit_Id == projectSupervisionUnit).ToList();
context.Database.CommandTimeout = 9999;
DataTable ds = new DataTable();
foreach (var i in item)
{
//这里是想一个sql语句查询出项目名称,项目负责人,创建日期,该项目的病例数,该项目的表单数,
//因此用的子查询,但是注意子查询的结果不能返回多条,负责报错。我这里的子查询根据id来查,因此只有一个结果不会报错。
//另外实际项目中最好起有意思的名字,什么p1,p2显得极为不专业
var testCommand = new SqlCommand(@"SELECT DISTINCT p1.Name, p1.OwnerUserCode, p1.CreateDate,
(SELECT COUNT(DISTINCT p3.Patient_Id) AS Patient_IdSUM
FROM Project AS p1 INNER JOIN
PatientGroup AS p2 ON p1.Id = p2.Project_Id INNER JOIN
PatientInGroup AS p3 ON p2.Id = p3.PatientGroup_Id
WHERE (p1.Id =@id)) AS Patient_IdSUMS,
(SELECT COUNT(DISTINCT CRFForm_Id) AS CRFForm_IdSUM
FROM CRF_Variable
WHERE (Project_Id =@id)) AS CRFForm_IdSUMS
FROM Project AS p1 INNER JOIN
PatientGroup AS p2 ON p1.Id = p2.Project_Id INNER JOIN
PatientInGroup AS p3 ON p2.Id = p3.PatientGroup_Id
WHERE (p1.Id =@id)", (SqlConnection)context.Database.Connection);
testCommand.Parameters.Add("@id", SqlDbType.Int);
testCommand.Parameters["@id"].Value = i.Project_Id;
//testCommand.Parameters.AddWithValue("@id", );
SqlDataAdapter adapter = new SqlDataAdapter(testCommand);
adapter.Fill(ds);
}
return ds;
}
}
SQL server 多表联合查询
最新推荐文章于 2024-08-12 13:17:14 发布