jimmy.cs

学生类：Student 教师类：Teacher 主任：AdminTeacher 班主任：MasterTeacher 授课老师： PrelectTeacher 班级类：classTeam 班

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using BusinessRule;
using System.Collections;
using Forms;

namespace DataAccess
{
	/// <summary>
	/// DataAccess 的摘要说明。
	/// </summary>

	public class jimmy
	{
		public jimmy()
		{
			
		}

		//提供一个打开的数据库联接
		public static SqlConnection GetConnection()
		{
			string source = "server=(local);integrated security=SSPI;database=APTECH";
			SqlConnection conn = new SqlConnection(source);
			conn.Open();
			return conn;
		}

		//获得一条SQL语句，在数据库中执行
		public static int DoSqlString(string select)
		{
			SqlCommand myCmd = new SqlCommand(select,GetConnection());
			return myCmd.ExecuteNonQuery();
		}

		//获得一条SQL查询语句，返回一个SqlDataReader
		public static SqlDataReader GetDataReaderBySqlString(string select)
		{
			SqlCommand myCmd = new SqlCommand(select,GetConnection());
			return myCmd.ExecuteReader();
		}

		//获得一条SQL查询语句，返回一个object结果
		public static object GetObjectBySqlString(string select)
		{
			SqlCommand myCmd = new SqlCommand(select,GetConnection());
			return myCmd.ExecuteScalar();
		}

		//获得一条Sql查询语句，返回一个DataTable
		public static DataTable GetDataTableBySqlString(string select)
		{
			ArrayList myAL = new ArrayList();
			myAL.AddRange(select.Split(new char[]{' '}));
			string tablename = myAL[myAL.IndexOf("from") + 1].ToString();
			SqlDataAdapter myDa = new SqlDataAdapter(select,GetConnection());
			DataTable myDt = new DataTable();
			myDa.Fill(myDt);
			return myDt;
		}

		//获得教师编号，判断是否存在，存在返回true
		public static bool teaIDJude(int teaID)
		{
			string select = "select Count(teaID) from TeacherInfo where teaID = " + teaID.ToString();
			if((int)jimmy.GetObjectBySqlString(select) != 0 )
			{
				return true;
			}
			return false;
		}

		//获得教师编号，返回教师权限，T：授课教师  C：班主任教师  A：主管教师
		public static char GetTeaLimitByTeaID(int teaID)
		{
			string select = "select teaLimit from TeacherInfo where teaID = " + teaID.ToString();
			return char.Parse((string)jimmy.GetObjectBySqlString(select));
		}

		//返回一个新的教师编号
		public static int GetNewTeaID()
		{
			SqlCommand myCmd =new SqlCommand("Proc_NewTeaID",GetConnection());
			myCmd.CommandType = CommandType.StoredProcedure;
			SqlParameter teaID = myCmd.CreateParameter();
			teaID.ParameterName = "@teaID";
			teaID.SqlDbType = SqlDbType.Int;
			teaID.Direction = ParameterDirection.Output;
			myCmd.Parameters.Add(teaID);
			myCmd.ExecuteNonQuery();
			return (int)myCmd.Parameters["@teaID"].Value;
		}

		//获得教师编号和教师密码，判断是否匹配，false：不匹配   true：匹配
		public static bool CheckUp(int teaID,string teaPassWord)
		{
			string select = "select teaPassWord from TeacherPassWordInfo where  teaID = " + teaID.ToString();
			if(teaPassWord.Equals((string)jimmy.GetObjectBySqlString(select)))
			{
				return true;;
			}
			else
			{
				return false;
			}
		}

		//获得教师编号，返回教师基本信息表中该教师数据
		public static SqlDataReader GetTeaInfoByTeaID(int teaID)
		{
			string select = "select * from TeacherInfo where teaID = " + teaID.ToString();
			return jimmy.GetDataReaderBySqlString(select);
		}

