System.Data.SQLite

Hi.

First of all i want to thank you for the nice work you have done with the ADO.NET for SQLite! Keep it going! :-) 

Now, my question / problem: I am using .NEt, C# for windows.I am binding a table to a DataGridView in order to enable easy view & editin one component. It work all fine until the moment i encrypt the database. Reading from an encrypted database is working, but writing the data fails (after creating the SQLiteCommand builder and trying to get the commands). I was not sure if i am making something wrong or this is a bug (or official an not supported feature, but i have found nothing like this on the site or elsewhere) so i post it here. 

Bellow is my test project. It only needs an empty windows form (gui si build dynamically), referencing the SQLite managed library and a database with 2 or 3 columns, one of them named 'id' as PK:

public partial class SQLiteTest : Form
{
    /* === table ===
    CREATE TABLE [products] (
    [id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
    [name] TEXT  NOT NULL,
    [description] TEXT  NOT NULL,
    [quantity] INTEGER DEFAULT '0' NOT NULL
    )
    */

    #region Fields (1)

    // Fields (1) 

    private const string password = @"blah";

    #endregion Fields 

    #region Constructors (1) 

    public SQLiteTest()
    {
        InitializeComponent();
        this.dataAdapter = new SQLiteDataAdapter();
        this.dataTable = new DataTable();

        var buttonLoad = new Button();
        var buttonSave = new Button();
        var buttonLock = new Button();
        var buttonUnlock = new Button();
        this.dataGridView = new DataGridView();

        this.dataGridView.SetBounds(1, 1, this.Size.Width - 1, this.Size.Height - 55);

        buttonLoad.Text = @"Load";
        buttonLoad.Click += buttonLoad_Click;
        buttonLoad.SetBounds(1, this.dataGridView.Size.Height + 5, 50, 25);
        buttonSave.Text = @"Save";
        buttonSave.Click += buttonSave_Click;
        buttonSave.SetBounds(buttonLoad.Size.Width + 5, this.dataGridView.Size.Height + 5, 50, 25);
        buttonLock.Text = @"Lock";
        buttonLock.Click += buttonLock_Click;
        buttonLock.SetBounds(buttonLoad.Size.Width + buttonSave.Size.Width + 10, this.dataGridView.Size.Height + 5, 50, 25);
        buttonUnlock.Text = @"Unlock";
        buttonUnlock.Click += buttonUnlock_Click;
        buttonUnlock.SetBounds(buttonLoad.Size.Width + buttonSave.Size.Width + buttonLoad.Size.Width + 15, this.dataGridView.Size.Height + 5, 50, 25);

        this.Controls.Add(buttonLoad);
        this.Controls.Add(buttonSave);
        this.Controls.Add(buttonLock);
        this.Controls.Add(buttonUnlock);
        this.Controls.Add(this.dataGridView);
    }

    #endregion Constructors 

    #region Properties (4) 

    // Private Properties (4) 

    private SQLiteConnection connection { get; set; }

    private SQLiteDataAdapter dataAdapter { get; set; }

    private DataGridView dataGridView { get; set; }

    private DataTable dataTable { get; set; }

    #endregion Properties 

    #region Methods (9) 

    // Private Methods (9) 

    private SQLiteCommand buildCommand(string cmd)
    {
        return new SQLiteCommand(this.connection) { CommandText = cmd };
    }

    private void baseFunc(Action action, bool state = false)
    {
        try
        {
            this.createConnection();
            if (state) connection.SetPassword(password);
            this.openConnection();  // comment for encrypted files
            //this.openConnection(password); // use for encrypted files
            action.Invoke();
            this.closeConnection();
        }
        catch (Exception exception) { MessageBox.Show(exception.Message); }
    }

    private void buttonLoad_Click(object sender, EventArgs e)
    {
        this.baseFunc(() =>
            {
                this.dataTable.Clear();
                this.dataTable.Columns.Clear();
                this.dataAdapter.SelectCommand = this.buildCommand(@"select * from products");
                this.dataAdapter.Fill(this.dataTable);
                dataGridView.DataSource = this.dataTable;
                dataGridView.Columns[0].Visible = false;
            });
    }

    private void buttonLock_Click(object sender, EventArgs e)
    {
        this.baseFunc(() => connection.ChangePassword(password));
    }

    private void buttonSave_Click(object sender, EventArgs e)
    {
        this.baseFunc(() =>
        {
            var cBuilder = new SQLiteCommandBuilder(this.dataAdapter);
            var cmdInsert = cBuilder.GetInsertCommand(); // <= if file is encrypted, app crashes here!
            var cmdUpdate = cBuilder.GetUpdateCommand();
            var cmdDelete = cBuilder.GetDeleteCommand();
            cmdUpdate.Connection = this.connection;
            cmdInsert.Connection = this.connection;
            cmdDelete.Connection = this.connection;
            this.dataAdapter.InsertCommand = cmdInsert;
            this.dataAdapter.UpdateCommand = cmdUpdate;
            this.dataAdapter.DeleteCommand = cmdDelete;
            this.dataAdapter.Update(this.dataTable);
        });
    }

    private void buttonUnlock_Click(object sender, EventArgs e)
    {
        this.baseFunc(() => connection.ChangePassword(String.Empty), true);
    }

    private void closeConnection()
    {
        if (this.connection != null)
        {
            if (this.connection.State == ConnectionState.Open) { this.connection.Close(); }
        }
    }

    private void createConnection()
    {
        this.connection = new SQLiteConnection { ConnectionString = @"Data Source=c:\temp\SQLite\products.storage" };
    }

    private void openConnection(string pwd = @"")
    {
        if (!String.IsNullOrEmpty(pwd)) { this.connection.SetPassword(pwd); }
        this.connection.Open();
    }

    #endregion Methods 
}

Thanks and greetings,

 Paul.