scores.cs

大二做的课程设计。一个学生信息管理系统

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace StudentLibrary.Business
{
    using StudentLibrary.DataAccess;

    public class Scores
    {
        private DataBaseOperate cDbObject = null;
        private bool bConn = false;


        public Scores()
        {
            cDbObject = new DataBaseOperate();
            bConn = false;
        }


        public Scores(DataBaseOperate dbOperate)
        {
            cDbObject = dbOperate;
            bConn = true;
        }
        /// <summary>
        /// 功能：获得分数
        /// 由主窗体加载时的getScore()调用
        /// </summary>
        /// <param name="iStudentId">学号ID</param>
        /// <param name="iCourseId">课程ID</param>
        /// <param name="iClassId">班级ID</param>
        /// <returns>返回数据集</returns>
        public DataSet SelectScore(
            int iStudentId,
            int iCourseId,
            int iClassId)
        {
            string sSql =
                "select a.StudentId"
                + ",a.StudentName"
                + ",b.CourseId"
                + ",c.SubjectName"
                + ",d.ScoreId"
                + ",d.Score"
                + ",e.ClassName"
                + " from student a"
                + " left join course b"
                + " on a.classid = b.classid"
                + " left join subject c"
                + " on b.subjectid = c.subjectid"
                + " left join score d"
                + " on b.courseid = d.courseid"
                + " and a.StudentId = d.StudentId"
                + " left join class e"
                + " on a.classid = e.classid"
                + " where 1 = 1";
            if (iStudentId != -1)
            {
                sSql += " and a.StudentId = " + iStudentId.ToString();
            }
            if (iCourseId != -1)
            {
                sSql += " and b.courseId = " + iCourseId.ToString();
            }
            if (iClassId != -1 && iStudentId == -1)
            {
                sSql += " and e.ClassId = " + iClassId.ToString();
            }
            DataSet dataSet = new DataSet();
            try
            {
                dataSet = cDbObject.Search(sSql, "Score");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return dataSet;
        }
        /// <summary>
        /// 功能：删除分数
        /// </summary>
        /// <param name="iScoreId">分数ID</param>
        /// <param name="iSubjectId">科目ID</param>
        /// <param name="iStudentId">学生ID</param>
        /// <param name="iClassId">班级ID</param>
        /// <returns>成功返回true</returns>
        public bool DeleteScore(
            int iScoreId,
            int iSubjectId,
            int iStudentId,
            int iClassId)
        {

            string sSql =
                "Delete Score"
                + " where 1 = 1";
            if (iScoreId != -1)
            {
                sSql += " and ScoreId = " + iScoreId.ToString();
            }
            if (iSubjectId != -1)
            {
                sSql += " and SubjectId = " + iSubjectId.ToString();
            }
            if (iStudentId != -1)
            {
                sSql += " and StudentId = " + iStudentId.ToString();
            }
            if (iClassId != -1)
            {
                sSql += " and StudentId in (Select StudentId from Student where ClassId = " + iClassId.ToString() + ")";
            }

            try
            {
                cDbObject.Execute(sSql);
            }
            catch (Exception e)
            {
                throw (e);
            }

            return true;
        }
        /// <summary>
        /// 功能：更新分数
        /// 由主窗体的dataGrid1_CurrentCellChanged()方法中的changeScore()方法调用
        /// </summary>
        /// <param name="iScoreId">分数ID</param>
        /// <param name="fScore">分数</param>
        /// <returns>成功返回true</returns>
        public bool UpdateScore(
            int iScoreId,
            float fScore)
        {
            string sSql =
                "update Score"
                + " set Score = " + fScore.ToString()
                + " where ScoreId = " + iScoreId.ToString();
            try
            {
                cDbObject.Execute(sSql);
            }
            catch (Exception e)
            {
                throw (e);
            }
            return true;
        }
        /// <summary>
        /// 由主窗体的dataGrid1_CurrentCellChanged()方法中的changeScore()方法调用
        /// </summary>
        /// <param name="iCourseId">课程ID</param>
        /// <param name="iStudentId">学生ID</param>
        /// <param name="fScore">分数</param>
        /// <returns>成功返回true</returns>
        public bool InsertScore(
            int iCourseId,
            int iStudentId,
            float fScore)
        {
            string sSql =
                "select Count(*)"
                + " from student a"
                + ",course b"
                + ",class c"
                + " where a.ClassId = b.ClassId"
                + " and b.ClassId = c.ClassId"
                + " and a.StudentId = " + iStudentId.ToString()
                + " and b.CourseId = " + iCourseId.ToString();

            DataSet dataSet = new DataSet();
            try
            {
                dataSet = cDbObject.Search(sSql, "Course");
            }
            catch (Exception e)
            {
                throw (e);
            }
            if (dataSet.Tables["Course"].Rows[0][0].ToString() == "0")
            {
                throw (new ApplicationException("该学生没有此门课程。"));
            }
            sSql =
                " insert Score("
                + " CourseId"
                + ",StudentId"
                + ", Score"
                + ")"
                + " values("
                + iCourseId.ToString()
                + "," + iStudentId
                + "," + fScore.ToString()
                + ")";

            try
            {
                cDbObject.Execute(sSql);
            }
            catch (Exception e)
            {
                throw (e);
            }
            return true;
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(true);
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <param name="disposing"></param>
        public virtual void Dispose(bool disposing)
        {
            if (!disposing)
            {
                return;
            }

            if (!bConn)
            {
                cDbObject.CloseDataBase();
            }
        }



    }
}