Portable Class Library for SQLite Windows 8.1

Microsoft Open Technologies has recently released a Portable Class Library for SQLite. Thanks to it, we can use SQLite in the same way in all the supported platforms.

As a prerequiste you need to have SQLite for Windows 8.1 and then add as a reference to your project. Because it is a native library, the “Any CPU” architecture is not supported, so we need to choose a specific target platform: Visual studio will reference the appropriate extension SDK version the the project compiles.

Adding Reference to the Project

Now we use NuGet to install the portable library to our project.

Portable Sqlite Library

Now everything is ready to start using the library. Suppose for example we want to create a database named data.db with a Users table:

using (var connection = new SQLiteConnection("data.db"))
{
    using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS Users (
                                                ID INTEGER NOT NULL PRIMARY KEY,
                                                FirstName NVARCHAR(50),
                                                LastName NVARCHUAR(50));"))
    {
        statement.Step();
    }
}

At this moment, SQLite PCL supports only direct SQL commands (no LINQ provider). At line 3, we use the connection.Prepare method to define the DDL query we want to execute. Then, on line 8, with statement.Step, we send the query to the database engine, that immediately executes it.
Now lets insert some data into the table.

using (var statement = connection.Prepare(@"INSERT INTO Users (FirstName, LastName)
                                            VALUES(@firstName, @lastName);"))
{
    statement.Bind("@firstName", "Rahber");
    statement.Bind("@lastName", "Ashraf");

    // Inserts data.
    statement.Step();

    // Resets the statement, to that it can be used again (with different parameters).
    statement.Reset();
    statement.ClearBindings();

    statement.Bind("@firstName", "Some User first name");
    statement.Bind("@lastName", "Some user last name");

    // Inserts data.
    statement.Step();
}

Again, the Prepare method is used to define the SQL command. In this case, it is an INSERT in which we have defined two parameters, @firstName and @lastName. At line 4-5, we bind them to their actual values, using the Bind method. The Step command (line 8) finalizes the operation.

And to retrieve the data we use the following code.

using (var statement = connection.Prepare(@"SELECT * FROM Users ORDER BY FirstName;"))
{
    while (statement.Step() == SQLiteResult.ROW)
    {
        var id = (long)statement[0];
        var firstName = (string)statement[1];
        var lastName = (string)statement[2];
    }
}

To read the records returned by the query, we need to iterate through the rows, in a way that resembles the SqlDataReader.Read method.

In order to retrieve the actual values, we need to use the indexer operator on the statement object, specifying the column number. As this method gets a result of Object type, we need to cast it to the real type of the column. If we want to avoid this syntax, and instead prefer to use generics, we can define a simple extension method:

public static class SQLitePCLExtensions
{
    public static T GetValue(this ISQLiteStatement statement, int index)
    {
        return (T)statement[index];
    }
}
And Finally now you can use the library easy 

Advertisements

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