Using SQLite in your Windows 8 Metro style applications

As a little surprise for developers, Windows 8 doesn’t come out with native database support. In the past months some SQLite portings came out, but none of the them was working really good. After the Windows Phone Summit (where Microsoft announced Windows Phone 8), the situation started to be more clear: SQLite will be the database officialy supported by Microsoft and SQLite will be officially released both for Windows 8 and Windows Phone 8. This way it’s likely to think that, as developers, we will be able to share not only the database but also the data access layer between the two platform: this will help us a lot porting our apps from one platform to the other.

Now SQLite’s branch for WinRT is available and we can start to use it to store the data of our applications, with the help of another library called sqlite-net, that provides LINQ based APIs to work with data, both with sync and async support.

In this post we’ll see how to start using SQLite in a XAML / C# application, how to create our first database and how to make some simple operations, like storing and reading data.

Adding SQLite to our Metro style app

The first step is to download from the official SQLite website the WinRT version: be careful that, since it’s a natice code library, you should get the specific version for the platform you’re going to support (x86 or x64). ARM is still not supported, but I expect it to be released soon.

For this example I’ve downloaded, from the Download page, the x86 version, which file name is  sqlite-dll-winrt-x86-3071300.zip

After you’ve downloaded it, you’ll have to extract the content somewhere and add the file sqlite3.dll to your project: be careful that, since the DLL it’s a native library that contains the SQLite engine (so it doesn’t provide any API to interact with it), you’ll simply have to copy it in the root of your project and make sure that the Build Action is set to Content.

Now that you have the engine you need something to interact with it: please welcome sqlite-net, a library available on NuGet and that supports WinRT, that provides data access APIs to interact with the database, using LINQ-based syntax.

Adding it is very simple: just right click on your project, choose Manage NuGet packages, search online for the package with name sqlite-net and install it. The package will add two classes in your project: SQLIite.cs (that provides sync access) and SQLIteAsync.cs (that provides, instead, asynchronous operations to interact with the database).

Now you’re ready to create your first database.

Create the database

The sqlite-net approach should be familiar to you if you’re a Windows Phone developer and you have already worked with SQL CE and native database support: we’ll have a class for every table that we want to create and we’re going to decorate our properties with some attributes, that will tell to the library how to generate them. We’ll use for this example a very simple class, that can be used to store a list of persons:

public class Person
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [MaxLength(30)]
    public string Name { get; set; }

    [MaxLength(30)]
    public string Surname { get; set; }

}

In this example you can see some of the simple attributes you can use to decorate your classes:

  • PrimaryKey is used to specify that the column will be the primary key of the table.
  • AutoIncrement usually is used in couple with a primary key; when this option is enabled the value of this column will be a number that will be automatically incremented every time a new row is inserted in the table.
  • MaxLength can be used with string properties to specify the maximum number of chars that will be stored in the column.

After you’ve defined the tables, it’s time to create the database. Since WinRT relies as much as possible on an asynchrnous pattern, we’ll use the async version of sqlite-net.

private async void CreateDatabase()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
    await conn.CreateTableAsync<Person>();
}

The first step is to create a SQLiteAsynConnection object, that identifies the connection to the database, like in the example: the parameter passed to the constructor is the name of the file that will be created in the local storage. Then we call the CreateTableAsync<T> method for every table that we want to create, where T is the type of data that we’re going to store in it (in the example, every row will be an element of the Person class). Notice that the method returns a Task, so we can use the keyword await to perform the operation asyncrhonously.

Play with the data

Now that we have a database, we can have some fun by adding and reading some data. Both operations arew very simple and, in both case, we’ll need a SQLiteAsyncConnection object that points to the same database.

To insert data we use the InsertAsync method, that simply accepts as parameter an istance of the object we’re going to save. Obviously, the object’s type should match the table’s type. Here is an example:

private async void Button_Click_1(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");

    Person person = new Person
    {
        Name = "Matteo",
        Surname = "Pagani"
    };

    await conn.InsertAsync(person);
}

To query the data, instead, we can access directly to the table using the Table<T> object: it supports LINQ queries, so we can simply use LINQ to search for the data we need. Then, we can call the ToListAsync method to get a List<T> of objects that matches the specified query. In the following example we look in the table Person all the users which name is Matteo and we print the results in the Output Window.

private async void Button_Click_2(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");

    var query = conn.Table<Person>().Where(x => x.Name == "Matteo");
    var result = await query.ToListAsync();
    foreach (var item in result)
    {
        Debug.WriteLine(string.Format("{0}: {1} {2}", item.Id, item.Name, item.Surname));
    }
}

Where is my data?

If you want to take a look at your data, you can access to the path where Windows 8 stores the local storage of application. To find it, simply get the value of the property Windows.Storage.ApplicationData.Current.LocalFolder.Path. Once you have it, you’ll find in that folder a file with the same name that you’ve specified as parameter when you’ve created the SQLiteAsyncConnection object. If you want to open it, I suggest you to download and install an utility called SQLite Database Browser, that you can find at the website http://sqlitebrowser.sourceforge.net/.

With this utility you can open the database and explore it: you can see the tables, query the data and so on. Have fun!

Update: here you can download a sample project

This entry was posted in Windows 8 and tagged , , . Bookmark the permalink.

