C# SqliteCommand.ExecuteScalar方法代码示例

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

            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.Main);

            // Get our button from the layout resource,
            // and attach an event to it
            Button button = FindViewById<Button>(Resource.Id.MyButton);

            button.Click += delegate { button.Text = string.Format("{0} clicks!", count++); };

            #region ADDED TO SAMPLE TO DEMONSTRATE Portable.Data.Sqlite

            string myTableName = "TestTable1";
            string sql;

            //Instantiate my "crypt engine"
            this.AppCryptEngine = this.AppCryptEngine ?? new FakeCryptEngine(this.AppPassword);

            #region Part 1 - ADO - Create a table, add a record, add a column, add encrypted data, read back data

            using (var dbConn = new SqliteAdoConnection(this.SqliteConnection, this.AppCryptEngine)) {

                Console.WriteLine("PART 1 - Doing ADO stuff");

                //Create the table if it doesn't exist
                sql = "CREATE TABLE IF NOT EXISTS " + myTableName + " (IdColumn INTEGER PRIMARY KEY AUTOINCREMENT, DateTimeColumn DATETIME, TextColumn TEXT);";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    dbConn.SafeOpen();
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("Table [" + myTableName + "] created (if it didn't exist).");
                }

                //Add a record
                sql = "INSERT INTO " + myTableName + " (DateTimeColumn, TextColumn) VALUES (@date, @text);";
                int newRowId;
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    cmd.Parameters.Add(new SqliteParameter("@date", DateTime.Now));
                    cmd.Parameters.Add(new SqliteParameter("@text", "Hello SQLite."));
                    dbConn.SafeOpen();
                    newRowId = Convert.ToInt32(cmd.ExecuteReturnRowId());  //Note: INTEGER columns in SQLite are always long/Int64 - including ID columns, so converting to int
                    Console.WriteLine("A record with ID " + newRowId.ToString() + " was created in table [" + myTableName + "].");
                }

                //Read the datetime column on the oldest record
                sql = "SELECT [DateTimeColumn] FROM " + myTableName + " ORDER BY [DateTimeColumn] LIMIT 1;";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    dbConn.SafeOpen();
                    DateTime oldest = Convert.ToDateTime(cmd.ExecuteScalar());
                    Console.WriteLine("The oldest record in table [" + myTableName + "] has timestamp: " + oldest.ToString());
                }

                //Add an encrypted column to the table
                //NOTE: There is no benefit to creating the column as SQLite datatype ENCRYPTED vs. TEXT
                //  It is actually a TEXT column - but I think it is nice to set it to type ENCRYPTED for future purposes.
                //  Hopefully a future version of SQLitePCL will make it easy to figure out if a column is defined as ENCRYPTED or TEXT
                //  (right now, it identifies both as TEXT)
                sql = "ALTER TABLE " + myTableName + " ADD COLUMN EncryptedColumn ENCRYPTED;";
                //Note: This column shouldn't exist until the above sql is run, since I just created the table above.  But if this application has been run multiple times, 
                //  the column may already exist in the table - so I need to check for it.
                bool columnAlreadyExists = false;
                #region Check for column
                using (var checkCmd = new SqliteCommand(dbConn)) {
                    checkCmd.CommandText = "PRAGMA table_info (" + myTableName + ");";
                    dbConn.SafeOpen();
                    using (var checkDr = new SqliteDataReader(checkCmd)) {
                        while (checkDr.Read()) {
                            if (checkDr.GetString("NAME") == "EncryptedColumn") {
                                Console.WriteLine("The [EncryptedColumn] column already exists.");
                                columnAlreadyExists = true;
                                break;
                            }
                        }
                    }
                }
                #endregion
                if (!columnAlreadyExists) {
                    using (var cmd = new SqliteCommand(sql, dbConn)) {
                        dbConn.SafeOpen();
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("The [EncryptedColumn] column was created in table [" + myTableName + "].");
                    }
                }

                //Add a record with an encrypted column value
                sql = "INSERT INTO " + myTableName + " (DateTimeColumn, TextColumn, EncryptedColumn) VALUES (@date, @text, @encrypted);";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    cmd.Parameters.Add(new SqliteParameter("@date", DateTime.Now));
                    cmd.Parameters.Add(new SqliteParameter("@text", "Hello data."));
                    cmd.AddEncryptedParameter(new SqliteParameter("@encrypted",
                        Tuple.Create<string, string, string>("Hello", "encrypted", "data")));
                    dbConn.SafeOpen();
                    newRowId = Convert.ToInt32(cmd.ExecuteReturnRowId());  //Note: INTEGER columns in SQLite are always long/Int64 - including ID columns, so converting to int
                    Console.WriteLine("A record featuring encrypted data with ID " + newRowId.ToString() + " was created in table [" + myTableName + "].");
                }

                //Get the value of the encrypted column
                sql = "SELECT [EncryptedColumn] FROM " + myTableName + " WHERE [IdColumn] = @id;";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
//.........这里部分代码省略.........

        public override long InsertWithOnConflict (String table, String nullColumnHack, ContentValues initialValues, ConflictResolutionStrategy conflictResolutionStrategy)
        {
            if (!String.IsNullOrWhiteSpace(nullColumnHack)) {
                var e = new InvalidOperationException("{0} does not support the 'nullColumnHack'.".Fmt(Tag));
                Log.E(Tag, "Unsupported use of nullColumnHack", e);
                throw e;
            }

            var command = GetInsertCommand(table, initialValues, conflictResolutionStrategy);

            var lastInsertedId = -1L;
            try {
                command.ExecuteNonQuery();

                // Get the new row's id.
                // TODO.ZJG: This query should ultimately be replaced with a call to sqlite3_last_insert_rowid.
                var lastInsertedIndexCommand = new SqliteCommand("select last_insert_rowid()", Connection, currentTransaction);
                lastInsertedId = (Int64)lastInsertedIndexCommand.ExecuteScalar();
                lastInsertedIndexCommand.Dispose();
                if (lastInsertedId == -1L) {
                    Log.E(Tag, "Error inserting " + initialValues + " using " + command.CommandText);
                } else {
                    Log.V(Tag, "Inserting row " + lastInsertedId + " from " + initialValues + " using " + command.CommandText);
                }
            } catch (Exception ex) {
                Log.E(Tag, "Error inserting into table " + table, ex);
            } finally {
                command.Dispose();
            }
            return lastInsertedId;
        }

 //A little static method to check and see how many records are in the encrypted table,
 //  that I will use multiple times in the sample code.
 //  No need to include something like this in your implementations of EncryptedTableItem
 public static long GetNumRecords(SqliteAdoConnection dbConn, string tableName) {
     string sql = "SELECT COUNT(*) FROM " + tableName + ";";
     using (var cmd = new SqliteCommand(sql, dbConn)) {
         dbConn.SafeOpen();
         return (long)cmd.ExecuteScalar();
     }
 }