//Method that gets specializations from database and returns list of specializations
public static List <ClassSpecialization> SpecializationList()
{
string querryp1 = "USE db_Clinic SELECT [Specialization_id],[Specialization] ";
string querryp2 = "FROM [tbl_Specialization]";
string querry = querryp1 + querryp2;
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
SqlDataReader dr = sqlCommand.ExecuteReader();
List <ClassSpecialization> SpecializationList = new List <ClassSpecialization>();
while (dr.Read())
{
ClassSpecialization specialization = new ClassSpecialization();
specialization.SpecializationId = dr.GetInt32("Specialization_id");
specialization.Specialization = dr.GetString("Specialization");
SpecializationList.Add(specialization);
}
sqlCon.Close();
return(SpecializationList);
}
public static List <ClassDoctor> DoctorList()
{
string querry = "" +
"USE[db_Clinic] SELECT[Doctor_id],[Name],[Surname],[Phone_number],[Active],[Degree],[Type_of_specialization],[Office_id],[Specialization], " +
"[tbl_Degree_of_doctor].[Degree_of_doctor_id],[tbl_Type_of_specialization].[Type_of_specialization_id],[tbl_Specialization].[Specialization_id] " +
"FROM[dbo].[tbl_Doctor], [dbo].[tbl_Degree_of_doctor], [dbo].[tbl_Specialization],[dbo].[tbl_Type_of_specialization], [dbo].[tbl_Employee] " +
"WHERE[tbl_Doctor].[Degree_of_doctor_id] =[tbl_Degree_of_doctor].[Degree_of_doctor_id] " +
"AND[tbl_Doctor].[Type_of_specialization_id] =[tbl_Type_of_specialization].[Type_of_specialization_id] " +
"AND[tbl_Type_of_specialization].[Specialization_id] =[tbl_Specialization].[Specialization_id] " +
"AND[tbl_Doctor].[Employee_id] =[tbl_Employee].[Employee_id] ";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
SqlDataReader dr = sqlCommand.ExecuteReader();
List <ClassDoctor> dctlist = new List <ClassDoctor>();
while (dr.Read())
{
ClassDoctor dct = new ClassDoctor();
dct.Doctor_id = dr.GetInt32("Doctor_id");
dct.Name = dr.GetString("Name");
dct.Surname = dr.GetString("Surname");
dct.PhoneNumber = dr.GetString("Phone_number");
dct.OfficeNumber = dr.GetInt32("Office_id");
if (dr.GetString("Active").Equals("Yes"))
{
dct.Active = true;
}
else
{
dct.Active = false;
}
ClassDegreeOfDoctor degree = new ClassDegreeOfDoctor();
degree.DegreeOfDoctorId = dr.GetInt32("Degree_of_doctor_id");
degree.Degree = dr.GetString("Degree");
dct.Degree = degree;
ClassTypeOfSpecialization typeOfSpecialization = new ClassTypeOfSpecialization();
typeOfSpecialization.TypeOfSpecializationId = dr.GetInt32("Type_of_specialization_id");
typeOfSpecialization.TypeOfSpecialization = dr.GetString("Type_of_specialization");
dct.TypeOfSpecialization = typeOfSpecialization;
ClassSpecialization specialization = new ClassSpecialization();
specialization.SpecializationId = dr.GetInt32("Specialization_id");
specialization.Specialization = dr.GetString("Specialization");
dct.Specialization = specialization;
dctlist.Add(dct);
}
sqlCon.Close();
return(dctlist);
}