The new Portable Class Library for SQLite z

浏览数：34 / 时间：2015年06月12日

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. Let’s see how to do that.

As prerequisite, we need to install the SQLite Extension SDK that corresponds to our platform. For example, for Windows 8.1, it is the SQLite for Windows Runtime (Windows 8.1) v3.8.2 extension SDK. The other versions are 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 not supported, so we need to choose a specific target platform: Visual studio will reference the appropriate extension SDK version the the project compiles.

Finally, let’s use NuGet to install the Portable Class Library for SQLite:

SQLitePCL on NuGet

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

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 an SQLiteConnection object that points to the specified file. If it isn’t rooted, the library assumes that it is located in the ApplicationData.Current.LocalFolder folder (the same assumption applies also for Windows Phone 8).

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.

The following example shows how to insert data:

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

Then, because we want to reuse the same statement to insert another record, we need to call Reset (line 11), that resets the prepared statement back to its initial state, ready to be re-executed, and ClearBindings (line 12), to remove the bindings that have been defined before.

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

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 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<T>(this 
ISQLiteStatement statement, int 
index)
    {
        return 
(T)statement[index];
    }
}

And so in the previous loop we can write:

var id = statement.GetValue<long>(0);
var firstName = statement.GetValue<string>(1);
var lastName = statement.GetValue<string>(2);

As we have 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 are available on CodePlex.

The new Portable Class Library for SQLite z,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

The new Portable Class Library for SQLite z