		//获得教师姓名，返回教师基本信息表中教师数据表
		public static DataTable GetTeaInfoTableByTeaName(string teaName)
		{
			string select = "select * from TeacherInfo where teaName = '" + teaName + "'";
			return jimmy.GetDataTableBySqlString(select);
		}

		//获得教师编号，返回教师基本信息表中DataTable
		public static DataTable GetTeaInfoTableByTeaID(int teaID)
		{
			string select = "select * from TeacherInfo where teaID = " + teaID.ToString();
			return jimmy.GetDataTableBySqlString(select);	
		}

		//获得教师编号，返回包含他可见的班级的班级编号的ArrayList
		public static ArrayList GetClassesByTeaID(int teaID)
		{
			ArrayList classes = new ArrayList();
			string select;
			char teaLimit = jimmy.GetTeaLimitByTeaID(teaID);
			if(teaLimit.Equals('A'))
			{
				select  = "select claID from ClassInfo where claID != '0000'";
			}
			else if(teaLimit.Equals('M'))
			{
				select = "select claID from ClassInfo where claID != '0000' and claMasterID = " + teaID.ToString() ;
			}
			else
			{
				select = "select claID from PrelectInfo where teaID = " + teaID.ToString();
			}
			SqlDataReader myRd = GetDataReaderBySqlString(select);
			while(myRd.Read())
			{
				classes.Add(myRd.GetString(0));
			}
			return classes;
		}

		//获得班级编号ArrayList，返回班级的基本信息数据表
		public static DataTable GetClassesInfoTableByArrayList(ArrayList myClasses)
		{
			DataTable myDt = new DataTable("ClassInfo");
			for(int i = 0;i<myClasses.Count;i++)
			{
				SqlDataAdapter myDa = new SqlDataAdapter("select * from View_ClassInfo where claID = '" + myClasses[i].ToString() + "'",GetConnection());
				myDa.Fill(myDt);
			}
			return myDt;
		}

		//获得教师缺勤编号，返回老师缺勤表中相应数据
		public static SqlDataReader GetTeacherDutyByAfdID(int afdID)
		{
			string select = "select * from teacherAFDinfo where afdID = " + afdID.ToString();
			return jimmy.GetDataReaderBySqlString(select);
		}

		//获得教师编号，返回包含教师缺勤实例的ArrayList
		public static ArrayList GetTeaDutysByTeaID(int teaID)
		{
			ArrayList teaDutys = new ArrayList();
			string select = "select afdID from teacherAFDinfo where teaID = " + teaID.ToString();
			SqlDataReader myRd = jimmy.GetDataReaderBySqlString(select);
			while(myRd.Read())
			{
				teaDutys.Add(new TeacherDuty((int)myRd[0]));
			}
			return teaDutys;
		}

		//获得教师基类，更新数据中相关内容
		public static void UpdateTeachenrInfoByTeacher(Teacher teacher)
		{
			char teaSex = (teacher.TeaSex)? '1' : '0';
			char teaIn_Service = (teacher.TeaIn_Service)? '1' : '0';
			string select = "Update TeacherInfo Set teaLimit = '" + teacher.TeaLimit +"',teaName = '" +
				teacher.TeaName + "',teaSex = " + teaSex + ",teaOrigin = '" + teacher.TeaOrigin +
				"', teaNation = '" + teacher.TeaNation + "',teaTelephone = '" + teacher.TeaTelephone +
				"',teaAddress = '" + teacher.TeaAddress + "',teaPostcode = '" + teacher.TeaPostcode +
				"',teaPicture = '" + teacher.TeaPicture + "',teaIn_Service = " + teaIn_Service + 
				",teaRemark = '" + teacher.TeaRemark + "' where teaID = " + teacher.TeaID.ToString();
			jimmy.DoSqlString(select);
		}

