Working with SQLite in Windows Phone 8: a sqlite-net version for mobile

With a perfect timing, as soon as I’ve published my previous post about using the chsarp-sqlite engine in combination with sqlite-net, Peter Huene has released a porting of the famous library for Windows Phone 8. What does it mean? That, finally, we are able to use the native SQLite engine that has been released as a Visual Studio extension and that we can use a common library to share our data layer with a Windows Store app for Windows 8.

At the moment, the project isn’t available on NuGet yet and requires two steps: the first one is to add a native class, that acts as a wrapper for the functions used by sqlite-net, and the second is to download a specific sqlite-net version, where the developer has replaced the usage of the csharp-sqlite engine with the native one.

Let’s start!

Please welcome GitHub

Both projects are hosted on GitHub (a popular website to host open source projects that also acts as a source control system based on Git), so the best way to download and use both them is using Git: you can also download the project in a single zip file but, this way, every time the developer will change something you’ll have to download everything again and add the new project to your solution. If you’re not familiar with Git, the easiest way to use it is to download GitHub for Windows, which is a Windows client that is able to connect to repositories hosted on GitHub and to keep files in sync with the server.

Just download and install the application from here: after you’ve launched it you’ll have to configure it for the first time. You’ll need to have a valid GitHub account: if you don’t have it, simply go to the website and create one. Once you’ve done you should see a window like this:

image

 

Unless you’ve already used GitHub and you already own one or more repositories, the window will be empty. Now go to the GitHub website and, specifically, to the sqlite-net-wp8 repository, that is available at the URL https://github.com/peterhuene/sqlite-net-wp8. At the top of the page, in the toolbar, you’ll find a button labeled Clone in Windows. Click on it and make sure that you’ve logged in in the website with the same credentials you used for the application, otherwise you’ll be redirected to the page to download the GitHub client.

image

Once you’ve done it the GitHub client will be opened and the repository will be automatically added to the local repositories list: after a while (the progress bar will show you the status of the operation) the whole repository will be downloaded in the default location, that is the folder C:\Users\User\Documents\GitHub\ (where User is your Windows username). Inside it you’ll find a folder called sqlite-net-wp8: that is the project that we need to add to our solution.

Since we’re already playing with GitHub, let’s download also the sqlite-net fork adapted to work with Windows Phone 8: repeat the operations we’ve just made on the repository available at the URL https://github.com/peterhuene/sqlite-net.

The last thing to do is to make sure you’ve installed the SQLite for Windows Phone extension, that is available from the Visual Studio Gallery.

Now that we have everything we need, we can start working on our Windows Phone 8 project.

Let’s play with SQLite

The first thing to do is to open Visual Studio 2012 and to create a Windows Phone 8 application. Once you have it, it’s time to add to the solution the sqlite-net-wp8 project we’ve downloaded from GitHub: simply right click on the solution, choose Add existing project and look for the file Sqlite.vcxproj in the sqlite-net-wp8 folder (that should be C:\Users\User\Documents\GitHub\sqlite-net-wp8). You’ll see the new project added in the Solution Explorer: it will have a different icon than the Windows Phone project, since it’s written in native code and not in C#.

image

As I’ve previously explained, this is just the native wrapper for some of the functions used by sqlite-net: now we need to add the real sqlite-net and we do that by simply copying the Sqlite.cs and SqliteAsync.cs files that are stored inside the src folder of the solution (that will be available, as for the other one, in the C:\Usesr\User\Documents\GitHub folder) into our project. We can do that by simply right clicking on the Windows Phone project and choosing Add existing item.

Now we need to add a reference in our Windows Phone application both to the sqlite-net-wp8 library and to the SQLite engine: right click on your project, choose Add reference and, in the Solution tab, look for the sqlite library; after that, look for the SQLite for Windows Phone library, that is available in the Windows PhoneExtensions section.

UPDATE: the developer, to keep supporting also the C# engine I’ve talked about in my previous post, has recently added a new requirement to use his library; you’ll have to add a specific contitional build symbol, in order to properly use the native engine. To do that, right click on your project (the one that contains the Sqlite.cs and SqliteAsync.cs files you’ve previously added), choose Properties, click on the Build tab and, in the Conditional compilation symbols textbox add at the end the following symbol: USE_WP8_NATIVE_SQLITE. In a standard Windows Phone 8 project, you should have something like this:

