Click here to Skip to main content
14,870,261 members
Articles / Programming Languages / C#
Technical Blog
Posted 27 Jan 2011

Tagged as

Stats

21.1K views
11 bookmarked

SQLite on WP7

Rate me:
Please Sign up or sign in to vote.
4.92/5 (5 votes)
27 Jan 2011CPOL
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:

C#
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.

C#
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:

C#
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:

C#
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

rudigrobler
South Africa South Africa
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Alaa Abdallat31-Jan-12 2:41
MemberAlaa Abdallat31-Jan-12 2:41 
GeneralTransaction Support Builtin / Transaction Perf Pin
torial28-Jan-11 6:38
Membertorial28-Jan-11 6:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.