doctoraccess.cs

实施医院管理系统

using System;
using System.Data;
using comman;

namespace DataAccess
{
	/// <summary>
	/// DoctorAccess 的摘要说明。
	/// </summary>
	public class DoctorAccess
	{
		DBconnection cn = new DBconnection();
		public DoctorAccess()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public DataTable SelectDoc()
		{
			string commandText = string.Format(@"SELECT Doctor.DoctorID, Doctor.Name, Doctor.SexID, Dictionary.Detail AS Sex, Doctor.Old, 
												Doctor.ProfessID, Dictionary_1.Detail AS Profess, Doctor.BelongOffID, 
												Dictionary_2.Detail AS BelongOff, Doctor.Specialty, Doctor.BelongID, 
												Hospitall.Name AS HospitalName, Doctor.DocTime, Doctor.TelphonNum, 
												Hospitall.ProvinceID, Dictionary_3.Detail AS ProvinceName, Hospitall.CityID, 
												City.CityName
											FROM Dictionary Dictionary_2 INNER JOIN
												Dictionary Dictionary_1 INNER JOIN
												Doctor INNER JOIN
												Dictionary ON Doctor.SexID = Dictionary.ID ON Dictionary_1.ID = Doctor.ProfessID ON 
												Dictionary_2.ID = Doctor.BelongOffID INNER JOIN
												Hospitall ON Doctor.BelongID = Hospitall.HospitalID INNER JOIN
												Dictionary Dictionary_3 ON Hospitall.ProvinceID = Dictionary_3.ID INNER JOIN
												City ON Hospitall.CityID = City.CityID");
			return DBAccess.ExcuteDataTable(cn.Connection,commandText);
		}

		public int AddDoc(Doctor doctor)
		{
			string commandText = string.Format(@"INSERT INTO Doctor (Name,SexID,Old,ProfessID,BelongOffID,Specialty,BelongID,DocTime,TelphonNum) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",doctor.Name,doctor.SexID,doctor.Old,doctor.ProfessID,doctor.BelongOffID,doctor.Specialty,doctor.BelongID,doctor.DocTime,doctor.TelphonNum);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}
		public int Updatedoc(Doctor doctor,int DoctorID)
		{
			string commandText = string.Format(@"UPDATE Doctor SET Name = '{0}',SexID = '{1}',Old = '{2}',ProfessID = '{3}',BelongOffID = '{4}',Specialty = '{5}',BelongID = '{6}',DocTime = '{7}',TelphonNum = '{8}' WHERE DoctorID ='{9}'",doctor.Name,doctor.SexID,doctor.Old,doctor.ProfessID,doctor.BelongOffID,doctor.Specialty,doctor.BelongID,doctor.DocTime,doctor.TelphonNum,DoctorID);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}
		public int DeleteDoc(int ID)
		{
			string commandText = string.Format(@"DELETE FROM Doctor WHERE DoctorID = '{0}'",ID);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}
		public int DeleteDocByBelongID(int BelongID)
		{
			string commandText = string.Format(@"DELETE FROM Doctor WHERE BelongID = '{0}'",BelongID);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}
		public DataTable SerchDoc(string DocName,string Belong,int BelongOffID,int ProfesID,int SexID)
		{
			string Null = "";
			string where = " WHERE";

			string commandText = string.Format(@"SELECT Doctor.DoctorID, Doctor.Name, Doctor.SexID, Doctor.Old, Doctor.ProfessID, 
												Dictionary_1.Detail AS Profess, Doctor.BelongOffID, 
												Dictionary_2.Detail AS BelongOff, Doctor.Specialty, Doctor.BelongID, 
												Hospitall.Name AS HospitalName, Doctor.DocTime, Doctor.TelphonNum, 
												Hospitall.ProvinceID, Dictionary_3.Detail AS ProvinceName, Hospitall.CityID, 
												City.CityName, Dictionary_4.Detail AS Sex
											FROM Dictionary Dictionary_2 INNER JOIN
												Dictionary Dictionary_1 INNER JOIN
												Doctor INNER JOIN
												Dictionary Dictionary_4 ON Doctor.SexID = Dictionary_4.ID ON 
												Dictionary_1.ID = Doctor.ProfessID ON 
												Dictionary_2.ID = Doctor.BelongOffID INNER JOIN
												Hospitall ON Doctor.BelongID = Hospitall.HospitalID INNER JOIN
												Dictionary Dictionary_3 ON Hospitall.ProvinceID = Dictionary_3.ID INNER JOIN
												City ON Hospitall.CityID = City.CityID");
			if(DocName != "")
			{
				if(Null == "")
					commandText += where;
				commandText += Null+string.Format(" Doctor.Name = '{0}'",DocName);
				Null = "AND";
			}
			if(Belong != "")
			{
				if(Null == "")
					commandText += where;
				commandText += Null+string.Format(" Hospitall.Name LIKE '{0}'",Belong);
				Null = "AND";
			}
			if(BelongOffID != -1)
			{
				if(Null == "")
					commandText += where;
				commandText += Null+string.Format(" Doctor.BelongOffID = '{0}'",BelongOffID);
				Null = "AND";
			}
			if(ProfesID != -1)
			{
				if(Null == "")
					commandText += where;
				commandText += Null + string.Format(" Doctor.ProfessID = '{0}'",ProfesID);
				Null = "AND";
			}
			if(SexID != -1)
			{
				if(Null == "")
					commandText += where;
				commandText += Null + string.Format(" Doctor.SexID = '{0}'",SexID);
				Null = "AND";
			}
			return DBAccess.ExcuteDataTable(cn.Connection,commandText);
			
		}
	}
}