Changes to SQLite are lost after connection.Close()

Question

Working with MonoTouch .NET for iPhone and SQLite on Mac OSX. I can read from the database no problem. However, when I attempt to make changes to the database, I get strange behavior. The code below runs without exception, but no real changes are being made to the database. I can create a table, insert a record, select that record, and display it in the console. But, when I close the db connection, all those changes go away. Further, if I put a break-point in the code before conn.Close() and check the database, the table doesn't exist. Sounds like I'm only working with an imaginary database that responds to actual SQL statements. Weird.

This code runs but does not actually write anything to the database:

const string _connectionString = "Data Source=App_Data/MyDatabaseFile";

var conn = new SqliteConnection(_connectionString);
conn.Open();

var command = conn.CreateCommand();

command.CommandText = "CREATE TABLE Test (id integer primary key AUTOINCREMENT, text varchar(100))";
command.ExecuteNonQuery();

var cmd2 = conn.CreateCommand();
cmd2.CommandText = "INSERT INTO Test (Text) Values ('test test test')";
var rowsAffected = cmd2.ExecuteNonQuery(); //rowsAffected is 1

var cmd3 = conn.CreateCommand();
cmd3.CommandText = "SELECT * FROM Test";
var reader = cmd3.ExecuteReader();
Console.WriteLine(reader["text"]); //writes "test test test" to console, nothing in database

conn.Close();

If I execute the same SQL statements in in SQLite Browser, they work fine:

CREATE TABLE Test (id integer primary key AUTOINCREMENT, text varchar(100));
INSERT INTO Test (Text) Values ('test test test');
SELECT * FROM Test;

... yields { id = 1, text = 'test test test' }

You can probably tell, but it's worth mentioning that I'm not using transactions.

UPDATE:

Interesting... I can change the code above that runs the SELECT query to look in 'Test2' (non-existent table) and it throws an SQLiteException: "No such table: Test2".

I also tried wrapping the above code in a transaction on the off chance that SQLite requires transactions... no dice.

UPDATE #2:

Adam suggested trying to open a new connection and SELECT from the new table again before stopping the application. I added the following code after the above code:

var conn2 = new SqliteConnection(_connectionString);
conn2.Open();
var cmd4 = conn2.CreateCommand();
cmd4.CommandText = "SELECT * FROM Test";
var reader2 = cmd4.ExecuteReader();
Console.WriteLine("2nd attempt: " + reader2["text"]); //outputs correctly
conn.Close();

Alright, so my assumption about it losing changes after connection.Close() is incorrect. It definitely found the newly inserted row and output it to the console. However, the table and row still disappear after I stop the application.

Answer 1

For anyone dealing with the same question, I thought I'd go ahead and post my findings. The behavior I described in my original post is mostly by design. To be more clear, here's a better explanation:

When MonoDevelop deploys your app to the iPhone Simulator, a COPY of your SQLite database goes with it to live in the iPhone environment. So, when your app modifies your database, the original database isn't being touched. When your app stops, the changes you made to the simulator copy of your database are lost. However, while the app is running, you can insert, update, delete and select all you want and everything should work as expected.

This is only true when using the Simulator.