コード例 #1
0
        public void RunQuery(string query)
        {
            //Object for Oracle database connection (creating new connection instance)
            OracleConnection con = new OracleConnection();

            con.ConnectionString = connectionStr;


            string results = "";

            try
            {
                //Opens that database connection as created above
                con.Open();

                //new Oracle command to query the database
                OracleCommand cmd = new OracleCommand();
                //SQL Query to run (the text in the command)
                cmd.CommandText = query;
                cmd.Connection  = con;

                //Executing the database command
                cmd.CommandType = CommandType.Text;
                OracleDataReader dr = cmd.ExecuteReader();


                //This will get the column names and display them at the start of the print

                /*for (int j = 0; j < dr.FieldCount; j++)
                 * {
                 *  MessageBox.Show(dr.GetName(j));
                 *  queryResultsList.Add(dr.GetName(j));
                 * }*/


                //Loops to read the query results
                while (dr.Read())
                {
                    //The reader will have read the entire row (this will find the number of columns)
                    int      numOfColumns = dr.FieldCount;
                    string[] columnValues = new string[numOfColumns];

                    //loops through each column and retrieves the value
                    for (int r = 0; r < numOfColumns; r++)
                    {
                        //if the reader is not NULL (if there is a value to read then proceeed to add the value to the results string
                        if (!dr.IsDBNull(r))
                        {
                            //MessageBox.Show(Convert.ToString(dr.GetFieldType(r)));    //USED TO DISPLAY THE DATATYPE OF A CURRENT COLUMN
                            if (Convert.ToString(dr.GetFieldType(r)) == "System.String")
                            {
                                results += dr.GetString(r) + " ";
                            }
                            else if (Convert.ToString(dr.GetFieldType(r)) == "System.Decimal")
                            {
                                //MessageBox.Show("This is the number" + dr.GetDecimal(r));
                                results += Convert.ToString(dr.GetDecimal(r)) + " ";
                            }
                        }
                        else
                        {
                            results += "NULL ";
                        }
                    }
                    results = "";
                }

                //Method 1:
                OracleDataAdapter sqlData         = new OracleDataAdapter(query, con);
                DataTable         gridViewResults = new DataTable();
                sqlData.Fill(gridViewResults);
                dgv_Results.DataSource = gridViewResults;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Could not read from database");
            }

            //Closes the database connection once done
            con.Close();
        }
コード例 #2
0
        private void InsertIntoTable(String insertStatement)
        {
            //Task 1: Check if dessertype being inserted already exists in database
            //2: Make sure that the NOT NULL columns (DessertType, Price) have data in the textboxes
            //3: Make sure all data in the textboxes is valid (Popularity is a number)
            //4: MessageBox showing what you are trying to add in and ask user to confirm

            //Object for Oracle database connection (creating new connection instance)
            OracleConnection con = new OracleConnection();

            con.ConnectionString = connectionStr;

            //Step1: Ensure that the primary key: desserttype does not already exist in the database
            //This is done in the try-catch while inserting. The exception error is checked to determine if it was due to a primary key duplicate


            //Step2: Ensure NOT NULL columns have filled in data (DessertType, Price)
            try
            {
                if (string.IsNullOrWhiteSpace(txtDessert.Text) || string.IsNullOrWhiteSpace(txtPrice.Text))
                {
                    throw new ArgumentException("Dessert and Price fields cannot be empty");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }


            //Step3: Validates the data to ensure datatypes are correct
            try
            {
                bool[] validateData = new bool[3];
                validateData[0] = char.TryParse(txtSweet.Text, out char tOrF);
                validateData[1] = Int32.TryParse(txtPopularity.Text, out int popularLevel);
                validateData[2] = float.TryParse(txtPrice.Text, out float priceInput);

                for (int i = 0; i < validateData.Length; i++)
                {
                    if (validateData[i] == false)
                    {
                        throw new ArgumentException("Invalid datatype inputted in insert fields");
                    }
                }
            }
            catch (ArgumentException ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }

            //Step4: Reads the user entered data from the text fields and then inserts the new record into the database
            try
            {
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = insertStatement;
                cmd.Connection  = con;
                cmd.CommandType = CommandType.Text;
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                }
                con.Close();

                MessageBox.Show("Successfully Added Record to Database:\n" + "Dessert:" + txtDessert.Text + "\nIs it Sweet:" + txtSweet.Text + "\nPopularity:" + txtPopularity.Text + "\nNotes:" + txtNotes.Text + "\nDate Added:" + txtDate.Text + "\nPrice:" + txtPrice.Text);

                txtDessert.Clear();
                txtSweet.Clear();
                txtPopularity.Clear();
                txtNotes.Clear();
                txtPrice.Clear();
            }
            catch (Exception ex)
            {
                //Checks if the insert error is due to an already existing desserttype (primary key in database)
                if (ex.Message.Contains("unique constraint")) //Message = "ORA-00001: unique constraint (HOMEUSER.SYS_C009071) violated"
                {
                    MessageBox.Show("This dessert already exists");
                }
                else
                {
                    MessageBox.Show("Could not add entry to database");
                }
            }
        }