×

First time here?

You are looking at the most recent posts. You may also want to check out older archives. Please leave a comment, ask a question and consider subscribing to the latest posts via RSS or email. Thank you for visiting!

At the “Developing Windows 8 Metro style apps with C++” event that happened on 18-May-2012, we saw and heard some very interesting things.  If you were watching live then hopefully you didn’t see how I tried to work through my presentation while my disk was suspiciously guzzling every last byte until it eventually ran out of space!  But I digress…

During the keynote presentation by Herb Sutter, we brought up several customers that are well-known in the native code world to talk about their experiences with Metro style apps and C++/Cx.  In particular hopefully this one caught your eye:

SQLite case study slide

That’s right, the team for SQLite was there to discuss how they were able to take their existing Win32 codebase and ensure that it worked well on Windows 8 as well as for Metro style apps.

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen().– Source: http://www.sqlite.org/about.html

Dr. Richard Hipp, the founder of SQLite, was on hand to announce the availability of the experimental branch they’ve been working on as well as that when the Release Preview of Windows 8 is made public that he will merge this code to the main trunk for SQLite, making it supported by them.  This is a really great thing for developers as SQLite has been a proven embedded database for numerous platforms and many, many customers.  The team prides themselves on testing and has millions of test cases that are validated each release.

As a Windows (and perhaps more specifically .NET) developer, you may not have had to build any lib from Open Source before of this type (i.e., native code) and since a binary is not being provided yet until Release Preview for Windows 8, I thought I’d share my tips on building the experimental bits, adding them to your projects and then using them with a client library.

UPDATE: I created a HOWTO video demonstrating the following steps of building and using from a C# Metro style app.

Building SQLite from source

If you are looking for the sqlite3.dll with this WinRT support anywhere on the sqlite.org site, you won’t find it.  You will have to build the source yourself. UPDATE:Since the origination of this post the SQLite team has released a version already compiled for 32/64-bit.  I highly recommend you get the code from the source rather than from any third party site.  Microsoft has worked with the team at SQLite to ensure compatibility and store certification.  For most .NET developers who have never grabbed native code source from an Open Source project and had to build it before, the maze of knowing what you should do can be confusing.  I’ve put together a cheat sheet on building SQLite from source for a Windows (and .NET developer) and put it on my SkyDrive: Building SQLite from source.  The OneNote I have has the details you need for the tools that will be required. 

In a nutshell you’ll need:

  • Visual Studio (easiest way to get the C++ compiler)
  • ActiveTcl
  • Update for gawk.exe
  • Fossil (the source control system used by SQLite)

Once you have these, you are ready to build SQLite.  Again, I’ll defer to my instructions on the details of setup.  Once your setup is complete, from a developer command prompt you’d run:

   1: nmake -f Makefile.msc sqlite3.dll FOR_WINRT=1

The result of this will give you basically 4 files that are of likely most importance to you: sqlite3.c, sqlite3.h, sqlite3.dll, sqlite3.pdb.

NOTE: The resulting pdb/dll that is built will be architecture specific.  If you used an x86 command prompt then that is what you have.  Be aware of this (as noted later in this post).

At a minimum you’ll want sqlite3.dll if you are a .NET developer, but as a native code developer you will likely be more interested in the others as well.  After this step, you now have a Windows Store compliant version of SQLite to include in your applications.

Runtime versus client access

Now at this point is where I’ve seen some confusion.  Folks are asking How do I include this, don’t I need a WinMD file to reference?  Let me diverge a bit and explain a few things.

The result of compiling the binary above produces primarily one thing…which I will call the “Engine” in this post.  This is the SQLite runtime and logic required to create/interact with SQLite database files.  This is NOT, however, an access library, which I will call the “Client” in this post.  If you are a managed code or JavaScript developer, at this point, all you have is the Engine, the database runtime.  You have no Client to access it.

Now, if you are a C++ developer you are probably okay at this point and don’t care much about what I have to say.  You have the header and are likely saying I’ve got the header, get out of my way.  And that is okay.  For C++ developers I think you’ll likely be accessing the database more directly through the SQLite APIs provided in the header.

I call out this distinction because this step provides you with the database engine you need to create a database and have it be store-compliant.  So if you are a JavaScript or .NET developer, what are you to do?  Stay tuned…let’s first get the Engine included in our app package.

Including SQLite in your app package

As I noted above, as a native code developer, having the header, lib and c file you may be okay and don’t care to read this.  I  personally think, however that I’d always just want the binary from my vendor rather than always include source in my files.  That said, the SQLite build process does product the amalgamation (sqlite3.c) you can just include in your native code app.

If you choose to go the binary file route (sqlite3.dll) then you need to simply follow a few principles to ensure that it is included in your package when you build your app/package.  These principles are simple:

  • include the architecture-specific binary
  • ensure the sqlite3.dll is marked as Content in your project
  • ensure you note that you now have a native code dependency (not needed if you are already a C++ Metro style app)

These two items will ensure that when you build (even for debug via F5) or when you package for the store, that the Engine is included in your package.  Marking as content is simply ensuring that after you add the file to your project, ensure the file properties note that it is content.  In .NET apps this is making the Build Action property Content.  In JavaScript applications ensure the Package Action is marked Content.

Declaring the native code dependency means you simply add a reference to the Microsoft C++ Runtime Library via the Add Reference mechanisms in .NET and JavaScript applications.  By doing this (and again, this is a requirement of including SQLite in your app) you now cannot be architecture-neutral. This means no more AnyCPU for .NET.  When producing a package you’ll have to produce architecture-specific packages before uploading to the store.  Not to worry though as Visual Studio makes this incredibly easy.  The one thing you’ll have to remember though is that you’ll have to change the sqlite3.dll before building the packages as the DLL is architecture-specific itself.

Now this all should be easier right?  Wouldn’t it be nice if you could just Add Reference to the Engine?  I personally think so.  I’ll be working with the SQLite team to see if they will adopt this method to make it as easy as this:

SQLite Extension SDK

In doing so, you as a developer would just add a reference to the Engine and then during build time Visual Studio (well MSBuild actually) will do all the right things in picking up the architecture-specific binary for your app.  No fiddling on your part.  This method also makes it easier for C++ developers as well as a props file would automatically be merged to include the .lib for linking and the header file for development.  This method uses the Visual Studio Extension SDK mechanism that was introduced in Visual Studio 11.

NOTE: Again note that as a managed (.NET) app I’d also have to ensure that my package includes the Microsoft C++ Runtime package in order for this to work and pass store certification.

Native code developers may scoff at this approach, but I could get started in 2 steps: Add Reference, #include.  No tweaking of my project files at all because the Extension SDK mechanism in VS does all this for me behind the scenes.

So why don’t I just give you the VSIX to install and accomplish the above?  Well simply, because SQLite is not my product and we’ve had a good relationship with their team and I want to make sure they understand the benefits of this method before jumping right in.  I hope that they will like it as I think it makes it *really* simple for developers.

Accessing the Engine from your app

Great, you’ve compiled the bits, you’ve understood how to ensure sqlite3.dll gets packaged in your application…now how do you use it!!!  Here’s the assessment of where we are at for Metro style apps as of the writing of this post.

C++ app developers: I think most C++ developers will get the header file (sqlite3.h) and be okay on their own with SQLite.  At this stage for them there doesn’t appear to be a real huge benefit of a WinRT wrapper to use the Engine.

.NET developers: I’ve messed around with a few libraries and believe the sqlite-net project to be the most favorable for what I believe most use cases will be for SQLite and Metro style apps.  This is a .NET-only library (not WinRT) but is basically a “LINQ to SQLite” approach.  The Mono team uses this one as well.  The necessary .NET 4.5 Core changes are already included in the project on github.  So you just need to get the SQLite.cs file and include it in your project.  Using this library allows you to write code like this:

   1: public sealed partial class BlankPage : Page
   2: {
   3:     public BlankPage()
   4:     {
   5:         this.InitializeComponent();
   6:  
   7:         string dbRootPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
   8:         using (SQLiteConnection db = new SQLiteConnection(Path.Combine(dbRootPath, "mypeople.sqlite")))
   9:         {
  10:             db.CreateTable<Person>();
  11:  
  12:             db.RunInTransaction(() =>
  13:                 {
  14:                     for (int i = 0; i < 10; i++)
  15:                     {
  16:                         db.Insert(new Person() { FullName = "Person " + i.ToString() });
  17:                     }
  18:                 });
  19:         }
  20:     }
  21: }
  22:  
  23: public class Person
  24: {
  25:     [AutoIncrement, PrimaryKey]
  26:     public int ID { get; set; }
  27:     public string FullName { get; set; }
  28:     public string EmailAddress { get; set; }
  29:     public double Salary { get; set; }
  30: }