213 Responses to Using SQLite in your Windows 8 Metro style applications

  1. Alexei Vinidiktov says:

    Thanks for the write-up, Matteo! I’ve managed to successfully repeat all the steps in your tutorial.

  2. Jenny says:

    What is the syntax for RunInTransactionAsync? This works for RunInTransaction but needs Action for Async – cannot work out what it is expecting?

    no good:
    mydb.RunInTransactionAsync(() =>
    {
    mydb.InsertAsync(new ACCOUNTS() { account_num = “12345”}

    Thanks!

    • Hi Jenny,
      the RunInTransactionAsync methods requires a parameter of type Action, which is the object you are going to use to interact with the database in your anonymous method.
      Here is an example:

      SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);

      await conn.RunInTransactionAsync(async (connection) =>
      {
      Person person = new Person
      {
      Name = “Matteo”,
      Surname = “Pagani”
      };

      await connection.InsertAsync(person);
      });

      I hope this helps!
      Cheers

  3. Waqas Siddique says:

    Thanks for the write-up, I thought I have to use only WebAPI to access data in metro-app,, very helpful!

  4. Shameer says:

    Really awesome blog. Thank u so much dude. Everything works well as i expected.

    Just having 1 question .If i use sqllite in this way ,will the app pass WACK tets for the appstore ?

  5. dancerjude says:

    Great article Matteo! Thank you for sharing with us.
    I have a question for you: I’m beginning porting of a Windows Phone 7 app to Windows 8, and where I was using a SQL CE 3.5 database, now I will use a SQLite db, so I will try to convert it.
    Once I have a SQLite db ready to use, how can I import it in the “Windows.Storage.ApplicationData.Current.LocalFolder” and how can I map it with LINQ to create an ORM layer ?
    TIA

  6. Jean Paul says:

    Thanks! Very useful!!!

  7. Sadaf says:

    When we publish an app that uses an SQLite database will other users be able to read information from the database from their own computers?
    I mean to say how accessible will the SQLite database be to other users?

  8. Sam says:

    Thanks..nice writing!!!!
    In my case i want to insert some default entries to the table at the time of db creation. How can i determine whether the db is already created or not?Because i dont want insert default entries every time user run the app.

    thanks in advance

    • You can check if the database file is already existing or not in the local storage. You’re going to insert default data only if the file is not existing:

      private async void CreateDatabase()
      {
      bool isDatabaseExisting = false;

      try
      {
      StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("people");
      isDatabaseExisting = true;
      }
      catch
      {
      isDatabaseExisting = false;
      }

      if (!isDatabaseExisting)
      {
      SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
      await conn.CreateTableAsync();
      }
      }

  9. Miro says:

    Hi,

    I have a problem. I am getting exception An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) during the CreateDatabase method. How can I solve this problem? thx

    • I’ve never experienced that issue. Can you try sending me a sample project with your problem? Contact me using the contact form and I’ll reply to you with my mail address.

    • kidroid says:

      can i use sync functions?
      i try to create MVVM structure, and i get data use TolistAsync() procedure in model view, but, i can’t dispaly data on screen.

      • The problem you’re experiencing probably happens because the ToListAsync() method returns a generic list, not an ObservableCollection. For this reason, the controls that are in binding with the collection aren’t automatically updated every time something changes in the collection.
        Try to convert the result of the ToListAsync() method into an ObservableCollection
        and bind the new collection to the control.

        Cheers

      • khalid says:

        Hi Matteo, i followed your step but facing exception.”An exception of ‘SQLite.SQLiteException’ occured in Apps_Name.DLL but was not handle in user code”.Please help me to solve this issue.

        • qmatteoq says:

          Hi Khalid, the message isn’t helping me to find what’s wrong. Have you installed the Visual Studio extension? Have you downloaded the latest sqlite-net package from NuGet? Otherwise, if you can send me your code I will take a look.

  10. atomaras says:

    Miro i had the same error and i solved it by referencing the sqlite dll from the main project (App) instead of a class lib.

  11. Craig says:

    Hi,

    I need your help 🙂 I have a problem when I generate my project : “SQLite.SQLiteException Could not open database file: C:UsersClémentAppDataLocalPackagescc121a7a-eac7-4655-8163-98ce14222e78_vjm86x7agcmvtLocalStatepeople (CannotOpen)”.
    However, I have follow all your instructions.

    Have you got an idea ?

  12. Hi Matteo, thanks for the really useful example.
    I was asking me if and how it is possible to make joins in the db using linq; I would be interested in using

    var query = conn.Table with multiple related tables … is it possible?

    Thanks and keep up the great work !
    Ciao !

  13. mushroom says:

    Thank you very much, very helpful to me

  14. Tiziano says:

    Grazie mille! Ora sono operativo 🙂

  15. vivekcek says:

    Can u share sample source code I am getting an exception

  16. Oscar says:

    Matteo, great post, I am new in sqllite, and new in windows 8, but does sqllite support image datatype like the SQL CE?

  17. Farhan says:

    How can I use foreign key constrain in SQLite for WinRT ? Can you please demo that scenario ?

    • Hi Farhan,
      I’ll investigate a bit and I’ll try to find the time to write a separate blog post. Actually, the operation is not really straightforward because sqlite-net doesn’t have built in support for foreign keys and relations, so you can’t do Person.Orders, for example, like you would do with LINQ to SQL or Entity Framework.

  18. Mani says:

    I have followed your tutorial in creating my app. Now I’m stuck with the fetching of images from sqlite.
    Kindly help me with that.

  19. Farhan says:

    “await conn.InsertAsync(person)” returns always 1, how can I get the PersonID as return value ? In method summery it’s written that it returns auto incremented primary key if it has one, but I am not getting.

  20. suman says:

    Hi,

    Anybody tried with DeleteAsync() or UpdateAsync() APIs. is it working for anyone. Though I didn’t get any exception while using this API, but the data inside the database is not deleted.Anybody has solution for this problem?

    Thanks & Regards,
    Suman

  21. kartheek says:

    hi i am facing problem with await conn.InsertAsync(People);
    it is giving exception “the await operator can only be used with in async method.

  22. Farhan says:

    How to store DateTime in SQLite database ? According to http://www.sqlite.org/datatype3.html, “SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values”, then what should me the data type of property in class (table) file ? String data type generates varchar(140) in SQLite

  23. Farhan says:

    And one more thing, is it possible to store blob data type data in SQLite ?

  24. Volker says:

    Every Time i Change Package.appxmanifest this delete my datbase?
    how can i Change this?

    this is the function for the init of my database…
    private async void InitDatabase()
    {
    bool isDatabaseExisting = false;
    try
    {
    StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(“Firmen.SQLite”);
    isDatabaseExisting = true;
    }
    catch
    {
    isDatabaseExisting = false;
    }
    if (!isDatabaseExisting) {
    await db.CreateTableAsync();
    MessageDialog dialog = new MessageDialog(“Die Datenbank wurde neu angelegt! Bitte das Programm neu starten!”, “Programm Hinweis”);
    dialog.ShowAsync();
    }
    }

  25. Nitin says:

    How can I execute sql query without knowing table structure? I mean, we don’t know what is the client will pass. They will just give the query and we have to execute.

    for example:

    “CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT)”;
    OR
    “CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT, phone REAL)”;
    OR
    “CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT, add TEXT)”;

    Any sql query they can ask, is it possible in metro app?

  26. Scott Nimrod says:

    I am not able to create a database using SQLite with my Metro application.

    Description:
    I continue to receive a runtime exception on a failure to load SQLite3.dll.

    Observation:
    I observed that the database directory never gets created in the app package’s LocalState directory. After observing this, I literally implemented file path creation at runtime and dropped the sqlite3.dll is in the intended location.

    Yet, I still receive an exception on failed to load SQLite3.dll.
    1.My CRUD code that leverages SQLite compiles fine.
    2.SQLite dependencies (packages.config, SQLite.cs, and sqlite3.dll) are located in a separate metro class library instead of the application’s executable project.
    3.The sqlite3.dll is based on the ARM processor.
    4.The sqlite3.dll is set to Content, Copy Always.

    Code:
    SQLiteAsyncConnection connection = new SQLiteAsyncConnection(DATABASE_DIRECTORY_NAME);
    return await connection.CreateTableAsync();

    Please help…

  27. Mesut Güngör says:

    We have create our database externally and when we want to connect to the database it gives error. Should we have to create the database at runtime ? because the sample code you have sent works if we click to the button create database and insert data then we can read otherwise it gives there is no such table.

  28. Peter Naldql says:

    I can’t make sqlite-net work on Windows Phone 8. The namespace Community.CsharpSqlite is not found. Do you have a solution to that or an alternative way to access SQLite on WP8?

    • At the moment I’m not aware of a LINQ library, like sqlite-net, that works with Windows Phone 8. As soon as I find some information I’ll surely blog about it, the topic is really interesting also for me.

  29. Renuka says:

    Hi,

    In your article you have mentioned that, we have to install sqlite-net by right clicking on the project.

    Do we have to follow the same process for all new Windows Store app we are creating?

  30. Dikshit Gokhale says:

    Your post was very helpful. Thank you.
    Just had 2 questions:
    1)How to specify foreign key relationships between tables.. I came across Sqlite.Indexed attribute which does the same thing but it doesn’t specify which table it indexes..
    2)Can we use the same for an WPF app??

    • 1) I’m working on a blog post about using relationships, I’ll cover this topic very soon 🙂
      2) No, the same code won’t work, since it’s based on WinRT APIs, that are available only for Windows Store apps for Windows 8.

      • Dikshit Gokhale says:

        All right.. Actually I am building an app which has the database tables without relationships.. Please write the blog on that soon:) Thank you..

  31. shamir says:

    heeloo
    can u help me with the foreign key ?
    or if i can , can u give me how to join or make a view in linq c# ?
    its my first time use linq
    hihihi
    thanks

    • Actually sqlite-net is a bit limited, compared to other LINQ to SQL implementations, so you can’t use it to make joins. I’m waiting for new and better library to come to improve sqlite support in Windows 8.

  32. shamir says:

    btw , where i can find the library of sqlite-net ?
    i am so confuse with linq
    hihi
    please give me some information
    thanks

    • It’s described in the post: you have to install sqlite-net using NuGet package manager, that you can open by right clicking on the project, choosing Manage Nuget packages and looking for sqlite-net using the search box.

  33. shamir says:

    Hello sir ,
    in Your code , ehm , how i can know the data already sucessfully inserted ?
    maybe some try catch or ?
    please help me sir

  34. yashavantha says:

    Hi Matteo
    I have some questions for you.

    *Can we have many tables in one db?
    *how to create table?
    *how to execute sqlite command?
    Thanks , please reply

    • Hi,
      yes, you can have many tables in one single db.
      For the other questions, please refer to the post: it’s described both how to create tables and how to do operations with them.

      Cheers

  35. wiklef says:

    I have downloaded your example. I have still message when I press any button:

    “Could not open database file: C:UsersMichałAppDataLocalPackagese12bc76c-4f76-4198-8ed7-a6b0d8828dd3_e7ma2tg35b58yLocalStatepeople.db (14)”

    • Have you already created it? If you don’t find a way to fix the issue, try to send me a sample project using the contact form and I’ll take a look.

      • wiklef says:

        But I have this message when I press button which should create a DB for me. So this msg is possible when I try to add something, not when I am creating a DB, yes?
        I will send you my code to see how it works in my project. Thanks 🙂

  36. piter leon says:

    hi matteo, i wanna to ask something important for my project. how to insert data from textbox to database?

    • Hi, the code is exactly the same you saw in the post, the only difference is that, to populate the object data to store in the database, you access to the Text property of the textboxes.
      For example, if you have two textboxes, one for the name and one for the surname, you can do something like:

      Person person = new Person();
      person.Name = txtName.Text;
      person.Surname = txtSurname.Text;

      After that you proceed to store the Person object like you’ve seen in the post.

  37. David says:

    Thank you for the code example. It explained a lot. I was looking for something I could use across platforms and this is it.

  38. piter leon says:

    sorry before cause i’am noob in sqlite. Thankyou for your reply before, now i want to read my data from database but there still an error occuring.i use textbox to read data..

    this is my code:

    private async void LoginUser(object sender, RoutedEventArgs e)
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“UserDatabase.sqlite”);

    var query = conn.Table().Where(x => x.Username == InUsername.Text);
    var result = await query.ToListAsync();}

  39. nkchandra says:

    How to create two tables of type Person in the db ?

    db.CreateTable(); // 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 for ex) ? 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 ?

    • qmatteoq says:

      You can’t, unless you give to the entity that is used to map the table a different name (for example, Person2). In this case you’ll have two tables, Person and Person2, with the same fields.
      By the way, I’ll suggest you to think twice about this architecture, having the same table “duplicated” with different names isn’t a good practice.

      • nkchandra says:

        Thanks for the info. I have 2 ListBoxes each contains different data, but of same type. So, what do you do to store data instead of having two different tables of sametype ? Give me some idea

        • qmatteoq says:

          You can add another column to the table to use as a filter. I’m guessing that you have the same type of data (the Person) but that belong to different categories (like, for example, the gendre). Use that field to filter the data to display the in the two ListBoxes (for example, one with just men and one with just women).

  40. Andreas says:

    Hello.

    How would one update their name, without using the InsertAsync? As in, user inputs name into text field -> text field.text replaces the name in the database.

    Thanks!

  41. Amol says:

    Hi, I got all the things you have mentioned now I want to provide password protection to my database file how to do that?

    • qmatteoq says:

      Actually, I’m not aware about built-in support to encrypt the database. You can use Windows 8 encryption APIs to encrypt the file that is created in the local storage.

  42. Jerry says:

    Windows 8 Phone APp question

    I am having trouble interfacing with the Windows Phone sqlite3 dll.

    I have this interface code working on Windows 8 Desktop but when I use it on the Windows Phone it gives me an error.

    Of course , there I realize that the Windows Phone has a different DLL than for the Windows Desktop but I am assuming the interface to each is the same.

    I am using VB in my application

    1. This is how I have the Sqlite3 dll installed and the version I am using.

    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\

    2. The code I am using to interface to Sqlite3. The code blows up when I execute the Open statement. I am able to detect the sql file is 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”)

    Dim isf As IsolatedStorageFile

    isf = IsolatedStorageFile.GetUserStoreForApplication()

    If (isf.FileExists(dbPath) = True) Then

    SQLOK = Open(dbPath, db)

    endif

    ‘ the interface I am using to the sqlite3 dll

    _
    Public Function Open(ByVal filename As String, ByRef db As IntPtr) As Integer
    End Function

    3. I am getting the following errors when I execute the Open for Sqlite3

    When I execute I am detecting the sql file at this location. I am assuming that this is the correct location in the working storage – YYYYYY is the name of the file

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

    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:

    Where XXXX is the name of my app

  43. Sudhanthira says:

    Hi,Thanks for your post.can you give me example source code for updating sqlite table.

  44. Jerry says:

    Hi, I am still having trouble with the interface to sqlite3. I have studied all the other references including the GtHub download from Hueme. My code runs fine on Windows 8 desktop , I have numerous apps in the Windows store using this code. I am trying to port one of my apps to the Windows Phone. I am using VB. I have also made reference to the correct Windows Phone Sqlite3.DLL and am using the latest. My interface to sqlite3 is simple , if I can use this method to interface to sqlite3 then all of my apps will port to the Phone. That is why I am trying to fix this.

    Below is the code I am successfully executing on the Windows Desktop. I check working storage to see if the data base is already there and if not I copy into working storage. The database has been included in my project as content and copy if newer. Once I determine the data base is in working storage or I copy the data base into working storage then I try to open it.

    I have two problems with the Phone work.

    1. If I use “Task” on the await on my FileExists function the app blows up – what should I be using?

    2. If I convert the Task to a subroutine and try it , it works but then it blows up on when I try to open the data base when I call Open(dbPath, db). I get the error System.TypeInitializationException was unhandled by user code

    3. The question is why doesn’t the same code that runs on the desktop windows 8 run on the Phone Windows 8? and is there another way or something wrong with interfacing with a DLL (sqlite3.dll) the way I was doing it on the desktop. One would think that since both platforms are Windows 8 and there has been a port of sqlite3 to the Windows Phone that this code would working without any changes.

    4. Another thought I had was maybe the code for determining the data path to the database should be different for the windows phone than the Windows desktop?

    Any help or ideas on this matter would be greatly appreciated.

    Thanks Jerry

    PS – I am sure that there are many other VB users out there that would like to use sqlite3 as their data engine.

    Public Sub New()

    InitializeComponent()

    ‘ call checkdb to see if data base is available in working storage , if not then copy it in working storage

    Call CheckDB()

    ‘ set the connection string of the database – open the database

    Call SetConnectionString()

    End Sub

    Private Async Sub CheckDB()

    ‘ routine that is called to check the data base in working storage

    Await FileExists()

    End Sub

    Public Async Function FileExists() As Task

    ‘ error my first problem – on the phone it blows up on the Task Statement – how is this converted and why?

    Try

    ‘ this code tests to see if the data base is in working storage

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

    Dim sampleFile As StorageFile = Await localFolder.GetFileAsync(“LeaseNeg.sqlite”)

    Return

    Catch e1 As Exception

    End Try

    ‘ if the data base is not in local storage then seed the data from local to working storage

    Dim seedFile = Await StorageFile.GetFileFromPathAsync(Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, “LeaseNeg.sqlite”))

    Await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder, “LeaseNeg.sqlite”, CreationCollisionOption.ReplaceExisting)

    End Function

    Public Sub SetConnectionString()

    Dim localSettings As Windows.Storage.ApplicationDataContainer = Windows.Storage.ApplicationData.Current.LocalSettings
    Dim localFolder As Windows.Storage.StorageFolder = Windows.Storage.ApplicationData.Current.LocalFolder

    ‘ determine the path to the file in working storage

    Dim dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, “LeaseNeg.sqlite”)

    Dim errmsg As String

    ‘ error open the file in local storage – it blows up here with initialization

    SQLOK = Open(dbPath, db)

    If (SQLOK = 0) Then

    Return

    Else

    errmsg = “Error: failed to open statement with message .” + GetErrmsg(db).ToString

    SQLOK = SQLOK

    End If

    End Sub

    ‘ interface statement to sqlite3

    _
    Public Function Open(ByVal filename As String, ByRef db As IntPtr) As Integer

    End Function

  45. udeesha says:

    Very Helpful post!

    I was wondering if there is a way/attribute to define Primary key as a combination of two columns while defining the class? Thanks in advance.

  46. Lieve says:

    Hello,

    Is it possible to set a password to a sqlite-net database in a winrt app so the database is encrypted? How can I do that?

    Thanks

  47. Lieve says:

    Hello,

    I have a sqlite database with Guid data types, when I look into the sqlite database I see that the Guid data types are correct, but when I use sqlite-net in my project and I read the records from my sqlite database I can’t read the Guid’s. My Guid field looks like garbage. I added SQLITE_SUPPORT_GUID to compilation symbol to support Guid, but I get the following error: System.FormatException: Guid should contain 32 digits with 4 dashes.

    Could you help me?

  48. Yagnesh says:

    Hi,

    i wanted to use JavaScript project in Win98 App with SQLite can you guide me how ?

  49. Sasidhar says:

    Hi…
    Can any one can give the code for Update query…
    Thanks in advance..

    • qmatteoq says:

      Hi, you just have to get the item you want to change from your database and then pass it to the UpdateAsync() method exposed by the connection.

      Recipe recipe = await connection.Table().Where(x => x.Id == recipeId).FirstOrDefaultAsync();
      recipe.Title = “Updated title”;
      connection.UpdateAsync(recipe);

  50. Nachi says:

    hey
    I’m Developing an app for windows 8 store..i’m now stuck up with the database connectivity..trying for the past one week..i followed all ur steps but i ve an error stating that NO SUCH TABLE:PERSON.pls help me to come out of this..Thanks in advance

    • qmatteoq says:

      Hi, from what I see in your project the CreateDatabase method is defined but never called, so the database is never created. You should call the CreateDatabase method, for example, in the constructor of the page.

  51. Nachi says:

    thank you for the reply sir..can u send the code for creating and inserting values into the database at runtime for sqlite…

  52. Nachi says:

    Sir i need to get value from the user and reflect in the database.

    • qmatteoq says:

      Just follow the post, specifically the chapter titled “Play with the data”. The sample code shows you how to insert a new value in the database. In my sample the values (like name and surname) are fixed in the code, in your real scenario probably they are retrieved from a TextBox, but the code to store the data in the database is the same.

  53. Nachi says:

    https://skydrive.live.com/redir?resid=1EDF15D162209A10!146

    Here is the edited code..But the inserted values are not reflected in the DB…

    • qmatteoq says:

      Can you please include on Skydrive also all the folders and the other files that are part of the project? Otherwise I’m not able to run it because it complains about missing files (like StandardStyles.xaml)

    • qmatteoq says:

      Sorry but the most important folders, like Assets or Properties, are still missing. Please zip the solution’s folder and upload it.

    • qmatteoq says:

      Can you describe me in details which is the problem you’re facing? I’ve tried to insert some data and to retrieve it and everything is working fine, even when the app is closed and opened again.

  54. Nachi says:

    Send us the app..so that we can map the errors..

    • qmatteoq says:

      I haven’t changed anything in the sample project you sent me, I’ve tested the app you’ve sent me and I see that everything is working fine.

  55. Nachi says:

    Thank you sir..It was our mistake.we didn’t properly see the database..Thank you so much

  56. Nachi says:

    hey qmatteoq,
    Since i’m new to sqlite,i need some help in all sorts of queries like UPDATE,DELETE,DISPLAY.If possible share me some links that can guide me through.Thank you

    • qmatteoq says:

      The SQLiteConnectionAsync object that is described in the post exposes all the methods you need, like UpdateAsync() and DeleteAsync().

      • jananidevi says:

        hi sir, i am new to SQLite. how to retrieve the data from the database dynamically.my table has two fields.one is id and one is name. when I click my button it has to display entire table but the reference shouldn’t be made.

  57. jananidevi says:

    sir, thanks for the reply.the exact query for retrieving the data from the database.
    during run time, how to display the table values without referring to a single attribute as like which was in your post.

    • qmatteoq says:

      Do you mean retrieving a set of data from the table and display it on the screen?

      • jananidevi says:

        yes sir.

        • qmatteoq says:

          Hi, the process to get the results from the database and display it on the screen has nothing to do with SQLite, since the procedure is independent from the data source. After you’ve retrieved all the data from the table (using the methods explained in the post), you need to include in the XAML a control to display the collection, like GridView or ListView. Once you’ve defined a template for the single item (using the ItemTemplate property), you have to set your collection as ItemsSource of the control in the XAML. I strongly suggest you to read some books or tutorials about how XAML and binding works, because it’s hard to create a complex application without having such knowledge.

  58. jananidevi says:

    one more question sir.
    how to validate data with the database. for example I have a login table and login page. if the user enters the values, it must be checked with the database and the further actions must be carried. if the user is not valid then it must show some errors.

    • qmatteoq says:

      The validation has nothing to do with the database, but it’s a generic process. In an authentication scenario probably you’ll have a table to store users, with a field for username and a field for the password. You’ll have to get the user you need to authenticate from the table and check that the values of the username and password fields are the same that, for example, the user has inserted in two TextBox controls that are placed in the page. The process to retrieve users from the table is the same explained in the post.

  59. amateur says:

    conn.InsertAsync(person) returns total no. of rows inserted i.e. 1, is there any other method with returns ‘id’ of the inserted row?

    • qmatteoq says:

      Hi, after you’called the InsertAsync method the person object is already updated with the data stored in the database. This means that, if you’ll take a look at the Id property of the Person object, you’ll find the value that SQLite has automatically assigned to it as identifier.

  60. Nachi says:

    Hi,
    How to retrieve a db content and display it in COMBOBOX dropdown list..

    • qmatteoq says:

      Hi, you have to use the standard mechanism exposed by XAML: after you have a collection of items to display (like the one that you can retrieve from the database using the Table object), you have to assign it to the ItemsSource property of your ComboBox control.

  61. Nachi says:

    This is my design:

    This the code behind
    private async void Category_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“A”);
    await conn.CreateTableAsync();
    var query = conn.Table
    ();
    var result = await query.ToListAsync();
    foreach (var item in result)
    {
    Category_Name.DataContext=item.H_name;
    }

    }

    But its not retrieve the contents from table B.H_name is the contents i need to display.

    • qmatteoq says:

      Hi, this approach is not correct. I suggest you a good book or a good tutorial about XAML, because you are missing some basic concepts that will make your word really hard if you don’t learn how to use them. A XAML control can have just one DataContext, which is the class that the control will be able to access for binding purposes. So, in your code, you’re basically overwriting the DataContext everytime the foreach statement is executed.
      What you need to do is to assign directly the ItemsSource property of the Combobox control to the “result” variable, where you’re storing the content of the table.

  62. Nachi says:

    the design is

    ComboBox HorizontalAlignment=”Right” x:Name=”Category_Name” ItemsSource=”{Binding}” SelectionChanged=”Category_SelectionChanged” Width=”264″ Margin=”0,10,37,600″

  63. Nachi says:

    I ve assigned the ItemSource to Binding in my xaml code and The ItemSource is assigned to result in c#,though it is not displaying.

  64. Nachi says:

    ComboBox HorizontalAlignment=”Right” x:Name=”Category_Name” ItemsSource=”{Binding}” SelectionChanged=”Category_SelectionChanged” Width=”264″ Margin=”0,10,37,600″

    Code:
    private async void Category_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“PickorKick”);
    await conn.CreateTableAsync();
    var query = conn.Table();
    var result = await query.ToListAsync();

    Category_Name.ItemsSource = result;

    }
    But how this will fetch me the exact row of my table

  65. Nachi says:

    I’m Half done.
    Give me the Syntax to retrieve a single row from the table and store it in result.

    await conn.CreateTableAsync();
    var query = conn.Table();
    var result = await query.ToListAsync();
    Category_Name.ItemsSource = result;

    • qmatteoq says:

      The syntax to get a single row is displayed in the post: you have to use the Where() operator to filter your data and then apply the FirstOrDefaultAsync() operator to get a single result.

  66. Nachi says:

    I ve a textblock and need to display contents from the database in a random manner(i.e.,Each time the user should be displayed with a new message from DB)..hw can i acheive that.

    • qmatteoq says:

      You can use the Random class (http://msdn.microsoft.com/en-us/library/system.random.next.aspx) to choose a random element from the collection, passing as parameter the number of rows of the table.

      • Nachi says:

        SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“ex”);
        await conn.CreateTableAsync();
        var query = conn.Table();
        var res = await query.FirstOrDefaultAsync();
        Random r = new Random(res.Id);
        text1.Text = r.Next().ToString();

        Id is the column name of table Person1.But i’m displayed with some random values.i need that Id values to be displayed in a random manner in the textBlock(text1)

        • qmatteoq says:

          Because you have to use the generated number to get an element from the table, not to display it in the TextBox. You have to do something like this:

          await List people = conn.Table.ToListAsync();
          Random r = new Random();
          int num = r.Next(people.Count);
          text1.Text = people[num].Name;

          • Nachi says:

            await List ex = conn.Table Person1().ToListAsync();

            i vean error(only assignment can be used as a statement) in the above line. ex is my database name and Person1 is the table name.where i’m going wrong.

          • qmatteoq says:

            Hi Nachi,
            I’m happy to help you and support you, but please don’t keep posting comments until I’ve replied, I don’t always have the time to answer immediately to questions 🙂
            About your code, please read more carefully my posts: as you can see from the samples I’ve published, the syntax is wrong, it should be conn.Table.ToListAsync(), not conn.Table.Person1().ToListAsync().

  67. Nachi says:

    thank u for ur kind help..i ve resolved the problem wit the help of ur code…

  68. Senthil says:

    I’m using telrick Datetime picker..I need to store the selected value in the DB(sqlite)..The value in DateTime Text has to be stored in my Sqlite Db.How to do that.Help me with some code.

    • qmatteoq says:

      SQLite supports the DateTime format, so it’s enough to add a new property to your class which type is DateTime.

  69. Senthil says:

    thank u.when i use the datetime class both date and time gets stored.i need only the date value to get stored in the db.And i have another query.I need to display user with some notification at their desktop in the specified date.is it possible?

    • qmatteoq says:

      Sqlite doesn’t support just date types, they are always in the datetime format. You need to store the complete datetime and, when you have to display the data on the screen, you need to format it to display just the date (for example, by using a converter). To display notifications you can use push notifications or background tasks, that are able to execute operations in background and send notifications to the user.

  70. Madhusudhan says:

    I want to extract the first column of my database. I did the program as mentioned above. I want the first column as List type. How to extract the first column alone from the db? Also, how to extract second column alone? Or third column? And convert to List ?

    • qmatteoq says:

      Actually I don’t know about a built in method to do that, you have to write on your own by querying the table to get all the rows and, for every row, to take just the first column’s value. Then, you add all the values to a List.

  71. Senthil says:

    Thank you Matteo.My application has lot of values to be stored in the database as default.When i use Person person=new Person() and insert my values, it is getting Inserted each time when i call that page.How to avoid this and have a default values when the app is launched.

    • qmatteoq says:

      If you need to do the operation only the first time is launched, you can use a boolean property saved in the application’s settings (the IsolatedStorageSettings class). When the application is launched for the first time you change the property and store the value in the settings. Then, everytime the app starts, you retrieve the value from the settings and, according to it, you load or not the default values.

      • Senthil says:

        Do i need to have a paid windows live dashboard account to use push notifications.is there any other possibility???

        • qmatteoq says:

          Hi, the information is not correct. Push notifications are just XML files that are sent over a HTTP Channel to a Uri, that is created when the applications subscribes itself to receive push notifications with the Windows Push Notification Service. You can send notifications with any technology, as long as it’s able to send POST requests using the HTTP protocol. There’s no need of paid subscriptions or accounts. You can find all the needed information here http://msdn.microsoft.com/en-us/library/windows/apps/xaml/Hh868244(v=win.10).aspx

  72. Senthil says:

    i suppose this is possible with Sqlite too.I have textbox values in an blankpage and i need to display the values in another blankpage.how is this navigation possible.

    • qmatteoq says:

      Hi Senthil,
      frankly I didn’t understand completely your question. Navigation and passing data between two pages is independent from the data source, so you can use the same approach you use with every other source: you can use, for example, the navigation service class, that accepts as parameters of the navigation method the page where to redirect the user and an object to carry on.

  73. Zahoor says:

    How to create a table with multiple column (composite) primay key?
    e.g

    public class CSPartsIdentified
    {
    [PrimaryKey, MaxLength(15)]
    public string PK_CALLID { get; set; }
    [PrimaryKey, MaxLength(20)]
    public string PK_ITEMNUM { get; set; }
    [MaxLength(8)]
    public int QUANTITY { get; set; }
    [MaxLength(25)]
    public string LOCATION { get; set; }

    }

    • qmatteoq says:

      Actually there’s a fork of sqlite-net that tries to add support for composite primary keys but, as far as I know, it’s not officially supported yet.

  74. Senthil says:

    Hi Matteo,
    My App is more of an remainder.User will input the date and time of when he has to reminded of the activity.How to popup the remainder to the user.

  75. Senthil says:

    protected override void OnNavigatedTo(NavigationEventArgs e)
    {
    UpdateList();
    }

    private void Button_Click_1(object sender, RoutedEventArgs e)
    {
    var template = ToastNotificationManager.GetTemplateContent(ToastTemplateType.ToastText01);

    DateTime d;
    DateTime.Parse( DateTime.TryParse(txt.Text,out d);
    ToastNotificationManager.CreateToastNotifier().AddToSchedule(new ScheduledToastNotification(template, new DateTimeOffset(d)));
    UpdateList();
    }
    private void UpdateList()
    {
    List items = new List();
    foreach (var n in ToastNotificationManager.CreateToastNotifier().GetScheduledToastNotifications())
    {
    items.Add(new Item { DeliveryTime = n.DeliveryTime });
    }

    }
    public class Item
    {
    public DateTimeOffset DeliveryTime { get; set; }
    }

    I have modified the source code.error is that The parameter is incorrect.though i have specified the correct input format in the textbox(txt).

    • qmatteoq says:

      I see something wrong in this approach:
      DateTime.Parse( DateTime.TryParse(txt.Text,out d);
      You’re doing a double parse, which is not needed. You just have to call DateTime.Parse(txt.Text) or DateTime.TryParse(txt.Text, out d), not both.

  76. Senthil says:

    Hi matteo,
    I need to update the database value when the user closes the aplication…where should i write the code.i.e at which part of app.xaml should i write the code.

    • qmatteoq says:

      There are two event handlers to manage your scenario: in the App.xaml.cs file: Applicaton_Closing, that is executed when the application is closing, and Application_Suspending, that is execute when the application is suspending (the user has pressed the Start button, he tapped on a toast notification, etc).

  77. Nikki says:

    Great. Works Perfectly fine.
    Please provide queries for update and delete also..

    Thanks

    Nikki Punjabi

    • qmatteoq says:

      Hi, the approach is exactly the same, you just have to use the methods UpdateAsync() (passing as parameter the object to update) and DeleteAsync() (passing as parameter the object to delete), exposed by the SQLiteAsyncConnection object.

  78. Alex says:

    Also, how do you specify the name of a column using variables?

  79. d.k says:

    is it possible to use sqlite as a backend database service for managing the data in front of windows 8 metro app?… means in front there will be a windows 8 app and using internet from smart phone and the app communicates the database server that using sqlite??

    • qmatteoq says:

      Sure, why not? if you use a server side technology, you can choose the one you prefer. The best approach would be to expose your server side data with a service (like a WCF or REST one, for example with WebAPI) and interact with it from your Windows 8 app.

  80. souvick says:

    Hi can any one give a code example for binding the data in a gridview after fetching the data from the database using the method described here?

    • qmatteoq says:

      Hi, actually there’s no difference between getting data from the database or from any other data source. Since you’re working with collections of objects, it’s enough that you assign the data retrieved from the database as ItemSource property of the GridView control.
      Example:

      private async void Button_Click_2(object sender, RoutedEventArgs e)
      {
      SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");

      var query = conn.Table().Where(x => x.Name == "Matteo");
      var result = await query.ToListAsync();
      MyGridView.ItemsSource = result;
      }

  81. Neutobe says:

    I have some question.
    I just need to put sqlite3.dll in the root of my project and then add sqlite-net wrapper.Is it means that should not add reference to sqlite3.dll?

  82. adi says:

    is there a method to encrypt sqlite database file so that users cannot open it with sqlite browser? like putting a password/

  83. Tshego says:

    Hi qmatteoq, thanks for your blog it really helps.

    I have a question, how can i be able to display my data from SQLite database to a datagridview?

    thanks in advance

    • qmatteoq says:

      Hi, do you mean the Windows 8 GridView control or the old DataGridView that was part of Windows Forms? Because such a control doesn’t exist for Windows Store apps.

  84. adi says:

    Can you give link to artical or blog which tells us how to encrypt a sqlite file using c# ? or how ican lock app local folder?

  85. Francesco says:

    Hi Matteo,
    I need to write a code which can retrieve (in a list) all the content of a columns. What i have to write?

    For example, let’s take your code snippet:

    private async void Button_Click_2(object sender, RoutedEventArgs e)
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);

    var query = conn.Table().Where(x => x.Name == “Matteo”);
    var result = await query.ToListAsync();
    foreach (var item in result)
    {
    Debug.WriteLine(string.Format(“{0}: {1} {2}”, item.Id, item.Name, item.Surname));
    }
    }

    what i have to write into the variable query for get a list that contains all the value of the columns “fermata1”?

    Thanks in advance

    • qmatteoq says:

      Hi, you can use a Select() statement to get only the values you need, like:


      await conn.Table().Select(x=>x.Fermata1).ToListAsync();

  86. ankit says:

    how to make app package in windows store app with sqlite database .whan i made a package and install it..it create database with 0kb size with no tables and data

    • qmatteoq says:

      Hi,
      did you follow all the exact steps in the post? I’ve published many Windows Store apps which use SQLite and I didn’t have any issue.

      • ankit says:

        yes i am following.i copy database in to local folder which is run perfectly in visual studio but when i make a app package it is not working

  87. Deon says:

    What would be the best way to implement Sql Lite on a Metro app using Sql lite if we do now know what the database structure will be. So on the app depending on who logs in we build the database as needed from data coming in for that user. Would building a class for each table is not so easy. Would we have to look at some generic class structure to try and re-use only using the fields we need? We are new to Metro apps so any help will be appreciated.

  88. masteruser20 says:

    Hey Matteo

    I have problem with SqlException, Please help me, because i need this database example.

    Code from MainPage.xaml.cs :
    public class Person
    {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [MaxLength(30)]
    public string Name { get; set; }

    [MaxLength(30)]
    public string Surname { get; set; }

    }
    private async void CreateDatabase()
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);
    await conn.CreateTableAsync();
    }
    private async void Button_Click_1(object sender, RoutedEventArgs e)
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);

    await conn.RunInTransactionAsync(async (connection) =>
    {
    Person person = new Person
    {
    Name = “Matteo”,
    Surname = “Pagani”
    };

    await connection.InsertAsync(person);
    });

    }
    private async void Button_Click_2(object sender, RoutedEventArgs e)
    {
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);

    var query = conn.Table().Where(x => x.Name == “Matteo”);
    var result = await query.ToListAsync();
    foreach (var item in result)
    {
    Debug.WriteLine(string.Format(“{0}: {1} {2}”, item.Id, item.Name, item.Surname));
    }
    }

    When I click on Button i have this exception :
    An exception of type ‘SQLite.SQLiteException’ occurred in DataBoundApp1.DLL but was not handled in user code

    • qmatteoq says:

      Are you able to provide me a sample project to reproduce the issue? I can’t say what’s wrong by your code.

  89. Pingback: Universal App with SQLite – Part 2 | A developer's blog

  90. adi says:

    sir..
    how to use collate no case in linq manner while creating sqlite table…

    • qmatteoq says:

      As far as I know, it’s not supported by sqlite-net. You have to use the manual wrapper, which allows you to perform SQL queries.

  91. TP says:

    Hi Matteo,

    Thanks.Your post is helpful to me.As in your attachment,you are using file named people.db.As well as its available in the project.How is the file created.I also created database by writing the following code:
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people.db”);
    await conn.CreateTableAsync();

    I am also able to get the inserted data.But no such file people.db created can be seen in my project.Even I am able to run your project after excluding the file from the project.Is this file necessary? If yes how can I open it.Like open with..

    Thanks in advance
    Waiting for reply

    • qmatteoq says:

      Try to set the path in the following way:

      SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, “people.db”), true);

  92. ch says:

    I am using sqlite in windows store app,but it is not allowing me to pass windows certifaction kit test,it fails package compliance test,says have to declare c++ reference n manifiest file,how i can do that?

  93. christiano says:

    How to get data like “Matteo” and show it in a text block ?

    • qmatteoq says:

      Just retrieve the data as shown in the example (using the Table object and the Where() method) and then assign the result to the Text property of the TextBlock control.

  94. VB says:

    is there any way to include already existing database in my project so that when I install app it uses that database to retrieve information. for example I have dictionary database and want to use it. In my case it is not practical to create database in app code because it is quite large and I already have it. For now I manually copied database to app data folder and it works, but I want for my app to use that database when app is installed on other system from store.

  95. Nasir says:

    Can not create the SQLiteAsyncConnection object. The error showed is The type or namespace SQLiteAsyncConnection does not exists …. are you missing some….
    Help me please

  96. pooja1650 says:

    Hello,

    I am having an app in WP store whose one of the table’s schema needs to be updated now. Can you please let me know how to do that?

    Have already checked, http://www.monkeyslaps.com/sqlite-on-wp-db-schema-management/
    where the option available is to delete and recreate the db altogether but i wouldn’t like to implement it.

    Another option available is, using “Local database”, like
    http://www.geekchamp.com/articles/windows-phone-local-database-schema-upgrade-part1—adding-new-columns
    but again, as i had already included SQLLite8 in my project, migrating to this approach will be troublesome.

    Is there any better option you can suggest?
    Thanks in advance! Indeed, your article is very helpful.

    • qmatteoq says:

      Hello, let me see if I’ve correctly understood your question: do you need to update the database schema when you release an update of your application, is it correct?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.