Example #1
0
        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";

            //verify input username is valid
            if (roomName == "" || roomName == "*" || roomName == " ") { 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 += "'" + roomName + "', ";
                queryFull += "'" + roomDesc + "', ";
                queryFull += "'" + Convert.ToString(roomActive) + "', ";
                queryFull += roomRate + ", ";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                RoomRecord test = new RoomRecord();
                int initCheck = test.Load(roomName);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query (resourceName is skipped because it's a primary key)
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "='" + roomName + "'";
                queryFull += attribNames[1] + "='" + roomDesc + "', ";
                queryFull += attribNames[2] + "='" + Convert.ToString(roomActive) + "', ";
                queryFull += attribNames[3] + "=" + roomRate + ", ";
                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;
            }

            //check if saved correctly if new
            if (NewOrOld == true)
            {
                int saveCheck = Load(roomName);
                if (saveCheck < 0) { return -1; }
            }

            NewOrOld = false;   //fail-safe

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            return 0;
        }
Example #2
0
        public int Populate(String fieldName, String criteria)
        {
            //re-initialise lists
            roomList = new List<RoomRecord>();
            roomNames = new List<String>();

            //Build query - only need a list of IDs to know which bookings to populate list with
            String queryFull = "SELECT " + attribNames[0] + " FROM " + tableName + " WHERE " + fieldName + "=" + criteria;

            //verify fieldname is valid
            Boolean fieldTest = false;
            for (int i = 0; i <= numAttribs; i++)
            {
                if (attribNames[i] == fieldName)
                {
                    fieldTest = true;
                    break;
                }
            }
            if (!fieldTest) { 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;
            }

            //read in all the results and add them to list of booking IDs
            while (dbReader.Read())
            {
                try
                {
                    roomNames.Add(dbReader[attribNames[0]].ToString());
                }
                catch { return -1; }
            }

            //verify at least one result was returned, else error
            if (roomNames.Count() < 1) { return -1; }

            //Load found bookings into list
            foreach (String res in roomNames)
            {
                RoomRecord newRoomRecord = new RoomRecord();
                int loadNewRoomRecord = newRoomRecord.Load(res);
                if (loadNewRoomRecord < 0) { return -1; }
                else
                {
                    roomList.Add(newRoomRecord);
                }
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }
            return 0;
        }
Example #3
0
        public int Delete()
        {
            //Use with caution! This will fail if orphaned entries exist
            String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "='" + roomName + "'";

            //verify input username is valid
            if (roomName == "" || roomName == "*" || roomName == " ") { return -1; }

            //Check record exists in the first place
            RoomRecord test = new RoomRecord();
            int initCheck = test.Load(roomName);
            if (initCheck < 0) { return -1; }

            /* TO DO - UNCOMMENT AND TEST LINES BELOW */
            //if room has been referenced by any work tickets, don't delete (orphaned entries are bad!)
            //WorkTicket wtNew = new WorkTicket();
            //int wtRoomCheck = wtNew.Search(2, roomName);
            //if(wtRoomCheck > -1) { return -1; }

            //likewise for room requests used by bookings
            //RoomRequest rrNew = new RoomRequest();
            //int rrCheck = rrNew.Search(1, roomName);
            //if(rrCheck > -1) { return -1; }

            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(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;
        }
Example #4
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;
        }