示例#1
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;
        }
示例#2
0
        public int Populate(int bookingID)
        {
            //re-initialise lists
            notes = new List<BookingNote>();
            userNames = 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;
            }

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

            //verify more than one result. If not, report error
            if (userNames.Count() < 1) { return -1; }

            //now we know both booking ID & their creators (primary key values)
            //...and can now use these to populate list of notes
            foreach (String user in userNames)
            {
                BookingNote newNote = new BookingNote();
                int importNote = newNote.Load(bookingID, user);
                if (importNote < 0) { return -1; }  //if failed, bail with error code
                else
                {
                    notes.Add(newNote); //if all went well, add to list
                }
            }

            //verify at least one note. If not, send error code to verify there are no notes related to this booking
            if (notes.Count() < 1) { return -1; }

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

            return 0;
        }