This is clearly just a sample, but demonstrates the simplicity of the library. 

NOTE: In the snippet above you do want to make sure you are creating your database in a path that is accessible from the AppContainer.  The best place is in the app’s ApplicationData location.  When specifying a path to SQLite in Open() for creation, give an explicit path always to ensure you aren’t relying on a temp file creation.

Some may ask about System.Data.Sqlite and this cannot be used because of the dependency of ADO.NET which is not a part of the .NET Framework Core profile.

Now this leads us to JavaScript developers.  Currently, there is not easy way for you to access this.  The Developer and Platform Evangelism team are working on some samples that are not quite complete yet.  JavaScript developers will need a WinRT library in order to access/create/query the Engine.  There are some out there (I haven’t played around with any of these) that would be good to see if they meet your needs.  Here are some pointers:

At the C++ event we talked with the SQLite team about a WinRT client library and will continue to talk with them to see if this is something of interest.  SQLite has a great history of working with the community and have a desire to continue this.  In the meantime, there are options for you to get started.  Also note, that since these are WinRT libraries they could also be used from C++ and .NET in Metro style apps.  At this point though it is my personal opinion that existing .NET libraries for .NET offer more value (i.e. LINQ) than how these WinRT ones exist.

Summary

This was a great announcement that the SQLite team made for Metro style app developers.  WinRT provides some existing local storage mechanisms which you should explore as well, however none that have structured storage with a query processor on top of it.  I’m really glad that the SQLite team was able to make a few diff’s to their code to accommodate a few store compliance areas and continue to offer their great product to this new class of applications.  It is very simple to get started by ensuring you have the Engine and picking your Client of your choice and write your app using SQLite for some local/structured storage!

Hope this helps and stay tuned for the release preview of Windows 8!


This work is licensed under a Creative Commons Attribution By license.


5/20/2012 5:19 PM | # re: Using SQLite in a Metro style app
Oh man this is some hot shit in here!
Is there also this kinda thingy for .NET (non-metro)?
5/20/2012 5:43 PM | # re: Using SQLite in a Metro style app
@Shimmy - you can already use SQLite in your non-metro applications no problem...using the same sqlite-net library in fact (and the same method of inclusion). In this case you can grab the windows binary that is the main download on their site...no special compile option needed.
5/20/2012 10:54 PM | # re: Using SQLite in a Metro style app
Hi Tim,

Great write up, was looking for this for my http://metrorssreader.codeplex.com

One question, do ARM/Windows RT devices also include the C++ runtime for this to run?
5/21/2012 1:46 PM | # re: Using SQLite in a Metro style app
Youve previously wrote on a forum that using SQL Lite would mean your app wouldnt pass the store validation - "the SQLWinRT project on codeplex is a wrapper to communicate with the classic SQLite engine...which uses APIs that would not pass store validation currently."
social.msdn.microsoft.com/...

Is this still the case?
5/21/2012 4:00 PM | # re: Using SQLite in a Metro style app
@KrisMac - compiling the SQLite binary in the experimental branch as prescribed (FOR_WINRT=1) will succeed in passing the store certification.
5/22/2012 9:37 AM | # re: Using SQLite in a Metro style app
Will this work on ARM devices?
5/22/2012 9:41 AM | # re: Using SQLite in a Metro style app
Will this work on ARM based devices?
5/22/2012 12:29 PM | # re: Using SQLite in a Metro style app
It looks the API is synchronous, but I thought that isolated storage access was asynchronous only in WinRT. So we can count on synchronous access to data in this case?
Thanks.
5/22/2012 1:07 PM | # re: Using SQLite in a Metro style app
@Sergey - no it isn't async by default as you note. You could wrap your calls in a Task if you wanted to get asnyc behavior, but in the end these are sync calls.
5/23/2012 2:54 AM | # re: Using SQLite in a Metro style app
Hi Tim,

One other person besides myself has asked whether this will run on ARM.

Do you have an answer for this fairly important question?

G
5/23/2012 7:13 PM | # re: Using SQLite in a Metro style app
I followed the steps, but as soon as line of code is hit that created new SQLite connection, the app just sits there, the next line is not hit, but no errors are thrown. Is there a way to debug this situation?

Thanks.
5/23/2012 7:21 PM | # re: Using SQLite in a Metro style app
I take it back, I did find an error - An attempt was made to load a program with an incorrect format. I used VS Native X64 Command prompt to build sqlite3.dll. Any suggestions are appreciated.
Thanks
5/23/2012 7:24 PM | # re: Using SQLite in a Metro style app
Got it to work finally. Had to change target for the app to x64 from any CPU, then ran without errors. I will write up a blog post with all detailed steps later this week.
Thanks for your help, Tim
5/23/2012 8:22 PM | # re: Using SQLite in a Metro style app
@Sergey - didn't you see my note above? :-) "Declaring the native code dependency means you simply add a reference to the Microsoft C++ Runtime Library via the Add Reference mechanisms in .NET and JavaScript applications. By doing this (and again, this is a requirement of including SQLite in your app) you now cannot be architecture-neutral. This means no more AnyCPU for .NET. When producing a package you’ll have to produce architecture-specific packages before uploading to the store. "
5/24/2012 1:48 AM | # re: Using SQLite in a Metro style app
And again Tim selectively answers questions.

I noticed that arm based questions on his twitter feed are also being ignored.

How about one of the following answers to the ARM question 'Will this work on ARM':
a) I don't know
b) yes
c) I'm not allowed to say - sorry
d) I don't care for your attitude young man and shall ignore all requests for information.
5/24/2012 9:30 AM | # re: Using SQLite in a Metro style app
@Gary - lol, (d)!!! Kidding of course. Right now no ARM hardware is available to the masses, so it wouldn't be prudent to say something is 'certified' on things themselves aren't available to us mortals. In talking with the SQLite guys they already run on ARM (i.e., Android) and are confident but also will hold any official support statements until they can run their tests on Win8 devices.
5/25/2012 10:42 PM | # re: Using SQLite in a Metro style app

I've tried to create/open SQLlite file in native code with C/C++ using "sqlite3_open()" command. But it doesn't success. What is the correct way to do that?

Thanks,
Poom
5/26/2012 2:38 PM | # re: Using SQLite in a Metro style app
@Poom - are you passing in the full *applicationdata* path? if you just specify "foo.db" then it won't work because it can't create the file in the right place.
5/26/2012 3:52 PM | # re: Using SQLite in a Metro style app
Thanks for your reply. I think I passed the full path. Here is my code:

int result = sqlite3_initialize();
if(result != SQLITE_OK)
{
myText->Text = "Init failed";
return;
}

sqlite3* db = NULL;
result = sqlite3_open("d:\\poom.db", &db);
if(result != SQLITE_OK)
{
myText->Text = "Open failed";
return;
}

myText->Text = "Open OK";


Thanks,
Poom
5/26/2012 7:51 PM | # re: Using SQLite in a Metro style app
Thanks Tim. I works now.
5/28/2012 7:06 AM | # re: Using SQLite in a Metro style app
i am creating a windows 8 metro app using HTML5/CSS3. how i can use the SQLite for the same. please help.
5/28/2012 11:19 AM | # re: Using SQLite in a Metro style app
Hi Tim:

I'm so frustrated. I have followed the instructions on building the source code which worked flawlessly. I built them using the Native x64 command prompt.

