hosptialaccess.cs

实施医院管理系统

using System;
using System.Data;
using comman;

namespace DataAccess
{
	/// <summary>
	/// HosptialAccess 的摘要说明。
	/// </summary>
	public class HosptialAccess
	{
		DBconnection cn = new DBconnection();
		
		public HosptialAccess()
		{
		}

		public Hospital[] GetHosByProID(int ProvinceID)
		{
			string commandText  = string.Format(@"SELECT * FROM Hospitall WHERE ProvinceID = '{0}'",ProvinceID);
			DataTable table = DBAccess.ExcuteDataTable(cn.Connection,commandText);
			Hospital[] hos = new Hospital[table.Rows.Count];
			for(int i=0;i<hos.Length;i++)
			{
				hos[i].HospitalID = Convert.ToInt32(table.Rows[i]["HospitalID"]);
				hos[i].Name = table.Rows[i]["Name"].ToString();
				hos[i].GradeID = Convert.ToInt32(table.Rows[i]["GradeID"]);
				hos[i].ProvinceID = Convert.ToInt32(table.Rows[i]["ProvinceID"]);
				hos[i].CityID = Convert.ToInt32(table.Rows[i]["CityID"]);
				hos[i].Traffic = table.Rows[i]["Traffic"].ToString();
				hos[i].Address = table.Rows[i]["Address"].ToString();
			}
			return hos;
		}
		
		public Hospital[] GetHosByCityID(int CityID)
		{
			string commandText  = string.Format(@"SELECT * FROM Hospitall WHERE CityID = '{0}'",CityID);
			DataTable table = DBAccess.ExcuteDataTable(cn.Connection,commandText);
			Hospital[] hos = new Hospital[table.Rows.Count];
			for(int i=0;i<hos.Length;i++)
			{
				hos[i].HospitalID = Convert.ToInt32(table.Rows[i]["HospitalID"]);
				hos[i].Name = table.Rows[i]["Name"].ToString();
				hos[i].GradeID = Convert.ToInt32(table.Rows[i]["GradeID"]);
				hos[i].ProvinceID = Convert.ToInt32(table.Rows[i]["ProvinceID"]);
				hos[i].CityID = Convert.ToInt32(table.Rows[i]["CityID"]);
				hos[i].Traffic = table.Rows[i]["Traffic"].ToString();
				hos[i].Address = table.Rows[i]["Address"].ToString();
			}
			return hos;
		}

		public DataTable Check(string HosName,int GradeID,int ProID,int CityID,string Symptom)
		{

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

			
		}

		public int AddHosp(Hospital hospital)
		{
			string commandText = string.Format("INSERT INTO Hospitall (Name,GradeID,ProvinceID,CityID,Address,Traffic) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')",hospital.Name,hospital.GradeID,hospital.ProvinceID,hospital.CityID,hospital.Address,hospital.Traffic);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}
		public DataTable HosTable()
		{
			string commandText = string.Format(@"SELECT Hospitall.Name, Hospitall.GradeID, Hospitall.ProvinceID, 
												Dictionary_1.Detail AS ProvinceName, Hospitall.CityID, City.CityName, 
												Hospitall.Address, Hospitall.Traffic, Hospitall.HospitalID, 
												Dictionary_2.Detail AS Grade
											FROM City INNER JOIN
												Dictionary Dictionary_1 INNER JOIN
												Dictionary Dictionary_2 INNER JOIN
												Hospitall ON Dictionary_2.ID = Hospitall.GradeID ON 
												Dictionary_1.ID = Hospitall.ProvinceID ON City.CityID = Hospitall.CityID");
			return DBAccess.ExcuteDataTable(cn.Connection,commandText);
		}

		public int UpdateHos(Hospital hospital,int HosptialID)
		{
			string commandText = string.Format(@"UPDATE Hospitall SET Name = '{0}',GradeID = '{1}',ProvinceID = '{2}',CityID = '{3}',Address = '{4}',Traffic = '{5}' WHERE HospitalID = '{6}'",hospital.Name,hospital.GradeID,hospital.ProvinceID,hospital.CityID,hospital.Address,hospital.Traffic,HosptialID);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}
		public int DeleteHos(int HosptialID)
		{
			string commandText = string.Format(@"DELETE FROM Hospitall WHERE HospitalID = '{0}'",HosptialID);
			return DBAccess.ExcuteCommand(cn.Connection,commandText);
		}

		public DataTable SerchHos(string HosName,int ProvinceID,int CityID,int GradeID)
		{


			string Null = "";
			string where = " WHERE";

			string commandText = string.Format(@"SELECT Hospitall.HospitalID, Hospitall.Name, Hospitall.GradeID, Hospitall.ProvinceID, 
													Hospitall.CityID, Hospitall.Address, Hospitall.Traffic, 
													Dictionary_1.Detail AS ProvinceName, City.CityName, 
													Dictionary_6.Detail AS Grade
												FROM Hospitall INNER JOIN
													Dictionary Dictionary_6 ON Hospitall.GradeID = Dictionary_6.ID INNER JOIN
													Dictionary Dictionary_1 ON Hospitall.ProvinceID = Dictionary_1.ID INNER JOIN
													City ON Hospitall.CityID = City.CityID");

			if(HosName != "")
			{
				if(Null == "")
					commandText += where;
				commandText +=Null+ string.Format(" Hospitall.Name LIKE '{0}'",HosName);
				Null = " AND";
			}
			if(ProvinceID != -1)
			{
				if(Null == "")
					commandText += where;
				commandText +=Null+ string.Format(" Hospitall.ProvinceID = '{0}'",ProvinceID);
				Null = " AND";
			}
			if(CityID != -1)
			{
				if(Null == "")
					commandText += where;
				commandText +=Null+ string.Format(" Hospitall.CityID = '{0}'",CityID);
				Null = " AND";
			}
			if(GradeID != -1)
			{
				if(Null == "")
					commandText += where;
				commandText +=Null+ string.Format(" Hospitall.GradeID = '{0}'",GradeID);
				Null = " AND";
			}

			return DBAccess.ExcuteDataTable(cn.Connection,commandText);
		}
	}
}