//Method that adds doctor to database when you add doctor in program
public static void AddDoctor(ClassDoctor doctor)
{
string querry = "SET IDENTITY_INSERT [db_Clinic].[dbo].[tbl_Doctor] ON " +
"INSERT INTO [db_Clinic].[dbo].[tbl_Doctor] (Doctor_id,Active,Type_of_specialization_id,Office_id,Degree_of_doctor_id,Employee_id) " +
"VALUES ((SELECT TOP 1 Doctor_id FROM tbl_Doctor ORDER BY Doctor_id DESC)+1,@active,@typeOfSpecializationId,(SELECT Office_id FROM tbl_Office WHERE Office_number=@officeId),@degreeOfDoctorId, @employee_id)" +
"SET IDENTITY_INSERT [db_Clinic].[dbo].[tbl_Doctor] OFF ";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand cmd = new SqlCommand(querry, sqlCon);
if (doctor.Active == true)
{
cmd.Parameters.AddWithValue("@active", "Yes");
}
else
{
cmd.Parameters.AddWithValue("@active", "No");
}
cmd.Parameters.AddWithValue("@typeOfSpecializationId", doctor.TypeOfSpecialization.TypeOfSpecializationId);
cmd.Parameters.AddWithValue("@officeId", doctor.OfficeNumber);
cmd.Parameters.AddWithValue("@degreeOfDoctorId", doctor.Degree.DegreeOfDoctorId);
cmd.Parameters.AddWithValue("@employee_id", doctor.EmployeeId);
cmd.ExecuteNonQuery();
sqlCon.Close();
}
//Method that updates doctor in database when you edit doctor in program
public static void UpdateDoctor(ClassDoctor doctor)
{
string querry = "UPDATE [db_Clinic].[dbo].[tbl_Doctor] SET " +
"[Active] = @active,[Type_of_specialization_id] = @TypeOfSpecializationId, " +
"[Degree_of_doctor_id] = @degreeOfDoctorId" +
" Where [Doctor_id] = @Doctor_id ";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand cmd = new SqlCommand(querry, sqlCon);
if (doctor.Active == true)
{
cmd.Parameters.AddWithValue("@active", "Yes");
}
else
{
cmd.Parameters.AddWithValue("@active", "No");
}
cmd.Parameters.AddWithValue("@Doctor_id", doctor.Doctor_id);
cmd.Parameters.AddWithValue("@degreeOfDoctorId", doctor.Degree.DegreeOfDoctorId);
cmd.Parameters.AddWithValue("@TypeOfSpecializationId", doctor.TypeOfSpecialization.TypeOfSpecializationId);
SqlDataReader dr = cmd.ExecuteReader();
sqlCon.Close();
}
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);
}