SILVERLIGHT;WINDOWS_PHONE;USE_WP8_NATIVE_SQLITE

And now? Now we can simply copy and paste the code we’ve already seen in the original post about Windows 8 or in the more recent post about csharp-sqlite: since all these libraries are based on sqlite-net, the code needed to interact with the database and to create or read data will be exactly the same. Here are the usual examples I make about doing common operations:

UPDATE: as some readers have pointed out in the comments, with the previous code eveyrthing was working fine, but the database file was missing in the local storage. As the sqlite-net-wp8 developer pointed me out, there’s a difference between the Windows 8 and the Windows Phone version of the library. In Windows 8 you don’t have to set the path, it’s automatically created in the root of the local storage, unless you specify differently. In Windows Phone 8, instead, you have to pass the full path of the local storage where you want to create the databse: the code below has been updated to reflect this change.

 

//create the database
private async void CreateDatabase()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
    await conn.CreateTableAsync<Person>();
}

//insert some data
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);

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

    await conn.InsertAsync(person);
}

//read the data
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);

    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));
    }
}

Be careful!

There are some things to keep in mind when you work with this library and SQLite. The first one is that, actually, both libraries are not available on NuGet: you’ll have to keep them updated by using GitHub for Windows and, from time to time, by syncing the repositories, in order to have your local copy updated with the changes. If you’re going to add the sqlite-net-wp8 project to the solution, like I did in the post, you won’t have to do anything, you’ll just have to rebuild your project. In case of the sqlite-net fork, instead, since we’ve simply copied the files, you’ll need to overwrite the old ones with new ones, in case they are updated. Or, even better, you can add the two files as a link from the original project: this way you’ll simply have to update the libraries from GitHub to see the updates in your application.

The second important thing to consider is that the sqlite-net-wp8 library is built against a specific SQLite version: if the SQLite team releases an update to the Visual Studio extension (so that Visual Studio is going to prompt you that there’s an update to install), don’t update it until the sqlite-net-wp8 project has been updated. Otherwise, many references will be missing and you won’t be able to open the project at all.

Have fun!

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

