/// <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 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> /// 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> /// 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> /// 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> /// 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 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> /// 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> /// 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> /// 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); }
public static DatabaseBroker GetInstance(string path = null) { if (path == null) { // no database file was provided return(currentInstance); } // if the connection string matches the one used to construct the current database connection, // return this broker instance string connectionString = "URI=file:" + path; if (currentInstance?.connection?.ConnectionString == connectionString) { return(currentInstance); } // otherwise, close the existing connection (if open) and create a new broker currentInstance?.CloseConnection(); currentInstance = new DatabaseBroker(connectionString); return(currentInstance); }
/// <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); }