Exemple #1
0
        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] + "='" + Creator + "'";

            //Verify input keys
            if (BookingID < 0) { return -1; }
            if (Creator == "" || Creator == " " || Creator == "*") { return -1; }

            /* TO-DO: Uncomment lines below when Booking class exists */
            //Booking test = New Booking()
            //int bTest = test.Load(BookingID);
            //if( bTest < 0) { return -1; }

            UserRecord uTest = new UserRecord();
            int uInTest = uTest.Load(Creator);
            if (uInTest < 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, Creator);
            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;
        }
Exemple #2
0
        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";

            //Verify input keys
            if (BookingID < 0) { return -1; }
            if (Creator == "" || Creator == " " || Creator == "*") { return -1; }

            /* TO-DO: Uncomment lines below when Booking class exists */
            //Booking test = New Booking()
            //int bTest = test.Load(BookingID);
            //if( bTest < 0) { return -1; }

            UserRecord uTest = new UserRecord();
            int uInTest = uTest.Load(Creator);
            if (uInTest < 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 += "'" + Creator + "', ";
                queryFull += "'" + Note + "', ";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record exists
                BookingNote test = new BookingNote();
                int initCheck = test.Load(BookingID, Creator);
                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] + "='" + Creator + "'"; //compound key!
                queryFull += attribNames[2] + "='" + Note + "', ";
                queryFull += attribNames[3] + "='" + 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 it saved correctly
            if (NewOrOld == true)
            {
                int testAgain = Load(BookingID, Creator);
                if (testAgain < 0) { return -1; }
            }

            NewOrOld = false;   //fail-safe
            return 0;
        }
Exemple #3
0
        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition="";

            //verify input username is valid
            if (userName == "" || userName == "*" || userName == " ") { 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 += "'" + userName + "', ";
                queryFull += "'" + userPass + "', ";
                queryFull += "'" + userRoles + "',";
                queryFull += "'" + userFirstName + "',";
                queryFull += "'" + userSecondName + "',";
                queryFull += "'" + userEmail + "',";
                queryFull += "'" + userJobTitle + "',";
                queryFull += "'" + userDept + "',";
                queryFull += "'" + colourCode + "',";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                UserRecord test = new UserRecord();
                int initCheck = test.Load(userName);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "='" + userName + "'";

                /* TO ADD TO DOCUMENTATION */
                //wot? no element '0'? This method does not update primary keys.
                //this is because a) the database prevents this by design
                //& b) we don't want orphaned entries.

                //to change a username, the following should happen
                //a) load in user record & verify it's the one you expected
                //b) Load in any associated bookings & work tickets (they'll load in any dependencies)
                //c) Run delete method for loaded bookings & work tickets (keep them in memory though)
                //d) Run delete method on existing user record
                //e) Use class setters to set username for loaded user, booking & work ticket objects
                //f) Run save method for user object, THEN for bookings & work ticket

                queryFull += attribNames[1] + "='" + userPass + "', ";
                queryFull += attribNames[2] + "='" + userRoles + "', ";
                queryFull += attribNames[3] + "='" + userFirstName + "', ";
                queryFull += attribNames[4] + "='" + userSecondName + "', ";
                queryFull += attribNames[5] + "='" + userEmail + "', ";
                queryFull += attribNames[6] + "='" + userJobTitle + "', ";
                queryFull += attribNames[7] + "='" + userDept + "', ";
                queryFull += attribNames[8] + "='" + colourCode + "', ";
                queryFull += attribNames[9] + "='" + 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(userName);
                if (saveCheck < 0) { return -1; }
            }

            NewOrOld = false;   //fail-safe
            return 0;
        }
Exemple #4
0
        public int Populate(String fieldName, String criteria)
        {
            //re-initialise lists
            userList = new List<UserRecord>();
            userNames = 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 + " ";
            String queryCondition = "WHERE " + fieldName + "=" + criteria;

            //verify fieldname is valid. If not assume we have 'special criteria'
            Boolean fieldTest = false;
            for (int i = 0; i <= numAttribs; i++)
            {
                if (attribNames[i] == fieldName)
                {
                    fieldTest = true;
                    break;
                }
            }
            if (!fieldTest) {
                if (criteria != "")
                {
                    queryCondition = "WHERE " + criteria;
                }
                else
                {
                    queryCondition = "";
                }
            }
            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;
            }

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

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

            //Load found bookings into list
            foreach (String res in userNames)
            {
                UserRecord newUserRecord = new UserRecord();
                int loadNewUserRecord = newUserRecord.Load(res);
                if (loadNewUserRecord < 0) { return -1; }
                else
                {
                    userList.Add(newUserRecord);
                }
            }

            //verify that more than one booking has been added
            if (userList.Count() < 1) { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }
            return 0;
        }
Exemple #5
0
        /* TO ADD TO DOCUMENTATION */
        // Delete does not take parameters. This is on purpose.
        // Before deleting a record it's important to load it in & verify this is what you want to do
        // Records that have child entries in other tables will throw an SQL error as these are needed for stats purposes
        public int Delete()
        {
            //Use with caution! This will fail if orphaned entries exist
            String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "='" + userName + "'";

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

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

            /* TO DO - UNCOMMENT AND TEST LINES BELOW */
            // block delete if there are any bookings created by or assigned to user
            //Booking bookingNew = new Booking();
            //int bookCreatorCheck = bookingNew.Search(3, userName);    //uses attribID & string as parameters
            //int bookAssignCheck = bookingNew.Search(1, userName);
            //if(bookCreatorCheck > -1 || bookAssignCheck > -1) { return -1; }

            //...likewise for work tickets
            //WorkTicket wtNew = new WorkTicket();
            //int wtCreatorCheck = wtNew.Search(1, userName);
            //int wtAssignCheck = wtNew.Search(3, userName);
            //if(wtCreatorCheck > -1 || wtAssignCheck > -1) { return -1; }

            //...and any requests
            //RoomRequest rrNew = new RoomRequest();
            //int rrCheck = rrNew.Search(2, userName);
            //if(rrCheck > -1) { return -1; }

            /* TO-DO: Make sure txtUserName is actually stored in resource request table */
            //ResourceRequest rsrNew = New ResourceRequest();
            //int rsrCheck = rsrNew.Search(2, userName);
            //if(rsrCheck > -1) { return -1; }

            //...and any log entries
            //WorkTicketLog wtlNew = new WorkTicketLog();
            //int wtlCheck = wtlNew.Search(3, userName);
            //if(wtlCheck > -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(userName);
            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;
        }