Output SQLite queries when using SQLite-net in Universal apps

Output SQLite queries when using SQLite-net in Universal apps

When debugging code which works with databases, you'll often want to see the queries which are being executed to find possible bugs or simply understand how the database wrapper works. There's a simple way to do it if you use SQLite-net wrapper, but as it's not documented, you have to guess or read the source code to find it.

Going through source code is usually not a big deal (open source ❤), but I hope this will make it easier for people who don't have the time to do it.

There are currently two dominant libraries you can use to work with SQLite in Universal Apps - SQLite-net (https://github.com/praeclarum/sqlite-net) and SQLite.net PCL (https://github.com/oysteinkrog/SQLite.Net-PCL). There are hundreds of forks on GitHub, but I don't want to go into the whole mess around SQLite libraries at the moment (preparing another blog post on that matter - it's interesting!). And there are async wrappers which come as separate Nuget packages.

Let's assume we have a simple User class defined.

public class User  
{
    [PrimaryKey, AutoIncrement]
    public int UserId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }
}

In this very simple example we'll be inserting a new user into a new table called User.

SQLite-net

So, in case you're using SQLite-net, you need to set the Trace bool to true.

string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Users.db");

using (var connection = new SQLiteConnection(path) { Trace = true })  
{
    connection.CreateTable<User>();
    connection.Insert(new User { FirstName = "Scott", LastName = "Hanselman" });
    var users = connection.Table<User>().ToList();
}

When set like this, you'll notice the info about queries in the Output window.

Executing: create table if not exists "User"( "UserId" integer primary key autoincrement not null , "FirstName" varchar , "LastName" varchar ) Executing Query: pragma table_info("User")
Executing: insert into "User"("FirstName","LastName") values (?,?)
Executing Query: select * from "User"

SQLite.net PCL

In case you're usinge SQLite.net PCL, you need to provide an ITraceListener implementation, such as this one

public class DebugTraceListener : ITraceListener  
{
    public void Receive(string message)
    {
        Debug.WriteLine(message);
    }
}

to the SQLiteConnection property called TraceListener.

string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Users.db");

using (var connection = new SQLiteConnection(new SQLitePlatformWinRT(), path))  
{
    connection.TraceListener = new DebugTraceListener();

    connection.CreateTable<User>();
    connection.Insert(new User { FirstName = "Scott", LastName = "Hanselman" });
    var users = connection.Table<User>().ToList();
}

This will then output the following:

Executing: create table if not exists "User"( "UserId" integer primary key autoincrement not null , "FirstName" varchar , "LastName" varchar ) Executing Query: pragma table_info("User")
Executing: insert into "User"("FirstName","LastName") values (?,?)
Executing Query: select * from "User"

SQLite.net PCL Async

In case you're using the async version of SQLite-net PCL, the setup is a bit different but the same ITraceListener implementation is used:

string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Users.db");

using (var connection = new SQLiteConnectionWithLock(new SQLitePlatformWinRT(), new SQLiteConnectionString(path, false)))  
{
    connection.TraceListener = new DebugTraceListener();
    var asyncConnection = new SQLiteAsyncConnection(() => { return connection; });

    await asyncConnection.CreateTableAsync<User>();
    await asyncConnection.InsertAsync(new User { FirstName = "Scott", LastName = "Hanselman" });
    var users = await asyncConnection.Table<User>().ToListAsync();
}

Outputs the same result

Executing: create table if not exists "User"( "UserId" integer primary key autoincrement not null , "FirstName" varchar , "LastName" varchar ) Executing Query: pragma table_info("User")
Executing: insert into "User"("FirstName","LastName") values (?,?)
Executing Query: select * from "User"

In all three cases, the table is properly created and a new user is inserted.

SQLite table created

SQLite added user is in the database

Hint - if you want to view your SQLite schema and data from inside Visual Studio 2013, use this great toolbox by @ErikEJ - SQL Server Compact/SQLite Toolbox Works with SQL CE, too!

I hope this helps. Which of these libraries/wrappers do you use in your apps? Is there a better one you know of? Let me know in the comments.

Igor Ralic

igor ralic

View Comments
Microsoft Certified Solutions Developer: Windows Store Apps in C#