×

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!

I got a few questions and comments about how to actually include SQLite in a C# Metro style app.  Since perhaps it wasn’t clear in describing in my post, I thought a quick video might help demonstrate the steps to build and use SQLite in a C# Metro style app.

The video walks through actually building SQLite from the source (Visual Studio 2012 required…express is fine) and adding it to a C# Metro style app, create a database, populate with some data based on a class and databind the query to a ListView.  The video references my OneNote notebook on the tools you need to download and build the SQLite source.  It also demonstrates using the sqlite-net library from NuGet on interacting with SQLite in a C# application.

This is a quick video to demonstrate the concept on how to get started and is not a full end-to-end sample.

NOTE: This video only demonstrates how to build SQLite until the team itself merges the WinRT changes and produces the supported build.  Until then this is a step you’d have to do on your own and these private branches are not fully supported by them until merged to their main release branch.

I hope this helps clarify things!


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


6/6/2012 9:46 AM | # re: HOW TO: Use SQLite in C# Metro style app
"This video has been removed by the user."
6/6/2012 10:11 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim
Your video has been removed from Youtube, and I really wanted to see it.
Have you re-uploaded it somewhere else?

Please let me know.
PS: I've compiled SQLite3 dll's but haven't worked with VStudio for a couple of years... so oiling the rusty gears are required.

6/6/2012 11:21 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Louis - not sure what happened...re-uploaded and fixed.
Gravatar
6/9/2012 9:45 AM | # re: HOW TO: Use SQLite in C# Metro style app
Tim, thanks for publishing this. Question before I really put time into trying to incorporate SQLite into my Metro app: Can it be compiled to work on ARM as well as X86 or x64?
6/9/2012 12:37 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Jon - the support for platforms is dictated by the vendor. That would be a question for them to answer rather than me comment anecdotally. SQLite does run on ARM platforms currently on Android.
6/10/2012 8:40 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thanx for your help, this tuto help me a lot :)
6/10/2012 10:26 PM | # re: HOW TO: Use SQLite in C# Metro style app
Great video and blog post. I admit I was holding out for a database solution integrated into the SDK, as was done in the Mango update. LINQ-to-SQL or LINQ-to-Entities would be amazingly helpful.
6/11/2012 2:38 PM | # re: HOW TO: Use SQLite in C# Metro style app
Followed the video and tried build SQLite on Win8 Consumer Preview. However, ran into a number of compilation issues. Too busy to look into it. Any one had similar issue?
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_DEPRECAT
-D_CRT_SECURE_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1 -DSQLITE_ENABL
_FTS3=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_MAX_TRIGGER_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(944) : error C2061: syntax error : identifier 'i64'
sqlite3.c(944) : error C2059: syntax error : ';'
sqlite3.c(945) : error C2061: syntax error : identifier 'u64'
sqlite3.c(945) : error C2059: syntax error : ';'
sqlite3.c(1220) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1220) : error C2143: syntax error : missing '{' before '*'
sqlite3.c(1221) : error C2059: syntax error : 'type'
sqlite3.c(1226) : error C2059: syntax error : ')'
sqlite3.c(1327) : error C2146: syntax error : missing ')' before identifier 'intKey'
sqlite3.c(1327) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1327) : error C2061: syntax error : identifier 'intKey'
sqlite3.c(1327) : error C2059: syntax error : ';'
sqlite3.c(1327) : error C2059: syntax error : ','
sqlite3.c(1330) : error C2059: syntax error : ')'
sqlite3.c(1333) : error C2146: syntax error : missing ')' before identifier 'nKey'
sqlite3.c(1333) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1333) : error C2061: syntax error : identifier 'nKey'
sqlite3.c(1333) : error C2059: syntax error : ';'
sqlite3.c(1333) : error C2059: syntax error : ','
sqlite3.c(1335) : error C2059: syntax error : ')'
sqlite3.c(1341) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1341) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1341) : error C2143: syntax error : missing '{' before '*'
sqlite3.c(1341) : error C2059: syntax error : ')'
sqlite3.c(1347) : error C2081: 'sqlite3_int64' : name in formal parameter list illegal
sqlite3.c(1348) : error C2061: syntax error : identifier 'sqlite3BtreeGetCachedRowid'
sqlite3.c(1348) : error C2059: syntax error : ';'
sqlite3.c(1348) : error C2059: syntax error : 'type'
sqlite3.c(1364) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1364) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1364) : error C2143: syntax error : missing '{' before '*'
sqlite3.c(1364) : error C2059: syntax error : ')'
6/11/2012 2:41 PM | # re: HOW TO: Use SQLite in C# Metro style app
Followed the video and got quite a few compilation issues. Too busy to look into it. Anyone had similar problem? Thanks ahead.

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_DEPRECAT
-D_CRT_SECURE_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1 -DSQLITE_ENABL
_FTS3=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_MAX_TRIGGER_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(944) : error C2061: syntax error : identifier 'i64'
sqlite3.c(944) : error C2059: syntax error : ';'
sqlite3.c(945) : error C2061: syntax error : identifier 'u64'
sqlite3.c(945) : error C2059: syntax error : ';'
sqlite3.c(1220) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1220) : error C2143: syntax error : missing '{' before '*'
sqlite3.c(1221) : error C2059: syntax error : 'type'
sqlite3.c(1226) : error C2059: syntax error : ')'
sqlite3.c(1327) : error C2146: syntax error : missing ')' before identifier 'intKey'
sqlite3.c(1327) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1327) : error C2061: syntax error : identifier 'intKey'
sqlite3.c(1327) : error C2059: syntax error : ';'
sqlite3.c(1327) : error C2059: syntax error : ','
sqlite3.c(1330) : error C2059: syntax error : ')'
sqlite3.c(1333) : error C2146: syntax error : missing ')' before identifier 'nKey'
sqlite3.c(1333) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1333) : error C2061: syntax error : identifier 'nKey'
sqlite3.c(1333) : error C2059: syntax error : ';'
sqlite3.c(1333) : error C2059: syntax error : ','
sqlite3.c(1335) : error C2059: syntax error : ')'
sqlite3.c(1341) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1341) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1341) : error C2143: syntax error : missing '{' before '*'
sqlite3.c(1341) : error C2059: syntax error : ')'
sqlite3.c(1347) : error C2081: 'sqlite3_int64' : name in formal parameter list illegal
sqlite3.c(1348) : error C2061: syntax error : identifier 'sqlite3BtreeGetCachedRowid'
sqlite3.c(1348) : error C2059: syntax error : ';'
sqlite3.c(1348) : error C2059: syntax error : 'type'
sqlite3.c(1364) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1364) : error C2081: 'i64' : name in formal parameter list illegal
sqlite3.c(1364) : error C2143: syntax error : missing '{' before '*'
sqlite3.c(1364) : error C2059: syntax error : ')'
6/14/2012 6:10 AM | # re: HOW TO: Use SQLite in C# Metro style app
GREAT post...very helpful. Any chance there is a VB solution to this as well?
6/15/2012 7:26 PM | # re: HOW TO: Use SQLite in C# Metro style app
Thanks Tim!