I then added the dll to my project along with the sqlLite.cs files. The dll is marked as content, but the statement "•ensure you note that you now have a native code dependency (not needed if you are already a C++ Metro style app) " is causing me problems. How do I note the code dependency?

I have 5 projects in the app. When I open the configuration manager, I can switch all the projects to x64 except for the main project which remains at any cPU. I get build errors. I changed the target on the main project's properties to be x64, same build errors about a mismatch in targets. I may be stupid, but I can't figure this out.

Any way when running my project under the default, it can't find the SQLite3.dll.

You're a very smart man, but I think you are taking a lot for granted. I need more information on what to do.

Thanks for your help.
5/28/2012 11:41 AM | # re: Using SQLite in a Metro style app
You absolutely cannot have AnyCPU as a configureation option anymore when including SQLite. It simply isn't an option. Future builds of VS will prevent you from doing this with the deployment model of SQLite that I'm proposing with them.

To add the CRT (C++ Runtime) dependency, choose Add Reference, then expand the Windows...Extensions node. You should see the "Microsoft ++ Runtime Library" option.
5/28/2012 12:25 PM | # re: Using SQLite in a Metro style app
Thanks Tim,

When I go the add the dependency, it adds the reference, but the build errors and says that the runtime file doesn't exist. Where do I go to get this?

5/28/2012 12:35 PM | # re: Using SQLite in a Metro style app
Okay, the runtime problem is solved. The target configuration on each project is x64. It still can't find the sqlite3.dll. If I add the path to the dll in the dllimport command, then I get an "access denied" msg. If I leave out the path, it can't find it.

Thanks again in advance.
5/28/2012 8:23 PM | # re: Using SQLite in a Metro style app
please reply as how can i use SQLite in Metro Apps built using javascript. TIA...
5/28/2012 11:08 PM | # re: Using SQLite in a Metro style app
@win8developer - see my comments right above the summary on this matter.
5/28/2012 11:43 PM | # re: Using SQLite in a Metro style app
ok. thanks.
5/30/2012 7:03 AM | # re: Using SQLite in a Metro style app
Thanks for all your help, Tim. I finally got it working.
5/31/2012 1:58 PM | # re: Using SQLite in a Metro style app
Hi Tim,

great article, I'm using this in conjunction with sqlite-net but when sqlite3_open is called I get a 'can't find entry point' exception. Having tried with everything compiled to x86 and then x64 I'm starting to think the versions of sqlite-net and sqlite I have are incompatible but its hard to determine why. Any other ideas would be most welcome.
5/31/2012 3:19 PM | # re: Using SQLite in a Metro style app
@JohnDB - are you sure that the sqlite3.dll is being included in your package?
6/1/2012 6:07 AM | # re: Using SQLite in a Metro style app
@Tim - yeah pretty sure. I'm actually trying to use it in WinRT based unit tests, and the sqlite3.dll was definitely being deployed. I got a different error message (i.e. can't find dll) when I renamed it to test that scenario.
6/1/2012 6:40 AM | # re: Using SQLite in a Metro style app
@Tim - I have it working now - I just grabbed the update from https://github.com/praeclarum/sqlite-net and have the WinRT unit tests running :-)
6/1/2012 5:07 PM | # re: Using SQLite in a Metro style app
@JohnDB - so you are good now?
6/3/2012 3:09 PM | # re: Using SQLite in a Metro style app
I don't understand why MS are advocating the use of SQLite whilst disallowing SQL Server and Access within Metro. Basically that says to me MS don't want devs to use MS's client technology on the tablet. I work in the countryside and a standalone database is prerequisite. If I have to go as far as using SQLite, believe me I will not waste time figuring out Metro.
6/3/2012 3:26 PM | # re: Using SQLite in a Metro style app
@Rod - Access requires an Office engine and SQL Server isn't designed for embedded database (the current SQL Local DB is closest, but still requires SQL Server core). The Metro style SDK surface area currently doesn't allow for those products to work as-is without modification from those engines and it would be their responsibility to see if it could work in the Metro SDK. Both of those usually are accessed via ADO.NET and that just isn't available in Metro style apps. SQLite is a fine (and proven) technology for an embedded database. I don't know why you wouldn't consider it. Additionally, folks have build more modern client access around it (i.e., LINQ) to make it similar to something like LINQ2SQL.
6/3/2012 5:26 PM | # re: Using SQLite in a Metro style app
Tim many thanks for getting back to me. I was thinking a few years ago one just needed a handful of Jet files so that ought to be do-able on a modern tablet. What is happening about SQL Server Compact? If one had a 'sometimes connected' Metro tablet, what single database and data access technology would make sense in for either mode? I would live in fear and dread that MS would just kill off SQLite within Metro. Sorry to ask so many Q's and for sounding negative, I am struggling for direction probably because I don't know how all the pieces are meant to fit together. Maybe Metro is designed to always be connected?
6/5/2012 7:03 AM | # re: Using SQLite in a Metro style app
c:\sqlite>nmake -f makefile.msc sqlite3.dll FOR_WINRT=1

Microsoft (R) Program Maintenance Utility Version 11.00.50214.1
Copyright (C) Microsoft Corporation. All rights reserved.

cl.exe -W3 -DSQLITE_OS_WIN=1 -I. -I.\src -fp:precise -DSQLITE_OS_WINRT=1
-MD -DNDEBUG -D_CRT_SECURE_NO_DEPRECATE -D_CRT_SECURE_NO_WARNINGS -DSQLITE_THRE
ADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1 -DSQLITE_ENABLE
_FTS3=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_MAX_T
RIGGER_DEPTH=100 -O2 -Zi -Fosqlite3.lo -c sqlite3.c
Microsoft (R) C/C++ Optimizing Compiler Version 17.00.50214.1 for x86
Copyright (C) Microsoft Corporation. All rights reserved.

sqlite3.c
sqlite3.c(32948) : error C2065: 'MapViewOfFileFromApp' : undeclared identifier
sqlite3.c(32948) : error C2099: initializer is not a constant
sqlite3.c(33012) : error C2065: 'CreateFileMappingFromApp' : undeclared identifi
er
sqlite3.c(33012) : error C2099: initializer is not a constant
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual Studio 11.0
\VC\BIN\cl.exe"' : return code '0x2'
Stop.
6/5/2012 4:39 PM | # Error when adding a reference to sqllite3 in C#
Hi Tim,
Thanks 3 this nice post , I get an error message when I'm adding a new reference to sqllite3.dll and the sqlite-net for Windows 8 isn't completed yet as mentioned in the web site
This is the message I get when adding the library : a reference to"library.dll" could not be added. please make sure that the file is accessible and that it is a valid assembly or com file
Is there is something that you advise me to do ??

Regards
Ibraheem Osama Mohamed
6/5/2012 5:04 PM | # re: Using SQLite in a Metro style app
@Ibraheem - you don't add a reference to the sqlite3.dll, you add it to your project and mark it as content.
6/6/2012 8:48 AM | # re: Using SQLite in a Metro style app
@Rod - I'm not on the SQL team so don't want to speak to their plans on SQL Compact. They've seemingly moved to SQL Server LocalDB as their embedded option and right now that is not built for the Metro style SDK for Windows (i.e., wouldn't pass certification). If you need a reliable, embedded, relational database with a query processor on it for your Metro style app today, SQLite is your choice.
6/6/2012 9:40 AM | # re: Using SQLite in a Metro style app
@Tim I did add the class as content but there is an error in the class (There is no method called GetRuntimeProperties())
var props = from p in MappedType.GetRuntimeProperties()

and when I entered the web site for the sqlite-net I found that the sqlite-net for Windows 8 haven't completed yet
is there is an other solutions ??
Thanks Tim I really appreciate your efforts :)

