/// <summary>
/// Triggered when the "Open" menu item is clicked
/// </summary>
/// <param name="sender">Control that triggered the event</param>
/// <param name="eventArgs">Arguments for this event</param>
private void OnOpenMenuItemClick(object sender, EventArgs eventArgs)
{
if (this.openFileDialog.ShowDialog(this) == DialogResult.OK)
{
try {
// initialize database broker with the selected database path and open a connection
UseWaitCursor = true;
this.generalStatusBarPanel.Text = $"Opening \"{this.openFileDialog.FileName}\"...";
DatabaseBroker.GetInstance(this.openFileDialog.FileName).OpenConnection();
// update control state
Text = $"{Path.GetFileName(this.openFileDialog.FileName)} - {System.Windows.Forms.Application.ProductName}";
this.generalStatusBarPanel.Text = "Connection open";
this.mainTabControl.Visible = false;
this.toolsMenuItem.Enabled = true;
// recreate tabs
this.mainTabControl.TabPages.Clear();
CreateObjectTabs();
this.mainTabControl.Visible = true;
} catch (Exception exception) {
this.generalStatusBarPanel.Text = "Could not connect to database";
MessageBox.Show(this, $"{this.generalStatusBarPanel.Text}: {exception.Message}",
System.Windows.Forms.Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
} finally {
UseWaitCursor = false;
}
}
}
/// <summary>
/// Inserts a new trip into the database
/// </summary>
/// <param name="trip">Trip to be inserted</param>
/// <returns>The trip instance</returns>
public Trip Insert(Trip trip)
{
// this trip may be associated with a new train. We should insert it first but
// we can not use its ID to check whether or not it already exists on the database.
// This could be solved with an additional property or method that indicates if
// the object was just inserted or retrieved from database, but for simplicity we
// won't be doing any automatic insertion here
if (trip.Product.ID == 0)
{
// this trip is associated with a new product -- insert it first
trip.Product.DAO.Insert(trip.Product);
}
long numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("INSERT INTO Trips(TripDate, Train, Product, TonsTransported) VALUES(@date, @train, @product, @tons)",
("date", trip.Date.ToSQLiteDateFormat()),
("train", trip.Train.ID),
("product", trip.Product.ID),
("tons", trip.TonsTransported));
if (numRows == 0)
{
throw new DataException("Could not perform insert operation");
}
return(trip);
}
/// <summary>
/// Deletes the row associated with the given train from the database
/// </summary>
/// <param name="train">Train to be deleted</param>
/// <returns>The train instance</returns>
public Train Delete(Train train)
{
int numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("DELETE FROM Trains WHERE TrainID=@id", ("id", train.ID));
if (numRows == 0)
{
throw new DataException("Could not perform delete operation");
}
return(train);
}
/// <summary>
/// Deletes the row associated with the given price from the database
/// </summary>
/// <param name="price">Price to be deleted</param>
/// <returns>The price instance</returns>
public Price Delete(Price price)
{
int numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("DELETE FROM Prices WHERE Product = @product AND PriceDate = @date",
("product", price.Product.ID),
("date", price.Date.ToSQLiteDateFormat()));
if (numRows == 0)
{
throw new DataException("Could not perform delete operation");
}
return(price);
}
/// <summary>
/// Deletes the row associated with the given trip from the database
/// </summary>
/// <param name="trip">Trip to be deleted</param>
/// <returns>The trip instance</returns>
public Trip Delete(Trip trip)
{
int numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("DELETE FROM Trips WHERE Product = @product AND TripDate = @date AND Train = @train",
("product", trip.Product.ID),
("date", trip.Date.ToSQLiteDateFormat()),
("train", trip.Train.ID));
if (numRows == 0)
{
throw new DataException("Could not pefrorm delete operation");
}
return(trip);
}
/// <summary>
/// Reads all the products from the database
/// </summary>
/// <returns>A list with the obtained data</returns>
public List <Product> ReadAll()
{
var products = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT ProductID, ProductDescription FROM Products");
Products.Clear();
foreach (var row in products)
{
Products.Add(new Product((long)row["ProductID"])
{
Description = (string)row["ProductDescription"]
});
}
return(Products);
}
/// <summary>
/// Reads all the trains from the database
/// </summary>
/// <returns>A list with the obtained data</returns>
public List <Train> ReadAll()
{
var trains = DatabaseBroker.GetInstance(this.path).ExecuteQuery("SELECT TrainID, TrainType FROM Trains");
Trains.Clear();
foreach (var row in trains)
{
var train = new Train((string)row["TrainID"])
{
Type = new TrainType((long)row["TrainType"])
};
train.Type.DAO.Read(train.Type);
Trains.Add(train);
}
return(Trains);
}
/// <summary>
/// Reads all the train types from the database
/// </summary>
/// <returns>A list with the obtained data</returns>
public List <TrainType> ReadAll()
{
var trainTypes = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT TrainTypeID, TrainTypeDescription, MaxCapacity FROM TrainTypes");
TrainTypes.Clear();
foreach (var row in trainTypes)
{
TrainTypes.Add(new TrainType((long)row["TrainTypeID"])
{
Description = (string)row["TrainTypeDescription"],
MaximumCapacity = (long)row["MaxCapacity"]
});
}
return(TrainTypes);
}
/// <summary>
/// Updates the row associated with the given train from the database
/// </summary>
/// <param name="train">Train to be updated</param>
/// <returns>The train instance</returns>
public Train Update(Train train)
{
// update the train type information
train.Type.DAO.Update(train.Type);
// update train information
int numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("UPDATE Trains SET TrainType=@type WHERE TrainID=@id",
("id", train.ID),
("type", train.Type.ID));
if (numRows == 0)
{
throw new DataException("Could not perform update operation");
}
return(train);
}
/// <summary>
/// Reads all the train types from the database
/// </summary>
/// <returns>A list with the obtained data</returns>
public List <Price> ReadAll()
{
var prices = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT Product, PriceDate, EurosPerTon FROM Prices");
Prices.Clear();
foreach (var row in prices)
{
var price = new Price((long)row["Product"], (string)row["PriceDate"])
{
EurosPerTon = (double)row["EurosPerTon"]
};
price.Product.DAO.Read(price.Product);
Prices.Add(price);
}
return(Prices);
}
/// <summary>
/// Updates the row associated with the given price from the database
/// </summary>
/// <param name="price">Price to be updated</param>
/// <returns>The price instance</returns>
public Price Update(Price price)
{
// update the product information
price.Product.DAO.Update(price.Product);
// update price information
int numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("UPDATE Prices SET EurosPerTon = @value WHERE Product = @product AND PriceDate = @date",
("value", price.EurosPerTon),
("product", price.Product.ID),
("date", price.Date.ToSQLiteDateFormat()));
if (numRows == 0)
{
throw new DataException("Could not perform update operation");
}
return(price);
}
/// <summary>
/// Fills a Train instance with data retrieved from the database
/// </summary>
/// <param name="train">The target instance</param>
/// <returns>The train instance</returns>
public Train Read(Train train)
{
// query information for the given train type object
var trains = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT TrainType FROM Trains WHERE TrainID = @id", ("id", train.ID));
if (!trains.Any())
{
throw new KeyNotFoundException("Could not find a train with this ID");
}
// fill object properties
var trainInfo = trains.First();
train.Type = new TrainType((long)trainInfo["TrainType"]);
train.Type.DAO.Read(train.Type);
return(train);
}
/// <summary>
/// Fills a Price instance with data retrieved from the database
/// </summary>
/// <param name="price">The target instance</param>
/// <returns>The price instance</returns>
public Price Read(Price price)
{
// query information for the given price object
var prices = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT EurosPerTon FROM Prices WHERE Product = @product AND PriceDate = @date",
("product", price.Product),
("date", price.Date.ToSQLiteDateFormat()));
if (!prices.Any())
{
throw new KeyNotFoundException("Could not find a price for this product on the specified date");
}
// fill object properties
var priceInfo = prices.First();
price.EurosPerTon = (double)priceInfo["EurosPerTon"];
return(price);
}
/// <summary>
/// Inserts a new train into the database
/// </summary>
/// <param name="train">Train to be inserted</param>
/// <returns>The train instance</returns>
public Train Insert(Train train)
{
if (train.Type.ID == 0)
{
// this train has a new train type -- insert it first
train.Type.DAO.Insert(train.Type);
}
long numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("INSERT INTO Trains(TrainID, TrainType) VALUES(@id, @type)",
("id", train.ID),
("type", train.Type.ID));
if (numRows == 0)
{
throw new DataException("Could not perform insert operation");
}
return(train);
}
/// <summary>
/// Obtains information about the most profitable trip
/// </summary>
/// <returns>A dictionary of columns and values</returns>
public Dictionary <string, object> QueryMostProfitableTrip()
{
return(DatabaseBroker.GetInstance(this.path).ExecuteQuery(@"
SELECT
Trips.TripDate,
Trains.TrainID,
TrainTypes.TrainTypeDescription,
Products.ProductID,
Products.ProductDescription,
Trips.TonsTransported,
Prices.EurosPerTon,
(Trips.TonsTransported * Prices.EurosPerTon) AS TripValue
FROM Trips
JOIN
Products ON Trips.Product = Products.ProductID,
Prices ON Prices.Product = Products.ProductID AND Trips.TripDate >= Prices.PriceDate,
Trains ON Trains.TrainID = Trips.Train,
TrainTypes ON Trains.TrainType = TrainTypes.TrainTypeID
ORDER BY TripValue DESC
LIMIT 1").First());
}
/// <summary>
/// Updates the row associated with the given trip from the database
/// </summary>
/// <param name="trip">Trip to be updated</param>
/// <returns>The trip instance</returns>
public Trip Update(Trip trip)
{
// update product and train information
trip.Product.DAO.Update(trip.Product);
trip.Train.DAO.Update(trip.Train);
// update trip information
int numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("UPDATE Trips SET TonsTransported = @tons WHERE Product = @product AND TripDate = @date AND Train = @train",
("product", trip.Product.ID),
("tons", trip.TonsTransported),
("date", trip.Date.ToSQLiteDateFormat()),
("train", trip.Train.ID));
if (numRows == 0)
{
throw new DataException("Could not perform update operation");
}
return(trip);
}
/// <summary>
/// Inserts a new price into the database
/// </summary>
/// <param name="price">Price to be inserted</param>
/// <returns>The price instance</returns>
public Price Insert(Price price)
{
if (price.Product.ID == 0)
{
// this price is associated with a new product -- insert it first
price.Product.DAO.Insert(price.Product);
}
long numRows = DatabaseBroker.GetInstance(this.path)
.ExecuteNonQuery("INSERT INTO Prices(Product, PriceDate, EurosPerTon) VALUES(@product, @date, @value)",
("product", price.Product.ID),
("date", price.Date.ToSQLiteDateFormat()),
("value", price.EurosPerTon));
if (numRows == 0)
{
throw new DataException("Could not perform insert operation");
}
return(price);
}
/// <summary>
/// Reads all the trips from the database
/// </summary>
/// <returns>A list with the obtained data</returns>
public List <Trip> ReadAll()
{
var trips = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT TripDate, Train, Product, TonsTransported FROM Trips");
Trips.Clear();
foreach (var row in trips)
{
var trip = new Trip((string)row["TripDate"], (string)row["Train"])
{
Product = new Product((long)row["Product"]),
TonsTransported = (long)row["TonsTransported"]
};
trip.Train.DAO.Read(trip.Train);
trip.Product.DAO.Read(trip.Product);
Trips.Add(trip);
}
return(Trips);
}
/// <summary>
/// Fills a Trip instance with data retrieved from the database
/// </summary>
/// <param name="trip">The target instance</param>
/// <returns>The trip instance</returns>
public Trip Read(Trip trip)
{
// query information from the database
var trips = DatabaseBroker.GetInstance(this.path)
.ExecuteQuery("SELECT TonsTransported FROM Trips WHERE Product = @product AND TripDate = @date AND Train = @train",
("product", trip.Product.ID),
("date", trip.Date.ToSQLiteDateFormat()),
("train", trip.Train.ID));
if (!trips.Any())
{
throw new KeyNotFoundException("Could not find a trip associated with this train at the specified date");
}
// fill object propetries
var tripInfo = trips.First();
trip.TonsTransported = (long)tripInfo["TonsTransported"];
trip.Product.DAO.Read(trip.Product);
return(trip);
}
public static int Main()
{
#if TEST
var broker = DatabaseBroker.GetInstance("C:\\Users\\Angel\\Documents\\trains.db");
broker.OpenConnection();
foreach (var row in broker.ExecuteQuery("SELECT * FROM TrainTypes"))
{
foreach (var column in row.AllKeys)
{
var value = row[column];
Console.WriteLine($"{column}: {value}");
}
Console.WriteLine();
}
return(0);
#endif
System.Windows.Forms.Application.EnableVisualStyles();
System.Windows.Forms.Application.SetCompatibleTextRenderingDefault(false);
System.Windows.Forms.Application.Run(new MainWindow());
return(0);
}
/// <summary>
/// Triggered before the close message is sent to the window
/// </summary>
/// <param name="eventArgs">Arguments for this event</param>
protected override void OnFormClosing(FormClosingEventArgs eventArgs)
{
this.generalStatusBarPanel.Text = "Closing connection...";
DatabaseBroker.GetInstance()?.CloseConnection();
base.OnFormClosing(eventArgs);
}
