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

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " (";
                //the numerical ID increments for us, so we just need to state which fields we're inserting into
                int i = 0;
                foreach (String att in attribNames)
                {
                    if (i > 0 && i < attribNames.Count()-1)
                    {
                        queryFull += att + ", ";
                    }
                    if(i==attribNames.Count()-1)
                    {
                        queryFull += att + ") ";
                    }

                    i++;
                }
                queryFull += "VALUES (";
                queryFull += "'" + bookUserAssigned + "', ";
                queryFull += "'" + bookClient + "', ";
                queryFull += "'" + bookCreator + "', ";
                queryFull += "'" + bookStart.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += "'" + bookEnd.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += "'" + bookTitle + "', ";
                queryFull += "'" + bookDesc + "', ";

                queryFull += "'" + Convert.ToString(bookApproved) + "', ";
                queryFull += "'" + Convert.ToString(bookActive) + "', ";
                queryFull += "'" + bookStatus + "', ";
                queryFull += "'" + Convert.ToString(bookRecurStatus) + "', ";
                queryFull += "'" + bookRecurInterval + "', ";
                queryFull += "'" + Convert.ToString(bookChargeable) + "', ";

                queryFull += bookAmount + ", ";
                queryFull += "'" + bookColour + "', ";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                Booking test = new Booking();
                int initCheck = test.Load(bookID);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query (clientName is skipped because it's a primary key)
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "=" + bookID;
                queryFull += attribNames[1] + "='" + bookUserAssigned + "', ";  //Note: Check this
                queryFull += attribNames[2] + "='" + bookClient + "', ";
                queryFull += attribNames[3] + "='" + bookCreator + "', ";
                queryFull += attribNames[4] + "='" + bookStart.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += attribNames[5] + "='" + bookEnd.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += attribNames[6] + "='" + bookTitle + "', ";
                queryFull += attribNames[7] + "='" + bookDesc + "', ";

                queryFull += attribNames[8] + "='" + Convert.ToString(bookApproved) + "', ";
                queryFull += attribNames[9] + "='" + Convert.ToString(bookActive) + "', ";
                queryFull += attribNames[10] + "='" + bookStatus + "', ";
                queryFull += attribNames[11] + "='" + Convert.ToString(bookRecurStatus) + "', ";
                queryFull += attribNames[12] + "='" + bookRecurInterval + "', ";
                queryFull += attribNames[13] + "='" + Convert.ToString(bookChargeable) + "', ";
                queryFull += attribNames[14] + "=" + bookAmount + ", ";
                queryFull += attribNames[15] + "='" + bookColour + "', ";

                queryFull += attribNames[16] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy HH: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; }

            NewOrOld = false;   //fail-safe

            return 0;
        }
示例#2
0
        public int Populate(String fieldName, String criteria)
        {
            //re-initialise lists
            bookingList = new List<Booking>();
            bookingIDs = new List<int>();

            //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 ";

            //verify fieldname is valid - if it isn't, assume criteria is a special case.
            Boolean fieldTest = false;
            for (int i = 0; i <= numAttribs; i++)
            {
                if (attribNames[i] == fieldName)
                {
                    fieldTest = true;
                    break;
                }
            }
            if (!fieldTest) { queryCondition += criteria; }
            else
            {
                queryCondition += fieldName + "=" + criteria;
            }
            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
                {
                    bookingIDs.Add(Convert.ToInt32(dbReader[attribNames[0]].ToString()));
                }
                catch { return -1; }
            }

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

            //Load found bookings into list
            foreach (int id in bookingIDs)
            {
                Booking newBooking = new Booking();
                int loadNewBooking = newBooking.Load(id);
                if (loadNewBooking < 0) { return -1; }
                else
                {
                    bookingList.Add(newBooking);
                }
            }

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

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