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();
}
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");
}
}
}