public bool updateMeetingStatus(MeetModel meeting)
{
bool result = true;
try
{
using (OracleConnection connection = new OracleConnection(OracleSQLConnector.GetConnectionString()))
{
try
{
connection.Open();
OracleCommand cmd;
string sql = String.Format("update meet_table set accepted = {1}, isnew = {2} where id = {0}", meeting.ID, meeting.Accepted, meeting.New);
cmd = new OracleCommand(sql, connection);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch
{
result = false;
}
}
}catch
{
result = false;
}
return(result);
}
/// <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);
}
}
private void loadData()
{
OracleSQLConnector oraclesql = new OracleSQLConnector();
acceptedMeetingsList = oraclesql.loadAllMeetingsFromDataBase(GlobalClass.getUserID(), "user");
separeteAcceptedFromPendingLists();
Sorts sort = new Sorts();
sort.sortListsByDateASC(acceptedMeetingsList);
sort.sortListsByDateASC(pendingMeetingsList);
acceptedMeetingsListBox.ItemsSource = acceptedMeetingsList;
pendingMeetingsListBox.ItemsSource = pendingMeetingsList;
}
/// <summary>
/// Filling textBoxes
/// </summary>
/// <param name="meeting"></param>
private void setTextBoxesWithData(MeetModel meeting)
{
OracleSQLConnector oracleSQLConnector = new OracleSQLConnector();
int globalUserID = GlobalClass.getUserID();
GlobalClass.setUserID(meeting.IDUser);
UserModel user = oracleSQLConnector.loadUserFromDataBase();
GlobalClass.setUserID(globalUserID);
PlaceModel place = oracleSQLConnector.loadPlaceFromDataBase(meeting.IDPlace);
userTextBox.Text = user.getFullName();
userPhoneNumberTextBox.Text = user.getPhoneNumberStr();
placeTextBox.Text = place.getFullDetails();
dateAndHourTextBox.Text = meeting.getDateAndHourStr();
}
private void loadUserData()
{
OracleSQLConnector oracleSQLConnector = new OracleSQLConnector();
TrainerModel trainer = oracleSQLConnector.loadTrainerFromDataBase();
trainerNameTextBlock.Text = trainer.getFullName();
trainerEmailTextBlock.Text = trainer.getEmailAddress();
trainerPhoneNumberTextBlock.Text = trainer.getPhoneNumberStr();
OracleSQLConnectorImages connector = new OracleSQLConnectorImages();
byte[] blob = trainer.getImageBlob();//connector.getImageBytes(GlobalClass.getTrainerID());
if (blob != null)
{
BitmapImage newImg = ToImage(blob);
userImage.Source = newImg;
}
}
public bool sendImageToDB(String tableName, int id, string filename)
{
bool success = true;
try
{
using (OracleConnection connection = new OracleConnection(OracleSQLConnector.GetConnectionString()))
{
try
{
if (filename != "")
{
FileStream fls;
fls = new FileStream(@filename, FileMode.Open, FileAccess.Read);
byte[] blob = new byte[fls.Length];
fls.Read(blob, 0, System.Convert.ToInt32(fls.Length));
fls.Close();
string sql = "Update " + tableName + " set image = :BLOBFILE WHERE id = " + id;
connection.Open();
using (OracleCommand cmd = new OracleCommand(sql, connection))
{
cmd.Parameters.Add("BLOBFILE", OracleDbType.Blob, blob, ParameterDirection.Input);
cmd.ExecuteNonQuery();
}
}
}
catch
{
success = false;
}
}
}
catch
{
success = false;
}
return(success);
}
public byte[] getImageBytes(String tableName, int id)
{
try
{
using (OracleConnection connection = new OracleConnection(OracleSQLConnector.GetConnectionString()))
{
connection.Open();
OracleCommand cmd;
string sql = String.Format("Select image from {1} where id = {0}", id, tableName);
cmd = new OracleCommand(sql, connection);
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
try
{
if (reader.Read())
{
if (reader["image"] != DBNull.Value)
{
return((byte[])reader["image"]);
}
else
{
return(null);
}
}
}
finally
{
reader.Close();
}
}
}catch
{
}
return(null);
}
private void trainersListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
OracleSQLConnector oracleSQLConnector = new OracleSQLConnector();
if (trainersListBox.SelectedIndex >= 0)
{
currentlySelectedTrainerItemID = trainerList[trainersListBox.SelectedIndex].getID();
trainerTextBox.Text = trainerList.ElementAt(trainersListBox.SelectedIndex).getFirstName() + " "
+ trainerList.ElementAt(trainersListBox.SelectedIndex).getLastName() + ", "
+ trainerList.ElementAt(trainersListBox.SelectedIndex).getPhoneNumberStr();
}
GlobalClass.setTrainerID(currentlySelectedTrainerItemID);
currentlySelectedPlaceItemID = -1;
placesListBox.UnselectAll();
placesListBox.SelectedIndex = -1;
placeList.Clear();
placeList = oracleSQLConnector.loadPlacesFromDataBase();
placesListBox.Items.Clear();
putIntoPlacesListBoxPlacesList(placeList);
}