Everything works well in my end! Am going to make some more tests and give you a credit about the tutorial you made!

Best,
Jayson R.
6/15/2012 7:31 PM | # re: HOW TO: Use SQLite in C# Metro style app
I forgot to ask.

I have SQLite.cs and other classes under it, then SQLiteAsync.cs and some classes under it too.

Will it be possible or safe to put that into a separate class library?

Best,
J

will be trying in my end too..
6/19/2012 8:16 AM | # re: HOW TO: Use SQLite in C# Metro style app
I get the same errors as Daniel - how do you resolve this? Trying to use an older version of SQLite I am getting errors using sqlite-net trying to open SQLiteConnection so I really want to build a new one...
6/19/2012 8:57 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Daniel/Jenny - I'm not getting those compiler errors for SQLite. Are you using the latest source?
6/20/2012 7:50 AM | # re: HOW TO: Use SQLite in C# Metro style app
Yes I watched your video work perfectly... I got the latest source again this morning - here is an excerpt when I run nmake. I am running on a 64 bit Windows 8 release candidate - I am running in the x86 command prompt as administrator - any thoughts?

c:\sqlite>copy /Y sqlite3.h tsrc
1 file(s) copied.
del /Q tsrc\sqlite.h.in tsrc\parse.y
tclsh85 .\tool\vdbe-compress.tcl < tsrc\vdbe.c > vdbe.new
move vdbe.new tsrc\vdbe.c
1 file(s) moved.
echo > .target_source
tclsh85 .\tool\mksqlite3c.tcl
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.50522.1 for x86
Copyright (C) Microsoft Corporation. All rights reserved.

