C# SQLiteAsyncConnection.QueryAsync方法代码示例

        private async void enterbtn_Click(object sender, RoutedEventArgs e)
        {
            var path = ApplicationData.Current.LocalFolder.Path + "/mydb.db";
            var con = new SQLiteAsyncConnection(path);
            person loginPerson = (await con.QueryAsync<person>("select isConfirmed from person where name='" + Dashboard.userName + "'"))[0];

            if (loginPerson.isConfirmed == 1)
            {
                List<person> all = new List<person>();
                all = await con.QueryAsync<person>("select name,email,country,gender,phone from person where name='" + searchboxtxt.Text + "' AND isConfirmed= 1  ");
               // var mm1 = new MessageDialog("no matches found").ShowAsync();
                String res = "";
                foreach (person a in all)
                {
                    res += "\n\n" + a.name + "\n\n" + a.email + "\n\n" + a.country + "\n\n" + a.gender + "\n\n" + a.phone;
                }
                restxtblock.Text = res;
            }
            else
            {
                var md1 = new MessageDialog("No permission to search for users").ShowAsync();
        
            }

            
        }

        private void AppBarToggleButton_Checked(object sender, RoutedEventArgs e)
        {

            AppBarToggleButton favButton = sender as AppBarToggleButton;
            //if ((bool)favButton.IsChecked)
            //{
            //    var path = ApplicationData.Current.LocalFolder.Path + "/myDb1.DB";
            //    var con = new SQLiteAsyncConnection(path);
            //    //await con.InsertAllAsync(results);

            //    await con.QueryAsync<Result>(string.Format("insert into Result (name,name) values('{0}','{1}')", results[0].name, tblock.Text));
            //}
            //else
            //{

            //}
            if ((bool)favButton.IsChecked)
            {
                var path = ApplicationData.Current.LocalFolder.Path + "/myDb1.DB";
                var con = new SQLiteAsyncConnection(path);
                con.QueryAsync<Favourite>(string.Format("insert into Favourite (placeName,userName) values('{0}','{1}')", results[0].name, tblock.Text));
            }
            else if (!(bool)favButton.IsChecked)
            {
                var path = ApplicationData.Current.LocalFolder.Path + "/myDb1.DB";
                var con = new SQLiteAsyncConnection(path);
                con.QueryAsync<Favourite>(string.Format("delete from Favourite where placeName = '{0}' and username='{1}'", results[0].name, tblock.Text));
            }


        }

        protected override async void OnNavigatedTo(NavigationEventArgs e)
        {
            tblock.Text = !string.IsNullOrEmpty((App.Current as App).NavigateText) ? (App.Current as App).NavigateText : string.Empty;
            var path = ApplicationData.Current.LocalFolder.Path + "/mydb.db";
            var con = new SQLiteAsyncConnection(path);

            //con.QueryAsync<person>("select name from person where name= ") + "";

            //con.UpdateAsync("update person set password='"+npwd.Text+"' where password='"+epwd.Text+"' ");
            //person p1 = new person();
            //List<person> allpersons1 = new List<person>();
            //allpersons1 = await con.QueryAsync<person>("select name from person where name=" + "\'" + tblock.Text + "\'");
            //if (allpersons1.Count == 1)
            //{
            //    p1 = allpersons1[0];

            //}
            //await con.QueryAsync<person>("alter TABLE person add column isConfirmed integer ");
            if (isAdmin)
            {
                users.Visibility = Visibility.Visible;
                List<person> notConfirmedUsers = await con.QueryAsync<person>("select name from person where isConfirmed = 0");
                users.ItemsSource = notConfirmedUsers;
            }
            else
            {
                users.Visibility = Visibility.Collapsed;
            }

        }

            async Task<ObservableCollection<MusicLibraryViewModel.ArtistItem>> Load()
        {
            var connection = new SQLiteAsyncConnection(_dbPath);

            return new ObservableCollection<MusicLibraryViewModel.ArtistItem>(
               await connection.QueryAsync<MusicLibraryViewModel.ArtistItem>(
                     "select * from ArtistItem"));
        }

        public async Task<ObservableCollection<MapLocationModel>> LoadLocations(Guid mapId)
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(SQLiteConfiguration.ConnectionString);

            _locations = new ObservableCollection<MapLocationModel>(await connection.QueryAsync<MapLocationModel>("SELECT * FROM MapLocations WHERE MapId = ?", mapId));

            return _locations;
        }

        public async Task<ObservableCollection<MapModel>> Load()
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(SQLiteConfiguration.ConnectionString);

            _dataSources = new ObservableCollection<MapModel>(await connection.QueryAsync<MapModel>("SELECT * FROM Maps"));

            return _dataSources;
        }

        private  void button_Click(object sender, RoutedEventArgs e)
        {

           if (File.Exists("Data.sql"))
            {
               // File.Create("data1.db");
                acre.Text = "Exist Exist";               
            }
            else if (File.Exists("data1.db"))
            {
                if (File.Exists("data.db"))
                {acre.Text = "Exist";}
            }

               SQLiteAsyncConnection conn = new SQLiteAsyncConnection(DB_PATH, true);

             var query = conn.QueryAsync<chemicalnames2>("select * from chemicalnames2");
    
             /*   if (File.Exists("data1.db"))
                { acre.Text = "data1 Exist"; }
                else
                    acre.Text = "data Exist";* /
            
              
              

             string[] array = new string[100];
             //string arr = dbconn.Query<Task>("select Title from Task ").FirstOrDefault().ToString();
             array[0] = "string";

             int i;
             for (i = 1; i < 100; i++)
             {
                 array[i] = dbconn.Query<chemicalnames2>("select chemicalname from chemicalnames2 where srno='" + i + "'").FirstOrDefault().ToString();
             }

             items = new ObservableCollection<string>();
             foreach (string str in array)
                 items.Add(str);
             areapicker.ItemsSource = items;
        
                             if (File.Exists("data.db"))
                                {
                                    // Create the database connection
                                    using(var db = new SQLiteConnection("data.db"))
                                    {
                                        // If the table does not exist, create it
                                        db.CreateTable<chemicalnames2>();
                                        acre.Text = "true";
                                        // Retrieve a list of data from the database
                                       // List data = db.Table<chemicalnames2>().ToList<chemicalnames2>().ToString();
                                    }
                                }*/
                            // var tp = dbconn.Query<chemicalnames2>("select chemicalname from chemicalnames2 where srno='" + area.Text + "'").FirstOrDefault().ToString();
                             //acre.Text = tp.ToString();


        }

            async Task<ObservableCollection<MusicLibraryViewModel.AlbumItem>> LoadAlbumsFromId(int artistId)
        {
            var connection = new SQLiteAsyncConnection(_dbPath);

            return new ObservableCollection<MusicLibraryViewModel.AlbumItem>(
               await connection.QueryAsync<MusicLibraryViewModel.AlbumItem>(
                     string.Format("select * from AlbumItem where ArtistId = {0}", artistId)));

        }

        private async void checkUser()
        {

            if (!string.IsNullOrWhiteSpace(LoginModel.BadgeId) && !string.IsNullOrWhiteSpace(LoginModel.Password))
            {
                SQLiteAsyncConnection connection = new SQLiteAsyncConnection("Test.db");
                var result = await connection.QueryAsync<Employee>("SELECT * from Employee where BadgeId=\"" + LoginModel.BadgeId + "\"");
                //var result = await connection.ExecuteAsync("SELECT count("+""+"\"BadgeId\""+")"+ " from Employee");
                System.Diagnostics.Debug.WriteLine("validating user");


                #region
                if (result.Count != 0 && result[0].Password == LoginModel.Password && result[0].BadgeId == LoginModel.BadgeId)
                {
                    System.Diagnostics.Debug.WriteLine("user validated");

                    Container.BadgeId = result[0].BadgeId;
                    Container.Designation = result[0].Designation;
                    Container.Manager = result[0].Manager;
                    Container.Skill = result[0].Skill;
                    Container.EName = result[0].EName;
                    Container.Level = result[0].Level;

                    if (result[0].Level != "1")
                    {
                        Container.Enable = Visibility.Visible;
                    }
                    else
                    {
                        Container.Enable = Visibility.Collapsed;
                    }
                    ((Frame)Window.Current.Content).Navigate(typeof(Profile));

                }
                #endregion
                else
                {
                    //set the message for user
                    //LoginModel.UserFeedBack = "Username or Password is Invalid!";
                    //UserFeedBack = Visibility.Visible;
                    //LoginModel.BadgeId = null;
                    //LoginModel.Password = null;

                    var errmsg = new MessageDialog("Invalid username or password");
                    await errmsg.ShowAsync();

                    ((Frame)Window.Current.Content).Navigate(typeof(MainPage));

                }
            }
            else
            {
                var errmsg = new MessageDialog("Please enter the Details");
                await errmsg.ShowAsync();
            }
        }

        protected async override void OnNavigatedTo(NavigationEventArgs e)
        {
            bool dbExist = await CheckDbAsync("moneybook.db");
            if (!dbExist)
            {
                await CreateDatabaseAsync();
                //await CreateTaiKhoanItems();
            }
            // Get Items
            SQLiteAsyncConnection conn = new SQLiteAsyncConnection("moneybook.db");
            var query = conn.Table<taikhoan>();
            taikhoans = await query.ToListAsync();

            var queryk = await conn.QueryAsync<ghichep>("SELECT * from ghichep order by ngay");
            List<ghichep> n = new List<ghichep>();
            foreach (ghichep g in queryk.ToList())
            {
                n.Add(g);
            }

            //var qGhiChep = conn.Table<ghichep>();
            //ghicheps = await qGhiChep.ToListAsync();

            // Show Tiems
            taikhoanList.ItemsSource = taikhoans;

            var result = from item in n
                         group item by DateTime.Parse(item.Ngay).ToString("dd-MM-yyyy")
                             into grp
                         orderby grp.Key
                         select grp;
            cvsGhiChepList.Source = result;

            ghichepListpv.DataContext = n;

            double sotienbandau = 0;
            var Sotienquery = await conn.QueryAsync<taikhoan>("SELECT sotienbandau from taikhoan");
            foreach (var g in Sotienquery.ToList())
            {
                sotienbandau += double.Parse(g.Sotienbandau);
            }
            tongsodutaikhoan.Text = "Tổng: " + sotienbandau.ToString("#,##0") +" đ";
        }

 protected override async void OnNavigatedTo(NavigationEventArgs e)
 {
     this.navigationHelper.OnNavigatedTo(e);
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("moneybook.db");
     var query = await conn.QueryAsync<taikhoan>("SELECT tentaikhoan from taikhoan");
     List<string> n = new List<string>();
     foreach (var g in query.ToList())
     {
         n.Add(g.Tentaikhoan);
     }
     tutaikhoan.ItemsSource = n;
 }

        protected async override void OnNavigatedTo(NavigationEventArgs e)
        {


             part = e.Parameter as string;
            label.Text = part;
            // Get users

            SQLiteAsyncConnection connection = new SQLiteAsyncConnection("institutionFinder.db");
            users = await connection.QueryAsync<Courses>("Select * FROM Courses");
            // Show users
            viewcourses.ItemsSource = users;

        }

        private async void Button_Click_3(object sender, RoutedEventArgs e)
        {
            string result = "";
            var path = ApplicationData.Current.LocalFolder.Path + @"\Users.db";
            var db = new SQLiteAsyncConnection(path);
            List<User> all = await db.QueryAsync<User>("Select* from Users");
            var count = all.Any() ? all.Count : 0;

            foreach (var item in all)
            {
                result += "email" + item.email.ToString() + "\nFstname" + item.name+"\n";
            }

            MessageDialog msg = new MessageDialog(result.ToString());
            await msg.ShowAsync();
        }

        protected override async void OnNavigatedTo(NavigationEventArgs e)
        {
            SQLiteAsyncConnection conn = new SQLiteAsyncConnection("moneybook.db");
            var taikhoanID = (int)e.Parameter;
            var query = await conn.QueryAsync<taikhoan>("SELECT * from taikhoan WHERE Id = " + taikhoanID);
            foreach (var g in query.ToList())
            {

                id = g.Id;
                tentaikhoan.Text = g.Tentaikhoan;
                loaitaikhoan.SelectedItem = loaitaikhoan.Items.Cast<ComboBoxItem>().Where(cmbi => (string)cmbi.Tag == g.Loaitaikhoan).First();
                loaitiente.SelectedItem = loaitiente.Items.Cast<ComboBoxItem>().Where(cmbi => (string)cmbi.Tag == g.Loaitiente).First();
                sotienbandau.Text = g.Sotienbandau;
                ghichu.Text = g.Ghichu;
            }
        }

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