/// <summary> /// Builds a select statement from criteria. /// </summary> /// <param name="aircraft"></param> /// <param name="criteria"></param> /// <param name="justCount"></param> /// <returns></returns> private string CreateSelectFrom(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, bool justCount) { StringBuilder result = new StringBuilder(); if (aircraft != null) { result.AppendFormat("SELECT {0} FROM [Flights] AS f", justCount ? "COUNT(*)" : FieldList()); } else { result.AppendFormat("SELECT {0}{1}{2} FROM ", justCount ? "COUNT(*)" : FieldList(), justCount ? "" : ", ", justCount ? "" : AircraftTable.FieldList()); if (FilterByAircraftFirst(criteria)) { result.Append("[Aircraft] AS a LEFT JOIN [Flights] AS f"); } else { result.Append("[Flights] AS f LEFT JOIN [Aircraft] AS a"); } result.Append(" ON (a.[AircraftID] = f.[AircraftID])"); } return(result.ToString()); }
/// <summary> /// Disposes of or finalises the object. Note that the object is sealed. /// </summary> /// <param name="disposing"></param> private void Dispose(bool disposing) { if (disposing) { _TransactionHelper.Abandon(); CloseConnection(); if (_AircraftTable != null) { _AircraftTable.Dispose(); _AircraftTable = null; } if (_FlightTable != null) { _FlightTable.Dispose(); _FlightTable = null; } if (_DbHistoryTable != null) { _DbHistoryTable.Dispose(); _DbHistoryTable = null; } if (_DbInfoTable != null) { _DbInfoTable.Dispose(); _DbInfoTable = null; } if (_LocationsTable != null) { _LocationsTable.Dispose(); _LocationsTable = null; } if (_SessionsTable != null) { _SessionsTable.Dispose(); _SessionsTable = null; } if (_SystemEventsTable != null) { _SystemEventsTable.Dispose(); _SystemEventsTable = null; } if (_ConfigurationStorage != null) { _ConfigurationStorage.ConfigurationChanged -= ConfigurationStorage_ConfigurationChanged; _ConfigurationStorage = null; } if (_DatabaseLog != null) { _DatabaseLog.Flush(); _DatabaseLog.Dispose(); _DatabaseLog = null; } } }
/// <summary> /// Disposes of or finalises the object. Note that the object is sealed. /// </summary> /// <param name="disposing"></param> private void Dispose(bool disposing) { if(disposing) { _TransactionHelper.Abandon(); CloseConnection(); if(_AircraftTable != null) { _AircraftTable.Dispose(); _AircraftTable = null; } if(_FlightTable != null) { _FlightTable.Dispose(); _FlightTable = null; } if(_DbHistoryTable != null) { _DbHistoryTable.Dispose(); _DbHistoryTable = null; } if(_DbInfoTable != null) { _DbInfoTable.Dispose(); _DbInfoTable = null; } if(_LocationsTable != null) { _LocationsTable.Dispose(); _LocationsTable = null; } if(_SessionsTable != null) { _SessionsTable.Dispose(); _SessionsTable = null; } if(_SystemEventsTable != null) { _SystemEventsTable.Dispose(); _SystemEventsTable = null; } if(_ConfigurationStorage != null) { _ConfigurationStorage.ConfigurationChanged -= ConfigurationStorage_ConfigurationChanged; _ConfigurationStorage = null; } if(_DatabaseLog != null) { _DatabaseLog.Flush(); _DatabaseLog.Dispose(); _DatabaseLog = null; } } }
/// <summary> /// Performs the work for the Get***Flights methods. /// </summary> /// <param name="connection"></param> /// <param name="transaction"></param> /// <param name="log"></param> /// <param name="aircraft"></param> /// <param name="criteria"></param> /// <param name="fromRow"></param> /// <param name="toRow"></param> /// <param name="sort1"></param> /// <param name="sort1Ascending"></param> /// <param name="sort2"></param> /// <param name="sort2Ascending"></param> /// <returns></returns> private List <BaseStationFlight> DoGetFlights(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending) { List <BaseStationFlight> result = new List <BaseStationFlight>(); sort1 = ConvertSortFieldToColumnName(sort1); sort2 = ConvertSortFieldToColumnName(sort2); StringBuilder commandText = new StringBuilder(); commandText.Append(CreateSelectFrom(aircraft, criteria, false)); string criteriaText = GetFlightsCriteria(aircraft, criteria); if (criteriaText.Length > 0) { commandText.AppendFormat(" WHERE {0}", criteriaText); } if (sort1 != null || sort2 != null) { commandText.Append(" ORDER BY "); if (sort1 != null) { commandText.AppendFormat("{0} {1}", sort1, sort1Ascending ? "ASC" : "DESC"); } if (sort2 != null) { commandText.AppendFormat("{0}{1} {2}", sort1 == null ? "" : ", ", sort2, sort2Ascending ? "ASC" : "DESC"); } } commandText.Append(" LIMIT ? OFFSET ?"); bool decodeFlightsFirst = aircraft != null || !FilterByAircraftFirst(criteria); using (IDbCommand command = connection.CreateCommand()) { command.Transaction = transaction; command.CommandText = commandText.ToString(); int limit = toRow == -1 || toRow < fromRow ? int.MaxValue : (toRow - Math.Max(0, fromRow)) + 1; int offset = fromRow < 0 ? 0 : fromRow; AddFlightsCriteriaParameters(command, aircraft, criteria); Sql.AddParameter(command, limit); Sql.AddParameter(command, offset); Sql.LogCommand(log, command); using (IDataReader reader = command.ExecuteReader()) { Dictionary <int, BaseStationAircraft> aircraftMap = new Dictionary <int, BaseStationAircraft>(); while (reader.Read()) { int ordinal = 0; BaseStationFlight flight = DecodeFullFlight(reader, ref ordinal); if (aircraft != null) { flight.Aircraft = aircraft; } else { if (aircraftMap.ContainsKey(flight.AircraftID)) { flight.Aircraft = aircraftMap[flight.AircraftID]; } else { flight.Aircraft = AircraftTable.DecodeFullAircraft(reader, ref ordinal); aircraftMap.Add(flight.AircraftID, flight.Aircraft); } } result.Add(flight); } } } return(result); }