In part 2 of this series we’re going to look at the second option that I presented on the use of sqlite which was to use the sqlite-net wrapper.
Just to recap, here is my list of goals:
– Blendable: My project has to be designable in Blend at all times!
– Sqlite: I want to use sqlite to store relational data across both Win8 and WP applications
– PCL: I want to be able to do all my data access from a portable class library
– Objects Not SQL: I want to be able to read and write objects, rather than write sql statements
For a more detailed understanding of these goals, check out Part 1.
Here’s a brief getting started walk through for sqlite-net .
– You’ll need to start by installing the SQLite for Windows and SQLite for Windows Phone extensions. If you’re only interested in supporting one of the platforms then you just need to install the extensions for that platform.
– You can either use these links to the extensions, or you can search the Visual Studio gallery from within Visual Studio. Go to Tools –> Extensions and Updates; search Online for SQLite. You’ll need to restart Visual Studio after installing both extensions.
– Create both a Windows Phone and a Windows 8 project within the same solution.
– For Windows Phone you’ll need an additional library that acts as a bridge between the native and managed world. This is the sqlite-net-wp8 library – Just because it’s on git, you don’t need to clone it; simply click the Zip download button to take a copy of the library.
– Unblock and extract the sqlite-net-wp8 library and copy the project into your solution folder.
– Add the sqlite-net-wp8 project into your solution.
– Add a reference from your Windows Phone project to the sqlite-net-wp8 project
– Add a reference to the SQLite for Windows Runtime extension to your Windows 8 project
– Next, you need to add sqlite-net from nuget – install this into both Windows 8 and Windows Phone projects. You should see it add both Sqlite.cs and SqliteAsync.cs files to these projects. These files project an easy to use, linq style, wrapper around Sqlite (remember our Objects not Sql goal!)
– Open the project properties page for the Windows Phone project by double-clicking on the properties node in Solution Explorer. Add USE_WP8_NATIVE_SQLITE to the Conditional compilation symbols for All Configurations and All Platforms via the Build tab.
– We’re almost there, we just need to write some code. Add the following code to the MainPage.xaml.cs pages (remembering to add the appropriate using statements).
public static string DB_PATH = Path.Combine(
Path.Combine(ApplicationData.Current.LocalFolder.Path,
"sample.sqlite"));
protected override void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
using (var con = new SQLiteConnection(DB_PATH))
{
con.BeginTransaction();
con.CreateTable<Person>();
con.Insert(new Person { Name = "Nick", Age = 15 });
var people = con.Table<Person>().ToArray();
Debug.WriteLine(people.Length);
con.Commit();
}
}
public class Person
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
– At this point we have all the bits we need but you’ll probably find when you attempt to run either application you’ll run into build errors to do with the supported architectures. You’ll need to alter the build configuration (right-click on the solution in Solution Explorer and select Configuration Manager). Remember you need x86 for the emulator and ARM for debugging on a real device (and the store).
With the correct configuration set, you should be all good to go to continue using sqlite
So, how did we go with our goals
– Blendable: Tick! We haven’t done anything to break this requirement
– Sqlite: Tick! Again, we’re using Sqlite
– PCL: Fail – Again, we don’t have a PCL – we’ll need another wrapper to make this happen
– Objects Not SQL: Tick! Woot! we’re stepping in the right direction now with working with objects
Stay tuned for the next instalment when we look at fixing the PCL issue.