6/6/2012 9:51 AM | # re: Using SQLite in a Metro style app
@Ibraheem - it is completed, I just used it yesterday in a video demonstrating it (timheuer.com/...) . Where are you getting it from?
6/6/2012 9:55 AM | # re: Using SQLite in a Metro style app
The Videos is removed by the user this message appear when I play the Video :)
I get the cs class from the package manager console using Nuget
6/6/2012 11:22 AM | # re: Using SQLite in a Metro style app
@Ibraheem - not sure what happened -- fixed the video.
Gravatar
6/11/2012 9:13 PM | # re: Using SQLite in a Metro style app
@Tim, I have an existing database that I want to include in my project and basically use it for reading data off it. I have tried adding the mydb.db file to the project, setting it as a "content" resource and accessing it. I am trying to access one of the table as follows:
using (SQLiteConnection db = new SQLiteConnection("mydb.db"))
{
listCats.ItemsSource = db.Table<Category>();
}

I have a table called "Category" in the database file; but for some reason I get an error "No such table". I know that the table exists - I have checked to verify this.
Any pointers as to what could be wrong here?

Thanks
6/12/2012 5:39 AM | # re: Using SQLite in a Metro style app
@DK - your path is wrong. Since you are deploying with your package, it should probably be passed in to the SQLIteConnection as: Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, "mydb.db");
6/14/2012 10:58 AM | # re: Using SQLite in a Metro style app
Since the app using sqlite will be platform dependent, developer will have to build 3 versions
1. x86
2. x64
3. ARM

I have some question for app store. Not sure if this forum is the correct place?
1. Does app store support uploading of platform specific packages? (Like x86 package, x64 package and ARM package)
2. When user downloads the app, will app store pick the correct app package for users computer?

Thanks,
Vinod

6/14/2012 1:06 PM | # re: Using SQLite in a Metro style app
@Vinod - yes you'd create 3 packages and VS helps you do this. Additionally the store will allow you to upload multiple packages. The user does not have to decide which one to install..the store app is smart enough to deliver the correct package for the machine being installed.
Gravatar
6/21/2012 11:16 AM | # re: Using SQLite in a Metro style app
Tim,
When I create the app package and then run the Windows APp Certification Test on the package, it fails certification tests. Its specifically failing on:
1. Windows security features test
Binary analyzer
Error Found: The binary analyzer test detected the following errors:◦File C:\Program Files\WindowsApps\57875b45-8733-4026-af87-aea5204a9ec9_1.0.0.2_x64__ej0brnsvte4bw\sqlite3.dll has failed the AppContainerCheck check.
* File C:\Program Files\WindowsApps\57875b45-8733-4026-af87-aea5204a9ec9_1.0.0.2_x64__ej0brnsvte4bw\sqlite3.dll has failed the NXCheck check.
* File C:\Program Files\WindowsApps\57875b45-8733-4026-af87-aea5204a9ec9_1.0.0.2_x64__ej0brnsvte4bw\sqlite3.dll has failed the DBCheck check.
* File C:\Program Files\WindowsApps\57875b45-8733-4026-af87-aea5204a9ec9_1.0.0.2_x64__ej0brnsvte4bw\sqlite3.dll has failed the SafeSEHCheck check.


2. Supported Metro style API test

Supported APIs
Error Found: The supported APIs test detected the following errors:◦API AreFileApisANSI in kernel32.dll is not supported for this application type. sqlite3.dll calls this API.
◦API CreateFileA in kernel32.dll is not supported for this application type. sqlite3.dll calls this API.
◦API CreateFileMappingA in kernel32.dll is not supported for this application type. sqlite3.dll calls this API.
◦API CreateFileMappingW in kernel32.dll is not supported for this application type. sqlite3.dll calls this API.
◦API CreateFileW in kernel32.dll is not supported for this application type. sqlite3.dll calls this API.
◦API CreateMutexW in kernel32.dll is not supported for this application type. sqlite3.dll calls this API.
......................

The failure is taking place inside the sqlite3.dll. It fails no matter what platform I create the package for. Any idea how I could resolve this?

6/21/2012 3:01 PM | # re: Using SQLite in a Metro style app
@DK - it doesn't seem like you are building the correct code.
6/22/2012 5:56 AM | # re: Using SQLite in a Metro style app
Hi Tim,

you said:
"If you are looking for the sqlite3.dll with this WinRT support anywhere on the sqlite.org site, you won’t find it"

But (may be new):
On the downloadpage of sqlite.org are precompiled binaries for different OS´s

Greetings
Jens
6/22/2012 7:08 AM | # re: Using SQLite in a Metro style app
@Jens - yes since the origination of this post, it has been made available
6/22/2012 7:36 AM | # re: Using SQLite in a Metro style app
As you said precompiled binaries are made available, I request you to post links with details so that we can get rid of this lengthy and complicated process......
6/22/2012 7:37 AM | # re: Using SQLite in a Metro style app
@Manan - I updated that portion of the post to reflect that binaries are now available. Some people still like to have the source built within their own projects.
6/22/2012 11:22 AM | # re: Using SQLite in a Metro style app

Dear Tim,

I followed you walk through to the letter. but while compiling (nmake -f makefile.msc sqlite3.dll FOR_WINRT=1) I get multiple errors and the compilation breaks.

I am using Visual Studio 2008, does that make a difference?

And I am compiling it on a windows 7 x64 PC.