sqlite3.c
sqlite3.c(951) : error C2061: syntax error : identifier 'i64'
sqlite3.c(951) : error C2059: syntax error : ';'
sqlite3.c(952) : error C2061: syntax error : identifier 'u64'
sqlite3.c(952) : error C2059: syntax error : ';'
sqlite3.c(1227) : error C2143: syntax error : missing ')' before '*'
sqlite3.c(1227) : error C2143: syntax error : missing '{' before '*'
6/20/2012 8:37 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Jenny - unfortunately I'm not seeing that. However, the SQLite team released the binaries for WinRT just this past week. You can get them on www.SQLite.org in the Windows section.
6/20/2012 7:40 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim :
is the "nmake" a tool that I have to install?
I saw you use VS 2012 command prompt , could you tell me how to open this window?
I use Visual Studio Express 2012 RC for window8, and just find the "Package Manager Console" window.
Thanks for your help.
6/21/2012 5:36 AM | # re: HOW TO: Use SQLite in C# Metro style app
I've been using SQLite in .NET for a couple of years, back then I searched for a provider and I've been using http://www.devart.com/dotconnect/ It has a free edition for any use, if you want Entity Framework and so you have to pay. This sqlite-net looks nice too at first sight, though.
6/21/2012 9:50 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Mandy - from the start screen start typing "command" and you'll see the VS x86 command prompt (and other) options.
6/21/2012 9:52 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Xavier - it looks like dotConnect relies on ADO.NET which wouldn't work in Metro style apps FYI
Gravatar
6/21/2012 11:30 AM | # re: HOW TO: Use SQLite in C# Metro style app
Excuse me for my naivete; I am not sure if SQLite will work for ARM based tablets?

I visited http://www.sqlite.org/download.html and saw that there are 2 versions for WinRT - one for 32-bit x86 and one for 64-bit x64. I don't see a version specifically for ARM. Will one of these work with ARM?

Also, I am guessing I have to add both DLLs to my project; when creating a package for x86, I add the 32-bit x86 dll to the project; and while creating package for x64, I will add the 64-bit x64 dll and remove the 32-bit dll. Does this sound right?
6/21/2012 2:59 PM | # re: HOW TO: Use SQLite in C# Metro style app
@DK - yes you'd have to pick the specific architecture to add to your project.
Gravatar
6/21/2012 9:38 PM | # re: HOW TO: Use SQLite in C# Metro style app
But how about ARM?
6/21/2012 9:42 PM | # re: HOW TO: Use SQLite in C# Metro style app
@DK - for ARM you'll have to compile yourself for now.
6/22/2012 1:57 AM | # re: HOW TO: Use SQLite in C# Metro style app
I didn't know Tim, thanks. I haven't dabbled in Metro yet.
6/22/2012 4:32 AM | # re: HOW TO: Use SQLite in C# Metro style app
Good video its work fine and thanks
6/22/2012 8:53 AM | # re: HOW TO: Use SQLite in C# Metro style app
What are your recommendations for checking if the table you created already exists?
6/22/2012 9:31 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Shaun - if you use the sqlite-net library when you create a table it automatically does an IF NOT EXISTS. Otherwise you can just Execute command for IF EXISTS in normal SQL syntax
6/22/2012 11:18 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim

What if I want use the Sqlite functions directly from C# application,
I mean pay pass the Sqlite-net layer.
6/23/2012 3:50 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Saber - SQLite is a native component so you'd need to p/invoke some how. That is what sqlite-net is, a client library to access the functions.
Gravatar
6/28/2012 1:10 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Timheuer,

I have created sqliteDB using SQLite manager .
how to add this SQLite DB file through solution or any other process?
that time how to get path and how to retrieve data from database ?
please help to me.

