public static void UpdateServiceCalculatedDuration(int calculatedDuration, int serviceId) { string sql = "UPDATE OR IGNORE ServiceTemplates SET CalculatedDuration=@CalculatedDuration WHERE Id=@ServiceId"; SQLiteData.SaveData <dynamic>(sql, new { calculatedDuration, serviceId }, SQLiteData.GetConnectionString()); }
public static void DeleteTimeEvent(int timeEventId) { string sql = "PRAGMA foreign_keys = ON;" + "DELETE FROM TimeEvents WHERE TimeEvents.Id=@TimeEventId;"; SQLiteData.SaveData <dynamic>(sql, new { timeEventId }, SQLiteData.GetConnectionString()); }
public static void InsertConnection(int serviceId, int timetableId) { string sql = @"INSERT OR IGNORE INTO ConnectTtSi (ServiceId, TimetableId) VALUES(@ServiceId, @TimetableId);"; SQLiteData.SaveData <dynamic>(sql, new { serviceId, timetableId }, SQLiteData.GetConnectionString()); }
public static List <ServiceClassModel> GetAllServiceClasses() { var sql = "SELECT Id, ServiceClassname, ServiceClassDescription, Category, Color FROM \"ServiceClasses\";"; var serviceClassList = SQLiteData.LoadData <ServiceClassModel, dynamic>(sql, new { }, SQLiteData.GetConnectionString()).ToList(); return(serviceClassList); }
public static void DeleteConnection(int serviceId, int timetableId) { string sql = "DELETE FROM ConnectTtSi WHERE ServiceId=@ServiceId AND TimetableId=@TimetableId"; SQLiteData.SaveData <dynamic>(sql, new { serviceId, timetableId }, SQLiteData.GetConnectionString()); }
public static List <TimeEventTypeModel> GetAllTimeEventTypes() { var sql = "SELECT Id, EventType, EventTypeDescription FROM \"TimeEventTypes\";"; var timeEventList = SQLiteData.LoadData <TimeEventTypeModel, dynamic>(sql, new {}, SQLiteData.GetConnectionString()).ToList(); return(timeEventList); }
public static List <TimetableModel> GetAllTimetablesPerRoute(int routeId) { string sql = "SELECT * FROM Timetables WHERE RouteId=@RouteId"; var timetableList = SQLiteData.LoadData <TimetableModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList(); return(timetableList); }
public static List <LocationModel> GetAllLocationsPerRoute(int routeId) { string sql = "SELECT * FROM Locations WHERE RouteId=@RouteId ORDER BY Locations.[Order] ASC"; var locationList = SQLiteData.LoadData <LocationModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList(); return(locationList); }
public static int InsertTimeEventForServiceTemplate(TimeEventModel timeEvent) { string sql = @"INSERT OR IGNORE INTO TimeEvents (EventType, ArrivalTime, WaitTime, ServiceTemplateId, LocationId, [Order]) VALUES(@EventType, @ArrivalTime, @WaitTime, @ServiceTemplateId, @LocationId, @Order)"; return(SQLiteData.SaveData <dynamic>(sql, new { timeEvent.EventType, timeEvent.ArrivalTime , timeEvent.WaitTime, timeEvent.ServiceTemplateId, timeEvent.LocationId, timeEvent.Order } , SQLiteData.GetConnectionString())); }
public static void UpdateService(ServiceModel service) { string sql = "UPDATE OR IGNORE Services SET ServiceName=@ServiceName, ServiceAbbreviation=@ServiceAbbreviation, StartTime=@StartTime, EndTime=@EndTime, ServiceTemplateId=@ServiceTemplateId WHERE Id=@Id"; SQLiteData.SaveData <dynamic>(sql, new { service.ServiceName, service.ServiceAbbreviation, service.StartTime, service.EndTime, service.ServiceTemplateId, service.Id }, SQLiteData.GetConnectionString()); }
public static List <RouteModel> GetAllRoutes() { string sql = "SELECT * FROM Routes"; var routesList = SQLiteData.LoadData <RouteModel, dynamic>(sql, new { }, SQLiteData.GetConnectionString()).ToList(); return(routesList); }
public static RouteModel GetRouteById(int routeId) { string sql = "SELECT * FROM Routes WHERE Id= @RouteId"; var route = SQLiteData.LoadData <RouteModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).FirstOrDefault(); return(route); }
public static List <string> GetAllTimeEventTypeStrings() { var sql = "SELECT Id, EventType, EventTypeDescription FROM \"TimeEventTypes\";"; var timeEventList = SQLiteData.LoadData <TimeEventTypeModel, dynamic>(sql, new {}, SQLiteData.GetConnectionString()).ToList(); var output = timeEventList.Select(x => x.EventType).ToList(); return(output); }
public static ServiceModel GetServiceById(int serviceId) { string sql = "SELECT * FROM Services WHERE Id= @serviceId"; var timeTable = SQLiteData.LoadData <ServiceModel, dynamic>(sql, new { serviceId }, SQLiteData.GetConnectionString()).FirstOrDefault(); return(timeTable); }
public static List <TimeEventModel> GetAllTimeEventsPerRoute(int routeId) { string sql = "SELECT * FROM TimeEvents, ServiceTemplates " + "WHERE TimeEvents.ServiceTemplateId=ServiceTemplates.Id AND ServiceTemplates.RouteId=@RouteId"; var timeEventList = SQLiteData.LoadData <TimeEventModel, dynamic>(sql, new { routeId } , SQLiteData.GetConnectionString()).ToList(); return(timeEventList); }
public static int InsertService(ServiceModel service) { string sql = @"INSERT OR IGNORE INTO Services (ServiceName, ServiceAbbreviation, StartTime, EndTime, ServiceTemplateId) VALUES(@ServiceName, @ServiceAbbreviation, @StartTime, @EndTime, @ServiceTemplateId);SELECT last_insert_rowid();"; return(SQLiteData.SaveData <dynamic>(sql, new { service.ServiceName, service.ServiceAbbreviation, service.StartTime, service.EndTime, service.ServiceTemplateId }, SQLiteData.GetConnectionString())); }
public static List <TimetableRouteModel> GetAllTimetables() { string sql = "SELECT Timetables.Id AS TimetableId, Routes.Id AS RouteId, Timetables.TimetableAbbreviation, Timetables.TimetableName, " + "Timetables.TimetableDescription, Timetables.IsMultiDirection, Timetables.ServiceDirectionId, " + "Routes.RouteName, Routes.RouteAbbreviation " + "FROM Timetables, Routes WHERE Timetables.RouteId=Routes.Id ORDER BY Routes.RouteAbbreviation"; var timetableList = SQLiteData.LoadData <TimetableRouteModel, dynamic>(sql, new {}, SQLiteData.GetConnectionString()).ToList(); return(timetableList); }
public static int InsertTimetableForRoute(TimetableModel timetable) { string sql = @"INSERT OR IGNORE INTO Timetables (TimetableName, TimetableAbbreviation, TimetableDescription, ServiceDirectionId, IsMultiDirection, RouteId) VALUES(@TimetableName, @TimetableAbbreviation, @TimetableDescription, @ServiceDirectionId, @IsMultiDirection, @RouteId);SELECT last_insert_rowid();"; return(SQLiteData.SaveData <dynamic>(sql, new { timetable.TimetableName, timetable.TimetableAbbreviation, timetable.TimetableDescription, timetable.ServiceDirectionId, timetable.IsMultiDirection, timetable.RouteId }, SQLiteData.GetConnectionString())); }
public static int InsertServiceTemplate(ServiceTemplateModel service) { string sql = @"INSERT OR IGNORE INTO ServiceTemplates (ServiceTemplateName, ServiceTemplateAbbreviation, ServiceTemplateDescription, ServiceType, ServiceDirectionId, CalculatedDuration, RouteId) VALUES(@ServiceTemplateName, @ServiceTemplateAbbreviation, @ServiceTemplateDescription, @ServiceType, @ServiceDirectionId, @CalculatedDuration, @RouteId);SELECT last_insert_rowid();"; return(SQLiteData.SaveData <dynamic>(sql, new { service.ServiceTemplateName, service.ServiceTemplateAbbreviation, service.ServiceTemplateDescription, service.ServiceType, service.ServiceDirectionId, service.CalculatedDuration, service.RouteId }, SQLiteData.GetConnectionString())); }
public static List <ServiceModel> GetServicesPerRoute(int routeId) { string sql = @"SELECT Services.Id AS Id, Services.ServiceName, Services.ServiceAbbreviation, Services.StartTime, Services.EndTime, Services.ServiceTemplateId FROM Services, ServiceTemplates WHERE @RouteId=ServiceTemplates.RouteId AND Services.ServiceTemplateId== ServiceTemplates.Id"; var serviceList = SQLiteData.LoadData <ServiceModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList(); return(serviceList); }
public static void UpdateTimetable(TimetableModel timetable) { string sql = @"UPDATE OR IGNORE Timetables SET TimetableName=@TimetableName, TimetableAbbreviation=@TimetableAbbreviation, TimetableDescription=@TimetableDescription, ServiceDirectionId=@ServiceDirectionId, IsMultiDirection=@IsMultiDirection, RouteId=@RouteId WHERE Id=@Id"; SQLiteData.SaveData <dynamic>(sql, new { timetable.TimetableName, timetable.TimetableAbbreviation, timetable.TimetableDescription, timetable.ServiceDirectionId, timetable.IsMultiDirection, timetable.RouteId, timetable.Id }, SQLiteData.GetConnectionString()); }
public static void ServiceTemplateMigration() { // Make sure to never do this twice! var sql = "SELECT 1 FROM sqlite_master WHERE type='table' AND name='ServiceTemplates'"; int tableExists = SQLiteData.LoadData <int, dynamic>(sql, new { }, SQLiteData.GetConnectionString()).FirstOrDefault(); if (tableExists == 0) { sql = File.ReadAllText("SQL\\ServiceTemplatesChange.sql"); SQLiteData.SaveData <dynamic>(sql, new { }, SQLiteData.GetConnectionString()); if (Settings.DatabaseVersion == 2) { VersionDataAccess.UpdateDatabaseVersion(3); } } }
public static void UpdateServiceTemplate(ServiceTemplateModel service) { string sql = @"UPDATE OR IGNORE ServiceTemplates SET ServiceTemplateName=@ServiceTemplateName, ServiceTemplateAbbreviation=@ServiceTemplateAbbreviation, ServiceTemplateDescription=@ServiceTemplateDescription, ServiceType=@ServiceType, ServiceDirectionId=@ServiceDirectionId, CalculatedDuration=@CalculatedDuration, RouteId=@RouteId WHERE Id=@Id"; SQLiteData.SaveData <dynamic>(sql, new { service.ServiceTemplateName, service.ServiceTemplateAbbreviation, service.ServiceTemplateDescription, service.ServiceType, service.ServiceDirectionId, service.CalculatedDuration, service.RouteId, service.Id }, SQLiteData.GetConnectionString()); }
public static int GetCurrentDatabaseVersion() { string sql1 = "SELECT 1 FROM sqlite_master WHERE type='table' AND name='Version'"; string sql2 = "SELECT VersionNr FROM Version"; try { int tableExists = SQLiteData.LoadData <int, dynamic>(sql1, new {}, SQLiteData.GetConnectionString()).FirstOrDefault(); if (tableExists == 1) { return(SQLiteData.LoadData <int, dynamic>(sql2, new { }, SQLiteData.GetConnectionString()) .FirstOrDefault()); } return(0); } catch (SQLiteException e) { return(0); } }
public static void DeleteRoute(int routeId) { string sql = "PRAGMA foreign_keys = ON;DELETE FROM Routes WHERE Routes.Id=@RouteId;"; SQLiteData.SaveData <dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()); }
public static void UpdateRoute(RouteModel route) { string sql = "UPDATE OR IGNORE Routes SET RouteName=@RouteName, RouteAbbreviation=@RouteAbbreviation, RouteDescription=@RouteDescription WHERE Id= @Id"; SQLiteData.SaveData <dynamic>(sql, new { route.RouteName, route.RouteAbbreviation, route.RouteDescription, route.Id }, SQLiteData.GetConnectionString()); }
public static int InsertRoute(RouteModel route) { string sql = "INSERT OR IGNORE INTO Routes (RouteName, RouteAbbreviation, RouteDescription) VALUES(@RouteName, @RouteAbbreviation, @RouteDescription);SELECT last_insert_rowid();"; return(SQLiteData.SaveData <dynamic>(sql, new { route.RouteName, route.RouteAbbreviation, route.RouteDescription }, SQLiteData.GetConnectionString())); }
public static List <ServiceTemplateModel> GetServiceTemplatesPerRoute(int routeId) { string sql = "SELECT * FROM ServiceTemplates WHERE RouteId=@RouteId"; var serviceList = SQLiteData.LoadData <ServiceTemplateModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList(); return(serviceList); }
public static void DeleteServiceTemplate(int serviceTemplateId) { string sql = "PRAGMA foreign_keys = ON;DELETE FROM ServiceTemplates WHERE ServiceTemplates.Id=@ServiceTemplateId;"; SQLiteData.SaveData <dynamic>(sql, new { serviceTemplateId }, SQLiteData.GetConnectionString()); }
public static List <ConnectTtSiModel> GetAllConnectTtSiPerRoute(int routeId) { string sql = "SELECT ConnectTtSi.Id, ConnectTtSi.TimetableId, ConnectTtSi.ServiceId FROM ConnectTtSi, Timetables WHERE ConnectTtSi.TimetableId= Timetables.Id AND Timetables.RouteId=@RouteId"; var connectList = SQLiteData.LoadData <ConnectTtSiModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList(); return(connectList); }