/* * Purpose: * Deletes a record from the class table * * Parameters: * classID to select a specific record * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void delete(int classID) { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("DeleteClassByID"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ClassID", SqlDbType.Int).Value = classID; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for deleting new records //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown delete: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } }
/* * Purpose: * Updates a record from the waitingList table * * Parameters: * waitingListID to select a specific record * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void update(WaitingListModel waitingListModel) { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("UpdateWaitingListByID"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@WaitingListID", SqlDbType.Int).Value = waitingListModel.id; commandCreate.Parameters.Add("@ClientID", SqlDbType.Int).Value = waitingListModel.clientId; commandCreate.Parameters.Add("@DogID", SqlDbType.Int).Value = waitingListModel.dogId; commandCreate.Parameters.Add("@ProgramTypeID", SqlDbType.Int).Value = waitingListModel.programTypeId; commandCreate.Parameters.Add("@JoinDate", SqlDbType.DateTime).Value = waitingListModel.joinDate; commandCreate.Parameters.Add("@InviteIssued", SqlDbType.Bit).Value = waitingListModel.inviteIssued; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for updating new records //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown update: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } }
/* * Purpose: * Updates a record from the dog table * * Parameters: * dogID to select a specific record * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void update(DogModel dogModel) { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("UpdateDogByID"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@DogID", SqlDbType.Int).Value = dogModel.id; commandCreate.Parameters.Add("@ClientID", SqlDbType.Int).Value = dogModel.clientID; commandCreate.Parameters.Add("@Name", SqlDbType.VarChar).Value = dogModel.name; commandCreate.Parameters.Add("@Age", SqlDbType.Int).Value = dogModel.age; commandCreate.Parameters.Add("@Breed", SqlDbType.VarChar).Value = dogModel.breed; commandCreate.Parameters.Add("@ExperienceOrQualification", SqlDbType.Bit).Value = dogModel.experienceOrQualification; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for updating new records //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown update: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } }
/* * Purpose: * Updates a record from the program table * * Parameters: * programID to select a specific record * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void update(ProgramModel programModel) { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("UpdateProgramByID"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programModel.id; commandCreate.Parameters.Add("@name", SqlDbType.Text).Value = programModel.name; commandCreate.Parameters.Add("@programVarietyID", SqlDbType.Int).Value = programModel.programVarietyId; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for updating new records //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown update: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } }
/* * Purpose: * Updates a record from the programCost table * * Parameters: * programCostID to select a specific record * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void update(ProgramCostModel programCostModel) { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("UpdateProgramCostByID"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ProgramCostID", SqlDbType.Int).Value = programCostModel.id; commandCreate.Parameters.Add("@DepositAmount", SqlDbType.Float).Value = programCostModel.depositAmount; commandCreate.Parameters.Add("@SessionCost", SqlDbType.Float).Value = programCostModel.sessionCost; commandCreate.Parameters.Add("@FullPaymentPercentageDiscount", SqlDbType.Float).Value = programCostModel.fullPaymentPercentageDiscount; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for updating new records //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown update: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } }
public List <ReportScheduleModel> readReportSchedule() { List <ReportScheduleModel> reportBillingList = new List <ReportScheduleModel>(); try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandRead = new SqlCommand("ScheduleReport"); commandRead.CommandType = System.Data.CommandType.StoredProcedure; //Which connection to execute the command against commandRead.Connection = connection; //Execute the command SqlDataReader dataReader = commandRead.ExecuteReader(); //Used for reading records //Handle return values while (dataReader.Read()) //Expecting multiple records - Read moves to the next record - Get values from the columns { //Create a new class model for every record in the data reader ReportScheduleModel reportScheduleModel = new ReportScheduleModel(); reportScheduleModel.classId = dataReader.GetInt32(0); reportScheduleModel.classDate = dataReader.GetDateTime(1); reportScheduleModel.startTime = dataReader.GetTimeSpan(2); reportScheduleModel.endTime = dataReader.GetTimeSpan(3); reportScheduleModel.programVarietyId = dataReader.GetInt32(4); reportScheduleModel.programVarietyName = dataReader.GetString(5); reportScheduleModel.programId = dataReader.GetInt32(6); reportScheduleModel.programName = dataReader.GetString(7); reportScheduleModel.staffId = dataReader.GetInt32(8); reportScheduleModel.staffName = dataReader.GetString(9); //Copy the new model onto the end of the list reportBillingList.Add(reportScheduleModel); } //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown ReportBillingList: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(reportBillingList); }
public List <ReportAttendeesModel> readReportAttendees(Int32 programID) { List <ReportAttendeesModel> reportAttendeesList = new List <ReportAttendeesModel>(); try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandRead = new SqlCommand("AttendeesReport"); commandRead.CommandType = System.Data.CommandType.StoredProcedure; commandRead.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programID; //Which connection to execute the command against commandRead.Connection = connection; //Execute the command SqlDataReader dataReader = commandRead.ExecuteReader(); //Used for reading records //Handle return values while (dataReader.Read()) //Expecting multiple records - Read moves to the next record - Get values from the columns { //Create a new class model for every record in the data reader ReportAttendeesModel reportAttendeesModel = new ReportAttendeesModel(); reportAttendeesModel.clientName = dataReader.GetString(0); reportAttendeesModel.dogName = dataReader.GetString(1); //Copy the new model onto the end of the list reportAttendeesList.Add(reportAttendeesModel); } //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown ReportAttendeesList: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(reportAttendeesList); }
/* * Purpose: * Reads all records from the class table * * Parameters: * No parameters * * Return: * All information from every record * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public List <ClassModel> readAll() { List <ClassModel> classs = new List <ClassModel>(); try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandRead = new SqlCommand("ReadClass"); commandRead.CommandType = System.Data.CommandType.StoredProcedure; //Which connection to execute the command against commandRead.Connection = connection; //Execute the command SqlDataReader dataReader = commandRead.ExecuteReader(); //Used for reading records //Handle return values while (dataReader.Read()) //Expecting multiple records - Read moves to the next record - Get values from the columns { //Create a new class model for every record in the data reader ClassModel classModel = new ClassModel(); //Copy information from the data reader into the new data model classModel = convertCursorRowToModel(dataReader); //Copy the new model onto the end of the list classs.Add(classModel); } //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown readAll: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(classs); }
/* * Purpose: * Creates a new record in the enrollment table * * Parameters: * EnrollmentModel will be populated, although enrollmentID will have a defualt value of 0 * * Return: * enrollmentID of the record created * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public int create(EnrollmentModel enrollmentModel) { int enrollmentID = 0; try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("CreateEnrollment"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@EnrollmentID", SqlDbType.Int).Direction = ParameterDirection.Output; //Output parameter that will be returned from this function commandCreate.Parameters.Add("@Name", SqlDbType.Text).Value = enrollmentModel.name; commandCreate.Parameters.Add("@ClientID", SqlDbType.Int).Value = enrollmentModel.clientId; commandCreate.Parameters.Add("@DogID", SqlDbType.Int).Value = enrollmentModel.dogId; commandCreate.Parameters.Add("@ProgramID", SqlDbType.Int).Value = enrollmentModel.programId; commandCreate.Parameters.Add("@PaymentMethod", SqlDbType.Int).Value = enrollmentModel.paymentMethod; commandCreate.Parameters.Add("@JoinDate", SqlDbType.Date).Value = enrollmentModel.joinDate; commandCreate.Parameters.Add("@InviteIssued", SqlDbType.Bit).Value = enrollmentModel.inviteIssued; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for creating new records //Handle return values enrollmentID = Convert.ToInt32(commandCreate.Parameters["@EnrollmentID"].Value); //Convert the output value from the SP into an int //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown create: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(enrollmentID); }
/* * Purpose: * Creates a new record in the programCost table * * Parameters: * ProgramVarietyModel will be populated, although programCostID will have a defualt value of 0 * * Return: * programCostID of the record created * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public int create(ProgramVarietyModel programVarietyModel) { int programCostID = 0; try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("CreateProgramVariety"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ProgramVarietyID", SqlDbType.Int).Direction = ParameterDirection.Output; //Output parameter that will be returned from this function commandCreate.Parameters.Add("@Name", SqlDbType.Text).Value = programVarietyModel.name; commandCreate.Parameters.Add("@DepositAmount", SqlDbType.Float).Value = programVarietyModel.depositAmount; commandCreate.Parameters.Add("@SessionCost", SqlDbType.Float).Value = programVarietyModel.sessionCost; commandCreate.Parameters.Add("@FullPaymentPercentageDiscount", SqlDbType.Float).Value = programVarietyModel.fullPaymentPercentageDiscount; commandCreate.Parameters.Add("@DogSpacesMaximum", SqlDbType.Int).Value = programVarietyModel.dogSpacesMaximum; commandCreate.Parameters.Add("@NoOfClasses", SqlDbType.Int).Value = programVarietyModel.noOfClasses; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for creating new records //Handle return values programCostID = Convert.ToInt32(commandCreate.Parameters["@ProgramVarietyID"].Value); //Convert the output value from the SP into an int //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown create: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(programCostID); }
/* * Purpose: * Creates a new record in the class table * * Parameters: * ClassModel will be populated, although classID will have a defualt value of 0 * * Return: * classID of the record created * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public int create(ClassModel classModel) { int classID = 0; try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("CreateClass"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ClassID", SqlDbType.Int).Direction = ParameterDirection.Output; //Output parameter that will be returned from this function commandCreate.Parameters.Add("@ProgramID", SqlDbType.Int).Value = classModel.programId; commandCreate.Parameters.Add("@StaffID", SqlDbType.Int).Value = classModel.staffId; commandCreate.Parameters.Add("@ClassDate", SqlDbType.Date).Value = classModel.classDate; commandCreate.Parameters.Add("@StartTime", SqlDbType.Time).Value = classModel.startTime; commandCreate.Parameters.Add("@EndTime", SqlDbType.Time).Value = classModel.endTime; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for creating new records //Handle return values classID = Convert.ToInt32(commandCreate.Parameters["@ClassID"].Value); //Convert the output value from the SP into an int //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown create: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(classID); }
/* * Purpose: * Creates a new record in the dog table * * Parameters: * DogModel will be populated, although dogID will have a defualt value of 0 * * Return: * dogID of the record created * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public int create(DogModel dogModel) { int dogID = 0; try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("CreateDog"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@DogID", SqlDbType.Int).Direction = ParameterDirection.Output; //Output parameter that will be returned from this function commandCreate.Parameters.Add("@ClientID", SqlDbType.Int).Value = dogModel.clientID; commandCreate.Parameters.Add("@Name", SqlDbType.VarChar).Value = dogModel.name; commandCreate.Parameters.Add("@Age", SqlDbType.Int).Value = dogModel.age; commandCreate.Parameters.Add("@Breed", SqlDbType.VarChar).Value = dogModel.breed; commandCreate.Parameters.Add("@ExperienceOrQualification", SqlDbType.Bit).Value = dogModel.experienceOrQualification; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for creating new records //Handle return values dogID = Convert.ToInt32(commandCreate.Parameters["@DogID"].Value); //Convert the output value from the SP into an int //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown create: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(dogID); }
/* * Purpose: * Counts the number of records in the class table * * Parameters: * No parameters * * Return: * The number of records * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public int count() { int count = 0; try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandRead = new SqlCommand("CountClass"); commandRead.CommandType = System.Data.CommandType.StoredProcedure; //Which connection to execute the command against commandRead.Connection = connection; //Execute the command SqlDataReader dataReader = commandRead.ExecuteReader(); //Used for counting records //Only overwrite defualt when record is returned if (dataReader.HasRows == true) { //Handle return values dataReader.Read(); //Expecting one record with one column count = dataReader.GetInt32(0); } //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown count: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(count); }
/* * Purpose: * Deletes all record from the class table * * Parameters: * No parameters * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void deleteAll() { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); SqlConnection connection = dataStoreHelper.createConnection(); SqlCommand commandCreate = new SqlCommand("DeleteClassAll"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Connection = connection; commandCreate.ExecuteNonQuery(); dataStoreHelper.closeConnection(connection); } catch (Exception ex) { Console.WriteLine("Exception thrown: " + ex.Message); } }
/* * Purpose: * Reads a record from the class table * * Parameters: * classID allows the user to select a specific record * * Return: * All information from the selected record * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public ClassModel read(int classID) { ClassModel classModel = new ClassModel(); try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandRead = new SqlCommand("ReadClassByID"); commandRead.CommandType = System.Data.CommandType.StoredProcedure; commandRead.Parameters.Add("@ClassID", SqlDbType.Int).Value = classID; //Which connection to execute the command against commandRead.Connection = connection; //Execute the command SqlDataReader dataReader = commandRead.ExecuteReader(); //Used for reading records //Handle return values dataReader.Read(); //Only expecting one record - Read moves to the first record - Get values from the columns classModel = convertCursorRowToModel(dataReader); //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown read: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } return(classModel); }
/* * Purpose: * Updates a record from the payment table * * Parameters: * paymentID to select a specific record * * Return: * No return * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public void update(PaymentModel paymentModel) { try { DataStoreConnectionHelper dataStoreHelper = new DataStoreConnectionHelper(); //Create connection to database SqlConnection connection = dataStoreHelper.createConnection(); //Set up the stored procedure and the parameters SqlCommand commandCreate = new SqlCommand("UpdatePaymentByID"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@PaymentID", SqlDbType.Int).Value = paymentModel.id; commandCreate.Parameters.Add("@EnrollmentID", SqlDbType.Int).Value = paymentModel.enrollmentId; commandCreate.Parameters.Add("@PaymentType", SqlDbType.Text).Value = paymentModel.paymentType; commandCreate.Parameters.Add("@PaymentAmountDue", SqlDbType.Float).Value = paymentModel.paymentAmountDue; commandCreate.Parameters.Add("@PaymentAmountDueDate", SqlDbType.Date).Value = paymentModel.paymentAmountDueDate; commandCreate.Parameters.Add("@PaymentRecieved", SqlDbType.Bit).Value = paymentModel.paymentRecieved; commandCreate.Parameters.Add("@PaymentRecievedDate", SqlDbType.Date).Value = paymentModel.paymentRecievedDate; commandCreate.Parameters.Add("@RecieptIssued", SqlDbType.Bit).Value = paymentModel.recieptIssued; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for updating new records //Close connection to database dataStoreHelper.closeConnection(connection); } catch (Exception ex) { //Log the exception and rethrow Console.WriteLine("Exception thrown update: " + this.GetType().Name + ". <" + ex.Message + ">"); throw ex; } }