Thanks
Rambabu
6/28/2012 1:15 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Ram - the database should be copied to the applications ApplicationData location using the Windows.Storage APIs. This will enable you to read/write from the app to that database.
Gravatar
6/28/2012 4:12 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thanks for your replaying .
but I have the problem there. I am adding like
right click on project(in solution flooder ) -->Add-->Existing item-->select my created SQLite file and right click on SQLite file and given build action content and copy to output option given copy always.
but I wrote these line of code .
string dbrootpath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
using (SQLiteConnection db = new SQLiteConnection(Path.Combine(dbrootpath, "Practicing.sqlite")))
{
List<person> _customerEntries = null;
// Constructor
List<person> tranlist = new List<person>();
_customerEntries = db.Query<person>("select * from Practicing");
foreach (person data in _customerEntries)
{
var key = data.Key;
var main = data.Mainmenu;
var name = data.Name;
var data1 = data.Data;
tranlist.Add(new person(Convert.ToInt32(key), main, name, data1));
}

}

this time it is created new SQLite file in local
how to i copy my SQLite file in local

please explain to me. please...
please explain detail..
6/28/2012 9:01 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Ram - I just did a quick post with a snippet: timheuer.com/...
Gravatar
6/28/2012 8:54 PM | # re: HOW TO: Use SQLite in C# Metro style app
thanks for replying ,

why not write like this using (SQLite.SQLiteConnection sql = new SQLite.SQLiteConnection("DB.sqlite")) ?
is it correct or not ?
but it is working fine
Gravatar
7/3/2012 4:13 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi timheuer
using (SQLite.SQLiteConnection sql = new SQLite.SQLiteConnection("DB.sqlite"))

when i am using this stmt. it is taking lot of time for execution

how to i resolve this issues please help to me.

Thanks
Ram
7/3/2012 9:17 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Ram - The path should be the Windows.Storage.ApplicationData.Current.LocalFolder starting point in a Metro style app
7/4/2012 11:22 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hello Tim,
at the point, when i want to create the sqlite3.dll i'm getting this errors:

sqlite3.c
sqlite3.c(60051) : error C2065: 'TK_REGISTER' : undeclared identifier
sqlite3.c(60058) : error C2065: 'TK_UMINUS' : undeclared identifier
sqlite3.c(60058) : error C2065: 'TK_INTEGER' : undeclared identifier
sqlite3.c(60058) : error C2065: 'TK_FLOAT' : undeclared identifier
sqlite3.c(60065) : error C2065: 'TK_STRING' : undeclared identifier
sqlite3.c(60065) : error C2065: 'TK_FLOAT' : undeclared identifier
sqlite3.c(60065) : error C2065: 'TK_INTEGER' : undeclared identifier
sqlite3.c(60074) : error C2065: 'TK_FLOAT' : undeclared identifier
sqlite3.c(60076) : error C2065: 'TK_INTEGER' : undeclared identifier
sqlite3.c(60076) : error C2065: 'TK_FLOAT' : undeclared identifier
sqlite3.c(60085) : error C2065: 'TK_UMINUS' : undeclared identifier
sqlite3.c(60099) : error C2065: 'TK_NULL' : undeclared identifier
sqlite3.c(60104) : error C2065: 'TK_BLOB' : undeclared identifier
sqlite3.c(64759) : error C2065: 'TK_VARIABLE' : undeclared identifier
sqlite3.c(66423) : error C2196: case value '0' already used
sqlite3.c(66697) : error C2196: case value '0' already used
sqlite3.c(66767) : error C2196: case value '0' already used
sqlite3.c(66768) : error C2196: case value '0' already used
.....

sqlite3.c(76248) : fatal error C1003: error count exceeds 100; stopping compilat
ion
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual Studio 11.0
\VC\BIN\cl.exe"' : return code '0x2'
Stop.

C:\sqlite>

