Example #1
0
        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;
        }
Example #3
0
        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);
        }
Example #5
0
        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);
        }
Example #6
0
        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);
        }
Example #7
0
        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);
        }
Example #8
0
        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);
        }
Example #9
0
        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);
        }
Example #10
0
        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);
        }
Example #11
0
        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);
        }
Example #12
0
        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);
        }
Example #13
0
        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);
        }
Example #14
0
        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);
        }