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] + "='" + ResName + "'"; //verify input keys if (BookingID < 0) { return -1; } if (ResName == "" || ResName == "*" || ResName == " ") { return -1; } //Check record exists in the first place ResourceRequest test = new ResourceRequest(); int initCheck = test.Load(BookingID, ResName); 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, ResName); 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 (ResName == "" || ResName == "*" || ResName == " ") { return -1; } ResourceRecord test = new ResourceRecord(); result = test.Load(ResName); 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 += "'" + ResName + "', "; queryFull += "'" + Requester + "', "; queryFull += Quantity + ", "; queryFull += "'" + Convert.ToString(Approved) + "', "; queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')"; } else { //verify the record actually exists ResourceRequest testA = new ResourceRequest(); int initCheck = testA.Load(BookingID, ResName); 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] + "='" + ResName + "'"; //compound key! queryFull += attribNames[2] + "='" + Requester + "', "; queryFull += attribNames[3] + "=" + Quantity + ", "; queryFull += attribNames[4] + "='" + Convert.ToString(Approved) + "', "; queryFull += attribNames[5] + "='" + 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; } //if new record, verify it saved correctly if (NewOrOld == true) { int saveCheck = Load(BookingID, ResName); if (saveCheck < 0) { return -1; } } NewOrOld = false; //fail-safe return 0; }
public int Populate(int bookingID) { //re-initialise lists resRequests = new List<ResourceRequest>(); resNames = 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 { resNames.Add(dbReader[attribNames[1]].ToString()); } catch { return -1; } } //verify more than one result. If not, report error if (resNames.Count() < 1) { return -1; } //Use this list to import resource request records foreach (String res in resNames) { ResourceRequest newRequest = new ResourceRequest(); int importNote = newRequest.Load(bookingID, res); if (importNote < 0) { return -1; } //if failed, bail with error code else { resRequests.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 (resRequests.Count() < 1) { return -1; } //Clean-up DB connection try { dbReader.Close(); dbConnection.Close(); } catch { return -1; } return 0; }