sorry for the long error code! can you help me?
7/4/2012 11:25 AM | # re: HOW TO: Use SQLite in C# Metro style app
i copied the "fossil.exe" and the content of the "UnxUpdates" directory to the created sqlite directory. Right?
7/5/2012 11:32 AM | # re: HOW TO: Use SQLite in C# Metro style app
@jannemann - you can copy them wherever you'd like but they need to be in your PATH environment variable, otherwise the commands will assume they are where it is being run (and may not be correct).
7/8/2012 8:56 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thanx for your posts about the installation of sqlite. I followed everything & use it now for my project. I found an error in sqllite.cs. In the insert function, the return value is count, but in the doc, it must be the key if there is one. I believe you are not the one who can correct in the source code, but maybe you know him? (I'll correct locally, hoping, hoping it'll be rigth in the next version.

Have a nice day

public int Insert (object obj, string extra, Type objType)
{
if (obj == null || objType == null) {
return 0;
}
var map = GetMapping (objType);
var cols = map.InsertColumns;
var vals = new object[cols.Length];
for (var i = 0; i < vals.Length; i++) {
vals [i] = cols [i].GetValue (obj);
}
var insertCmd = map.GetInsertCommand (this, extra);
var count = insertCmd.ExecuteNonQuery (vals);
if (map.HasAutoIncPK) {
var id = SQLite3.LastInsertRowid (Handle);
map.SetAutoIncPK (obj, id);
}

return count;
}
7/13/2012 5:00 AM | # re: HOW TO: Use SQLite in C# Metro style app
hello I wonder how I can copy an existing database into a local data storage, for example, to copy it from the Assets, or how I can specify the path to an external database file???
Thanks in advance for your help.
7/13/2012 12:06 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Sergi - see timheuer.com/...
7/18/2012 9:24 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hey Tim,

Thanks for the great getting started video.

Is there a way to bind a SQLite column to a field with a different name?

For example

Table1
-Foo
-Bar

could be bound to a model which looks like

Student
-Name
-Age

I'm dealing with an existing SQLite Db that I want to package with my app, but I don't want to be stuck with the existing column names.

Thanks!
7/18/2012 9:45 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Mike - I believe that is possible but you'd have to rely on whatever client library you use to accomplish this for you.
7/23/2012 2:09 AM | #  NMAKE : fatal error U1073: don't know how to make 'sqllite3.dll'
after applying the other steps using the VS command (ALL)
The Nmake Step is returning this error:

NMAKE : fatal error U1073: don't know how to make 'sqllite3.dll'
why is that? am i missing something?
Thanks
Yoav
7/23/2012 8:38 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Yoav - sounds like you aren't issuing the command with either the full source open or in the right directory.
Gravatar
7/31/2012 11:43 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thank you for your various articles and this tutorial on this subject. They seem to be the only current ones on this subject out there. For us Metro newbies just starting to dip into that world, it was a real eye-opener to find that all of our previous database ease in application development is now out the window, and we have to venture into this new third-party territory. I now have to learn not only how to work with SQLite for the first time, but also have to learn C# (It doesn't look like these methods work with VB). No problem. I seem to have managed to get all the prerequisites installed and even went as far as building a SQLite db, including new tables populated with data from a previous project that I'm trying to migrate to Metro. My question is how do I access those existing tables? Your example creates a new table on the fly, but I would like to access my existing table. What is the code to get a list box to display items from a table already in the .db file? If you have some time to quickly explain that to me it would be much appreciated.
7/31/2012 4:33 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Jim - you should be able to compile a library that works from VB as well if you want... For existing databases, take a look at my seeding post timheuer.com/...
Gravatar
8/1/2012 8:14 AM | # re: HOW TO: Use SQLite in C# Metro style app
I thank you for your quick response. I'll take a look at your article and try to apply it to my scenario. As for the VB issue, I guess I'm not quite sure what you mean by "compile a library that works from VB". I followed your tutorial on each a C# and VB project (including using all the same "Imports"/"using"), and in the C# project the SQLite library and references dropped right in and were instantly picked up in the AutoFill, but in VB I can't them recognized. Does the issue at all relate to the 2 cs files that the Nuget SQLite-net extensions placed into the project? Do those have to be converted to VB? Again any help would be appreciated. If not, I'll keep going with C#. Nothing wrong with trying to learn a new language.
8/1/2012 11:32 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Jim - what I meant was that if you want your app to be in VB, then just first create a C# class library to add the SQLite-net stuff to it, compile that and add a reference to that in your VB app...basically just using the compiled version of the lib instead of the source version. Or yeah, learn C# to learn something new!
Gravatar
8/1/2012 12:45 PM | # re: HOW TO: Use SQLite in C# Metro style app
OK. I get what you mean now. I'll give a try. Thanks
8/9/2012 12:35 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,

Great post, thanks..

Just wanted to confirm using SQLite in C# metro style app passes windows store certification?

Thanks
Sandy
8/13/2012 7:38 AM | # re: HOW TO: Use SQLite in C# Metro style app
Do any of you guys know if there is any way to generate C# classes from the SQLite database in Window 8 WinRT, or how to work with design-first approach (if I already have SQLite database I want to use in my Metro project)? Something similar to SQLmetal (for WP7 + SQL CE)? Tutorial on this topic would be nice!
8/30/2012 10:44 AM | # re: HOW TO: Use SQLite in C# Metro style app
Getting compilation error -
nmake -f makefile.msc sqlite3.dll FOR WINRT=1 OPTS=DWAPI_FAMILY=WINAPI_PARTITION_APP

del /Q trc\sqlite.h.in tsrc\parse.y
'tclsh85' is not recognized as an internal or external command,
operable program or batch file.
NMAKE : fatal error U1077: 'tclsh85' " return code '0x1
Stop


8/30/2012 4:59 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Rahs - there is no need to compile your own version now unless you are modifying the source -- their download site has the compiled bits now. But the error to me seems to indicate you didn't download the tools as a prerequisite
9/3/2012 7:18 PM | # re: HOW TO: Use SQLite in C# Metro style app
Thank Tim,
Good video its work but can you help me make a video about build sqlite for ARM ? I can't compile in ARM
or Can you share dll file in ARM... ? I think it's perfect
9/3/2012 8:36 PM | # re: HOW TO: Use SQLite in C# Metro style app
This is a great video and is very helpful. One question though:

How do I setup the solution file such that it picks the correct DLL (x86,64,arm) depending upon my selection in the build configuration popover? Is it possible to dynamically load the correct DLL? Finally, will this mean that I need to make 3 different submissions to the Windows Store, one for each platform?

9/3/2012 10:42 PM | # re: HOW TO: Use SQLite in C# Metro style app
@tuandq/@ken grant - please see the updated post: timheuer.com/... where there is a new installer that makes life a bit easier.
9/5/2012 9:12 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi, Can you advise how i go about amending a row in a database with new information by code as i am having problems. basically i have a key of WOT_ID if this is the same as another value then WOT_Rating = a new value.

I am quite new to this so apologies
9/5/2012 10:18 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thank you so much Tim! :)
Some how could not compile even after having all the tools.
So took the Compiled one for x64, which is available now.
Example worked out great with compiled version!
But Ithere is no compile one for ARM.... any ideas.
Should I work on VS 2012 ARM Command Prompt?
Whaat else would I need?
9/5/2012 10:21 AM | # re: HOW TO: Use SQLite in C# Metro style app
@rahs - the latest download of the VSIX has the ARM build in it.
9/5/2012 12:13 PM | # re: HOW TO: Use SQLite in C# Metro style app
Thank you Tim!
Got it! Great! Thank you for the great POST!
9/11/2012 1:20 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi,

