/* * 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; } }
private ProgramCostModel convertCursorRecordToModel(SqlDataReader dataReader) { ProgramCostModel programCostModel = new ProgramCostModel(); programCostModel.id = dataReader.GetInt32(0); programCostModel.depositAmount = dataReader.GetDouble(1); programCostModel.sessionCost = dataReader.GetDouble(2); programCostModel.fullPaymentPercentageDiscount = dataReader.GetDouble(3); return(programCostModel); }
/* * Purpose: * Reads all records from the programCost table * * Parameters: * No parameters * * Return: * All information from every record * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public List <ProgramCostModel> readAll() { List <ProgramCostModel> programCosts = new List <ProgramCostModel>(); 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("ReadProgramCost"); 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 programCost model for every record in the data reader ProgramCostModel programCostModel = new ProgramCostModel(); //Copy information from the data reader into the new data model programCostModel = convertCursorRecordToModel(dataReader); //Copy the new model onto the end of the list programCosts.Add(programCostModel); } //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(programCosts); }
/* * Purpose: * Creates a new record in the programCost table * * Parameters: * ProgramCostModel 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(ProgramCostModel programCostModel) { 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("CreateProgramCost"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ProgramCostID", SqlDbType.Int).Direction = ParameterDirection.Output; //Output parameter that will be returned from this function 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 creating new records //Handle return values programCostID = Convert.ToInt32(commandCreate.Parameters["@ProgramCostID"].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: * Reads a record from the programCost table * * Parameters: * programCostID 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 ProgramCostModel read(int programCostID) { ProgramCostModel programCostModel = new ProgramCostModel(); 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("ReadProgramCostByID"); commandRead.CommandType = System.Data.CommandType.StoredProcedure; commandRead.Parameters.Add("@ProgramCostID", SqlDbType.Int).Value = programCostID; //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 programCostModel = convertCursorRecordToModel(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(programCostModel); }