Mono.Data.Sqlite and filelocks

While porting my projects to Mono I noticed some file locking on databases. There were no problems with the dotNET code. Seems like a difference in behavior between the two frameworks.

A simple query to a Sqlite database would look a lot like the code below

// create the connection
var connection = new SqliteConnection("Data Source=sample.db");
// don't forget to open the connection once created
connection.Open();
// create command
var command = connection.CreateCommand();
// get the value field from the data table
command.CommandText = "SELECT [Value] FROM [DATA] WHERE [ID] = 2";
var value = command.ExecuteScalar().ToString();
connection.Close();

This code works well on both dotNET and Mono. But on Mono the database file is locked until the end of the program. In the sample project at the end of this post I demonstrate this by deleting the database file right after the query result was read, resulting in an Exception. When the project is run on dotNET (change Sqlite reference to dotNET assembly) the sample project works without exceptions.

IO Exception thrown when sample project is run on mono

The solution to this problem is the IDisposable interface. All Sqlite objects implement it. With using blocks the Dispose is automatically called. The code above with using blocks looks like this

var value = default(string);
using (var connection = new SQLiteConnection("Data Source=sample.db"))
{
    // don't forget to open the connection once created
    connection.Open();
    // create command
    using (var command = connection.CreateCommand())
    {
        // get the value field from the data table
        command.CommandText = "SELECT [Value] FROM [DATA] WHERE [ID] = 1";
        value = command.ExecuteScalar().ToString();
    } // disposes the command
    connection.Close(); // not needed anymore, but no damage done when calling it
} // disposes the connection

The code with using blocks works great on both dotNET and Mono.

Sample project

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , , , , . Bookmark the permalink.

2 Responses to Mono.Data.Sqlite and filelocks

  1. Pingback: BasicHttpBinding blocking under Mono | Erictummers's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s