Your article and things are great but I have one question related to security. As per you say, if we put or .sqlite file in the local path then it is accessible by all.

Now suppose i submit my app in windows metro store and it gets live. When user install the app in their pc, then will not they able to update my database directly or am i missing the flow and understanding?

I tried to resolve this by putting my db and my project folder but then when i run the project, it gives me read only error so i need to update the read/write settings in "\bin\Debug\AppX" then only my db works. Now i do not expect user to update this settings. Please help me understand this.

Thanks.
David
Gravatar
9/11/2012 7:26 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,
Your video helps a lot. But i'm not able to get the two files which you used, i mean Fossil and another one, from your sky drive location. Can you please check whether the skydrive link is still active or can you please get me the direct links from which i can download them ?

Thanks
-Ram
Gravatar
9/11/2012 7:59 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,
I'm able get things done by referring your update for this post @ timheuer.com/.... Please ignore my previous question.

Thanks
-Ram
9/11/2012 8:19 AM | # re: HOW TO: Use SQLite in C# Metro style app
@David -- in order to *write* to the database it would have to be in one of the appdata locations (Windows.Storage.ApplicationData.Current.LocalFolder)
9/12/2012 4:44 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thanks tim. I did like that way and it's working cool. However, i am just worried about security. What if someone directly break the security and update this db?

It would hurt my objective if someone break to the db.

Any advice?

David
9/19/2012 6:59 AM | # re: HOW TO: Use SQLite in C# Metro style app
Thanks the tutorial. It helped me a lot.:)
Gravatar
9/23/2012 11:17 PM | # re: HOW TO: Use SQLite in C# 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