133 Responses to Working with SQLite in Windows Phone 8: a sqlite-net version for mobile

  1. Quickhorn says:

    Thank you for this post. This is exactly what I needed today.

  2. Alex says:

    Ok, I’m trying that. Database working, but i can’t find *.sql file in Isolated Storage and database rewrite after updating the application.

  3. Jon says:

    I have the same problem as Alex. The database doesn’t seem to be written to Isolated Storage. Did you manage to fix this Alex?

    • qmatteoq says:

      Hi Jon and Alex, I can confirm the problem, I’ve tried by myself. I’ve contacted Peter, the porting’s developer, I will update the post as soon as I have more info to share.

      • qmatteoq says:

        Hi, I’ve update the code in the post to fix the problem you’ve reported. Thanks for pointing me out!

        • Josh says:

          I just went through this tutorial (4:30 PM CST) and I am still getting the “Community” issue on this line: using Sqlite3 = Community.CsharpSqlite.Sqlite3;

          Can you leave a reply with the update as well?

        • Francisco says:

          Thanks for the update. I also pointed to update the repository to reflect the latest Sqlite for Windows Phone version in the Visual Studio Gallery.

          Although everything is working fine in the emulator, I’m trying to deploy to a real WP8 device (Nokia Lumia 820), and there is where I have the problem. The build fails with:

          Deployment failed because an app with target platform x86 cannot be deployed to Device. If the target platform is win32/ x86, select an emulator. If the target platform is ARM, select Device.

          Any steps I’m missing? Anyone manage to use the suggested solution configuration to build and deploy an app in a real device?

          Thanks for the great article!

          • qmatteoq says:

            Hi, sqlite engine can’t be built both for ARM and X86, but you have to set just one target platform. To do it, click on the Debug / Release dropdown and choose Configuration Manager. In this windows set ARM as platform in the Build dropdown and try again.

  4. Francisco says:

    Thanks, it works fine! I totally forgot the configuration manager, I was just trying to verify the project properties. It works great, now I have a great foundation to refactor my new app to target WP8 and W8

    Thanks again for the great article and helpful tips. It covers nicely the gaps left by Microsoft with their “partial” support for sqlite for WP developers!

  5. Jon says:

    Thanks Matteo, it’s all working now.

    Great article. I’ll be using this as the storage foundation for my next app. Can’t thank you enough!

  6. Anton says:

    Hi,

    Is there a way to access the database that is created? I have determined that its directory is: “C:\Data\Users\DefApps\AppData\{DBB9F7C7-0D4B-4019-8450-82FAD1DE51A8}\Local\people.db” but I don’t have access to this and can’t open it.

    Apart from that,t hanks for your tutorial, everything worked out great!

    • qmatteoq says:

      Hi, the database is created in the local storage of the emulator or the device, so it isn’t stored on your hard drive like a normal file. You can use an utility like Windows Phone Power Tools to access to the isolated storage of your application and download the file. You can download it from http://wptools.codeplex.com/

  7. Dennis says:

    Hello, I am able to run my app fine on a virtual device provided by the emulator. But when I try to deploy it on a real WP8 device, I get the error:

    “The type or namespace name ‘Community’ could not be found (are you missing a using directive or an assembly reference?)”

    Thank you.

    • qmatteoq says:

      Are you deploying to the device using Debug or Release configuration? Make sure that the compilation symbol described in the post is added in the project’s properties for every configuration.

      • Dennis says:

        Thank you! I had added the compilation symbol only to the emulator and had not added it to the device itself. Everything works great now, thanks!

  8. Francisco says:

    Any thoughts about doing an article about a “clean” way to using sqlite with a project that has a platform specific part and a PCL for the common part. What would be the best strategy? Create an abstraction in the PCL to access sqlite? etc.

    I’m sure there are a lot of people right now working on solutions and ideas on how to combine sqlite, multiplatform projects and PCLs…

  9. Suresh says:

    I am using codeplex tool “Sqlite Client for Windows Phone” available at http://sqlitewindowsphone.codeplex.com/releases. Is it allowed to use? it is working fine in my Windows phone 8 and 7.5 emulators. I haven’t tested my app in the device yet. does the Microsoft WP store accepts my app with it or do I need to change to this new library “sqlite-net”? Please suggest..

  10. kedarnath says:

    Hi Matteo,
    Found this postvery interesting.
    If you can make a post which explains creating a common datalayer which can be used for both windows phone 8 and windows 8,that would be really great.
    Thanks in advance.

  11. seetharam says:

    Hi , First of all thanks for the great post 🙂 , i have few doubts 1) does the same process work even for windows phone 7 ????,( i am developing a windows phone app , i would like to target all windows phone devices i.e, windows phone 7, 8) 2) sqlite support for windows phone 7 ??? please let me know , looking forward for your reply

  12. seetharam says:

    Windows phone 8 app how to Import an already existing SQLite database file ????

    hi, i have a followed your post “Import an already existing SQLite database in a Windows 8 application” in and successfully implemented the way you have guided in the post and imported the existing sqlite database in my windows 8 app , but now i am trying to implement the same thing in my windows phone 8 app, since the file is saved not on the hard disc but saved saved in local storage of emulator /device , how how can i check every time that file is present or not on application 1st page load and if there, don’t do anything if not there copy the data present in exisisting external sqlite db file present in project to that emulator/device isolated storage. i request you please let me know on how to implement it , it would be very helpful to me i request you please let me know on how to implement it , looking forward to your reply . thanks in advance

    • qmatteoq says:

      You have to follow the same approach described in my post about Windows 8: the code sample will work also on Windows Phone 8, since the APIs of the Windows Runtime used to work with the storage are the same in the two platforms.

  13. Derek says:

    I’ve been using this for a bit and it works great. A problem I have is that my app works great until I submit it to the app store. I’ve been submitting betas and have yet to figure out why it doesn’t work after being submitted. I’ll update if I ever figure it out.

  14. Teo says:

    Hi and thanks for the tutorial.

    After spending some hours setting up sqlite I finally got rid of all the errors and could compile. However when I try to run this example I’m getting “An exception of type ‘System.BadImageFormatException’ occurred in SQLtesting.DLL but was not handled in user code” error.

    This happens on “await conn.InsertAsync(person); ” line.
    Any idea on what might be wrong?

    • qmatteoq says:

      Hey Teo, do you think you can send me a sample of your project I can play with? With just the information you gave me, I can’t understand what’s going on.

  15. tanuj says:

    hi..you are doing a great job..
    i just want your help..i followed your process for database access
    for wp8 it just work fine..
    and now i want to access only the column of a table from my database but not have any clue..
    plzz..reply..and if you can add something more about using sqlite
    in wp8 about query purpose plzz..do..

    • qmatteoq says:

      To do that, you should do a manual query against the database, otherwise you have to get the whole object and get only the information you need.

  16. Prabakaran says:

    1028: The native API api-ms-win-core-interlocked-l1-2-0.dll:InterlockedCompareExchange() isn’t allowed in assembly sqlite3.dll. Update it and then try again.
    I Got This Problem While Uploading xap file In Store Help Me Fix It For windows phone 8

    • qmatteoq says:

      Please check that the app has been compiled for ARM and not for x86 or Any CPU, since SQLite engine can’t be compiled for the Any CPU target.

  17. Prateek says:

    Hi.. Thanks alot for your post… very helpfull.
    Is SQlite better than SQl CE for windows phone 8 applications w.r.t performance ?

    • qmatteoq says:

      Yes, the downside is that SQLite is still not flexible and powerful as using SQL CE with LINQ to SQL (for example, in managing relations).

  18. Tony says:

    I need some help. Do you have a code snippet of when the app first starts up, it checks if the db exists, and if does not it creates it. I’m having a major issue. I created a db using SQLite Expert Personal and its quick and works fine. I created a new project and updated all of SQLite to latest version. I run my app, write data to a table and it never gets written and throws no errors. I cannot figure out the problem. So I’m starting from scratch.

  19. Tony says:

    How do I retrieve the last inserted record row id?

    I tried something like this:
    cmd.CommandText = “select last_insert_rowid() as id from myTable”;
    var temp = cmd.ExecuteQuery();

    Is there any documentation on this anywhere?

    • qmatteoq says:

      It’s enough to access to the id property of the object you’ve just inserted.
      Here is a sample where I show a message with the ID of the record I’ve just added to the table.

      private async void OnInsertDataClicked(object sender, RoutedEventArgs e)
      {
      SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people.db");
      Person person = new Person
      {
      Name = "Matteo",
      Surname = "Pagani"
      };
      await conn.InsertAsync(person);
      MessageBox.Show(person.Id.ToString());
      }

  20. Tony says:

    Thank you. I’m doing it slightly different

    using (var db = new SQLite.SQLiteConnection(System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, “myDB.db”), true))
    {
    int sucess;
    sucess = db.Insert(new myTable()
    {
    TableID = _vm.TableID,
    Description = _vm.Description,
    TableDT = _vm.TableDT
    });

    }

    I’m using a viewmodel I have created. How would I get the ID doing it this way?

    • qmatteoq says:

      Instead of using creating a new myTable instance inside the Insert() method, create it outside and pass it to the Insert() method. Or is there any specific reason why you’re using this code?

  21. Louis says:

    Hi, thank you for the write-up! Is it not safe to use the praeclarum version of sqlite-net alongside sqlite-net-wp8?

    • qmatteoq says:

      There are some differences between Windows Phone and Windows 8, so the official sqlite-net version from praeclarum doesn’t work Windows Phone, but just with Windows Store apps for Windows 8.

      • Peter Huene says:

        I’m waiting on praeclarum to accept a pull request that fixes a build break introduced by an pull request unrelated to WP8. Once that pull request is accepted, I will update the instructions to use the original sqlite-net repo and delete my fork.

  22. Kenneth says:

    Hey Matt,

    I’ve done all you’ve said, the SQlite project is added and the SQLite.cs and SQLiteAsync.cs too. I have references to Sqlite and SQLite for Windows Phone.

    I’ve also updated my phone app by putting “using SQLite” and “using Sqlite”.

    The compilation symbols are updated too, for every configuration.

    After I’ve entered all code, and I try to build or debug it, I get several errors. These are:
    Error 1 The name ‘Path’ does not exist in the current context
    Error 2 The name ‘ApplicationData’ does not exist in the current context
    Error 7 The name ‘Debug’ does not exist in the current context

    Any ideas how I can resolve this?
    Thanks in advance!

    • qmatteoq says:

      I think you’re missing the namespaces to work with the local storage, that are

      using System.IO;
      using Windows.Storage;

  23. Sadiq says:

    hi sir….i got the following problem when i click on the insert / show button of the project…i done every thing as mentioned in the post….
    the error is…..
    “An exception of type ‘SQLite.SQLiteException’ occurred in PhoneApp11.DLL but was not handled in user code”
    it gives me error in the ” return stmt;” of SQLite.cs class.

    i have searched google but i found nothing….???? plz help me.

    • qmatteoq says:

      Hi, can you upload the project somewhere (on Skydrive or Dropbox, for example) and send me the link to download it? So I can give it a look.

      • Sadiq says:

        respected sir…this is the link to download my application…
        https://skydrive.live.com/?cid=634069DE8E33CE9D&id=634069DE8E33CE9D!156

        • qmatteoq says:

          Hi, I’ve found two issues in your code:

          1) The first one is that the createdatabase metod is never called, so the database is never created. I’ve changed the code to call the method in the Loaded event of the page.
          2) The debug information you are printing are wrong, because you’re doing you’ve added to the String.Format statement 3 parameters, but you have only 2 of them. The correct code should be:


          foreach (var item in result)
          {
          Debug.WriteLine("{0}: {1}", item.id, item.name);
          }

          With these changes, the app is working fine for me.
          Cheers

          • Jaime says:

            Hi, thanks for posting these examples! I was having a problem inserting data and I was making the same mistake. The db was not being created. 🙂

  24. Benjamin Davis says:

    Hi I have used this code but when I add in the conditional for the project in the SQLite.cs file for WP it gives the errors saying that SqLite could not be found and this error comes up on in the beginning of the file here #if USE_CSHARP_SQLITE
    using Sqlite3 = Community.CsharpSqlite.Sqlite3;
    using Sqlite3DatabaseHandle = Community.CsharpSqlite.Sqlite3.sqlite3;
    using Sqlite3Statement = Community.CsharpSqlite.Sqlite3.Vdbe;
    #elif USE_WP8_NATIVE_SQLITE
    using Sqlite3 = Sqlite.Sqlite3;
    using Sqlite3DatabaseHandle = Sqlite.Database;
    using Sqlite3Statement = Sqlite.Statement;

    #else
    using Sqlite3DatabaseHandle = System.IntPtr;
    using Sqlite3Statement = System.IntPtr;
    #endif

    • qmatteoq says:

      Hi, have you added to your project a reference both to the GitHub project and to the SQLite runtime for Windows Phone, as described in the post?

  25. Sadiq says:

    plz sir upload the modified project some where…i want to check…best regards

  26. Neutobe says:

    I am try to check if a table is exist like this:
    var coon = new SQLiteConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, databaseName), true);
    var r = coon.Execute(“select count(*) from sqlite_master where type =’table’ and name=’?'”, typeof(T).FullName);

    But it throw an exception.How to check if a table is exist and see if create is needed?Thanks a lot.

    • qmatteoq says:

      Hi, the method CreateTableAsync() already does that: if the table already exists, it isn’t created again.

      • Neutobe says:

        ok,even if I do not need to check if table is exist when I am trying to create table,’get table’ action Table() will throw exception when table not exist.If existential checking is not supported,the exception is only thing can be used to ensure table’s existence,right?

        • qmatteoq says:

          Yes, but I suggest to call the methods to create the tables every time the app starts, instead of catching exception. This way, you’ll be sure that the tables are there.

  27. FENI says:

    I am getting error in read method while reading data on button click event.
    var result = await query.ToListAsync();
    Error : the await operator can only be used within an async method.Consider Marking this method with ‘async’ modifier & changing it return type to “TASK” .
    & If i removes await then compiler gives me error in foreach saying that foreach cannot operate on variables of System.Threading.Task.Task<System.Collection.Generic.List>

    • qmatteoq says:

      Hi, it seems that you didn’t mark the method that contains your code with the async keyword. It should be something like this:


      public void async GetData()
      {
      var result = await query.ToListAsync();
      }

      • FENI says:

        First Of all Thanks For reply …
        But Buddy “ToListAsync” is the inbuilt method of
        SQLiteAsync.cs class so i cant change the return type.The return type of “ToListAsync” method is predefined & it is Task.
        So What To Do Now ? I am not writting my own code I am just copying the above code which you have given as example above.help if you get the scenario of my doubt.

        • qmatteoq says:

          Maybe there was a misunderstanding. I’m not saying that you have to change the return type of the method, I’m asking if you have marked the method where you are calling ToListAsync() (in my sample, the button event handler) with the async keyword.
          Something like this:

          private async void Button_Click_1(object sender, RoutedEventArgs e)

          Notice the async keyword between private and void

          • FENI says:

            Friend Thank You Very Much I got it. That’s My Problem Sorry I wont Get that in first time. Thank You So Much.

  28. Sildio Mbonyumuhire says:

    Thank you for this,
    It just what I wanted. But the question I have is that still I cant use the library (like in using System.Data.SQLite). I tried to install it from project-> Manage NuGet packages-> online but it tells me <>
    Waiting for your reaction

    • qmatteoq says:

      Did you follow the steps described in the post? You don’t have to install it using NuGet, but you have two downloads the two projects (sqlite-net and sqlite-net-wp8) from GitHub.

  29. Garegin says:

    First of all thanks for tutorial. I have one question/issue, I successfully add data to tables, but when I launch application second time, changes are discarded ? Here is my code

    SQLiteAsyncConnection sqLiteConnection = new SQLiteAsyncConnection(db_path, true);
    await sqLiteConnection.InsertAsync(new Scores()
    {
    BiletID = 1,
    Date = DateTime.Now.ToString(),
    Score = _20
    });

    Is there something I have to do to save changes ? Or am I doing something wrong ?
    For getting results I use following code

    using(var dbCon= new SQLiteConnection(db_path))
    {
    var _scores = dbCon.Table().ToList();
    }

    As I said it works fine until I re-run application, changes are discarded, and _scores returns nothing.

    • qmatteoq says:

      Hi, which is the the value of the db_path string?
      It should be a full path to the local isolated storage, like in the samples:


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

      Otherwise, you’ll get the behavior you described: the application is able to save the data in the database, but when the app is closed data is lost.

  30. sathwik says:

    Hi,

    i am getting some error in sqlite.cs file. When i debug the application i am getting in this part

    public static Result Open(string filename, out Sqlite3.sqlite3 db)
    {
    return (Result) Sqlite3.sqlite3_open(filename, out db);
    }

    public static Result Open(string filename, out Sqlite3.sqlite3 db, int flags, IntPtr zVfs)
    {
    return (Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, null);
    }

    can u please say me. I am very new to windows phone 8.

  31. Alex says:

    qmatteoq, please how do you specify “Foreign Key” and “References”?

  32. Neutobe says:

    If I want to graft my wp project to windows 8,ofcourse ths wp8 wrapper is not suit. So can I only use the wrapper sqlite-net from nuget?

    • qmatteoq says:

      Yes, exactly. However, the code you wrote to use sqlite-net on WP8 will work without changes on Windows 8, APIs are the same.

  33. sathwik says:

    Hai

    Can u please give an example for update query by using this wrapper.
    it should extract one particular row when we selected it and then updated data should go into the table..

    Can u please help me out for this

    • qmatteoq says:

      Here is a sample of an update query to update a person’s name:


      private async void OnUpdateButtonClicked(object sender, RoutedEventArgs e) { SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path. Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
      Person person = await conn.Table().Where(x => x.Name == "Matteo"). FirstOrDefaultAsync(); person.Name = "Carlo";
      await conn.UpdateAsync(person); }

      • sathwik says:

        Hai,

        wait conn.Table ().Where(x =>
        Iam getting an error here can u please explain me why i am getting an error here..

        • qmatteoq says:

          Hi Sathwik,
          can I please ask to post your questions just once? Due to family / work duties I can’t always reply to comments immediately, but it doesn’t mean I didn’t read them 🙂
          Regarding your question, please make sure that you’ve added the System.Linq namespace to your class, otherwise all the Linq methods (like Where() or FirstOrDefault()) won’t be available.

          • sathwik says:

            hai

            Sry for ur inconvenience actually i got stocked in the project so u r the person giving me the reply’s soon so i am repeatedly posting. I am new to windows phone 8 development so i am learning each and every topic from posts itself. thanks for your reply’s your posts are really amazing thank you.

  34. sathwik says:

    hai,

    i am retrieving the data from person table and appending that data to the observable collection list and calling the selected indexed item to show specific data to the user

    int index = 0;
    protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
    {

    string selectedIndex = “”;
    if (NavigationContext.QueryString.TryGetValue(“selectedItem”, out selectedIndex))
    {
    index = int.Parse(selectedIndex);
    DataContext = App.ViewModel.Items[index];
    }
    base.OnNavigatedTo(e);

    App.userid1 = App.ViewModel.Items[index].Lineone;

    }

    by using this i am getting the specific row which i have selected. now i want to update the some more data to this row and update it in the table so can u please help me

    • qmatteoq says:

      I guess that App.ViewModel.Items[index] returns the Person object selected by the person. You have to cast it as Person (Person person = App.ViewModel.Items[index] as Person), to change the properties you want to edit and to call the UpdateAsync() method of the SQLiteAsyncConnection object, passing as parameter the edited Person object.


      Person person = App.ViewModel.Items[index] as Person;
      person.Name = "Matteo";
      await conn.UpdateAsync(person);

      • sathwik says:

        THanks for your reply, This is not working qmatteoq can please assist me to update that particular row by calling the Id of that row.

        • qmatteoq says:

          Hi, what do you mean with “is not working”? Can you give more details about the problem?

          • sathwik says:

            hai,
            Thanks for ur reply,initially i am inserting some data row in the table, later i have to insert some more data into that particular row in the table. so need to update that rows usually.

          • sathwik says:

            THanku so much qmatteoq, Atlast i have done it successfully.
            you helped me a lot in working with sqlite,and i thank you a lot for ur paitance 🙂 and for all ur reply’s.

          • qmatteoq says:

            Good to know 🙂 Feel free to contact me again if you have further troubles.

          • sathwik says:

            surely 🙂

  35. Neutobe says:

    I got some confuse on auto ‘AutoIncrement’ primary key. When I need to delete one row from my table by this ‘await connection.DeleteAsync(obj)’,but it can not delete it bucause when I init my ‘obj’,I didn’t(cann’t) tell it what primary key is its.So, how can I delete one row when using ‘AutoIncrement’ primary key?

    • qmatteoq says:

      Hi, you shouldn’t delete an item by passing to the DeleteAsync() method a new object, but you should first retrieve it from the table.
      Something like:


      private async void OnReadDataClicked(object sender, RoutedEventArgs e)
      {
      SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
      Person person = await conn.Table().FirstOrDefault(x => x.Name == "Matteo");
      await conn.DeleteAsync(person);
      }

  36. Mahipatsinh says:

    Thanks you… Very much

    Great tutorial I referenced so far..
    as a begineer

  37. Neutobe says:

    Do sqlite-net support database encryption?

  38. victor says:

    Take a look on a free tool Valentina Studio. Super thing!!!
    I highly recommend – is best manager for SQLite http://www.valentina-db.com/en/valentina-studio-overview

  39. Ermis says:

    In SQLCE we use pulling and pushing to create tables and data from SQLServer. Can we use similar commands for SQLITE DB, when creating the DB?

  40. dilip says:

    Will this work with Xamarin. I mean Shall we use the same for Android and IOS SQLite db.

  41. SonofNun says:

    I’m having trouble getting it to create a table. I’m using a custom class with mostly string properties, but a few List<KeyValuePair> as well. It throws an error saying it doesn’t know what to do with Lists or Dictionary (what I was using before).

    Any ideas on how to work around this?

    Thanks,
    SonofNun

  42. Swanand Kulkarrni says:

    C6011 Dereferencing null pointer Dereferencing NULL pointer ‘value’. Sqlite sqlitewp8.cpp 203
    ‘value’ may be NULL 203
    ‘value’ is dereferenced, but may still be NULL 203

    • qmatteoq says:

      Hi, when do you exactly get this error?

      • Swanand Kulakrni says:

        When I run code Analysis for solution, I got this error. And another thing is that please let me know steps to add application on app store that have SQLite database. because I am not able to add my application on app store.

        • qmatteoq says:

          It’s been a while since I have submitted a SQLite based application on the Windows Store, I don’t remember having problems, except for the fact that I needed to prepare specific packages for the different architectures (x86, x64 and ARM).

  43. Elena Liu says:

    Thank you very much!
    For your help, I’ve fixed my problem.

  44. Elena Liu says:

    Thank you very much!
    For this article, I could fix my SQL problem!

  45. Joel says:

    Any idea why I don’t have a “Build” tab in Visual Studio Express 2012 for Windows Phone in properties of my project? Any ideas on how to get around this?

    • qmatteoq says:

      Weird. Are you sure are you selecting the right project and not the solution? I don’t have Visual Studio Express installed, so unfortunately I can’t check right now.

  46. Ranga says:

    Recently I have downloaded SQLite for Windows Phone 8.1 from the below link. http://visualstudiogallery.msdn.microsoft.com/5d97faf6-39e3-4048-a0bc-adde2af75d1b

    Now In Project setting i did changes by changing the “conditional compilation symbols” to NETFX_CORE;WINDOWS_PHONE_APP;USE_WP8_NATIVE_SQLITE

    I am getting error from the below code saying

    Error 3 The type or namespace name ‘Sqlite’ could not be found (are you missing a using directive or an assembly reference?) C:\XYZ\PhonePOC\SQLLiteApp\WindowsRuntimeComponent1\DataStore\SqlHelpers\SQLite.cs 40 17 WindowsRuntimeComponent1

    #elif USE_WP8_NATIVE_SQLITE
    using Sqlite3 = Sqlite.Sqlite3;
    using Sqlite3DatabaseHandle = Sqlite.Database;
    using Sqlite3Statement = Sqlite.Statement;
    #else

    Please let me know How to use the “sqlite-net” in the Windows Phone 8.1 (Without silver light)

    Thanks in advance.

    • Ranga says:

      Earlier It was working fine in Windows Phone Apps such as
      Blank App(Windows Phone 8.1)
      Blank App (Windows Phone 8.1 silver light )

      But it is not working in
      Windows runtime component (Windows Phone 8.1).

      when i was build after adding reference to the Windows runtime component project below error was getting.

      Error 5 Windows Runtime type ‘Windows.Foundation.Point’ was found in multiple referenced winmd files. Please remove either ‘C:\Program Files (x86)\Microsoft SDKs\WindowsPhoneApp\v8.1\ExtensionSDKs\Microsoft.VCLibs\12.0\References\CommonConfiguration\neutral\platform.winmd’ or ‘C:\Program Files (x86)\Windows Phone Kits\8.1\References\CommonConfiguration\Neutral\Windows.winmd’ from the list of referenced files. C:\Synch\Ranga\PhonePOC\SQLLiteApp\WindowsRuntimeComponent1\WINMDEXP WindowsRuntimeComponent1

      When i removed the platform.winmd from the ExtensionSDKs. It works perfectly fine.

    • qmatteoq says:

      Are you still facing the issue? I haven’t understood if the second comment you made means that you’ve fixed it or not. Thanks!

  47. B@$U says:

    Does anyone know if there is any updated SQLite version available to use in Windows phone 8.1. When I use the github SQLite project in windows phone 8.1 project, I am unable to reference the sqllite project. The reference is working properly with window phone 8 version. So, I believe SQLite is not supporting 8.1 yet. Anyone have alternatives to use this in WP 8.1?

  48. Umang Jain says:

    Thank You for such a great post !
    First of all , I wanna let you know that I am Beginner in developing windows apps.
    So rather than talking of the code , I need guidance.
    I followed your post and everything worked fine. I just created sample database , inserted data, retrieved etc. , All good (Thanks to you).

    1) But I want to implement a existing database (.sql format) , so how to do that ?

    2) Furthermore, rather than creating a database(going into all that stuff) , we can also create a list and add items to that, because in database we are inserting data manually via objects. So what’s the use of database ?
    Again, I am a beginner , just trying to learn , No offence please 🙂

    • qmatteoq says:

      Hi Umang,
      I’m glad you’ve found my post useful!

      Here are the answers to your questions:

      1) I’ve published a post about this topic: it’s valid both for Windows and Windows Phone 8. You can read it here: http://wp.qmatteoq.com/import-an-already-existing-sqlite-database-in-a-windows-8-application/
      2) Database is persisted in the isolated storage, so even if you close and reopen the app the data isn’t lost. If you simply insert your data in a collection (like List or ObservableCollection) data isn’t automatically persisted, so when the app is closed, the content of the list is lost.

      And don’t worry, there are no stupid questions, there’s always something new to learn 🙂 Cheers

      • Umang Jain says:

        Thank You ! I got my answer. I followed your post and could implement an existing database in my app. Thanks a lot again. 🙂

Leave a Reply to Swanand Kulakrni Cancel 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.