		//获得教师基类，添加如数据库中
		public static void InsertTeacherInfoByTeacher(Teacher teacher)
		{
			char teaSex = (teacher.TeaSex)? '1' : '0';
			char teaIn_Service = (teacher.TeaIn_Service)? '1' : '0';
			string select = "insert TeacherInfo values('" + teacher.TeaLimit + "','" + teacher.TeaName +
				"'," + teaSex + ",'" + teacher.TeaOrigin + "','" + teacher.TeaNation + "','" +
				teacher.TeaTelephone + "','" + teacher.TeaAddress + "','" + teacher.TeaPostcode + "','" +
				teacher.TeaPicture + "'," + teaIn_Service + ",'" + teacher.TeaRemark + "')";
			jimmy.DoSqlString(select);
		}

		//获得班级编号，判断班级年级，返回字符1、2、3，分别代表一期、二期和三期
		public static char ClassGradeJudge(string ClassID)
		{
			char[] ch;
			ch = ClassID.ToCharArray();
			if(ch[0].Equals('Y'))
			{
				return '3';
			}
			else if(ch[1].Equals('2'))
			{
				return '2';
			}
			else
			{
				return '1';
			}
		}

		//获得班级编号，判断是否还在上课
		public static bool CheckClassIsOn(string claID)
		{
			string select = "select claCeaseDate from ClassInfo where claID = '" + claID 
				+ "' and claCeaseDate = '2000-1-1'";
			if(jimmy.GetDataTableBySqlString(select).Rows.Count != 0)
			{
				return true;
			}
			return false;
		}

		//获得班级编号，返回班级基本系息表中相应数据
		public static SqlDataReader GetClaInfoByClaID(string claID)
		{
			string select = "select * from ClassInfo where claID = '" + claID + "'";
			return jimmy.GetDataReaderBySqlString(select);
		}

		//获得班级编号，返回班级考试信息表中所有该班的信息
		public static DataTable GetClaExamInfoByClaID(string claID)
		{
			string select = "select * from ClassExamInfo where claID = '" + claID + "'";
		    return	jimmy.GetDataTableBySqlString(select);
		}

		//返回所有教室编号
		public static SqlDataReader GetAllClaRoomID()
		{
			string select = "select crID from ClassroomInfo";
			return jimmy.GetDataReaderBySqlString(select);
		}

		
		
		//返回所有正在上课的班级编号
		public static SqlDataReader GetAllClaID()
		{
			string select = "select claID from ClassInfo where claID <> '0000'"
				+ " and claCeaseDate = '2000-1-1'";
			return jimmy.GetDataReaderBySqlString(select);
		}

		//获得班级编号，返回包含班级所有考试信息实例的ArrayList
		public static ArrayList GetClaExamsByClaID(string claID)
		{
			DataTable myDt = GetClaExamInfoByClaID(claID);
			ArrayList classExams = new ArrayList(myDt.Rows.Count);
			foreach(DataRow dr in myDt.Rows)
			{
				classExams.Add(new ClassExam(dr));
			}
			return classExams;
		}

		//获得考试编号，返回学生考试信息表中所有关于这次考试的信息
		public static DataTable GetStuExamInfoByExamID(int examID)
		{
			string select = "select * from StudentExamInfo where examID = " + examID.ToString();
			return jimmy.GetDataTableBySqlString(select);
		}

		//获得学员编号，判断是否存在，存在则返回true
		public static bool stuIDJude(int stuID)
		{
			string select = "select Count(stuID) from StudentInfo where stuID = " + stuID.ToString();
			if((int)jimmy.GetObjectBySqlString(select) != 0 )
			{
				return true;
			}
			return false;
		}

		//获得学生编号，返回学生基本信息表中相应数据
		public static SqlDataReader GetStudentInfoByStuID(int stuID)
		{
			string select = "select * from StudentInfo where stuID = " + stuID.ToString();
			return jimmy.GetDataReaderBySqlString(select);
		}

		//获得学员实例，添加入数据库
		public static void InsertStudentInfoByStudent(Student student)