Thanks
Rambabu
Gravatar
10/3/2012 12:55 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,

please give answer for above question.

Thanks
Rambabu N
10/8/2012 2:30 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hai friend,

Am getting an error at: sqlite>nmake -f Makefile.msc sqlite3.dll FOR_WINRT=1

'tclsh85' is not recognized as an internal or external command,
operable program or batch file.
NMAKE : fatal error U1077: 'tclsh85' : return code '0x1'
Stop.
10/8/2012 7:49 AM | # re: HOW TO: Use SQLite in C# Metro style app
@sivavarri - you don't need to build your own code anymore. See timheuer.com/.... But your error is because you don't have all the tools installed to build it.
11/6/2012 2:09 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,

Have you tried running this sample on a tablet, using Remote Debugging? My discovery is that this method of P/Invoking fails with a DLLNotFoundException.

Can you verify this?
11/6/2012 2:48 PM | # re: HOW TO: Use SQLite in C# Metro style app
please make sure you are using the very latest from SQLite -- this issue is related to the remote device not having the debug version of the C++ runtime. If you build release and do it, it will likely work. The latest version of the SQLite VSIX package has a fix for this.
11/13/2012 1:12 AM | # re: HOW TO: Use SQLite in C# Metro style app
Tim, thank you for your post.
For those of you who got the error started with:
"sqlite3.c
sqlite3.c(944) : error C2061: syntax error : identifier 'i64'
sqlite3.c(944) : error C2059: syntax error : ';'
sqlite3.c(945) : error C2061: syntax error : identifier 'u64'
"
Before executing of "nmake -f makefile.msc sqlite3.dll FOR_WINRT=1"
You should install Tcl for Windows: http://www.activestate.com/activetcl/downloads (8.5 or higher)
And and it to your PATH

After that clean the C:\sqlite directory and make the steps described in Tim's video
11/15/2012 3:28 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hello Tim,

Thanks for such amazing tutorials - as well as your whole blog in general.
I just wonder; should I use ARM/x86/x64 in order to reach most people with my Modern UI app? I assume ARM apps can be ran at all the different architectures, or am I completely wrong? I really want to use SQLite in my app(s), but I don't want to exclude a group of people because they can't run my app on their tablet/computer.

Regards!
1/3/2013 10:00 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,

Will able to use LINQ to SQL database in Windows 8 metro apps.
1/20/2013 2:57 AM | # re: HOW TO: Use SQLite in C# Metro style app
while adding the reference to the project i get an error namely

"A reference to 'C:/sqlite/sqlite3.dll' could not be added. Please make sure that the file is accessible, and that is a valid assembly or COM Component. "
2/11/2013 1:55 AM | # re: HOW TO: Use SQLite in C# Metro style app
@Tim
I have downloaded the two files(i.e.,fossil n UnxUpdates).I ve also created the path variable...but in cmd its displaying the error (fossil is not recognised as an internal or external command)...help me to get out of here...
2/28/2013 2:10 AM | # re: HOW TO: Use SQLite in C# Metro style app
could you please explain detailed steps for javascript app to use sqlite...........please i am struggling a lot for that...........
3/1/2013 12:38 AM | # re: HOW TO: Use SQLite in C# Metro style app
First: great tutorial, helped me.
Second: Is there a way to define foreign keys in the class itself?
4/6/2013 2:26 AM | # re: HOW TO: Use SQLite in C# Metro style app
hello! Why when I try to run the project the error, "no such table Person"  and does not create a database???
5/28/2013 12:38 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hi,
Great tutorial but i have little problem. When i'm trying create new connection to database: new SQLiteConnection(path) "path the same which you posted in your tutorial" I have an exceprion entrypointnotfoundexception :"In DLL file 'sqlite3 could not find entry point 'sqlite3_winrt32_set_directoru'. Someone had this problem?

