The new portable Class Library for SQLite Z

Microsoft Open Technologies have recently released a portable Class Library for SQLite. Thanks to it, we can use SQLite in the same-in-the-supported platforms. Let's see how-to-do.

As prerequisite, we need to install the SQLite Extension SDK, which corresponds to our platform. For example, for Windows 8.1, it's the SQLite for Windows Runtime (Windows 8.1) v3.8.2 extension SDK. The other versions is available on the SQLite official download page. Once downloaded, it's necessary to change the extension from ZIP to VSIX, then double click it to install the SDK. Now we can add the extension to the project using the Add Reference command:

SQLite for Windows Runtime

Because It is a native library, the ' Any CPU ' architecture is isn't supported, so we need to choose a specific target PLATFO Rm:visual Studio would reference the appropriate extension SDK version of the project compiles.

Finally, let's use NuGet-to-Install the portable Class Library for SQLite:

SQLITEPCL on NuGet

Now everything are ready to start using the library. Suppose for example we want to create a database named storage.db with a people table:

?

1 2 3 4 5 6 7 8 9 10

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

First of all, we create a sqliteconnection object that points to the specified file. If it isn ' t rooted, the library assumes that it's located in the ApplicationData.Current.LocalFolder folder (the same Umption applies also for Windows Phone 8).

At this moment, the 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, which is immediately executes it.

The following example shows how to insert data:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

using (var statement = connection.Prepare(@"INSERT INTO People (FirstName, LastName)                                             VALUES(@firstName, @lastName);")) {     statement.Bind("@firstName", "Donald");     statement.Bind("@lastName", "Duck");    // Inserts data.     statement.Step();     // Resets the statement, to that it can be used again (with different parameters).     statement.Reset();     statement.ClearBindings();    statement.Bind("@firstName", "Mickey");     statement.Bind("@lastName", "Mouse");    // Inserts data.     statement.Step(); }

Again, the Prepare method is used to define the SQL command. In this case, it's an INSERT in which we have defined, 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.

Then, because we want to reuse the same statement to insert another record, we need to call Reset (line one), that Resets the prepared statement back to their initial state, ready-to-be-re-executed, and clearbindings (line), T o Remove the bindings that has been defined before.

Finally, it's the moment to retrieve the saved data:

?

1 2 3 4 5 6 7 8 9

using (var statement = connection.Prepare(@"SELECT * FROM People 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-a-to-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 t He 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:

?

1 2 3 4 5 6 7

public static class SQLitePCLExtensions {     public static T GetValue<T>(this ISQLiteStatement statement, int index)     {         return (T)statement[index];     } }

And so in the previous loop we can write:

?

1 2 3

var id = statement. getvalue< long > (0); var firstname = statement. getvalue< string > (1); var lastname = statement. getvalue< string > (2);

As we have the seen, this library is very straightforward. Its usage mimics the native C + + library (the Prepare, Step and Reset methods, for example), with a great advantage:we can Code against one single API, regardless of whether we ' re developing Windows Store, Windows Phone or. NET 4.5 projects.

More information about the portable Class Library for SQLite is available on CodePlex.