/* * 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; } }
private ProgramModel convertCursorRecordToModel(SqlDataReader dataReader) { ProgramModel programModel = new ProgramModel(); programModel.id = dataReader.GetInt32(0); programModel.name = dataReader.GetString(1); programModel.programVarietyId = dataReader.GetInt32(2); return(programModel); }
private ProgramModel convertCursorRecordToModel(SqlDataReader dataReader) { ProgramModel programModel = new ProgramModel(); programModel.id = dataReader.GetInt32(0); programModel.programTypeId = dataReader.GetInt32(1); programModel.programCostId = dataReader.GetInt32(2); programModel.dogSpacesMaximum = dataReader.GetInt32(3); programModel.noOfClasses = dataReader.GetInt32(4); return(programModel); }
/* * Purpose: * Reads all records from the program table * * Parameters: * No parameters * * Return: * All information from every record * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public List <ProgramModel> readAll() { List <ProgramModel> programs = new List <ProgramModel>(); 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("ReadProgram"); 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 program model for every record in the data reader ProgramModel programModel = new ProgramModel(); //Copy information from the data reader into the new data model programModel = convertCursorRecordToModel(dataReader); //Copy the new model onto the end of the list programs.Add(programModel); } //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(programs); }
/* * Purpose: * Creates a new record in the program table * * Parameters: * ProgramModel will be populated, although programID will have a defualt value of 0 * * Return: * programID of the record created * * Exceptions: * Logs any exception thrown within the stored procedure and rethrows */ public int create(ProgramModel programModel) { int programID = 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("CreateProgram"); commandCreate.CommandType = System.Data.CommandType.StoredProcedure; commandCreate.Parameters.Add("@ProgramID", SqlDbType.Int).Direction = ParameterDirection.Output; //Output parameter that will be returned from this function commandCreate.Parameters.Add("@programTypeID", SqlDbType.Int).Value = programModel.programTypeId; commandCreate.Parameters.Add("@programCostID", SqlDbType.Int).Value = programModel.programCostId; commandCreate.Parameters.Add("@dogSpacesMaximum", SqlDbType.Int).Value = programModel.dogSpacesMaximum; commandCreate.Parameters.Add("@noOfClasses", SqlDbType.Int).Value = programModel.noOfClasses; //Which connection to execute the command against commandCreate.Connection = connection; //Execute the command commandCreate.ExecuteNonQuery(); //Used for creating new records //Handle return values programID = Convert.ToInt32(commandCreate.Parameters["@ProgramID"].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(programID); }
/* * Purpose: * Reads a record from the program table * * Parameters: * programID 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 ProgramModel read(int programID) { ProgramModel programModel = new ProgramModel(); 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("ReadProgramByID"); 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 dataReader.Read(); //Only expecting one record - Read moves to the first record - Get values from the columns programModel = 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(programModel); }