private static async void createFavouritesTable() { String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCDirection'"); if (count == 0) { await conn.CreateTableAsync<OCDirection>(); } }
private static async void createSettingsTable() { String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCSettings'"); if (count == 0) { await conn.CreateTableAsync<OCSettings>(); OCSettings settings = OCSettings.newOCSettings(500); settings.id = 1; await conn.InsertAsync(settings); } }
/// <summary> /// Initialise the Database connection and creates tables/default values if not existant. /// </summary> private async void ConnectionInit() { //Connection init var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite"); Database = new SQLite.SQLiteAsyncConnection(dbPath); //Create tables if not exists await Database.CreateTablesAsync(new Account().GetType(), new DatabaseRoute().GetType(), new DatabasePOI().GetType()); await Database.ExecuteAsync("create table if not exists \"RouteBinds\"(\"RouteID\" integer,\"WaypointID\" integer);", new object[] { }); //Set default Admin Admin password var result = await Database.ExecuteScalarAsync<String>("Select Gebruikersnaam From Account WHERE Gebruikersnaam = ? AND Password = ?", new object[] { "Admin", "Admin" }); if(result == null) await Database.InsertAsync(new Account("Admin", "Admin")); }
public async Task<int> AddLocation(MapLocationModel mapLocation, Guid mapId) { SQLiteAsyncConnection connection = new SQLiteAsyncConnection(SQLiteConfiguration.ConnectionString); int count = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM MapLocations WHERE MapId = ? AND Latitude = ? AND Longitude = ?", mapId, mapLocation.Latitude, mapLocation.Longitude); if (count > 0) return 0; mapLocation.MapId = mapId; _locations.Add(mapLocation); return await connection.InsertAsync(mapLocation); }
public static async Task<OCSettings> getSettings() { String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCSettings'"); if (count > 0) { String Query = "SELECT * from OCSettings LIMIT 1;"; List<OCSettings> settings = await conn.QueryAsync<OCSettings>(Query); return settings.First<OCSettings>(); } else { createSettingsTable(); return await getSettings(); } }
public async Task<int> GetNumberOfFavoriteVideosAsync () { try { var db = new SQLiteAsyncConnection (_path); // this counts all records in the database, it can be slow depending on the size of the database var count = await db.ExecuteScalarAsync<int> ("SELECT Count(*) FROM FavVideoDataModel"); // for a non-parameterless query // var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy"); return count; } catch (SQLiteException ex) { Log.Error (TAG, ex.Message); return -1; } }
public static async Task<ObservableCollection<OCDirection>> getFavourites() { //TODO Math. String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCDirection'"); if (count > 0) { String Query = "SELECT Id, * from OCDirection;"; List<OCDirection> stops = await conn.QueryAsync<OCDirection>(Query); ObservableCollection<OCDirection> directionsCollection = new ObservableCollection<OCDirection>(stops); return directionsCollection; } else { createFavouritesTable(); return await getFavourites(); } }
public static async Task<List<OCSchedule>> getScheduleForDayAndStop(String day, String stop, int route) { String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'"); if (count > 0) { int date = getTodaysDate(); String Query = "SELECT * from OCSchedule where start_date <= " + date + " AND end_date >= " + date + " AND stop_code = '" + stop + "' AND route_short_name='" + route + "' AND " + day.ToLower() + " = '1' ORDER BY arrival_time"; List<OCSchedule> schedule = await conn.QueryAsync<OCSchedule>(Query); return schedule; } else { OCTranspoStopsData.initDB(); return await getScheduleForDayAndStop(day, stop, route); } }
//Schedules public static async Task<List<OCStop>> getStopIDForCode(String stop) { String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'"); if (count > 0) { int date = getTodaysDate(); String Query = "SELECT stop_id from OCStop where stop_code = '" + stop + "'"; List<OCStop> codes = await conn.QueryAsync<OCStop>(Query); return codes; } else { OCTranspoStopsData.initDB(); return await getStopIDForCode(stop); } }
public static async Task<List<OCStop>> getStopByNameOrID(String value) { String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'"); if (count > 0) { if (value.Length > 0) { String Query = "SELECT DISTINCT(stop_id), * from OCStop where stop_name LIKE '%" + value.ToUpper() + "%' OR stop_code LIKE '" + value + "%';"; List<OCStop> stops = await conn.QueryAsync<OCStop>(Query); return stops; } else { return new List<OCStop>(); } } else { OCTranspoStopsData.initDB(); return await getStopByNameOrID(value); } }
// Stops public static async Task<ObservableCollection<OCStop>> getCloseStops(double latitude, double longitude, double zoomLevel) { //TODO Math. String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite"; SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'"); if (count > 0) { OCSettings settings = await getSettings(); OCGeoMath latlong = OCGeoMath.getRange(latitude, longitude, settings.nearbyDistance); String Query = "SELECT DISTINCT(stop_id), * from OCStop where ((stop_lat BETWEEN " + latlong.lowerLat + " AND " + latlong.upperLat + ")" + " AND (stop_lon BETWEEN " + latlong.lowerLong + " AND " + latlong.upperLong + ")) ORDER BY stop_id;"; List<OCStop> stops = await conn.QueryAsync<OCStop>(Query); ObservableCollection<OCStop> stopsCollection = new ObservableCollection<OCStop>(stops); return stopsCollection; } else { OCTranspoStopsData.initDB(); return await getCloseStops(latitude, longitude, zoomLevel); } }
public async Task<IEnumerable<string>> GetAllFavoriteVideosAsync () { try { var db = new SQLiteAsyncConnection (_path); // this counts all records in the database, it can be slow depending on the size of the database var list = await db.ExecuteScalarAsync<IEnumerable<string>> ("SELECT Id FROM UserVideo WHERE IsFavorite = true"); // for a non-parameterless query // var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy"); return list; } catch (SQLiteException ex) { Log.Error (TAG, ex.Message); return new List<string>(); } }
public async Task<IEnumerable<string>> GetAllWatchlistVideosAsync () { try { var db = new SQLiteAsyncConnection (_path); // this counts all records in the database, it can be slow depending on the size of the database var list = await db.ExecuteScalarAsync<IEnumerable<string>> ("SELECT Id FROM UserVideo WHERE IsInWatchlist = true"); return list; } catch (SQLiteException ex) { Log.Error (TAG, ex.Message); return new List<string> (); } }
private async Task<int> findNumberRecords(string path) { try { var db = new SQLiteAsyncConnection(path); // this counts all records in the database, it can be slow depending on the size of the database var count = await db.ExecuteScalarAsync<int>("SELECT Count(*) FROM Person"); // for a non-parameterless query // var count = db.ExecuteScalarAsync<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy"); return count; } catch (SQLiteException ex) { return -1; } }