public void WriteAndReadData_EncryptedColumn_IsSuccessful()
        {
            //Arrange
            var    cryptEngine   = new SampleAesCryptEngine("myTestPassword");
            string dbPath        = TempDatabaseFiles.GetNewDatabasePath(this);
            int    dataItemIndex = 0;

            using (var db = new SqliteConnection(dbPath, cryptEngine, true))
            {
                //Creating my table & adding encrypted column
                using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
                {
                    db.SafeOpen();
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = String.Format(addColumnSql, tableName);
                    cmd.ExecuteNonQuery();
                }

                //Act
                using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db))
                {
                    //Adding my records
                    foreach (Tuple <DateTime, string> dataItem in _sampleData)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@date", dataItem.Item1);
                        cmd.Parameters.Add("@text", dataItem.Item2);
                        cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption
                        db.SafeOpen();
                        cmd.ExecuteNonQuery();
                    }
                }

                //Assert
                using (var cmd = new SqliteCommand(String.Format(selectSql1, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
                    using (var reader = new SqliteDataReader(cmd))
                    {
                        while (reader.Read())
                        {
                            Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetString("TextColumn"));
                            Assert.AreNotEqual(_sampleData[dataItemIndex].Item2, reader.GetString("EncryptedColumn"));          //The encrypted text should not match the unencrypted text
                            Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text

                            dataItemIndex++;
                        }
                        Assert.AreEqual(3, dataItemIndex); //Should have read three records
                    }
            }
        }
        public void WriteAndReadData_EncryptedColumnWithWrongPassword_CausesException()
        {
            //Arrange
            string    rightPassword    = "******";
            string    wrongPassword    = "******";
            var       cryptEngine      = new SampleAesCryptEngine(rightPassword);
            string    dbPath           = TempDatabaseFiles.GetNewDatabasePath(this);
            Exception decryptException = null;

            using (var db = new SqliteConnection(dbPath, cryptEngine, true))
            {
                //Creating my table & adding encrypted column
                using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
                {
                    db.SafeOpen();
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = String.Format(addColumnSql, tableName);
                    cmd.ExecuteNonQuery();
                }

                //Act
                using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db))
                {
                    //Adding my records
                    foreach (Tuple <DateTime, string> dataItem in _sampleData)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@date", dataItem.Item1);
                        cmd.Parameters.Add("@text", dataItem.Item2);
                        cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption
                        db.SafeOpen();
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            //Assert
            cryptEngine = new SampleAesCryptEngine(wrongPassword); //Testing first with the wrong password
            using (var db = new SqliteConnection(dbPath, cryptEngine, true))
                using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
                    using (var reader = new SqliteDataReader(cmd))
                    {
                        while (reader.Read())
                        {
                            try
                            {
                                //This should fail, because the value that is read from the column cannot be properly decrypted without the correct password
                                Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn"));
                            }
                            catch (Exception e)
                            {
                                decryptException = e;
                            }
                        }
                        Assert.IsNotNull(decryptException as CryptographicException);
                    }

            //Just checking to make sure we can still decrypt the value with the correct password
            cryptEngine = new SampleAesCryptEngine(rightPassword); //Should work correctly with the right password
            using (var db = new SqliteConnection(dbPath, cryptEngine, true))
                using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
                    using (var reader = new SqliteDataReader(cmd))
                    {
                        while (reader.Read())
                        {
                            Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text
                        }
                    }
        }