5/28/2013 1:19 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Norbert - are you using the latest version of SQLite runtime itself? This was a new export API for them to support WinRT apps.
6/1/2013 1:26 AM | # re: HOW TO: Use SQLite in C# Metro style app
Could you teach me How to used sqlite with C, but not C# for win8 metro style app? I used sqlite version 3.7.17, but it come to many problem. I want to used sqlite-WinRT-3071700, but I didn't know how to used it.
6/1/2013 4:30 PM | # re: HOW TO: Use SQLite in C# Metro style app
@gumc - if you want to use it with C, just use the APIs that SQLite provides...no need for a wrapper...just #import sqlite3.h
6/3/2013 11:51 AM | # re: HOW TO: Use SQLite in C# Metro style app
HI, I downloaded the latest version SQLite. But just in case i execute all steps again
6/3/2013 1:22 PM | # re: HOW TO: Use SQLite in C# Metro style app
This is my solution of my problem (xD).

var connectionString = new SQLiteConnectionString(ss,false);
var connectionPool = new SQLiteConnectionPool();

using (var connection = connectionPool.GetConnection(connectionString))
{
connection.CreateTable<Person>();
connection.Insert(new Person(){ss = "aa",Type = "xx"});
}

It works for me but I do not know whether it is a good idea, I need test it.
7/10/2013 2:56 PM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim,

First of all, congratulations for your great tutorial :)

In my case, I need to add full text search functionality to the library (-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS) so the new extension (timheuer.com/...) works well but is not suitable for my program (no rank function, etc).

When I compile following your stepsin the video (not using FTS, for ensure it's not the problem) everything goes well and I compile succesfully the library with the x86 command prompt. But when I execute my windows store app, I get the following error when I try to connect to the database:

_________________________
System.EntryPointNotFoundException: Can't found 'sqlite3_win32_set_directory' entry point in the 'sqlite3' DLL file.
StackTrace:
at SQLite.SQLite3.SetDirectory(UInt32 directoryType, String directoryPath)
at SQLite.SQLiteConnection..ctor(String databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks) in c:\Users\Pedro\Documents\Visual Studio 2012\Projects\PruebaSQLite\PruebaSQLite\SQLite.cs:line 136
________________________

My program is configured to compile for x86 (as you show in your video).

What I'm doing wrong?

Thank you very much (and sorry for my bad english :p)
7/12/2013 3:11 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hey Tim, my fault :)

I see that the extension (timheuer.com/...) supports FTS (I can query with MATCH).

Rank is a user-defined function used in the examples. So I have to figure how to register C# (or C) functions to be used on queries. Do you have any clue?

Thank you anyway :)
8/7/2013 3:48 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hello Tim,

Nice post, really helped me for compiling SQLite for x86 environment.

But I was unable to run the App with SQLite in Microsoft Surface Windows 8 RT (i.e. ARM environment).

Can you please help to compile dll for ARM processor.


Thanks,
Chirag
12/2/2013 6:14 AM | # re: HOW TO: Use SQLite in C# Metro style app
While executing "fossil checkout winrt" i get "current directory is not within an open checkout". I couldn't find any help regarding this issue over the internet.
12/2/2013 5:48 PM | # re: HOW TO: Use SQLite in C# Metro style app
@Vinay - you need to 'open' the branch first. FYI though, the Windows Runtime versions are released and available on the SQLite.org/download.html website now, no need to build yourself anymore really.
12/3/2013 4:59 PM | # re: HOW TO: Use SQLite in C# Metro style app
Great help, Tim. Thanks for the post.
2/24/2014 2:04 AM | # re: HOW TO: Use SQLite in C# Metro style app
Hi Tim, You can make another tutorials how to add custom functions to Lib ex : radians,cos,sin ... etc ?
6/6/2014 5:51 AM | # re: HOW TO: Use SQLite in C# Metro style app
Did some try to build SQLite for Windows Phone? I've tried to do that according to Nokia's article (developer.nokia.com/...), but had some linker errors:
sqlite3.lo : error LNK2019: unresolved external symbol sqlite3CodecGetKey referenced in function sqlite3RunVacuum sqlite3.lo : error LNK2019: unresolved external symbol sqlite3CodecAttach referenced in function attachFunc sqlite3.lo : error LNK2019: unresolved external symbol sqlite3_key_v2 referenced in function sqlite3Pragma sqlite3.lo : error LNK2019: unresolved external symbol sqlite3_rekey_v2 referenced in function sqlite3Pragma sqlite3.lo : error LNK2019: unresolved external symbol sqlite3_activate_see referenced in function sqlite3Pragma
sqlite3.dll : fatal error LNK1120: 5 unresolved externals

 
Please add 3 and 6 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.