Monday, October 27, 2014

“Database First” with SQLite in a Universal App using the SQLite Toolbox and sqlite-net

In this blog post I will describe how to use the reverse engineering feature of the SQLite Toolbox to generate code for use with the sqlite-net codebase in a Universal App. A Universal App is a special solution template, that allows you to shared code and resource assets between a Windows Phone 8.1 and Windows Store 8.1 Application, in order to minimize code duplication and increase code reuse, if your application targets both these platforms.

My fellow MVP Nicolò Carandini has a couple of great blog posts on how to get started with SQLite in a Universal App. Since he posted this, the SQLite extension SDKs have been updated and are now directly available in the Extension Manager in Visual Studio 2013, so go and install these two downloads – go to Tools, Extensions and Updates, select Online and search for “sqlite”:

image

Install these three extensions:
- SQLite for Windows Phone 8.1
- SQLite for Windows Runtime (Windows 8.1)
- SQL Server Compact/SQLite Toolbox

The two other SQLite extensions are for version Windows/Windows Phone 8.0. Notice that in order to use the SQLite Toolbox, currently you must have SQL Server Compact 4.0 or 3.5 installed, this will not be required in the next release. Now restart Visual Studio.

Follow the steps in Nicolo’s blog until you have the two SQLite-net files available in the .Shared “project”:

050814_2231_UniversalAp3[1]

Instead of typing the table classes by hand, the SQLite Toolbox can help you generate the required classes for you based on an existing SQLite database file. You could even then include the SQLite database file with your application, for example if any of the tables contain reference data. I describe how to handle reference data in my previous blog post here.

Notice that the sqlite-net classes are not a full blown OR-M, but rather a simple SQL to class translator. It does not support for example change tracking, relationships (foreign keys), Unit of Work, multi column primary keys etc. Those features will be present and available for Universal apps in the new Entity Framework release coming beginning of 2015, EF7.

Open the SQLite Toolbox from Server Explorer or the Tools menu, and connect to an existing SQLite database. For an overview of all SQLite features in the Toolbox, see my blog post here.

image

Select the shared project in Solution Explorer, then right click the database and select “Add sqlite-net Model.cs to current project”

image

The generated code (Model.cs) contains a SQLiteDb class with a method to create all the tables in the database (if you just want to use the existing database file as a template), and class definitions for each table in the database:

    public partial class Album
{
[PrimaryKey]
[Unique(Name = "IPK_Album", Order = 0)]
public Int64 AlbumId { get; set; }

[MaxLength(160)]
[NotNull]
public String Title { get; set; }

[Indexed(Name = "IFK_AlbumArtistId", Order = 0)]
[NotNull]
public Int64 ArtistId { get; set; }

}

As you can see, attributes like MaxLength, Index and NotNull help define the table.


Hope you find this timesaving feature and the other SQLite features in the Toolbox helpful, and please provide any feedback here.

10 comments:

Unknown said...

Hi Erik:

I'm running Visual Studio 14 CTP4 on Windows 10 and found that to compile Nicolò Carandini's TCPWare.SQLiteTest demo I had to install Visual Studio 2013 to get some of the bits for a Universal app.

It took me a while to figure out what was wrong but eventually I got the info needed from Raman Sharma's blog post

"Building 8.1 apps (Windows Phone and Windows Store) using Visual Studio “14” CTP2"

at

http://blogs.msdn.com/b/vcblog/archive/2014/07/09/building-8-1-apps-windows-phone-and-windows-store-using-visual-studio-14-ctp2.aspx

I have been reading your blog post for several years and although I tried SQL Server Compact, my preference has been for SQLite.

Thank you for sharing all your work. You really have made things so much easier for us with your tools and clear explanations of how they work.

Unknown said...

Hi Erik,

Thanks for the very useful article. Could you please let me know i can do the same in PCL project also.

Thanks,
Kiran

ErikEJ said...

Kiran: Yes, you can

Unknown said...

SQLite or SQL Server compact -- which version to choose?

And if using SQLite, the need to pic x86/x64 or ARM, does this preclude using it as an AnyCPU application? That is, will the app now be pinned to one architecture?

ErikEJ said...

Manni: SQL CE is not available in Universal Apps, and the processor platform is just a build issue, you can target any CPU (the build process will build 3 packages for Windows Store apps, one per processortype)

Unknown said...

Erik,
No SQL CE? What are they thinking? So it's SQLite or nothing then.

And thanks for the pointer about the different CPU platforms. Understood.

CountingRoughDrafts said...

Currently trying to develop an application for the new Universal Windows Platform (one app project) and am wondering: what should I use now in place of the W8.1 and WP8.1 NuGet packages? Is there anything currently available? I have a current SQLite database that I can work from and would like to do so if at all possible. Do you have any pointers or know of any bloggers that have covered this topic?

ErikEJ said...

It is not NuGet packages, it is Extension SDks deleivered as a VSIX - so you can use this: https://visualstudiogallery.msdn.microsoft.com/4913e7d5-96c9-4dde-a1a1-69820d615936

Unknown said...

need SQL Server Compact and SQLite Toolbox for visual studio 2010. please help me..

ErikEJ said...

@Narayanan - pls email me