On my other system (Windows 8) I have the express edition of the visual studio 2012. (don't have the command Prompt there)

Can you help me out.

Also, after succeeding in building the DLL can I use it inside the express edition of visual studio. (I'll follow your how-to video).


Eagerly awaiting help.
6/22/2012 9:32 PM | # re: Using SQLite in a Metro style app
@Ankur - yes you should use VS2012 for best results. The command prompt is there you just have to search for it (type "command" from the start screen and you'll see them).
6/22/2012 11:19 PM | # re: Using SQLite in a Metro style app
How to used out side "help.sqlite" file in current project and how to fetch the value for the SQLite. in SQLite that have two tables. One name is "Application_Name" and the other Table name is = "Application_Developer_Person";
6/23/2012 3:51 PM | # re: Using SQLite in a Metro style app
@HHHH - I have no idea what your question is.
6/24/2012 10:06 PM | # re: Using SQLite in a Metro style app
proxyDataFolder = await Package.Current.InstalledLocation.GetFolderAsync("File");
proxyFile = await proxyDataFolder.GetFileAsync("test1.sqlite");
if (proxyFile != null)
{
var db = new SQLite.SQLiteConnection(proxyFile.Path,SQLiteOpenFlags.ReadWrite);
SQLiteCommand cmd = new SQLiteCommand(db);
cmd.CommandText = "insert into test('Name') values('hello');";
cmd.ExecuteNonQuery();
}

An exception of type 'SQLite.SQLiteException' occurred in SQLite_Sample.exe but was not handled in user code
Additional information: ReadOnly
If there is a handler for this exception, the program may be safely continued.



This type of Error are generated when i used outside database from the project and how can i insert, update and delete record for SQLite.
6/25/2012 5:28 AM | # re: Using SQLite in a Metro style app
@HHHH - you can't write files to the app package location. If using an existing database, you should copy it over to the ApplicationData location where you can read/write.
6/27/2012 1:37 AM | # re: Using SQLite in a Metro style app
Hi everyone,

Thanks Tim for this great post.

Has anyone got it working on Release Preview ?
I always get 'Could not open database ... (14)", whatever path I give for my db. (I use the compiled version from sqlite.org).

Anyone knows how to fix this error 14 ?

Kind regards,
Jean-François
6/27/2012 5:39 AM | # re: Using SQLite in a Metro style app
@Jean-Francois - where are you trying to open the database? what's the path of the db?
6/27/2012 6:02 AM | # re: Using SQLite in a Metro style app
I have bug with database openning when my windows account name written not in English( in my case Russian)
6/27/2012 6:09 AM | # re: Using SQLite in a Metro style app
I get same error from previous comment( Jean-Francois ) : "Could not open database ... (14)"
Maybe it is because LocalFolder contain windows account name witten not in english ( in my case "C:\Users\Руслан\AppData\Local\Packages\f0a7a14a-6e11-4aa7-be5b-019266be7b78_3k6rj4k30b3e6\LocalState")
6/27/2012 2:02 PM | # re: Using SQLite in a Metro style app
@Tim, the code is similar to your sample:

string dbRootPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
using (SQLiteConnection db = new SQLiteConnection(Path.Combine(dbRootPath, "mypeople.sqlite")))
{
........

If you want to take a look, the sample is available at http://dl.free.fr/rNJR0E4XM

My box is x64 and I compile for x86 (and of course I picked the x86 version of sqlite3.dll).
I can read/write files in Windows.Storage.ApplicationData.Current.LocalFolder.Path, but opening a SQLite3 db will always fail.
Same thing if I try to create a database in KnownFolders.DocumentLibrary (I declared the .sqlite extension in package.appxmanifest).

I really don't know what to try next!
6/27/2012 9:28 PM | # re: Using SQLite in a Metro style app
@Jean-Francois - your sample worked fine for me (FYI you won't be able to create in DocumentLibrary). Can you tell me the full path of what the Path.Combine results in? Perhaps it is indeed related to certain Unicode characters?
6/28/2012 6:21 AM | # re: Using SQLite in a Metro style app
@Tim, you have found the issue!! Thanks a lot.
I have a 'ç' in my first name and the path was "C:\Users\Jean-François\AppData\Local\Packages\569e8a16-efb8-4992-ada5-7407fecb3dee_fv7d1kf84c3t4\LocalState\games.db"

I created a new account and voila it works!
I'll report the issue to sqlite dev team.
6/29/2012 3:41 AM | # re: Using SQLite in a Metro style app
Great article...

But I had to find out the hard way, that you can't use the sqlite3.dll in a WINRT class library :-(.
6/29/2012 7:28 AM | # re: Using SQLite in a Metro style app
Well now I got a new Problem... I wanted to update (about 40 rows) within a Transaction. The SQLite3 dll gives me randomly the Response Code 14 (unable to open the db). I think somewhere in SQLite3 the async is wrong implemented, because it tries to write 2 times in the -journal :(
or somewhere the FileStream is not correctly disposed...
6/29/2012 2:26 PM | # re: Using SQLite in a Metro style app
@Mosquito - what do you mean you can't use it from a class library? Also on the RC14, what is the path you are trying to create it in?
7/1/2012 11:26 AM | # re: Using SQLite in a Metro style app
"help.sqlite" file in current project and how to fetch the value for the SQLite. in SQLite that have two tables. One name is "Application_Name" and the other Table name is = "Application_Developer_Person"; Thanks for sharing.
7/1/2012 11:37 AM | # re: Using SQLite in a Metro style app
@Core - if help.sqlite is your db file, then when you include it in your project you would use some method of querying it (if C# app I recommend sqlite-net).
7/1/2012 7:48 PM | # re: Using SQLite in a Metro style app
I have an exist sqlite database file and how to copy it to LocalFolder when the app be installed?
now I have to create table in code.
7/2/2012 3:58 AM | # re: Using SQLite in a Metro style app
i am using Linq to Sqlite and i have 1 issue ie i dont need specific property as Column ie
Class Emp
{
[PrimaryKey]
public int Eno{get;set;}
public int Ename{get;set;}
public int Sal{get;set;}
public LinearGradientBrush SalColor{get;set;}
}

in the above i no need to create column in Emp table so how to do this? i want Emp class as Column Properties and Non Column Properties
7/2/2012 8:00 AM | # re: Using SQLite in a Metro style app
@troyou - see timheuer.com/...

@Jilani - I haven't tried that myself but depending on your use this might not be possible. Of course you can always use direct SQL queries with SQLite itself and not use the automatic table mappings provided by sqlite-net.
7/3/2012 10:22 PM | # re: Using SQLite in a Metro style app
Hi Tim ,
Great Article. I followed your instructions and everything is working fine for me.
I have a question though about a declaration. I want to have a table with more that one columns as Primary Key.
For eg. I have the below class.
public class Attachments
{

[MaxLength(18)]
public Int64 AttachmentID { get; set; }
[AutoIncrement, MaxLength(18)]
public Int64 AutoSequence { get; set; }
public Byte[] File_Data {get; set;}

}

and I want to make AttachmentID and AutoIncrement a Compostite Primary Key.
How do I do that ???
When I try to do the below I get an error from Sqlite which says that I have declared PrimaryKey twice.
public class Attachments
{

[PrimaryKey,MaxLength(18)]
public Int64 AttachmentID { get; set; }
[PrimaryKey,AutoIncrement, MaxLength(18)]
public Int64 AutoSequence { get; set; }
public Byte[] File_Data {get; set;}

}

Any Ideas ?
Thank you in advance


7/5/2012 11:29 AM | # re: Using SQLite in a Metro style app
@ZKar - good question, I'm not sure about that one and would have to defer to the owner of the library.
7/5/2012 9:44 PM | # re: Using SQLite in a Metro style app
Hi Tim ,
I found a solution , but I don't know if it is the right one. It works though...
I took advantage of the open source functionality which sqlite has , and I went to the createtable method and before I send the query for execution I 'm changing it a little bit.
Since the statements are working for a single PrimaryKey declaration , I 'm checking if there are more than two in my public class and changing the query dynamically.

In the public int CreateTable(Type ty) method before the var count=Execute(query) I did the below.

//first find the occurences
if (CountStringOccurrences(query, "primary key") > 1)
{
//start changing the query
query = this.ChangeTheQuery(query);
}

and the methods are.
public static int CountStringOccurrences(string text, string pattern)
{
// Loop through all instances of the string 'text'.
int count = 0;
int i = 0;
while ((i = text.IndexOf(pattern, i)) != -1)
{
i += pattern.Length;
count++;
}
return count;
}

public string ChangeTheQuery(string mystring)
{

string myfinalstring = "";
string mytemp = "";
string[] myarray=new string[20];//are you going to have more than 20 fields as primary ???? I believe 5 are more than enough but let's be large on this :)
int mycounter = 0,myindex=0;
//mystring = mystring.Replace("primary key", "");
string[] words = mystring.Split('\n');
foreach (string word in words)
{
if (word.IndexOf("primary key") > 0)
{
mytemp = word.Replace("primary key", "");
int first = mytemp.IndexOf('"') + 1 ;
int last = mytemp.LastIndexOf('"');
myarray[mycounter] = mytemp.Substring(first, last - first);
mycounter++;
}
else
{
mytemp = word;
}
myfinalstring += mytemp + "\n";
}

if (mycounter <= 0) return mystring;
int mylast = myfinalstring.LastIndexOf(")");
if (mylast == 0) return mystring;
myfinalstring = myfinalstring.Substring(0, mylast - 1);

for (myindex = 0; myindex <= mycounter; myindex ++)
{
if (myindex == 0) myfinalstring += " ,\n PRIMARY KEY (";
myfinalstring += '"' + myarray[myindex] + '"' + ",";

}
string pattern = "," + '"';
mylast = myfinalstring.LastIndexOf(pattern);
myfinalstring = myfinalstring.Substring(0, mylast );
myfinalstring += "))\n";

return myfinalstring;

}

7/5/2012 9:48 PM | # re: Using SQLite in a Metro style app
-- Continuing from the previous post

And all this returns the below statement

create table if not exists "Attachments"(
"AttachmentID" bigint not null ,
"AutoSequence" bigint not null ,
"File_Data" blob ,
PRIMARY KEY ("AttachmentID","AutoSequence"))


which is correct and it creates the table.
So I can now have my public class with the below declaration and using the createtable command it makes my life very easy.
public class Attachments
{

[PrimaryKey,MaxLength(18)]
public Int64 AttachmentID { get; set; }
[PrimaryKey, MaxLength(18)]
public Int64 AutoSequence { get; set; }
public Byte[] File_Data {get; set;}

}

In conclusion.
I don't know if this is a good approach . And also I don't know if it is working if you want to create a unique index or a foreign key or whatever because I must check the syntax for each one sql statement. Also For my project I'm not planning on using those.
Anyway.
Am I going to have problems if they release a new version of Sqlite ? Is there something I need to know ?
I'm asking this because I used the CreateTable Method in the Sqlite.cs.
I would appreciate any comment on this.

thank you in advance
zkar

Thank you
Gravatar
7/23/2012 4:42 PM | # re: Using SQLite in a Metro style app
I'm actually seeing the same problem Mosquito did - performing more than one update in a transaction is giving me error "14" - and I do think it is tied to the journal file locking. Note that multiple inserts work fine, or updates that don't actually result in a row change work fine.

I'm guessing you'd see it if you changed your sample to do this inside your transaction:
for (int i = 0; i < 10; i++)
{
var person = new Person() { ID = i, FullName = "Person " + i.ToString() };
if(db.Update(person) == 0))
{
db.Insert(person);
}
}
7/23/2012 4:59 PM | # re: Using SQLite in a Metro style app
@cob - can you run your inserts in a transaction?
Gravatar
7/23/2012 6:17 PM | # re: Using SQLite in a Metro style app
Yep - inserts work. I should have pointed out that I only get the issue the second time I run the code I included above. So the first pass it does all the updates (that result in 0 rows modified) and the inserts. The second time I run it, it gives me error 14 on the second update.
7/24/2012 6:09 AM | # re: Using SQLite in a Metro style app
Hi
I am trying to use sqlite with window8 metro style applications. i referred you article and followed same steps to create sqlite3.dll, while using following statement from VS cmd prompt (fossil clone http://www.sqlite.org/cgi/src sqlite3.fossil), i am getting following error
C:\Program Files\Microsoft Visual Studio 11.0\VC>nmake

Microsoft (R) Program Maintenance Utility Version 11.00.50522.1
Copyright (C) Microsoft Corporation. All rights reserved.

NMAKE : fatal error U1064: MAKEFILE not found and no target specified
Stop.

Please suggest me to proceed further,

Thanks and Regards
Suresh
7/24/2012 8:10 AM | # re: Using SQLite in a Metro style app
@Suresh, you aren't issuing the command in the right place...you need to issue the nmake statement where the source is. See: timheuer.com/.... However, you don't need to build it anymore as they released built versions available now in 3.7.13 on their site in the download area.
7/25/2012 6:01 AM | # re: Using SQLite in a Metro style app
Thanks Tim,
its working now with latest dlls.
I have one more question, is there any other way to run "select" query than what it is shown in video? in my project we have requirement to write dynamic query to fatch data from different tables, can you please suggest how to do this..

Thanks in advance,

Regards
Suresh
8/5/2012 2:29 AM | # re: Using SQLite in a Metro style app

0Hi,

I am using SQLite for Windows 8 metro style application in C# , with the help of below link:

wp.qmatteoq.com

I have downloaded SQLite3.dll for my x86 machine from http://sqlite.org/download.html and added the same in my project. Futher i have also installed sqlite-net from "Manage nu-get packages". I am able to compile and build the solution but it breaks at run-time with below exception:

"System.DllNotFoundException: Unable to load DLL 'sqlite3'" when the below line is executed:

SQLiteConnection sqlConnection = new SQLiteConnection("Servicehealth");

I have also added Microsoft C++ runtime in the refrences, but still the problem persists.

Any help on the same will be grateful !!

Thanks!

8/5/2012 1:44 PM | # re: Using SQLite in a Metro style app
@Lipsa - you need to make sure the DLL is marked as Content and copied to the package.
8/6/2012 5:28 AM | # re: Using SQLite in a Metro style app
Thanks Tim !

Yes.. sqlite3.dll is marked as Content, steps followed are:
1. Add Sqlite3.dll to the project.
2. Right click the added dll and set Build action as "Content" and Copy to directory as "Copy always".

It gets build succesfully but at runtime, it throws exception in SQLiteConnection method of SQLiteConnection class in "SQLite.cs" file at the below line:

var r = SQLite3.Open (DatabasePath, out handle);

Exception is :
DllNotFoundException -
Unable to load DLL 'sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
8/6/2012 8:01 AM | # re: Using SQLite in a Metro style app
@Lipsa - you added it to the root of the project right? Can you look in the deployed folder when you run the app and ensure it is there?
8/7/2012 2:57 AM | # re: Using SQLite in a Metro style app
Thanks Tim !!...Problem is solved:)

Actually There are multiple projects in my solution, I had added Sqlite3.dll in the project where all the Database related operations are done and defined a "MakeDBConnection()"method to create tables. But I was trying to call the specific method(MakeDBConnection()) from another project where all the UI related work is done.

Now, when I added Sqlite3.dll in the other project as well I was able to create db and tables successfully.
Gravatar
8/9/2012 4:35 AM | # re: Using SQLite in a Metro style app
Can we use entity framework in WinRT-SQLite?
8/14/2012 12:46 AM | # re: Using SQLite in a Metro style app
I need your help :-) I have a problem when I generate my project : “SQLite.SQLiteException Could not open database file: C:\Users\Clément\AppData\Local\Packages\cc121a7a-eac7-4655-8163-98ce14222e78_vjm86x7agcmvt\LocalState\people (CannotOpen)”.
However, I have follow all your instructions.

Have you got an idea ?
8/16/2012 4:01 AM | # re: Using SQLite in a Metro style app
Craig, as your path contains non unicode characters you should modify SQLite.cs to use sqlite3_open16() function instead of sqlite3_open(). The same thing with sqlite3_prepare().
8/16/2012 8:03 AM | # re: Using SQLite in a Metro style app
@Craig/Roman - what he meant to say is your path *contains* Unicode chars (your user name). How are you accessing SQLite? is this a native app? If so, the method Roman suggests is accurate. If a managed app and using SQLite-net, we fixed this in the SQLiteConnection call a few weeks back so make sure you have the latest version of that.
8/19/2012 9:03 AM | # re: Using SQLite in a Metro style app
I'm developing metro app using XAML and C# ,I had successfully integrated SQLite 3.7.13 in my project and also i'm able to create table and insert data in it ,But how can we give prepared statements to this SQLite for example in MYSQL "SELECT COUNT(*) FROM table_name WHERE column_name = 'Y'". So how can we pass this query to database, Can anyone help me with this please.
8/19/2012 2:28 PM | # re: Using SQLite in a Metro style app
@Nishanth - if you are using the SQLite-net library you can use the Execute*() methods to do this or if on a single column you can use TableQuery<T>() with a LINQ expression to get this. Also look at SQLiteCommand that will allow you to have parameterized queries.
8/20/2012 9:40 AM | # re: Using SQLite in a Metro style app
Thanks for the response. it worked. I'm having issues with delete, do you have any ideas
db.Delete<Tablename>(new Tablename() { id = 2});
where "id" is primary Key and Auto increment
8/20/2012 9:48 AM | # re: Using SQLite in a Metro style app
@nishanth - do you have a fuller repro that I could try to replicate?
8/20/2012 1:50 PM | # re: Using SQLite in a Metro style app
Hi TIm,
congrats for the really good articles; i have one question: i'm using sqlite-net and have success in doing conn.Table<tabname>() ; is there a way to make a join in linq query? It would be very useful for me to do it.
Thanks in advance.
8/20/2012 10:29 PM | # re: Using SQLite in a Metro style app
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Test.db");
using (var db = new SQLite.SQLiteConnection(dbPath))
{
var data = db.Table<tablename>().Where(tablename => tablename.uploaded_bool == false && tablename.Sid == 26);
try
{
int iDataCount = data.Count();
int id;
if (iDataCount > 0)
{
for (int i = 0; i < iDataCount; i++)
{
Elements = data.ElementAt(i);
id = Elements.id;
/*
Doing some code
*/
}
int i = db.Delete<tablename>(new tablename() { Sid = 26 });
}
}
catch (Exception ex)
{

}
}
where "Sid" is column in my database and with number "26" i will get n number of rows So, using a for loop i need to do some code and after the for loop I need to delete records of Sid(26) in database, So at this line

int i = db.Delete<tablename>(new tablename() { Sid = 26 });

I'm getting unable to close due to unfinalised statements exception, So my question is how to finalise the statement in sqlite3,Apparently SQLite3 has a finalize method for destroying previous DB calls but I am not sure how to implement this. Please help me.
8/30/2012 6:32 AM | # re: Using SQLite in a Metro style app
Good morning Tim! When I add aa dependence arise compilation errors and says that the file does not exist runtime. How can I solve this? Thanks!
9/6/2012 1:12 AM | # re: Using SQLite in a Metro style app
I am having trouble creating the SQLiteConnection from my managed (C#) app, when I have unicode characters (the Danish letter 'ø') in my username. When I try to create the SQLiteConnection I get the following error:

"Could not open database file: C:\Users\Thorbjørn\AppData\Local\Packages\8db1ac66-f975-4911-8e60-5dcf8404ea2d_qqar37d0axeeg\LocalState\mypeople.sqlite (CannotOpen)"

First I tried with the NuGet sqlite-net package, but then read Tim's comment about the issue being fixed in the latest version, so I pulled that from GitHub (commit 0761928376) and copied the SQLite.cs file to my solution, but it still fail with the same error and I cannot figure out how to fix the issue by modifing SQLite.cs to use sqlite3_open16() function instead of sqlite3_open(), as Roman suggested.

If I create another Windows user without special characters in the username it works fine, but that is not a solution :)

Any ideas how to overcome the issue?
9/6/2012 1:48 AM | # re: Using SQLite in a Metro style app
Never mind - Found the solution to the unicode character issue myself about changing the SQLiteConnection() constructor to use SQLite3.Open16() instead of SQLite3.Open() - as Roman also wrote. Now I am just thinking why it is not using this per default...

Tim, thanks a lot for the supreme step by step guide and also the followup on the comments, which saved my day :)
9/6/2012 5:50 AM | # re: Using SQLite in a Metro style app
The procedure to build sqlite dll is overcomplicated. All you need to do is to build a dll from 2 files - sqlite3.c (amalgamation, v3.7.13+) and dummy dllmain.c (VS will make it for you). On top of that you need to select correct platform and that's it.

As an example here is the most complex case - ARM compilation.
You will need these nonstandard switches:
- Compiler: /DSQLITE_OS_WIN=1
- Linker: /MACHINE:ARM /APPCONTAINER
Because ARM target is unsupported by VS IDE, you need to build the dll from the command prompt. (ARM cross tools command prompt) It does not matter whether you invoke cl/link from a batch file or write a simple makefile.
9/6/2012 6:36 AM | # re: Using SQLite in a Metro style app
Sorry, correction: You need compiler switch /DSQLITE_OS_WINRT=1.
9/6/2012 10:43 AM | # re: Using SQLite in a Metro style app
@Thorbjorn - it should be using this by default (not open16 but a method that gets around the Unicode issue). It isn't in the simple SQLiteConnection() but in the override where you'd provide flags.
9/6/2012 10:44 AM | # re: Using SQLite in a Metro style app
@jan - the updated WinRT downloads now build an ARM version for you and packaged in an Extension SDK for easy consumption in a VS environment.
9/7/2012 2:15 AM | # re: Using SQLite in a Metro style app
@tim - I know that. It is suitable for majority of users, but not for us - we use own encryption and couple of other addons. That's way we need to build our own dll.

I wrote this as I noted that several users repeat the procedure described in your article, i.e. building sqlite from the source tree. That's an overkill for most of them.

Consider modifying the article by explaining how to properly use the amalgamation release. If you are interested, I can email you a simpler procedure for building the dll.
9/7/2012 8:20 AM | # re: Using SQLite in a Metro style app
@jan - in that case you also need this option OPTS=/DWINAPI_FAMILY=WINAPI_PARTITION_APP
9/9/2012 10:39 PM | # re: Using SQLite in a Metro style app
Hello friends,

I am using SQLite in C# Metro style (windows store) application on Windows 8 machine. I have retrieved records from single table by following way -

var data = db.Table<DataAccess.CalendarGroups>();
List<CCalendarGroup> objCalGroups = new List<CCalendarGroup>();

if (data.Count() > 0)
{
for (int i = 0; i < data.Count(); i++)
{
CCalendarGroup objCalGroup = new CCalendarGroup();
objCalGroup.CalGroupName = data.ElementAt(i).Name;
objCalGroup.CalGroupId = data.ElementAt(i).Id;
objCalGroups.Add(objCalGroup);
}
}

But now I want to retrieve records from multiple tables like INNER JOIN query clause.
Please help with an example.

Regards,
9/11/2012 4:31 AM | # re: Using SQLite in a Metro style app
I had tried the delete statement as well. The delete statement as follows is returning NotSupportedException was unhandled by user code,

var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "People.db");
//create database
using(var db = new SQLite.SQLiteConnection(dbpath))
{
db.CreateTable<Person>
db.Delete<Person>(new Person { ID= 2});
People.ItemsSource = db.Table<Person>();
}

Is there anything I missed out here that are causing the error. Please help!
9/11/2012 11:39 PM | # re: Using SQLite in a Metro style app
Hi Aloy,
We have implemented delete in another way -
db.RunInTransaction(() =>
{
db.Execute("DELETE FROM HolidayLocations");
});
If you have any better way to do it, let us know.
9/11/2012 11:44 PM | # re: Using SQLite in a Metro style app
Hello Friends,

If anybody know the sample example link for all the SQLite operations like (INSERT, UPDATE, DELETE, SELECT from single/multiple tables. Also WHERE clause). Please share.

Regards,
9/12/2012 11:07 PM | # re: Using SQLite in a Metro style app
How to insert name like "umesh D'suza" in sqldatabse using c# due to ' charector i am getting an exception
9/14/2012 8:06 AM | # re: Using SQLite in a Metro style app
@timheuer palease can you give an example related to update and delete statement in sqllite....Please...?
9/14/2012 11:23 AM | # re: Using SQLite in a Metro style app
@mohammed - it is standard SQL syntax...delete from [table] where [your conditions]
9/17/2012 4:00 AM | # re: Using SQLite in a Metro style app

Thanks' a lot for your quick reply

@timheuer actually I don't have any idea fro writing a where clause in SQLite ,so please can you give me a code snippet
so I can implement in a better way....?
9/17/2012 7:43 AM | # re: Using SQLite in a Metro style app
@mohammed - SQLite supports the basic SQL syntax (http://www.sqlite.org/lang.html). If you aren't familiar with this I suggest seeking out resources to understand the fundamentals of SQL syntax.
9/18/2012 5:09 AM | # re: Using SQLite in a Metro style app
Hi,Tim
with your help,sqlite DB work well in my app,but I cannt pass the windows app certification,here is the erro message

◦ This type of application is not support the API CreateFileMappingW in the kernel32.dll . sqlite3.dll calling this API.
◦ This type of application is not supported API MapViewOfFileEx in kernel32.dll. sqlite3.dll calling this API.

even i follow what you doing in your video, The problem persists.
p.s English is not my first language,so.... you know....:P
9/25/2012 10:23 PM | # re: Using SQLite in a Metro style app
Hi Tim,

I am planning to make some changes in sqlite native source code ie in sqlite3.c file. After making the changes how to create the sqlite3.dll which can be used in the metro style apps. Can you gimme the procedure to create the sqlite3.dll

Thanks & Regards,
Suman
Gravatar
9/26/2012 2:47 AM | # re: Using SQLite in a Metro style app
Hi Tim,
how to get row count ?
I am using SQLite in metro style apps.

"Select Count(columnName) From TableName where some condition"

how to execute this query please help to me..

Thanks
Ram
9/29/2012 4:42 PM | # re: Using SQLite in a Metro style app
Big thanks for your post and comments! It's work fine)
10/1/2012 8:26 PM | # re: Using SQLite in a Metro style app
Hi, when I updated to windows enterprise and download visual studio 2012 express for windows 8. I didn't have visual C++ runtime when adding reference. So that when I try to validate package after build, it failed because lacking visual C++ runtime. How can I do now?
Gravatar
10/2/2012 11:32 PM | # re: Using SQLite in a Metro style app


i am developing windows store apps by using xaml and c#.i am using SQLite db in my app.i am getting problem for getting count of row in table.
how to get row count ?
please help to me ..

ex : SELECT COUNT( distinct column ) FROM TABLE
10/6/2012 10:43 AM | # re: Using SQLite in a Metro style app
trying to follow the procedure inside a background task gives this error

Windows Runtime type 'Windows.Foundation.Point' was found in multiple
referenced winmd files. Please remove either 'C:\Program Files (x86)\
Microsoft SDKs\Windows\v8.0\ExtensionSDKs\Microsoft.VCLibs\11.0\References
\CommonConfiguration\neutral\platform.winmd' or 'C:\Program Files (x86)\
Windows Kits\8.0\References\CommonConfiguration\Neutral\Windows.winmd'
from the list of referenced files.

how can i solve?
10/17/2012 4:21 PM | # re: Using SQLite in a Metro style app
Will Sync Framework be supported for Metro Style apps?

Thanks.
11/4/2012 5:58 AM | # re: Using SQLite in a Metro style app
Hello, I have write first application in modern ui with SQLite but I have problem certification App in Windows Store, my application don't certification with Windows App Certification Kit for use some API that don't pass test.
11/21/2012 10:05 AM | # re: Using SQLite in a Metro style app
Hey Tim,

Visual Studio does not recognize SQlite as a Keyword and keep's telling me: "The type or namespace name 'SQlite' could not be found (are you missing a using directive or an assembly reference?)"

I've added the two References (as shown in your tutorial above) and there are also displayed Solution Explorer but somehow i can't use them.

what did i miss?

PS: screenshot could be found here: imageshack.us/.../sqlitenotworking.png/
11/22/2012 12:58 PM | # re: Using SQLite in a Metro style app
I missed this step: Accessing the Engine from your app

now it works fine, thanks a lot
12/11/2012 5:21 AM | # re: Using SQLite in a Metro style app
Thanks for the great post Tim.

In my app, I have a situation where I am making 2 web requests in two different pages of the app, both return the response in the same format, so after parsing I have the data on the same class name. (In other words, I want to create tables of Person class twice, each time with diferent data)

db.CreateTable<Person>(); // create a table of Person type
//insert some data and bind to some UI in one page

//Now somewhere in the app, I have the different data of type Person class
//how to insert into separate table (like Person2 etc) ? so that both the tables are not messed up ?

the above code creates a single table. How can I have a different table with the same Person type of columns ?

12/23/2012 3:33 AM | # re: Using SQLite in a Metro style app
Hi Tim Heuer, Is it possible to store StorageFile object on SQlite database, If possible how can i do this please help me.
1/6/2013 2:58 PM | # re: Using SQLite in a Metro style app
Based on your articles I have successfully interfaced sqlite3 with my apps on the Windows desktop. Thank you.

I use VB and the0 interface to the DLL is as follows

<DllImport("sqlite3", EntryPoint:="sqlite3_open", CallingConvention:=CallingConvention.Cdecl)> _
Public Function Open(ByVal filename As String, ByRef db As IntPtr) As Integer
End Function

I tried this same on the Windows 8 Phone project and have had problems. I use the version of the DLL

Sqlite for Windows Phone 3.7.15.1


Installed under

C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.7.15.1\

I get the following error when I try to perform the Open the file

An exception of type 'System.TypeInitializationException' occurred in XXXXX.DLL but was not handled in user code


System.TypeInitializationException was unhandled by user code
HResult=-2146233036
Message=The type initializer for ' XXXXX.UtilityTest' threw an exception.
Source= XXXXX
TypeName=WIn8Calc.UtilityTest
StackTrace:
at XXXXX.UtilityTest.VB$StateMachine_1_SetConnectionString.MoveNext()
InnerException: System.NotImplementedException
HResult=-2147467263
Message=The method or operation is not implemented.
Source=Windows
StackTrace:
at Windows.Storage.ApplicationData.get_LocalSettings()
at XXXXX.UtilityTest..cctor()
InnerException:


Do you have any ideas? has the interface to the DLL changed with the Windows Phone Version of the DLL from the WIndows 8 Desktop version of the DLL?

I have detected that the file does exist in Working storage.

Dim localFolder As Windows.Storage.StorageFolder = Windows.Storage.ApplicationData.Current.LocalFolder

Dim dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "MyDataBase.sqlite")

The name of the file it has detected is as follows

dbPath = "C:\Data\Users\DefApps\AppData\{6AAA4956-725D-440B-A801-F370CFAD1001}\Local\YYYYYY.sqlite"


Gravatar
1/21/2013 2:41 PM | # can SQLite be persistent with Winstore app?
I understand that all file access which is available for a C# Winstore app is - local store and app data.. and these are being cleaned once the app is re-installed / new version installed.... this isnt the right way to use a 'database'.. right?

what am I missing here? is there a way to avoid sql file being deleted ?
1/28/2013 7:53 AM | # re: Using SQLite in a Metro style app
how to insert image into the database using sqlite.plz help soon
2/27/2013 2:52 AM | # re: Using SQLite in a Metro style app
hi timheuer,could you please explain me in detail how to make sqlite database connection to windows 8 using javascript,HTML........waiting for your reply
5/13/2013 6:31 AM | # re: Using SQLite in a Metro style app
Hi Tim , Great article , I have been using sqlite-net for my couple of winRT apps . One of the app is straight forward and it works great with small DB but the second needs an encryption to be applied to protect some sensitive data,
Can you please suggest a way to get this done.
I have about 10 tables , max entries would be 1000+ (in rare case in a table),
9/25/2013 12:55 AM | # re: Using SQLite in a Metro style app
Hello there Tim Heuer, Is it feasible to store StorageFile object on SQlite databases, If it is possible how to do that please assist me. Also let me know how to place image into the databases using sqlite.
10/24/2013 3:26 AM | # re: Using SQLite in a Metro style app
Just wondering: is this part of Microsoft's on going death wish? You fail to supply an embedded DB (despite having SQL Express/Compact at your finger tips); instead you send us to a third party. Then you don't even supply compiled files and wrappers to down load but expect us all to do the considerable leg work ourselves. Might be easier to just not develop win8 apps and let the platform die quietly.
10/24/2013 9:33 PM | # re: Using SQLite in a Metro style app
@Robert - I understand your frustration. However when looking at the landscape of what people are trying to do with mobile apps across many devices, SQLite is the defacto standard. There really are only two steps, add the runtime, add the wrapper. Done. Having a compiled wrapper wouldn't change the second step as you'd still have to add it. I don't see this considerable legwork at all and there are many people moving to this approach as it provides consistency in their mobile code base.

As to SQLCE, unfortunately the version used actually uses a server component and that wasn't feasible to migrate to this application model. Local databases are becoming a commodity market and there are clear winners in this space. SQLite is shipped in 96% of all mobile platforms. Guess which one it isn't shipped in.
7/3/2014 1:52 AM | # re: Using SQLite in a Metro style app
Can I use these steps to compile sqlcipher? I need an encrypted sqlite and sqlcipher don't provide community edition binaries for WinRT (unlike Android). I've try many ways to compile it for WinRT (Windows 8.1 and Windows Phone 8.1) with no success.
7/3/2014 11:36 AM | # re: Using SQLite in a Metro style app
Just having 1 question .If i use sqllite in this way ,will the app pass WACK tets for the appstore ?
7/20/2014 10:34 PM | # re: Using SQLite in a Metro style app
My windows store application has a pdf which resides in the app. Ofcourse I can decide where to place it in the app.

On click of a button I need to open it and display on the screen.

Can you help me with the code for it.
7/21/2014 8:12 PM | # re: Using SQLite in a Metro style app
Since SQLite.dll is native dll, existing for x32/x64/arm, you have to make/test/publish 3 different versions of your app - for each platform.
7/27/2014 8:07 PM | # re: Using SQLite in a Metro style app
There's no code to close the SQLiteAsyncConnection. Should there be? Is the connection close implicitly by codes in SQLiteAsync.cs file?
9/19/2014 2:00 AM | # re: Using SQLite in a Metro style app
I already added it to the project. But when i want to open the local database named "Thulog.db", it always returned 14.
when i changed the file'name to "Thulog" or "Thulog.txt", it will be OK. Do you know what it happened?

 
Please add 6 and 3 and type the answer here:

DISCLAIMER:

The opinions/content expressed on this blog are provided "ASIS" with no warranties and are my own personal opinions/content (unless otherwise noted) and do not represent my employer's view in any way.