C# SQLiteAsyncConnection.ExecuteScalarAsync方法代码示例

 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;
            }
        }