public int Delete() { //Use with caution! This will fail if orphaned entries exist String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "=" + BookingID + " AND " + attribNames[1] + "='" + RoomName + "'"; //verify input keys if (BookingID < 0) { return -1; } if (RoomName == "" || RoomName == "*" || RoomName == " ") { return -1; } //Check record exists in the first place RoomRequest test = new RoomRequest(); int initCheck = test.Load(BookingID, RoomName); if (initCheck < 0) { return -1; } //Wot? No DB integrity checks? Resource Requests don't need them, as deleting won't result in orphan entries queryFull += queryCondition; //pull query together //attempt DB connection try { dbConnection = new SqlConnection(dbInString); dbConnection.Open(); } catch { return -1; } //create command that will run query dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = queryFull; dbCommand.CommandType = CommandType.Text; dbCommand.CommandTimeout = 15; //Run command, and tidy-up if exceptions occur try { dbReader = dbCommand.ExecuteReader(); } catch { dbReader.Close(); dbConnection.Close(); return -1; } //Check was deleted successfully int delCheck = Load(BookingID, RoomName); if (delCheck > -1) { return -1; } NewOrOld = true; //as the record no longer exists in the database, is considered 'new' //Clean-up DB connection try { dbReader.Close(); dbConnection.Close(); } catch { return -1; } return 0; }
public int Save() { //Strings to handle query as it's built String queryFull = "", queryCondition = ""; int result = 0; //verify input keys if (BookingID < 0) { return -1; } /* TO-DO: UNCOMMENT ONCE BOOKING CLASS EXISTS */ //Booking test = new Booking(); //result = test.Load(BookingID); if (RoomName == "" || RoomName == "*" || RoomName == " ") { return -1; } RoomRecord test = new RoomRecord(); result = test.Load(RoomName); if(result<0) { return -1; } //is this a new record? If so, we need an INSERT statement. Else, an UPDATE if (NewOrOld == true) { queryFull = "INSERT INTO " + tableName + " VALUES ("; queryFull += BookingID + ", "; queryFull += "'" + RoomName + "', "; queryFull += "'" + Requester + "', "; queryFull += "'" + Convert.ToString(Approved) + "', "; queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')"; } else { //verify the record actually exists RoomRequest testA = new RoomRequest(); int initCheck = testA.Load(BookingID, RoomName); if (initCheck < 0) { return -1; } //build the UPDATE query (the two primary key fields are skipped here) queryFull = "UPDATE " + tableName + " SET "; queryCondition = "WHERE " + attribNames[0] + "=" + BookingID + " AND " + attribNames[1] + "='" + RoomName + "'"; //compound key! queryFull += attribNames[2] + "='" + Requester + "', "; //as before, ensures bit value is sent queryFull += attribNames[3] + "='" + Convert.ToString(Approved) + "', "; queryFull += attribNames[4] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' "; queryFull += queryCondition; } //attempt DB connection try { dbConnection = new SqlConnection(dbInString); dbConnection.Open(); } catch { return -1; } //create command that will run query dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = queryFull; dbCommand.CommandType = CommandType.Text; dbCommand.CommandTimeout = 15; //Run command, and tidy-up if exceptions occur try { dbReader = dbCommand.ExecuteReader(); } catch { dbReader.Close(); dbConnection.Close(); return -1; } //Clean-up DB connection try { dbReader.Close(); dbConnection.Close(); } catch { return -1; } //check if saved correctly if new if (NewOrOld == true) { int saveCheck = Load(BookingID, RoomName); if (saveCheck < 0) { return -1; } } NewOrOld = false; //fail-safe return 0; }
public int Populate(int bookingID) { //re-initialise lists roomRequests = new List<RoomRequest>(); roomNames = new List<String>(); //Build query - only need a list of creators' usernames, so query has been tailored accordingly String queryFull = "SELECT " + attribNames[1] + " FROM " + tableName + " WHERE " + attribNames[0] + "=" + bookingID; //Verify the ID paramerer value is sensible if (bookingID < 0) { return -1; } //attempt DB connection try { dbConnection = new SqlConnection(dbInString); dbConnection.Open(); } catch { return -1; } //create command that will run query dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = queryFull; dbCommand.CommandType = CommandType.Text; dbCommand.CommandTimeout = 15; //Run command, and tidy-up if exceptions occur try { dbReader = dbCommand.ExecuteReader(); } catch { dbReader.Close(); dbConnection.Close(); return -1; } //import list of resource names from Db while (dbReader.Read()) { try { roomNames.Add(dbReader[attribNames[1]].ToString()); } catch { return -1; } } //verify more than one result. If not, error. if (roomNames.Count() < 1) { return -1; } //Use this list to import resource request records foreach (String res in roomNames) { RoomRequest newRequest = new RoomRequest(); int importNote = newRequest.Load(bookingID, res); if (importNote < 0) { return -1; } //if failed, bail with error code else { roomRequests.Add(newRequest); //if all went well, add to list } } //verify more than one result. Else, error (important, as must be at least one resource request per booking) if (roomRequests.Count() < 1) { return -1; } //Clean-up DB connection try { dbReader.Close(); dbConnection.Close(); } catch { return -1; } return 0; }