scores.cs

c#编写“三层体系结构的应用-学员管理系统2(界”

using System;
using System.Data;

namespace Aptech.Student.DataAccess
{
	using Aptech.Student.Common;

	/// <summary>
	/// Scores 的摘要说明。
	/// </summary>
	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;
		}

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

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

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

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

		public virtual void Dispose(bool disposing)
		{
			if(!disposing)
			{
				return;
			}
			
			if(!bConn)
			{
				cDbObject.CloseDataBase();
			}
		}
	}
}