这个属于实体类在 Windows 程序中的应用范畴,实际数据需求如下图:
上面的图中数据来自于三个表,其中“学员姓名”来自于“Student”数据表; “科目”来自于“Subject”数据表; “成绩”、“考试时间”来自于“Result”数据表。
实现思路:DataGridView 先绑定Result,然后获取Student 和Subject 数据
【方案1】辅助列:作为中间数据的隐藏列 ,使用辅助列显示关联表数据
表示层的关键代码:
foreach (DataGridViewRow row in this.dgvResult.Rows)
{
int id = (int)(row.Cells["SubjectNo"].Value);
row.Cells["SubjectName"].Value = subjectManager.GetSubjectDataBySubjectId(id).SubjectName;
int stuNo = (int)(row.Cells["StudentNo"].Value);
row.Cells["StudentName"].Value = studentManager.GetStudentNameById(stuNo);
}
【方案2】组合框列:使用组合框栏显示关联表数据
关键步骤,
1、设置列的类型为DataGridViewComboBoxColumn
2、设置属性DisplayStyle
3、绑定外键表数据
4、设置ReadOnly属性值为True
关键代码,
//查询学生成绩
this.dgvResult.DataSource = ResultManager.ReviewStudentResult(strSubjectNo,strStuName);
//进行二次数据绑定
DataGridViewComboBoxColumn cbo = (DataGridViewComboBoxColumn)this.dgvResult.Columns["StudentNo"];
cbo.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
cbo.DisplayMember = "StudentName";
cbo.ValueMember = "StudentNo";
cbo.DataSource = StudentManager.GetStudentData();
cbo = (DataGridViewComboBoxColumn)this.dgvResult.Columns["SubjectNo"];
cbo.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
cbo.DisplayMember = "SubjectName";
cbo.ValueMember = "SubjectNo";
cbo.DataSource = SubjectManager.GetSubjectData();
【方案3】使用扩展实体类:面向对象角度方案思想,推荐使用这个方法
扩展实体又称业务实体,一般由数据实体派生而来,为业务功能的需要而扩展
在“表示层”获取到数据后,直接绑定到DataGridView控件即可。
public class Result
{
public int StudentNo {get;set;}
public int SubjectNo { get; set; }
public int StudentResult { get; set; }
public DateTime ExamDate { get; set; }
}
public class ResultBusiness : Result
{
public string StudentName { get; set; }
public string SubjectName { get; set; }
}
“数据访问层”的关键代码:
//创建Sql语句
StringBuilder sb = new StringBuilder();
sb.Append(@" SELECT a.*,b.studentName,c.subjectName
FROM RESULT a JOIN student b ON a.studentno = b.studentno
JOIN subject c on c.subjectNo = a.subjectNo
WHERE 1=1 ");
if (subjectNo != "-1")
{
sb.Append(" AND a.subjectNo = @SubjectNo");
}
if (!string.IsNullOrEmpty(stuName))
{
sb.Append(" AND b.studentName like '%@StudentName%'");
}
SqlParameter[] para = {
new SqlParameter("@SubjectNo", subjectNo),
new SqlParameter("@StudentName",stuName)};
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(paras);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<ResultBusiness> resultList = new List<ResultBusiness>();
while (reader.Read())
{
ResultBusiness result = new ResultBusiness();
result.StudentNo = Convert.ToInt16(reader["StudentNo"]);
result.SubjectNo = Convert.ToInt16(reader["SubjectNo"]);
result.StudentName = Convert.ToString(reader["StudentName"]);
result.SubjectName = Convert.ToString(reader["SubjectName"]);
result.StudentResult = Convert.ToInt16(reader["StudentResult"]);
result.ExamDate = Convert.ToDateTime(reader["ExamDate"]);
resultList.Add(result);
}
reader.Close();
return resultList;
}
文中的这3中方案的示例源码如下:(提取码:400e)
待续…… C#中DataGridView的多表数据展示的方法(二)