C# SqliteCommand.ExecuteReturnRowId方法代码示例

        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)) {
//.........这里部分代码省略.........