C# SQLiteConnection.Query方法代码示例

        public void OnSuccess(object response)
        {
            Deployment.Current.Dispatcher.BeginInvoke(() =>
         {
             Game game = (Game)response;
             string scoreid = game.GetScoreList()[0].GetScoreId();

             dbConn = new SQLiteConnection(DB_PATH);
             var testdata = dbConn.Query<Task>("select * from task where id='" + 1 + "'").FirstOrDefault();
             // Check result is empty or not
             if (testdata == null)
                 MessageBox.Show("id Not Present in DataBase");
             else
             {

                 var tp = dbConn.Query<Task>("update task set Scoreid='" + scoreid  + "' where Username = '" +testdata.Username  + "'").FirstOrDefault();
                 // Update Database
                 dbConn.Update(tp);
                
             }
             NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));


         });
        }

        public static void addCompletedCircle(Circle circle)
        {
            completeCircles.Add(circle);

            string dbPath = GetDBPath();
            SQLiteConnection db;
            db = new SQLiteConnection(dbPath);
            db.Insert(circle);

            var result = db.Query<Line>("SELECT * FROM Lines");
            foreach (Line l in result) {
                Console.WriteLine("Line DB: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y);
            }
            var result2 = db.Query<Circle>("SELECT * FROM Circles");
            foreach (Circle c in result2) {
                Console.WriteLine("Circle DB: cx {0}, cy {1}, p2x {2}, p2yy {3}", c.center.X, c.center.Y, c.point2.X, c.point2.Y);
            }

            db.Close();
            db = null;

            foreach (Line l in completeLines) {
                Console.WriteLine("Line CL: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y);
            }
            foreach (Circle c in completeCircles) {
                Console.WriteLine("Circle CC: cx {0}, cy {1}, p2x {2}, p2yy {3}", c.center.X, c.center.Y, c.point2.X, c.point2.Y);
            }
        }

        public Results()
        {
            InitializeComponent();
            App42API.Initialize(Constants.API_KEY, Constants.SECRET_KEY);
            string gameName;
            if ((int)settings["trigger"] == 0)
            {
                gameName = "Bollywood_game";
            }
            else
            {
                gameName = "Hollywood_game";
            }


            new_xp = Global.findxp(Global.p1Score, Global.p2Score);
            dbConn = new SQLiteConnection(DB_PATH);

            var tpdata = dbConn.Query<Task>("select * from task where id='" + 1 + "'").FirstOrDefault();
            // Check result is empty or not
            if (tpdata == null)
                MessageBox.Show("Title Not Present in DataBase");
            else
            {

                scoreId = tpdata.ScoreId;
                prev_xp = Convert.ToDouble(tpdata.XP);
                pres_xp = new_xp + prev_xp;

                var tp = dbConn.Query<Task>("update task set XP='" + pres_xp + "' where Username = '" + tpdata.Username + "'").FirstOrDefault();
                // Update Database
                dbConn.Update(tp);
               level =  Global.levelFromXP(pres_xp);
                switch(level)
                {
                    case 1:
                        achievementName = "level_1";
                        break;
                    case 2:
                        achievementName = "level_2";
                        break;
                    case 3:
                        achievementName = "level_3";
                        break;
                    case 4:
                        achievementName = "level_4";
                        break;
                    
                }
               
            }

            scoreBoardService.SaveUserScore(gameName, Global.localUsername, Global.p1Score, this);
          
            scoreBoardService.EditScoreValueById(scoreId, pres_xp, this);
            
            achievementService.EarnAchievement(Global.localUsername, achievementName, "xp_game", "",this);  
        }

		protected override void OnCreate (Bundle bundle)
		{
			base.OnCreate (bundle);

			SetContentView (Resource.Layout.ChatLayout);

			//LISTVIEW
			mListView = FindViewById<ListView> (Resource.Id.listViewBox);

			mItems = new List<TableMessages> ();

			string dbPath = System.IO.Path.Combine (System.Environment.GetFolderPath
				(System.Environment.SpecialFolder.Personal), "ormDMS.db3");
			var db = new SQLiteConnection (dbPath);
			DBRepository dbr = new DBRepository ();

			var table = db.Query<TableMessages> ("SELECT * FROM TableMessages where codeChauffeur=?",Data.userAndsoft);
			var i = 0;

			foreach (var item in table) {
				mItems.Add (new TableMessages () {
					texteMessage = item.texteMessage,
					utilisateurEmetteur = item.utilisateurEmetteur,
					statutMessage = item.statutMessage,
					dateImportMessage = item.dateImportMessage,
					typeMessage = item.typeMessage,
					Id = item.Id
				});
				i++;
			}

			if(i > 6){
				View view = LayoutInflater.From (this).Inflate (Resource.Layout.ListeViewDelete, null, false);
				mListView.AddHeaderView (view);
				view.Click += Btndeletemsg_Click;
			}

			adapter = new ListeViewMessageAdapter (this, mItems);
			mListView.Adapter = adapter;

			//EDITTEXT
			var btnsend = FindViewById<LinearLayout>(Resource.Id.btn_send);
			btnsend.Click += Btnsend_Click;

			//STATUT DES MESSAGES RECU TO 1
			var tablemsgrecu = db.Query<TableMessages> ("SELECT * FROM TableMessages where statutMessage = 0");
			foreach (var item in tablemsgrecu) {
				var updatestatutmessage = db.Query<TableMessages> ("UPDATE TableMessages SET statutMessage = 1 WHERE statutMessage = 0");
				var resintegstatut = dbr.InsertDataStatutMessage (1,DateTime.Now,item.numMessage,"","");
			}

		}

        public void UpdateLV(SQLiteConnection connect)
        {

            var Decks = connect.Query<Deck>("SELECT * FROM Deck");
            lvDecks.ItemsSource = Decks;

        }

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            base.OnNavigatedTo(e);
            string msg = "";
            int index = 0;
            if (NavigationContext.QueryString.TryGetValue("msg", out msg))
            {
                index = Convert.ToInt32(msg);
            }

            using (var db = new SQLiteConnection(MainPage.dbPath))
            {
                existing = db.Query<Person>("select * from Person where Id = " + index).ToList();
                if (existing != null)
                {
                    BitmapImage b = new BitmapImage();
                    b = MainPage.BytesToImage(existing[0].Images);

                    tb_name.Text = existing[0].Names;
                    tb_number.Text = existing[0].Numbers;
                    tb_email.Text = existing[0].Emails;
                    pic.Source = b;

                    pic.Opacity = 10;

                    tb_address.Text = existing[0].Addresses;
                    tb_website.Text = existing[0].Websites;
                }
            }
        }

		public static List<Drink> getDrinks()
		{
			var conn = new SQLiteConnection (System.IO.Path.Combine (documentsFolder(), "database.db"));
			var results = conn.Query<Drink> ("SELECT * FROM Drink");
			conn.Close ();
			return results;
		}

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            if (NavigationContext.QueryString.ContainsKey("transaccion"))
                transaccion = NavigationContext.QueryString["transaccion"].ToString();

            if (transaccion.Equals("crear"))
                Mensaje.Text = "       Selecciona el cliente para \n               agregar pedido";
            else
                if (transaccion.Equals("consultar"))
                {
                    Mensaje.Text = "       Selecciona el cliente para \n               consultar pedido";
                }

            db = new SQLiteConnection("naturapp.db");

            var count = (from x in db.Table<tablaClientes>() select x.idCliente).Count();
            if (count > 0)
            {
                clientes = db.Query<tablaClientes>("SELECT idCliente, nombres, apellidos, direccion, telefono, correo, sexo, fechaNacimiento, estadoCivil from tablaClientes");

                arrClientes = new ObservableCollection<Cliente>();
                foreach (var cliente in clientes)
                {
                    arrClientes.Add(new Cliente(cliente.idCliente.ToString(), cliente.nombres, cliente.apellidos, cliente.direccion, cliente.telefono, cliente.correo, cliente.sexo, cliente.fechaNacimiento, cliente.estadoCivil));
                }

                listClientes.ItemsSource = arrClientes;
            }
            else
            {
                MessageBox.Show("No hay clientes agregados");
            }

            base.OnNavigatedTo(e);
        }

        private void UnshufflePlaylist(SQLiteConnection connection, int currentSong)
        {
            connection.Execute("update CurrentPlaylistSong set Rank = ActualRank");

            var song = connection.Query<CurrentPlaylistSong>("Select Rank from CurrentPlaylistSong where SongId = ?", currentSong).FirstOrDefault();
            this.CurrentIndex = song == null ? 0 : song.Rank;
        }

        public override StartCommandResult OnStartCommand(Android.Content.Intent intent, StartCommandFlags flags, int startId)
        {
            //start v2
            //apicall.addReport("Application opened");

            t2.Interval = 120000; // = 2 mins
            t2.Elapsed += new System.Timers.ElapsedEventHandler(t2_Elapsed);
            t2.Start();

            t3.Interval = 30000; // = 4 mins
            t3.Elapsed += new System.Timers.ElapsedEventHandler(t3_Elapsed);
            t3.Start();

            //stop v2

            // get DEVID

            string dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "settingsk.db3");
            var db = new SQLiteConnection(dbPath);
            db.CreateTable<Settings>();
            List<Settings> myId = db.Query<Settings>("select devID from Settings where identificator = '1607'");

            string gotID = "";
            foreach (Settings s in myId)
            {
                gotID = s.devID.ToString();
            }
            //devID = myId.Last().devID.ToString();
            devID = gotID;

            //stop getdevid
            return StartCommandResult.Sticky;
        }

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            db = new SQLiteConnection("naturapp.db");

            if (NavigationContext.QueryString.ContainsKey("idCliente"))
                idCliente = NavigationContext.QueryString["idCliente"].ToString();

            int id = Convert.ToInt32(idCliente.ToString());

            var count = (from x in db.Table<tablaPedidos>() where x.idCliente == id select x.idCliente).Count();
            if (count > 0)
            {
                pedidos = db.Query<tablaPedidos>("SELECT * from tablaPedidos where idCliente like " + idCliente);

                arrPedidos = new ObservableCollection<PedidoConsulta>();
                foreach (var pedido in pedidos)
                {
                    arrPedidos.Add(new PedidoConsulta(pedido.producto,pedido.fechaPedido.ToShortDateString(),pedido.totalPedido.ToString()));
                }

                listPedidos.ItemsSource = arrPedidos;
            }
            else
            {
                MessageBox.Show("No hay pedidos generados para este cliente");
            }

            base.OnNavigatedTo(e);
        }

 public void Clear()
 {
     using (var connection = new SQLiteConnection(_dbPath))
     {
         connection.Query<Account>("DELETE * FROM ACCOUNT");
     }
 }

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            db = new SQLiteConnection("naturapp.db");

            var count = (from x in db.Table<tablaClientes>() select x.idCliente).Count();
            if (count > 0)
            {
                clientes = db.Query<tablaClientes>("SELECT * FROM tablaClientes WHERE idCliente IN (SELECT idCliente FROM tablaPedidos");

                if (clientes == null)
                {
                    MessageBox.Show("No hay pedidos agregados");
                }
                else
                {
                    arrClientes = new ObservableCollection<Cliente>();
                    foreach (var cliente in clientes)
                    {
                        arrClientes.Add(new Cliente(cliente.idCliente.ToString(), cliente.nombres, cliente.apellidos, cliente.direccion, cliente.telefono, cliente.correo, cliente.sexo, cliente.fechaNacimiento, cliente.estadoCivil));
                    }

                    listClientes.ItemsSource = arrClientes;
                }
            }
            else
            {
                MessageBox.Show("No hay clientes agregados");
            }

            base.OnNavigatedTo(e);
        }

 public Exercise GetExerciseById(int id)
 {
     using (var dbConn = new SQLiteConnection(App.DB_PATH))
     {
         var exercise = dbConn.Query<Exercise>("select * from exercise where ExerciseID = ?", id).FirstOrDefault<Exercise>();
         return exercise;
     }
 }

 // Retrieve the specific record from the database.   
 public Record getRecordById(int recordId)
 {
     using (var dbConn = new SQLiteConnection(App.DB_PATH))
     {
         var existingRecord = dbConn.Query<Record>("select * from Record where Id =" + recordId).FirstOrDefault();
         return existingRecord;
     }
 }