SQLite on 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:

 Collapse | Copy Code

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.

 Collapse | Copy Code

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:

 Collapse | Copy Code

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:

 Collapse | Copy Code

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!

Reference To:

http://www.codeproject.com/Articles/151179/SQLite-on-WP7