三层结构之联接查询的实现

试想：如果不使用数据库中的 view 视图，那么在三层结构的开发环境下，如何实现两表或多表( join  table )联接查询所需要的显示结果呢？

为了直观揭露实现办法的本质，于是在此弱化了一些操作步骤，如：配置文件保存连接字符串、数据访问类( DBHelper.cs )访问数据库等的具体实现。

在本示例的数据库中：学生信息表(Students)与成绩表(Score)是一对多的关系( 1:N )。

 

实体层（Entity）：
Student.cs

using System;

using System.Collections.Generic;

using System.Text;



namespace Entity

{

    public class Student

    {

        int code;

        public int Code

        {

            get { return code; }

            set { code = value; }

        }



        string name;

        public string Name

        {

            get { return name; }

            set { name = value; }

        }  

    }

}

Score.cs

using System;

using System.Collections.Generic;

using System.Text;



namespace Entity

{

    public class StuScore

    {

        int stuID;

        public int StuID

        {

            get { return stuID; }

            set { stuID = value; }

        }



        float score;

        public float Score

        {

            get { return score; }

            set { score = value; }

        }

    }

}

数据访问层（DAL）：

StudentDAL.cs

using System;

using System.Collections.Generic;

using System.Text;

//myself

using System.Data.SqlClient;

using Entity;



namespace DAL

{

    public class StudentDAL

    {

        public List<Student> GetAllStudent()

        {

            List<Student> allStudent = new List<Student>();



            string strCon = "server=.;database=S100201A;uid=sa;pwd=sa;";

            string sql="select SCode,SName from Students";

            SqlCommand cmd = new SqlCommand(sql, new SqlConnection(strCon));

            cmd.Connection.Open();

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())

            {

                Student stu = new Student();

                stu.Code = (int)dr[0];

                stu.Name = dr[1].ToString();

                allStudent.Add(stu);

            }

            return allStudent;

        }

    }

}

ScoreDAL.cs

using System;

using System.Collections.Generic;

using System.Text;

//myself

using Entity;

using System.Data.SqlClient;



namespace DAL

{

    public class ScoreDAL

    {

        public List<StuScore> GetAllScore(int code)

        {

            List<StuScore> StuScores = new List<StuScore>();



           string strCon = "server=.;database=S100201A;uid=sa;pwd=sa;";

            string sql="select StudentID,Score from Score where StudentID="+ code;

            SqlCommand cmd = new SqlCommand(sql, new SqlConnection(strCon));

            cmd.Connection.Open();

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())

            {

                StuScore stuScore = new StuScore();

                stuScore.StuID = (int)dr[0];

                stuScore.Score = Convert.ToSingle(dr[1]);

                StuScores.Add(stuScore);

            }

            return StuScores;

        }

    }

}

业务逻辑层（BLL）：

StudentAndScore.cs

using System;

using System.Collections.Generic;

using System.Text;



namespace BLL

{

    /// <summary>

    /// 封装一个包含2张表字段的实体

    /// </summary>

    public class StudentAndScore

    {

        int code;

        public int Code

        {

            get { return code; }

            set { code = value; }

        }



        string name;

        public string Name

        {

            get { return name; }

            set { name = value; }

        }



        float? stuScore;

        public float? StuScore

        {

            get { return stuScore; }

            set { stuScore = value; }

        }

    }

}

StudentAndTeacherBLL.cs

using System;

using System.Collections.Generic;

using System.Text;

//myself

using DAL;

using Entity;



namespace BLL

{

    /// <summary>

    /// 获得2张表对应的数据

    /// </summary>

    public class StudentAndTeacherBLL

    {

        /// <summary>

        /// 注意：该方法获得所有学生的成绩，

        ///       属于“左外联接”(当没有成绩时，成绩显示为空)。

        ///       若要实现“内联接”的显示效果，

        ///       则去掉：if(stuScores.Count == 0) 的判断即可！

        /// </summary>

        /// <returns>2张表数据的结果集</returns>

        public List<StudentAndScore> GetStudentAndTeacher()

        {

            //用来保存结果集

            List<StudentAndScore> allStudentAndScore = new List<StudentAndScore>();

            //获得所有学生的信息

            List<Student> students = new StudentDAL().GetAllStudent();

            foreach (Student stu in students)

            {

                //根据学号获得该学生的所有成绩

                List<StuScore> stuScores = new ScoreDAL().GetAllScore(stu.Code);



                if (stuScores.Count == 0) //如果没有该学生的成绩

                {

                    //用来保存单个联接实体

                    StudentAndScore studentAndScore = new StudentAndScore();

                    studentAndScore.Code = stu.Code;

                    studentAndScore.Name = stu.Name;

                    studentAndScore.StuScore = null; //成绩为null

                    //添加到结果集

                    allStudentAndScore.Add(studentAndScore);

                }

                else //如果有该学生的成绩

                {

                    foreach (StuScore SScore in stuScores)

                    {

                        //用来保存单个联接实体

                        StudentAndScore studentAndScore = new StudentAndScore();

                        studentAndScore.Code = stu.Code;

                        studentAndScore.Name = stu.Name;

                        studentAndScore.StuScore = SScore.Score;

                        //添加到结果集

                        allStudentAndScore.Add(studentAndScore);

                    }

                }

            }

            return allStudentAndScore; //返回结果集

        }

    }

}

用户界面层（UI）：

Form1.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

//myself

using BLL;

using Entity;



namespace JoinSelect_3Layer

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }



        private void Form1_Load(object sender, EventArgs e)

        {

            List<StudentAndScore> stus = new StudentAndTeacherBLL().GetStudentAndTeacher();

            dataGridView1.DataSource = stus;

        }

    }

}

显示结果如下：

说明：Code、Name  字段来自信息表(Students)，而StuScore 字段来自成绩表(Score)。

Demo下载