SQLite on WP7

After finishing my previous article on Sterling (the object-oriented database for WP7), I found a very interesting project on CodePlex…

SQLite for WP7

“C# Sqlite Port for Windows phone 7 and possibly Silverlight 3, 4. The core engine was slightly modified to be used with IsolatedStorage and SqliteClient were ported by using missing codes from Mono project in order to maximize usability and portability from desktop.”

Although sterling works great, it is essentially an object-based database (NoSql-like)… If you truly need a relational database or if your application currently uses SQLite and you want to port it to Windows Phone 7, this might just be the answer!

Let’s get started… As with traditional ADO.NET, we first need to create a connection:

using (SqliteConnection conn = 
	new SqliteConnection("Version=3,uri=file:Super14Database.db"))
{
    conn.Open();
    // Use the connection here...
}

And now we can start using the database with “normal” SQL statements… Let’s create a table.

using (SqliteCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "CREATE TABLE matches ( [id] INTEGER PRIMARY KEY, _
	[team1] TEXT, [score1] INTEGER, [team2] TEXT, [score2] TEXT, [date] TEXT)";
    cmd.ExecuteNonQuery();
}

And to insert data:

cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "INSERT INTO matches(score1, team1, 
	score2, team2, date) VALUES(@score1, @team1, @score2, @team2, @date);";

cmd.Parameters.Add("@score1",  null);
cmd.Parameters.Add("@team1", null);
cmd.Parameters.Add("@score2",  null);
cmd.Parameters.Add("@team2",  null);
cmd.Parameters.Add("@date",  null);

cmd.Parameters["@score1"].Value = 20;
cmd.Parameters["@team1"].Value =  "Blues";
cmd.Parameters["@score2"].Value =  34;
cmd.Parameters["@team2"].Value =  "Hurricanes";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();


cmd.Parameters["@score1"].Value =  15;
cmd.Parameters["@team1"].Value =  "W Force";
cmd.Parameters["@score2"].Value =  24;
cmd.Parameters["@team2"].Value =  "Brumbies";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();

cmd.Parameters["@score1"].Value =  34;
cmd.Parameters["@team1"].Value =  "Cheetahs";
cmd.Parameters["@score2"].Value =  51;
cmd.Parameters["@team2"].Value =  "Bulls";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();

cmd.Parameters["@score1"].Value =  32;
cmd.Parameters["@team1"].Value =  "Crusaders";
cmd.Parameters["@score2"].Value =  17;
cmd.Parameters["@team2"].Value =  "highlanders";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();

cmd.Parameters["@score1"].Value =  28;
cmd.Parameters["@team1"].Value =  "Reds";
cmd.Parameters["@score2"].Value =  30;
cmd.Parameters["@team2"].Value =  "Waratahs";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();

cmd.Parameters["@score1"].Value =  13;
cmd.Parameters["@team1"].Value =  "Lions";
cmd.Parameters["@score1"].Value =  26;
cmd.Parameters["@team2"].Value =  "Stomers";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();

cmd.Parameters["@score1"].Value =  18;
cmd.Parameters["@team1"].Value =  "Sharks";
cmd.Parameters["@score2"].Value =  19;
cmd.Parameters["@team2"].Value =  "Chiefs";
cmd.Parameters["@date"].Value =  "12/02/10";
cmd.ExecuteNonQuery();
                    
cmd.Transaction.Commit();

NOTE: Notice the transaction support build in!

To fetch data from the database:

cmd.CommandText = "SELECT * FROM matches";
using (SqliteDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        var team1 = reader.GetValue(1);
        var score1 = reader.GetValue(2);
        var team2 = reader.GetValue(3);
        var score2 = reader.GetValue(4);
        var date = reader.GetValue(5);
    }
}

And that’s it! It is more verbose but it does work great and the translation from a normal desktop application that used SQLite should be simple!

In the next article, I will compare performance between the different data access technologies!

CodeProject