C# SQLiteConnection.Execute方法代码示例

        public App()
        {
            // The root page of your application
            MainPage = new ContentPage {
                Content = new StackLayout {
                    VerticalOptions = LayoutOptions.Center,
                    Children = {
                        new Label {
                            XAlign = TextAlignment.Center,
                            Text = "Welcome to Xamarin Forms!"
                        }
                    }
                }
            };

            // path to db
            var path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments), "mydb");

            // open connection and attempt to apply encryption using PRAGMA statement
            var conn = new SQLiteConnection (path);
            conn.Execute ("PRAGMA key = 'passme'");
            int v = conn.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master");
            conn.Close ();

            // open another connection, this time use wrong password. It will still open, but should fail the
            // query (see docs on key PRAGMA https://www.zetetic.net/sqlcipher/sqlcipher-api/)
            var conn2 = new SQLiteConnection (path);
            conn2.Execute ("PRAGMA key = 'wrongpassword'");
            int v2 = conn2.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master");
            conn2.Close ();
        }

        private void ShufflePlaylist(SQLiteConnection connection, int currentSong)
        {
            connection.Execute("create temp table TempPlaylistOrder as select Rank as ShuffledRank from CurrentPlaylistSong order by case when SongId = ? then -1 else abs(random()) end", currentSong);
            connection.Execute("update CurrentPlaylistSong set Rank = (select rowid from TempPlaylistOrder where ShuffledRank = Rank) - 1");
            connection.Execute("drop table TempPlaylistOrder");

            this.CurrentIndex = 0;
        }

        public static void CreateTables(SQLiteConnection db)
        {
            db.BeginTransaction();

            db.CreateTable<DriveInformation>();
            db.CreateTable<DirectoryInformation>();
            db.CreateTable<FileInformation>();
            db.CreateTable<FileAttributeInformation>();
            db.CreateTable<FileAttribute>();

            db.Execute("CREATE INDEX if not exists \"main\".\"ix_DirectoryInformation_driveid_path\" ON \"DirectoryInformation\" (\"DriveId\" ASC, \"Path\" ASC)");
            db.Execute("CREATE INDEX if not exists \"main\".\"ix_FileInformation_driveid_directoryid\" ON \"FileInformation\" (\"DirectoryId\" ASC, \"DriveId\" ASC)");

            db.Commit();
        }

 public static int ExecuteNonQuery(string sql)
 {
     using(SQLiteConnection Con = new SQLiteConnection(conStr))
     {
         return	Con.Execute(sql);
     }
 }

        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 List<Beacon> GetListOfBeacons()
 {
     lock (dbLock)
     {
         using (var sqlCon = new SQLiteConnection(DBPath))
         {
             sqlCon.Execute(Constants.DBClauseSyncOff);
             sqlCon.BeginTransaction();
             var data = sqlCon.Query<Beacon>("SELECT * FROM Beacon");
             return data;
         }
     }
 }

 public void CleanUpDB()
 {
     lock (this.dbLock)
     {
         using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
         {
             sqlCon.Execute(Constants.DBClauseSyncOff);
             sqlCon.BeginTransaction();
             try
             {
                 sqlCon.Execute("DELETE FROM Beacon");
                 sqlCon.Commit();
                 sqlCon.Execute(Constants.DBClauseVacuum);
             }
             catch (Exception ex)
             {
                 #if(DEBUG)
                 System.Diagnostics.Debug.WriteLine("Error in CleanUpDB! {0}--{1}", ex.Message, ex.StackTrace);
                 #endif
                 sqlCon.Rollback();
             }
         }
     }
 }

        public bool CheckContactExists(string contactAccountID)
        {
            lock (this.dbLock)
            {

                using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
                {

                    sqlCon.Execute(WZConstants.DBClauseSyncOff);

                    List<ContactDB> contacts =
                        sqlCon.Query<ContactDB>("SELECT * FROM ContactDB WHERE ContactAccountGuid=?", contactAccountID);

                    return contacts.Count == 1;

                }//end using sqlCon

            }//end using lock
        }

        public void DeleteContentInfoIfExists(ContentInfo contentInfo)
        {
            lock (this.dbLock)
            {

                using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
                {

                    sqlCon.Execute(WZConstants.DBClauseSyncOff);

                    sqlCon.BeginTransaction();

                    try
                    {

                        ContentInfo contentInfoToDelete =
                            sqlCon.Query<ContentInfo>("SELECT * FROM ContentInfo WHERE ID=?", contentInfo.ID)
                                .FirstOrDefault();

                        if (null != contentInfoToDelete)
                        {

                            // Take care of the message first.
                            MessageDB messageDB = sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE ID=?", contentInfo.MessageDBID)
                                .FirstOrDefault();
                            if (messageDB.MessageID == default(Guid))
                            {
                                // Delete it, because the message has already been inserted from message create.
                                sqlCon.Execute("DELETE FROM MessageDB WHERE ID=?", messageDB.ID);
                            }//end if

                            sqlCon.Execute("DELETE FROM VoiceCache WHERE ContentInfoID=?", contentInfo.ID);
                            sqlCon.Execute("DELETE FROM PollingStepDBCache WHERE ContentInfoID=?", contentInfo.ID);
                            sqlCon.Execute("DELETE FROM MessageRecipientDBCache WHERE MessageDBID=?", messageDB.ID);
                            sqlCon.Execute("DELETE FROM MessageStepDBCache WHERE MessageDBID=?", messageDB.ID);
                            sqlCon.Execute("DELETE FROM ContentState WHERE ContentInfoID=?", contentInfo.ID);

                            sqlCon.Execute("DELETE FROM ContentInfo WHERE ID=?", contentInfo.ID);

                        }//end if

                        sqlCon.Commit();

                    } catch (Exception ex)
                    {

            #if(DEBUG)
                        Console.WriteLine("Error deleting content info! {0}--{1}", ex.Message, ex.StackTrace);
            #endif
                        sqlCon.Rollback();

                    }//end try catch

                }//end using sqlCon

            }//end lock
        }

        public List<ContentPackItemDB> GetAllContentPackItems(int forContentPackID)
        {
            lock (this.dbLock)
            {

                using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
                {

                    sqlCon.Execute(WZConstants.DBClauseSyncOff);

                    return sqlCon.Query<ContentPackItemDB>("SELECT * FROM ContentPackItemDB WHERE ContentPackID=?", forContentPackID);

                }//end sqlCon

            }//end lock
        }

        public List<ContentPackDB> GetAllContentPacks(GenericEnumsContentPackType packType)
        {
            lock (this.dbLock)
            {

                using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
                {

                    sqlCon.Execute(WZConstants.DBClauseSyncOff);

                    return sqlCon.Query<ContentPackDB>("SELECT * FROM ContentPackDB WHERE ContentPackTypeID=?", packType);

                }//end using sqlCon

            }//end lock
        }

        public bool SetupDB()
        {
            lock (this.dbLock)
            {

                try
                {

                    // LOL db
                    using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
                    {

                        sqlCon.CreateTable<UserDB>();
                        sqlCon.CreateTable<ContactDB>();
                        sqlCon.CreateTable<ContactOAuthDB>();
                        sqlCon.CreateTable<ContentPackDB>();
                        sqlCon.CreateTable<ContentPackItemDB>();
                        sqlCon.CreateTable<MessageDB>();
                        sqlCon.CreateTable<MessageRecipientDB>();
                        sqlCon.CreateTable<MessageStepDB>();
                        sqlCon.CreateTable<PollingStepDB>();

                        sqlCon.CreateTable<ContentInfo>();
                        sqlCon.CreateTable<ContentState>();
                        sqlCon.CreateTable<MessageStepDBCache>();
                        sqlCon.CreateTable<MessageRecipientDBCache>();
                        sqlCon.CreateTable<PollingStepDBCache>();
                        sqlCon.CreateTable<VoiceCache>();

                        sqlCon.Execute(WZConstants.DBClauseVacuum);

                    }//end using sqlCon

                    // AnimationData db
                    using (SQLiteConnection sqlCon = new SQLiteConnection(this.AnimationDBPath))
                    {

                        sqlCon.CreateTable<AnimationInfo>();
                        sqlCon.CreateTable<FrameInfo>();
                        sqlCon.CreateTable<LayerInfo>();
                        sqlCon.CreateTable<DrawingInfo>();
                        sqlCon.CreateTable<BrushItem>();
                        sqlCon.CreateTable<TransitionInfo>();
                        sqlCon.CreateTable<TransitionEffectSettings>();
                        sqlCon.CreateTable<PathPointDB>();

                        sqlCon.CreateTable<AnimationAudioInfo>();

                        sqlCon.CreateTable<UndoInfo>();

                        // Create tables here
                        sqlCon.Execute(WZConstants.DBClauseVacuum);

                    }//end using sqlCon

                    return true;

                } catch (SQLiteException ex)
                {
                    throw ex;
                } catch (Exception ex)
                {
                    throw ex;
                }
                /*finally
                {

                    if (File.Exists(this.DBPath))
                    {
                        // Mark the database to be excluded from iCloud backups.
                        NSError error = NSFileManager.SetSkipBackupAttribute(this.DBPath, true);

                        if (null != error)
                        {
                            Console.WriteLine("Could not mark LOL DB's SkipBackupAttribute: {0}", error.LocalizedDescription);
                            error.Dispose();
                        }//end if

                    }//end if

                    if (File.Exists(this.AnimationDBPath))
                    {

                        // Mark the database to be excluded from iCloud backups.
                        NSError error = NSFileManager.SetSkipBackupAttribute(this.AnimationDBPath, true);

                        if (null != error)
                        {
                            Console.WriteLine("Could not mark AnimationData DB's SkipBackupAttribute: {0}", error.LocalizedDescription);
                            error.Dispose();
                        }//end if

                    }//end if

                }//end try catch finally
                */
            }//end lock
        }

        public List<ContentInfo> GetAllContentInfo()
        {
            lock (this.dbLock)
            {

                using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
                {

                    sqlCon.Execute(WZConstants.DBClauseSyncOff);

                    List<ContentInfo> toReturn =
                        sqlCon.Query<ContentInfo>("SELECT * FROM ContentInfo");

                    foreach (ContentInfo eachContentInfo in toReturn)
                    {

                        MessageDB msg = sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE ID=?", eachContentInfo.MessageDBID)
                            .FirstOrDefault();
                        eachContentInfo.Message = MessageDB.ConvertFromMessageDB(msg);

                        List<MessageStepDBCache> msgSteps =
                            sqlCon.Query<MessageStepDBCache>("SELECT * FROM MessageStepDBCache WHERE MessageDBID=?", eachContentInfo.MessageDBID);
                        eachContentInfo.Message.MessageSteps = new List<MessageStep>();

                        for (int i = 0; i < msgSteps.Count; i++)
                        {
                            eachContentInfo.Message.MessageSteps [i] = MessageStepDBCache.ConvertFromMessageStepDB(msgSteps [i]);

                        }//end for

                        List<MessageRecipientDBCache> msgRcp =
                            sqlCon.Query<MessageRecipientDBCache>("SELECT * FROM MessageRecipientDBCache WHERE MessageDBID=?", eachContentInfo.MessageDBID);
                        eachContentInfo.Recipients = new List<Guid>();
                        eachContentInfo.Message.MessageRecipients = new List<Message.MessageRecipient>();

                        for (int i = 0; i < msgRcp.Count; i++)
                        {
                            eachContentInfo.Recipients [i] = new Guid(msgRcp [i].AccountGuid);
                            eachContentInfo.Message.MessageRecipients [i] = new Message.MessageRecipient() {

                                AccountID = eachContentInfo.Recipients[i],
                                IsRead = false

                            };
                        }//end for

                        List<VoiceCache> voiceRecordings =
                            sqlCon.Query<VoiceCache>("SELECT * FROM VoiceCache WHERE ContentInfoID=?", eachContentInfo.ID);
                        eachContentInfo.VoiceRecordings =
                            voiceRecordings.ToDictionary(s => s.StepNumber, s => s.VoiceData);

                        List<PollingStepDBCache> pollingSteps =
                            sqlCon.Query<PollingStepDBCache>("SELECT * FROM PollingStepDBCache WHERE ContentInfoID=?", eachContentInfo.ID);
                        eachContentInfo.PollingSteps =
                            pollingSteps.ToDictionary(s => s.StepNumber, s => PollingStepDB.ConvertFromPollingStepDB(s));

                        if (sqlCon.Query<ContentState>("SELECT * FROM ContentState WHERE ContentInfoID=?", eachContentInfo.ID).Count == 1)
                        {

                            ContentState contentState = new ContentState(MessageDB.ConvertFromMessage(eachContentInfo.Message));
                            //TODO: Set animation items to remove in DBManager.GetAllContentInfo()!
            #if(DEBUG)
                            Console.WriteLine("TODO: Set animation items to remove in DBManager.GetAllContentInfo()!");
            #endif
                            contentState.RemoveExistingItems(null,
                                                             new List<int>(voiceRecordings
                                                             .Where(s => s.IsSent)
                                                             .Select(s => s.StepNumber)),
                                                             new List<int>(pollingSteps
                                                             .Where(s => s.IsSent)
                                                             .Select(s => s.StepNumber)), null);

                            eachContentInfo.ContentState = contentState;

                        }//end if

                    }//end foreach

                    return toReturn;

                }//end using sqlCon

            }//end lock
        }

        private static void ProcessFolder(SQLiteConnection db, DriveInformation drive, List<string> arrHeaders, DirectoryInfo directory)
        {
            try {

                if (!directory.Exists)
                    return;

                if (IgnoreFolder(directory)) {
                    return;
                }

                //go get the cached items for the folder.

                var directoryId = DatabaseLookups.GetDirectoryId(db, drive, directory);

                var cmd = db.CreateCommand("Select * from " + typeof(FileInformation).Name + " Where DriveId = ? AND DirectoryId = ?", drive.DriveId, directoryId);
                var databaseFiles = cmd.ExecuteQuery<FileInformation>();

                //obtain the file metadata for all of the files in the directory so we can determine if we care about this folder.

                var processList = GetFilesToProcess(databaseFiles, arrHeaders, directory);

                if (processList.Count > 0) {

                    db.BeginTransaction();

                    Shell32.Shell shell = new Shell32.Shell();
                    Shell32.Folder folder = shell.NameSpace(directory.FullName);

                    foreach (var item in processList) {
                        try {
                            var fi = item.FileInfo;
                            var headerList = new List<FileAttributeInformation>();

                            for (int i = 0; i < arrHeaders.Count; i++) {

                                var header = arrHeaders[i];

                                if (!IgnoreHeader(header)) {
                                    var value = folder.GetDetailsOf(item.FolderItem, i);

                                    if (!string.IsNullOrWhiteSpace(value)) {
                                        headerList.Add(new FileAttributeInformation() {
                                            AttributeId = DatabaseLookups.GetAttributeId(db, header),
                                            Value = value
                                        });
                                    }
                                }
                            }

                            //this should have been already checked but we want to be safe.
                            if (fi.Exists) {

                                var fileInfo = databaseFiles.FirstOrDefault(info => info.FileName.Equals(fi.Name, StringComparison.OrdinalIgnoreCase));

                                if (fileInfo == null) {
                                    fileInfo = new FileInformation() {
                                        DriveId = drive.DriveId,
                                        DirectoryId = directoryId,
                                        FileName = fi.Name
                                    };
                                    SetFileInformation(fi, fileInfo);
                                    db.Insert(fileInfo);
                                    Console.WriteLine("Inserted:" + fi.FullName);
                                }
                                else {
                                    SetFileInformation(fi, fileInfo);
                                    db.Update(fileInfo);

                                    var deleteCount = db.Execute("Delete from " + typeof(FileAttributeInformation).Name + " WHERE FileId = ?", fileInfo.FileId);
                                    Console.WriteLine("Changed:" + fi.FullName);
                                }

                                //save the headers
                                headerList.ForEach(hl => hl.FileId = fileInfo.FileId);
                                db.InsertAll(headerList);
                            }
                        }
                        catch (Exception ex) {
                            Console.WriteLine(ex.ToString());
                        }
                    }

                    db.Commit();

                }

                //see if we have any additional folders. If we get access denied it will throw an error
                try {
                    foreach (var subDirectory in directory.GetDirectories()) {
                        ProcessFolder(db, drive, arrHeaders, subDirectory);
                    }
                }
                catch (Exception ex) {
                    Console.WriteLine(ex.ToString());
                }
            }
            catch (UnauthorizedAccessException) {

            }
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                                    long length = responseStream.Length;
                                    byte[] receiveStream = new byte[length];
                                    progressBarUpdate.Visibility = ViewStates.Visible;
                                    progressBarUpdate.Indeterminate = false;
                                    progressBarUpdate.Max = (int)length;
                                    progressBarUpdate.Progress = 0;

                                    while (true)
                                    {
                                        int readLength = (int)(length - count > 1000 ? 1000 : length - count);
                                        int num = await responseStream.ReadAsync(receiveStream, count, readLength);
                                        if (num == 0)
                                            break;

                                        count += num;
                                        progressBarUpdate.Progress = count;
                                    }

                                    var receive = Encoding.UTF8.GetString(receiveStream, 0, (int)length);

                                    //var receive = await sr.ReadToEndAsync();

                                    var byteValue = Convert.FromBase64String(receive);
                                    string decodeReceive = Encoding.UTF8.GetString(byteValue, 0, byteValue.Length);

                                    var personList = JsonConvert.DeserializeObject<List<PERSON>>(decodeReceive);

                                    var dbFile = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "contacts.db");
                                    using (SQLiteConnection conn = new SQLiteConnection(dbFile))
                                    {
                                        try
                                        {
                                            var sql = "delete from PERSON";
                                            int oldDataCount = conn.Execute(sql);
                                            new AlertDialog.Builder(this).SetMessage(string.Format("旧数据有{0}条", oldDataCount)).Show();

                                            sql = "DROP TABLE [PERSON];";
                                            conn.Execute(sql);
                                            sql = @"CREATE TABLE [PERSON] (
                                          [ID] nvarchar(2147483647) NOT NULL
                                        , [NAME] nvarchar(2147483647) NULL
                                        , [DEPARTMENT] nvarchar(2147483647) NULL
                                        , [MOBILE_PHONE] nvarchar(2147483647) NULL
                                        , [VIRTUAL_PHONE] nvarchar(2147483647) NULL
                                        , [POSITION] nvarchar(2147483647) NULL
                                        , [REGION] nvarchar(2147483647) NULL
                                        , [OFFICE_PHONE] nvarchar(2147483647) NULL
                                        , [INNER_PHONE] nvarchar(2147483647) NULL
                                        , [PY] nvarchar(2147483647) NULL
                                        , [CAR] nvarchar(2147483647) NULL
                                        , CONSTRAINT [sqlite_autoindex_PERSON_1] PRIMARY KEY ([ID])
                                        );";
                                            conn.Execute(sql);

                                            conn.BeginTransaction();
                                            conn.InsertAll(personList);

                                            textViewCurrentVersion.Text = textViewLatestVersion.Text;
                                            editor.PutString("version", textViewCurrentVersion.Text);
                                            editor.Commit();

                                            sql = "select count(ID) from PERSON";
                                            int newDataCount = conn.ExecuteScalar<int>(sql);
                                            new AlertDialog.Builder(this).SetMessage(string.Format("新数据有{0}条", newDataCount)).Show();

                                            conn.Commit();