/// <summary>
/// Inserts object into Oracle DataBase and returns its ID
/// </summary>
/// <param name="place"></param>
/// <returns></returns>
public int insertPlaceToDBReturnItsID(PlaceModel place)
{
int output = -1;
try
{
using (OracleConnection connection = new OracleConnection(OracleSQLConnector.GetConnectionString()))
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = String.Format(
"INSERT INTO place_table(city, postcode, street, description, trainer_table_id) VALUES('{0}', '{1}', '{2}', '{3}', {4}) RETURNING id INTO :id",
place.getCity(), place.getPostCode(), place.getStreet(), place.getDescription(), GlobalClass.getTrainerID());
cmd.Connection = connection;
cmd.Parameters.Add(new OracleParameter
{
ParameterName = ":id",
OracleDbType = OracleDbType.Int32,
Direction = ParameterDirection.Output
});
cmd.ExecuteNonQuery();
String outputStr = cmd.Parameters[":id"].Value.ToString();
output = Convert.ToInt32(outputStr);
}
}catch
{
}
return(output);
}
/// <summary>
/// Sends object to Oracle DataBase for an update.
/// </summary>
/// <param name="place"></param>
/// <returns></returns>
public bool updatedPlaceToDataBase(PlaceModel place, int currentlySelectedItemID)
{
try
{
using (OracleConnection connection = new OracleConnection(OracleSQLConnector.GetConnectionString()))
{
connection.Open();
OracleCommand cmd;
string sql = String.Format(
"UPDATE place_table SET city = '{0}', postcode = '{1}', street = '{2}', description = '{3}' WHERE id = {4}",
place.getCity(), place.getPostCode(), place.getStreet(), place.getDescription(), currentlySelectedItemID);
cmd = new OracleCommand(sql, connection);
cmd.CommandType = CommandType.Text;
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
return(false);
}
else
{
return(true);
}
}
}catch
{
return(false);
}
}
/// <summary>
/// Updates row in Oracle DataBase and changes List and ListBox values for this specific object.
/// </summary>
private void updatePlace()
{
if (isPlaceTextBoxesGood())
{
PlaceModel place = new PlaceModel(cityTextBox.Text, postcodeTextBox.Text, streetTextBox.Text, descriptionTextBox.Text);
OracleSQLConnectorTrainerWindow oraclesql = new OracleSQLConnectorTrainerWindow();
if (oraclesql.updatedPlaceToDataBase(place, currentlySelectedItemID))
{
placesList.ElementAt(placesListBox.SelectedIndex).setCity(place.getCity());
placesList.ElementAt(placesListBox.SelectedIndex).setPostCode(place.getPostCode());
placesList.ElementAt(placesListBox.SelectedIndex).setStreet(place.getStreet());
placesList.ElementAt(placesListBox.SelectedIndex).setDescription(place.getDescription());
placesListBox.Items[placesListBox.SelectedIndex] = place.getCity() + ", " + place.getDescription();
communicationLabel.Content = "Place updated!";
}
else
{
communicationLabel.Content = "Failed to update palce.";
}
}
}
/// <summary>
/// Adds new PlaceModel object to Oracle DataBase
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void addPlaceButton_Click(object sender, RoutedEventArgs e)
{
if (isPlaceTextBoxesGood())
{
PlaceModel place = new PlaceModel(cityTextBox.Text, postcodeTextBox.Text, streetTextBox.Text, descriptionTextBox.Text);
{
OracleSQLConnectorTrainerWindow oraclesql = new OracleSQLConnectorTrainerWindow();
int placeID = oraclesql.insertPlaceToDBReturnItsID(place);
if (placeID < 0)
{
communicationLabel.Content = "Record not inserted";
}
else
{
place.setID(placeID);
placesList.Add(place);
placesListBox.Items.Add(place.getCity() + ", " + place.getDescription());
placesListBox.UnselectAll();
currentlySelectedItemID = -1;
communicationLabel.Content = "Success! Place has been created";
}
}
}
}