public SqlException Update(IsIn isin, int wardNumber)
{
string update = "UPDATE IsIn " +
"SET Shift = @Shift, StaffNumber = @StaffNumber" + "WHERE Number = @wardNumber;";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(update);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@Shift", SqlDbType.Int)
{
Value = isin.Shift
},
new SqlParameter("@StaffNumber", SqlDbType.NVarChar)
{
Value = isin.StaffNumber
},
new SqlParameter("@Number", SqlDbType.Int)
{
Value = isin.WardNumber
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(PatientAppointment PA)
{
//insert
string insert = "INSERT INTO PatientAppointment (AppointmentNumber, DateOfAppointment, AppointmentRoom, PatientNumber)" +
" VALUES(@AppointmentNumber, @DateOfAppointment, @AppointmentRoom, @PatientNumber)";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List<SqlParameter> prm = new List<SqlParameter>()
{
new SqlParameter("@AppointmentNumber", SqlDbType.Int) {Value = PA.AppointmentNumber},
new SqlParameter("@DateOfAppointment", SqlDbType.DateTime) {Value = PA.DateOfAppointment},
new SqlParameter("@AppointmentRoom", SqlDbType.NVarChar) {Value = PA.AppointmentRoom},
new SqlParameter("@PatientNumber", SqlDbType.Int) {Value = PA.PatientNumber},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return res;
}
public SqlException Read(int wardNumber)
{
string select = "SELECT Patient.FirstName, Patient.LastName, Ward.Number FROM Patient " +
"JOIN InPatient ON Patient.PatientNumber=InPatient.PatientNumber JOIN Ward ON Ward.Number=InPatient.WardNumber " +
"WHERE Ward.Number = @WardNumber";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(select);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@WardNumber", SqlDbType.Int)
{
Value = wardNumber
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(Patient p)
{
//insert
string insert = "INSERT INTO Patient (PatientNumber, FirstName, LastName, Address, MaritalStatus, Gender, Telephone, DateOfBirth)" +
" VALUES(@PatientNumber, @FirstName, @LastName, @Address, @MaritalStatus, @Gender, @Telephone, @DateOfBirth)";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@PatientNumber", SqlDbType.Int)
{
Value = p.PatientNumber
},
new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = p.FirstName
},
new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = p.LastName
},
new SqlParameter("@Address", SqlDbType.NVarChar)
{
Value = p.Address
},
new SqlParameter("@MaritalStatus", SqlDbType.NVarChar)
{
Value = p.MartialStatus
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = p.Gender
},
new SqlParameter("@Telephone", SqlDbType.NVarChar)
{
Value = p.Telephone
},
new SqlParameter("@DateOfBirth", SqlDbType.DateTime)
{
Value = p.DateOfBirth
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(InPatient ip)
{
//insert
string insert = "INSERT INTO InPatient (PatientNumber, WardNumber, InWardOrWaitingList, DateExpectedToLeave, DatePlacedOnWaitingList, ExpectedDurationOfStay, ActualDateLeft)" +
" VALUES(@PatientNumber, @WardNumber, @InWardOrWaitingList, @DateExpectedToLeave, @DatePlacedOnWaitingList, @ExpectedDurationOfStay, @ActualDateLeft)";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@PatientNumber", SqlDbType.Int)
{
Value = ip.PatientNumber
},
new SqlParameter("@WardNumber", SqlDbType.Int)
{
Value = ip.WardNumber
},
new SqlParameter("@DatePlacedOnWaitingList", SqlDbType.DateTime)
{
Value = ip.DatePlacedOnWaitingList
},
new SqlParameter("@ExpectedDurationOfStay", SqlDbType.Int)
{
Value = ip.ExpectedDurationOfStay
},
new SqlParameter("@ActualDateLeft", SqlDbType.DateTime)
{
Value = ip.ActualDateLeft
},
new SqlParameter("@InWardOrWaitingList", SqlDbType.NVarChar)
{
Value = ip.InWardOrWaitingList
},
new SqlParameter("@DateExpectedToLeave", SqlDbType.DateTime)
{
Value = ip.DateExpectedToLeave
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Read()
{
string select = "SELECT * FROM Ward";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(select);
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Update(InPatient ip, int patientID)
{
string update = "UPDATE InPatient " +
"SET PatientNumber = @PatientNumber, WardNumber = @WardNumber, DatePlacedOnWaitingList = @DatePlacedOnWaitingList," +
"ExpectedDurationOfStay = @ExpectedDurationOfStay, ActualDateLeft = @ActualDateLeft, InWardOrWaitingList = @InWardOrWaitingList" +
"WHERE PatientNumber = @PatientID;";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(update);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@PatientNumber", SqlDbType.Int)
{
Value = ip.PatientNumber
},
new SqlParameter("@WardNumber", SqlDbType.Int)
{
Value = ip.WardNumber
},
new SqlParameter("@DatePlacedOnWaitingList", SqlDbType.DateTime)
{
Value = ip.DatePlacedOnWaitingList
},
new SqlParameter("@ExpectedDurationOfStay", SqlDbType.Int)
{
Value = ip.ExpectedDurationOfStay
},
new SqlParameter("@ActualDateLeft", SqlDbType.DateTime)
{
Value = ip.ActualDateLeft
},
new SqlParameter("@PatientID", SqlDbType.Int)
{
Value = patientID
},
new SqlParameter("@InWardOrWaitingList", SqlDbType.DateTime)
{
Value = ip.InWardOrWaitingList
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Read()
{
string select = "SELECT Staff.FirstName, Staff.LastName, Ward.Number FROM Staff " +
"JOIN IsIn ON Staff.StaffNumber=IsIn.StaffNumber JOIN Ward ON Ward.Number=IsIn.Number";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(select);
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(WorkExperience WE)
{
//insert
string insert = "INSERT INTO WorkExperience (WorkExperienceID, PreviousPosition, NameOfOrganisation, StartDate, FinishDate)" +
" VALUES(@WorkExperienceID, @PreviousPosition, @NameOfOrganisation, @StartDate, @FinishDate)";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@WorkExperienceID", SqlDbType.Int)
{
Value = WE.WorkExperienceID
},
new SqlParameter("@PreviousPosition", SqlDbType.NVarChar)
{
Value = WE.PreviousPosition
},
new SqlParameter("@NameOfOrganisation", SqlDbType.NVarChar)
{
Value = WE.NameOfOrganisation
},
new SqlParameter("@StartDate", SqlDbType.DateTime)
{
Value = WE.StartDate
},
new SqlParameter("@FinishDate", SqlDbType.DateTime)
{
Value = WE.FinishDate
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(Ward ward)
{
//insert
string insert = "INSERT INTO Ward (Number, WardName, Location, TotalNumberOfBeds, TelephoneExtentionNumber)" +
" VALUES(@WardNumber, @WardName, @Location, @TotalNumberOfBeds, @TelephoneExtentionNumber); ";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@WardNumber", SqlDbType.Int)
{
Value = ward.WardNumber
},
new SqlParameter("@WardName", SqlDbType.NVarChar)
{
Value = ward.WardName
},
new SqlParameter("@Location", SqlDbType.NVarChar)
{
Value = ward.Location
},
new SqlParameter("@TotalNumberOfBeds", SqlDbType.Int)
{
Value = ward.TotalNumberOfBeds
},
new SqlParameter("@TelephoneExtentionNumber", SqlDbType.NVarChar)
{
Value = ward.TelephoneExtentionNumber
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Update(Ward ward, int id)
{
string update = "UPDATE Ward " +
"SET Number = @WardNumber, Location = @Location, TotalNumberOfBeds = @TotalNumberOfBeds, TelephoneExtentionNumber = @TelephoneExtentionNumber" + "WHERE WardNumber = @id;";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(update);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@WardNumber", SqlDbType.Int)
{
Value = ward.WardNumber
},
new SqlParameter("@Location", SqlDbType.NVarChar)
{
Value = ward.Location
},
new SqlParameter("@TotalNumberOfBeds", SqlDbType.Int)
{
Value = ward.TotalNumberOfBeds
},
new SqlParameter("@TelephoneExtentionNumber", SqlDbType.NVarChar)
{
Value = ward.TelephoneExtentionNumber
},
new SqlParameter("@id", SqlDbType.Int)
{
Value = id
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(Qualification qualification)
{
//insert
string insert = "INSERT INTO Qualification (DateOfQualification, NameOfInstitution, QualificationID, Type)" +
"VALUES(@DateOfQualification, @NameOfInstitution, @QualificationID, @Type)";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@DateOfQualification", SqlDbType.DateTime)
{
Value = qualification.DateOfQualification
},
new SqlParameter("@NameOfInstitution", SqlDbType.NVarChar)
{
Value = qualification.NameOfInstitution
},
new SqlParameter("@QualificationID", SqlDbType.Int)
{
Value = qualification.QualificationID
},
new SqlParameter("@Type", SqlDbType.NVarChar)
{
Value = qualification.Type
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Delete(int id)
{
string delete = "DELETE IsIn WHERE IsInNumber = @id";
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(delete);
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@id", SqlDbType.Int)
{
Value = id
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(IsIn isin)
{
//insert "Number" is ward number
string insert = "INSERT INTO IsIn (Shift, StaffNumber, Number)" +
" VALUES(@Shift, @StaffNumber, @Number); ";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@Shift", SqlDbType.NVarChar)
{
Value = isin.Shift
},
new SqlParameter("@StaffNumber", SqlDbType.Int)
{
Value = isin.StaffNumber
},
new SqlParameter("@Number", SqlDbType.Int)
{
Value = isin.WardNumber
}
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Create(Staff staff)
{
//insert
string insert = "INSERT INTO Staff (StaffNumber, FirstName, LastName, FullAddress, DateOfBirth, Gender, InsuranceNumber, CurrentPosition, CurrentSalary, SalaryScale, NumberOfHoursPerWeek, PermenantOrTemporary, SalaryPayment, AppointmentNumber, WorkExperienceID, QualificationID)" +
" VALUES(@StaffNumber, @FirstName, @LastName, @FullAddress, @DateOfBirth, @Gender, @InsuranceNumber, @CurrentPosition, @CurrentSalary, @SalaryScale, @NumberOfHoursPerWeek, @PermenantOrTemporary, @SalaryPayment, @AppointmentNumber, @WorkExperienceID, @QualificationID); ";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@StaffNumber", SqlDbType.Int)
{
Value = staff.StaffNumber
},
new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = staff.FirstName
},
new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = staff.LastName
},
new SqlParameter("@FullAddress", SqlDbType.NVarChar)
{
Value = staff.FullAddress
},
new SqlParameter("@DateOfBirth", SqlDbType.DateTime)
{
Value = staff.DateOfBirth
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = staff.Gender
},
new SqlParameter("@InsuranceNumber", SqlDbType.Int)
{
Value = staff.InsuranceNumber
},
new SqlParameter("@CurrentPosition", SqlDbType.NVarChar)
{
Value = staff.CurrentPosition
},
new SqlParameter("@CurrentSalary", SqlDbType.Float)
{
Value = staff.CurrentSalary
},
new SqlParameter("@SalaryScale", SqlDbType.NVarChar)
{
Value = staff.SalaryScale
},
new SqlParameter("@NumberOfHoursPerWeek", SqlDbType.Int)
{
Value = staff.NumberOfHoursPerWeek
},
new SqlParameter("@PermenantOrTemporary", SqlDbType.NVarChar)
{
Value = staff.PermenentOrTemporary
},
new SqlParameter("@SalaryPayment", SqlDbType.NVarChar)
{
Value = staff.SalaryPayment
},
new SqlParameter("@AppointmentNumber", SqlDbType.Int)
{
Value = staff.AppointmentNumber
},
new SqlParameter("@WorkExperienceID", SqlDbType.Int)
{
Value = staff.WorkExperienceID
},
new SqlParameter("@QualificationID", SqlDbType.Int)
{
Value = staff.QualificationID
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public SqlException Update(Staff staff, int id)
{
string update = "UPDATE Staff " +
"SET StaffNumber = @StaffNumber, FirstName = @FirstName, LastName = @LastName, FullAddress = @FullAddress, DateOfBirth = @DateOfBirth, " +
"Gender = @Gender, InsuranceNumber = @InsuranceNumber, CurrentPosition = @CurrentPosition, CurrentSalary = @CurrentSalary, " +
"SalaryScale = @SalaryScale, NumberOfHoursPerWeek = @NumberOfHoursPerWeek, PermenantOrTemporary = @PermenantOrTemporary, " +
"SalaryPayment = @SalaryPayment, AppointmentNumber = @AppointmentNumber, WorkExperienceID = @WorkExperienceID, QualificationID = @QualificationID " +
"WHERE StaffNumber = @id;";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(update);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@StaffNumber", SqlDbType.Int)
{
Value = staff.StaffNumber
},
new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = staff.FirstName
},
new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = staff.LastName
},
new SqlParameter("@FullAddress", SqlDbType.NVarChar)
{
Value = staff.FullAddress
},
new SqlParameter("@DateOfBirth", SqlDbType.DateTime)
{
Value = staff.DateOfBirth
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = staff.Gender
},
new SqlParameter("@InsuranceNumber", SqlDbType.Int)
{
Value = staff.InsuranceNumber
},
new SqlParameter("@CurrentPosition", SqlDbType.NVarChar)
{
Value = staff.CurrentPosition
},
new SqlParameter("@CurrentSalary", SqlDbType.Float)
{
Value = staff.CurrentSalary
},
new SqlParameter("@SalaryScale", SqlDbType.NVarChar)
{
Value = staff.SalaryScale
},
new SqlParameter("@NumberOfHoursPerWeek", SqlDbType.Int)
{
Value = staff.NumberOfHoursPerWeek
},
new SqlParameter("@PermenantOrTemporary", SqlDbType.NVarChar)
{
Value = staff.PermenentOrTemporary
},
new SqlParameter("@SalaryPayment", SqlDbType.NVarChar)
{
Value = staff.SalaryPayment
},
new SqlParameter("@AppointmentNumber", SqlDbType.Int)
{
Value = staff.AppointmentNumber
},
new SqlParameter("@WorkExperienceID", SqlDbType.Int)
{
Value = staff.WorkExperienceID
},
new SqlParameter("@QualificationID", SqlDbType.Int)
{
Value = staff.QualificationID
},
new SqlParameter("@id", SqlDbType.Int)
{
Value = id
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}