Using SQLite in Windows 8 Store Apps


In episode 52 of my Visual Studio Toolbox show, I showed a sample Windows 8 app that uses SQLite. The app manages customers and their projects. In this post, I will show you how to build that app.

[11/28/2012]: I uploaded the sample to the MSDN Code Samples. You will have to add the references and get the SQLite-net package, but you won’t have to write any of the code.

The first thing you need to do is get SQLite. Select Tools|Extensions and Updates. In the Extensions and Updates dialog, select Online. Type sqlite in the search box. You will see SQLite for Windows Runtime. This is the SQLite binaries packaged up by the SQLite team. Install this.

Picture1

Next, create a new C# Windows Store project. Select the Blank App template and name the project SQLiteDemo. Next, add a reference to both SQLite and the VC++ Runtime Package.

Picture2

After you add the references, you will notice yellow triangles in the Solution Explorer. This is never a good sign. Build the app and you will see the following errors:

Picture3

When you create a new C# or VB Windows Store project in Visual Studio, it supports all architectures (x86, x64 and ARM) by default. But since you added SQLite to the project, you can’t build one package that targets all architectures. You have to build one target for each. Select Build|Configuration Manager and select x86, x64 or ARM from the Platform drop-down list. Now the project builds.

NOTE: If you are going to put this app in the Windows Store, you will want to build a package for each architecture and upload them all to the Store. When a user installs the app, the Store will send the correct package to the user.

Before you write code to work with SQLite, you should add a NuGet package that will make your life easier. Select Tools|Library Package Manager|Manage NuGet Packages for Solution. In the Manage NuGet Packages dialog, select Online and type sqlite-net in the search text box. The sqlite-net package enables you to work with SQLite databases using a LINQ syntax. After you install it, you will see two new files in your project: SQLite.cs and SQLiteAsync.cs.

Picture4

NOTE: You can also install this package using the Package Manager Console. Select Tools|Library Package Manager|Package Manager Console and type install-package sqlite-net.

Now you are ready to work with SQLite.

Open App.xaml.cs and add the following properties to the App class:

public string DBPath { get; set; }
public int CurrentCustomerId { get; set; }

DBPath will represent the location of the SQLite database and CurrentCustomerId will represent the customer you selected on the main page.

In the OnLaunched method, add the following code in bold:

// Get a reference to the SQLite database
this.DBPath = Path.Combine(
    Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");
// Initialize the database if necessary
using (var db = new SQLite.SQLiteConnection(this.DBPath))
{
    // Create the tables if they don't exist
    db.CreateTable<Customer>();
    db.CreateTable<Project>();
}
// Place the frame in the current Window
Window.Current.Content = rootFrame;

A SQLite database is a single file. In this app, it will be called customers.sqlite. The file lives in the app’s local folder. To connect to the database, the code uses SQLite.SQLiteConnection. This will create the database first if it doesn’t exist.

Once you have a connection to the database, you can work with tables. Unlike SQL Server, you don’t define tables using a SQL-like syntax. Rather, you create classes. SQLite will construct the tables from the classes. This is just like the Code First model in the Entity Framework.

You will use a simple MVVM (Model-View-ViewModel) pattern in this app, so create a Models folder in the project and add classes for Customer and Project:

public class Customer
{
    [SQLite.PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public string Contact { get; set; }
}
public class Project
{
    [SQLite.PrimaryKey]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public DateTime DueDate { get; set; }
}

These are basic POCO classes. Notice that you have attributed the Id fields and identified them as primary keys.

Back in App.xaml.cs, add the following to the top of the code file:

using SQLiteDemo.Models;

Add the following code in bold:

using (var db = new SQLite.SQLiteConnection(this.DBPath))
{
    // Create the tables if they don't exist
    db.CreateTable<Customer>();
    db.CreateTable<Project>();
}

The CreateTable method of the SQLConnection class takes a class as a parameter and creates a table in the database based on the class.

Download the SQLiteDemo.zip file attached to this post and extract the contents.

In the MVVM pattern, ViewModels represent data. They sit between the UI (views) and the actual data (models). Create a ViewModels folder in the project and add the contents of the ViewModels folder from the zip file.

Open the CustomerViewModel.cs file. Notice there is a property for each field in the Customer class. The GetCustomer method retrieves a customer and sends back an instance of the CustomerViewModel class. This instance represents the customer. Notice the code to query the Customer table in the database. Notice the LINQ syntax. Thank you sqlite-net NuGet package!

public CustomerViewModel GetCustomer(int customerId)
{
    var customer = new CustomerViewModel();
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        var _customer = (db.Table<Customer>().Where(
            c => c.Id == customerId)).Single();
        customer.Id = _customer.Id;
        customer.Name = _customer.Name;
        customer.City = _customer.City;
        customer.Contact = _customer.Contact;
    }
    return customer;
}

The SaveCustomer method takes an instance of the CustomerViewModel class as an argument. If this represents an existing customer, the method updates the database using SQLiteConnection.Update. Otherwise, the method adds a new customer using SQLiteConnection.Insert.

public string SaveCustomer(CustomerViewModel customer)
{
    string result = string.Empty;
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        string change = string.Empty;
        try
        {
            var existingCustomer = (db.Table<Customer>().Where(
                c => c.Id == customer.Id)).SingleOrDefault();

            if (existingCustomer != null)
            {
                existingCustomer.Name = customer.Name;
                existingCustomer.City = customer.City;
                existingCustomer.Contact = customer.Contact;
                int success = db.Update(existingCustomer);
            }
            else
            {
                int success = db.Insert(new Customer()
                {
                    Id = customer.id,
                    Name = customer.Name,
                    City = customer.City,
                    Contact = customer.Contact
                 });
            }
            result = "Success";
        }
        catch (Exception ex)
        {
            result = "This customer was not saved.";
        }
    }
    return result;
}

The DeleteCustomer method takes a customer Id as an argument. If the customer exists, the method deletes the customer’s projects and the customer using the SQLiteConnection.Delete method.

public string DeleteCustomer(int customerId)
{
    string result = string.Empty;
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        var projects = db.Table<Project>().Where(
            p => p.CustomerId == customerId);
        foreach (Project project in projects)
        {
            db.Delete(project);
        }
        var existingCustomer = (db.Table<Customer>().Where(
            c => c.Id == customerId)).Single();

        if (db.Delete(existingCustomer) > 0)
        {
            result = "Success";
        }
        else
        {
            result = "This customer was not removed";
        }
    }
    return result;
}

Open the CustomersViewModel.cs file. You will use this ViewModel to work with collections of customers. It has a GetCustomers method which retrieves all of the customers in the database and orders them by name. The method returns an ObservableCollection of CustomerViewModel.

public ObservableCollection<CustomerViewModel> GetCustomers()
{
    customers = new ObservableCollection<CustomerViewModel>();
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        var query = db.Table<Customer>().OrderBy(c => c.Name);
        foreach (var _customer in query)
        {
            var customer = new CustomerViewModel()
            {
                Id = _customer.Id,
                Name = _customer.Name,
                City = _customer.City,
                Contact = _customer.Contact
            };
            customers.Add(customer);
        }
    }
    return customers;
}

The ProjectViewModel and ProjectsViewModel classes have similar code.

In the MVVM pattern, Views represent the user interface. Before you add views, you need to add some plumbing to the project. Add a new item to the project and use the Basic Page template. You can call it BasicPage1, because you are going to delete it immediately. Click Yes when you see the following dialog.

Picture5

Notice that Visual Studio added a number of files to the Common folder. The XAML files you will add next rely on these.

Picture6

Delete the BasicPage1.xaml and MainPage.xaml files from the project. Create a Views folder in the project and add the contents of the Views folder from the zip file.

Open the MainPage.xaml.cs file. When you run the app, this page gets loaded and the OnNavigatedTo method gets called. This method creates a new instance of CustomersViewModel and calls the GetCustomers method to retrieve the customers from the database. The code then populates the CustomersViewSource with the customers. The CustomersGridView on the page is bound to that view source and will display customers.

The MainPage XAML uses a converter to display dates, so create a Converters folder in the project and add the contents of the Converters folder from the zip file.

The various XAML pages use AppBars to display commands such as Add, Edit and Delete. The StandardStyles.xaml file in the Common folder defines a large number of AppBar button styles you can use. This saves you from having to create icons for AppBar buttons. Notice however, that they are all commented out. This is to prevent them from all being loaded into memory. Locate and uncomment the EditAppBarButtonStyle, SaveAppBarButtonStyle, DeleteAppBarButtonStyle and AddAppBarButtonStyle styles.

Open the App.xaml.cs file and add the following to the top of the file. This is needed because MainPage.xaml is now in the Views folder instead of the project’s main folder.

using SQLiteDemo.Views;

Finally, add the following method to add some customers and projects to the database. This code uses SQLiteConnection.DeleteAll to empty the Customer and Project tables and then add sample data to them.

private void ResetData()
{
    using (var db = new SQLite.SQLiteConnection(this.DBPath))
    {
        // Empty the Customer and Project tables 
        db.DeleteAll<Customer>();
        db.DeleteAll<Project>();

        // Add seed customers and projects
        db.Insert(new Customer()
        {
            Id = 1,
            Name = "Adventure Works",
            City = "Bellevue",
            Contact = "Mu Han"
        });
        db.Insert(new Customer()
        {
            Id = 2,
            Name = "Contoso",
            City = "Seattle",
            Contact = "David Hamilton"
        });
        db.Insert(new Customer()
        {
            Id = 3,
            Name = "Fabrikam",
            City = "Redmond",
            Contact = "Guido Pica"
        });
        db.Insert(new Customer()
        {
            Id = 4,
            Name = "Tailspin Toys",
            City = "Kent",
            Contact = "Michelle Alexander"
        });

        db.Insert(new Project()
        {
            Id = 1,
            CustomerId = 1,
            Name = "Windows Store app",
            Description = "Expense reports",
            DueDate = DateTime.Today.AddDays(4)
        });
        db.Insert(new Project()
        {
            Id = 2,
            CustomerId = 1,
            Name = "Windows Store app",
            Description = "Time reporting",
            DueDate = DateTime.Today.AddDays(14)
        });
        db.Insert(new Project()
        {
            Id = 3,
            CustomerId = 1,
            Name = "Windows Store app",
            Description = "Project management",
            DueDate = DateTime.Today.AddDays(24)
        });
        db.Insert(new Project()
        {
            Id = 4,
            CustomerId = 2,
            Name = "Windows Phone app",
            Description = "Soccer scheduling",
            DueDate = DateTime.Today.AddDays(6)
        });
        db.Insert(new Project()
        {
            Id = 5,
            CustomerId = 3,
            Name = "MVC4 app",
            Description = "Catalog",
            DueDate = DateTime.Today.AddDays(30)
        });
        db.Insert(new Project()
        {
            Id = 6,
            CustomerId = 3,
            Name = "MVC4 app",
            Description = "Expense reports",
            DueDate = DateTime.Today.AddDays(-3)
        });
        db.Insert(new Project()
        {
            Id = 7,
            CustomerId = 3,
            Name = "Windows Store app",
            Description = "Expense reports",
            DueDate = DateTime.Today.AddDays(45)
        });
        db.Insert(new Project()
        {
            Id = 8,
            CustomerId = 4,
            Name = "Windows Store app",
            Description = "Kids game",
            DueDate = DateTime.Today.AddDays(60)
        });

    }
}

Now add a call to ResetData to the OnLaunched method.

using (var db = new SQLite.SQLiteConnection(this.DBPath))
{
    // Create the tables if they don't exist
    db.CreateTable<Customer>();
    db.CreateTable<Project>();
}
ResetData();

Run the app and you should see customers on the main page.

Picture7

Try out the various features of the app:

  • On the main page, with no customers selected, swipe up or right-click and you should see an Add button in the AppBar. Tap that and you can add a customer.
  • On the main page, select a customer and the AppBar should appear with an Edit button. Tap that and you can edit or delete that customer.
  • On the main page, tap a customer and you should see the projects for that customer.
  • On the projects page, with no projects selected, swipe up or right-click and you should see an Add button in the AppBar. Tap that and you can add a project.
  • On the projects page, select a project and the AppBar should appear with an Edit button. Tap that and you can edit or delete that project.
  • If you tap the Back button, the app will return you to the last page you viewed.
  • If you add, edit or delete a customer, the app returns you to the main page. If you add, edit or delete a project, the app returns you to the projects page.

And that, my friends, is how you can add SQLite to a Windows Store app.

 

Comments (94)

  1. Muneeb R. Baig says:

    Thanks for the video; really enjoyed and waiting for the code to upload to get a quick start.

  2. Muneeb, I didn't upload the entire project. You can build the project pretty quickly using the steps above and the code in the zip file, which contains the Views, ViewModels and Converters. There is a link to the zip file in the text above.

  3. Kenneth Bell says:

    Can someone provide an example using Javascript/HTML5 instead of C# or is that possible?

  4. Thomas Svensen says:

    Very nice walkthrough, really inspires me to start using SQLite in a Windows Store app!

  5. Matt says:

    How can I create a pre-populated set of SQLLite tables and then package it?  I have so much data that I reference, that it is not practical to use code to populate and create the tables.  I am migrating a WPF access application across.

  6. Leon Meijer says:

    When is the Fabrikam Fiber demo coming out that you showed us in Redmond/BUILD?

  7. Is it possible to ask a question here?

  8. Thanks for the article. And sorry for the previous post, I submitted a comment without being logged in and I couldn't see it listed.

    Now, I can see some Linq syntax being used. Does sqlite-net support the loading of the related fields(i.e. perform a single query to get the Customers along with their Projects) via the Include function.

    If not, the only solution that comes into my mind is to have hard coded SQL queries that would perform such queries. In this case, it is unclear to me how would you convert the result to some object (i.e. how suppose you hard code a SQL query that performs a LEFT OUTER JOIN of Projects with Customers, how would you convert the result to a ICollection<Project> where each project Customers property would be filled)?

  9. Thanks for this post ,but i want to ask if their is any way to insert foreign key ?? how does i make the relationships between tables ??

  10. Devendra says:

    When i try run your Simple SqLite demo application it is getting an exception as displaying 'System.DllNotFoundException' occured, & Unable to load DLL 'sqlite3'

    What is Team foundation server, Why i have to integrate with it. When i start your sample app it is asking for    Team Foundation server Version control "microsoftdpe.tfspreview.com/defaultcollection".  

    Please help me, Thanks in advance.

  11. Devendra says:

    I have some issues with Sq lite. I read a Microsoft article

    In that site it is mentioning as "When you create a new C# or VB Windows Store project in Visual Studio, it supports all architectures (x86, x64 and ARM) by default. But since you added Sq Lite to the project, you can’t build one package that targets all architectures. You have to build one target for each. Select Build|Configuration Manager and select x86, x64 or ARM from the Platform drop-down list."

    My application is Newspaper app developed using C# for windows tablet. Can i build for all processors.?

  12. Devendra,

    The default when you create a Windows Store project in Visual Studio is that it supports all processors. If you built an app in C# then you can leave the config set to Any CPU and build one app package that runs on all architectures.

    If you use C++ in your app then you can't build one package that runs on all architectures. You have to build separate packages.

    And you can safely ignore the Team Foundation Server message. I guess I didn't disconnect the project from source control before uploading it.

    Robert

  13. inte says:

    Hello.

    I did everything from this tutorial and when I debug something is wrong.

    Error:

    An exception of type 'System.ArgumentNullException' occurred in mscorlib.dll but was not handled in user code

    Additional information: Value cannot be null.

    Visual Studio show me method

    static byte[] GetNullTerminatedUtf8 (string s)

    in Sqlite.cs

    Where is problem? I need your help.

  14. inte,

    The error is in the SQLite.cs code? That is the Nuget package. What were you doing before that error occurred? Reading data? Writing data?

    Robert

  15. inte says:

    Robert, Thanks for your answer.

    I resolved this problem.

    The error was in App.xaml.cs file in method onLaunched.

    Now everything is OK.

  16. inte,

    Glad to hear that. Let me know if you have any questions or run into any other issues.

    Robert

  17. Tiago Alves says:

    Hello, I am thinking to use SQLite im my application.

    If my database structure changes across versions, how can I mantain the previous version data?

    Best regards,

    Tiago Alves

  18. Illuminator says:

    Why when I close demo app and relaunch demo app, the customer and project that I added disappear?

  19. ResetData() on line 66 of App.xaml.cs will reset all data in SQLite.

  20. Mr.Green,

    .Max(); always returns zero, thus a never ending update of an existing Customer. Should remove the  GetNewCustomerId() method and add attribute to Models.Customer to AutoIncrement I.E.

    [SQLite.PrimaryKey, SQLite.AutoIncrement] After doing so you, this will enable to add a new Customer.

    FYI – James

  21. Allan Leven says:

    Thanks Robert.. the example worked perfectly 🙂

    stupid question: I'm just getting going to try a develop some store apps… is the purpose of using SqlLite so that the app will run on mobile devices? I guess so as I'm thinking that any software program that is connecting to a data store using web services would need it's own local database… and if the person downloads it from the Windows store onto their mobile device that they would need a local DB

    am I correct in this thinking?

    Allan

  22. Prakul says:

    On Launching the app this error was prompted :

    Windows was unable to communicate with the target application.This usually indicates that the target application's process aborted. More information may be available in the Debug pane of the Output window (Debug->Windows->Output'.

    How to resolve this problem?

  23. Jay says:

    An exception of type 'System.DllNotFoundException' occurred in SQLiteDemo.exe but was not handled in user code

    Additional information: Unable to load DLL 'sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

    If there is a handler for this exception, the program may be safely continued.

  24. I am doing a VB project trying to use SQLite. I installed and referenced the SQLite Runtime and installed the sqlite-net nugget package – it added SQLite.cs and SQLiteAsync.cs in my project, but I can't get it to show up as useable .

    Is there something special I need to do to add a .cs class into my VB project – I thought we could use them together. So when I try to do something like dim db = new SQLite.SQLiteConnection my project says there is no SQLite. Any suggestions? Thanks.

  25. Dean,

    Bill Burrows has a tutorial and code that shows how to use VB with SQLite. Check it out at http://www.myvbprof.com/…/WinRT_SQLite.aspx.

    Robert

  26. Thanks Robert, I will check it out. Thanks for the video- I enjoyed it

  27. I wanna share my SQLite database with WF8 and Windows store apps.

    How can I do this?

  28. Luke Lucas says:

    Thank you for this tutorial…

    Can someone please tell me from where I can locate the database so I can modify it using Mozilla Fire Fox?

    Thanks

  29. Ricardo Sanchez says:

    This is the best tutorial in this topic. Thank you very much.

  30. Kishori Nanduri says:

    Hi Robert,

    I had just left a comment regarding the support of SQLLite for WinRT Assembly on ARM based devices.

    I missed a step…It worked !! Thank You

  31. Kishori Nanduri says:

    Perfecto !!

  32. Subhash says:

    Is there any compatibility issues between Windows8 & SQL?

  33. Subhash,

    I am not sure I understand the question. Do you mean compatibility issues between Windows 8 and SQL Server? Between Windows 8 and SQLite? What scenarios are you wondering about?

    Robert

  34. Oliver says:

    Hi Robert,

    I have followed your instructions and when I run the solution I receive an pop up warning: A debugger is attached to the SQLiteDemo.exe but not configured to debug this unhandled exception. To debug this exception, detach the current debugger. I'm hoping you can lead me to the a solution 🙂

    Thanks for this tutorial

  35. hi, great sample.

    question – how to find issue with 'unsupported function' exceptions being thrown from my linq 'where' clause when using something like this against an async-connection: .where(s=>s.description.tolower()==desc.tolower())

  36. As far as I can tell, this demo does not work. Changes to customer projects are not persisted to the database. Running the app a second time just reloads the initial data. I'm not convinced the database file is even created. I can not locate the database file in the project or anywhere else.

    Can someone verify changes by the user are actually saved and displayed when running the program a second time? If it is verified working, can someone give me a clue as to where I'm going wrong?

    Thanks

  37. hacknsack,

    Go into App.xaml.cs. Find the OnLaunched method. There is a call to ResetData. That creates the initial data. I left that in there as a convenience so you wouldn't have to enter data the first time you run the app. Comment that out and your changes should be persisted.

    The database is in the app's local folder. Go to C:Users<yourname>AppDataLocalPackages550d73c6-2d9b-49fb-bc80-9b43de170397_9n8k8qa6wx40cLocalState. You should see customers.s3db in there.

    Hope this helps,

    Robert

  38. Robert,

    Just prior to reading your reply, I located the database by setting a break point in your code. Commented out ResetData as you instructed and it works! Awesome! I'm looking forward to working with SQlite and Windows Store Applications.

    Thank you so much.

  39. hacknsack,

    Excellent. Let me know if you have any other questions.

    Robert

  40. Great walkthrough. but how to store Image into SQLite.

  41. Ahmed,

    Take a look at stackoverflow.com/…/how-do-i-store-and-retrieve-a-blob-from-sqlite and see if that answers your question on how to store images.

    Robert

  42. Naveen says:

    Robert,

    I have a question.

    I am looking for the code to find the no of records/rows in a particular SQLite table.

    Can you share the code for the same.

  43. Naveen says:

    This is in connection to my previous query. Wanted to highlight the point that I need code to find the no of rows or records in SQLite table from a windows store app.

  44. Naveen says:

    Robert,

    My windows store application has a pdf which resides in the app. Ofcourse  I can decide where to place it in the app.

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

    Can you help me with the code for it.

  45. Basma says:

    Bonjour,

    Je vous remercie pour ce document.

    Je cherche à modifier le path de ma base de donnée pour le mettre sous C: par exemple.

    Est il possible de le faire?

  46. Henry says:

    Hi Robert how can i display my data from sqlite database to datagridview? im really need help with that thanks

  47. Henry,

    Are you having issues with the sample app or with a different app?

    Robert

  48. Henry says:

    Hi Robert it's a different app i even tried to use  SQLiteDataAdapter library but i don't know how to install it in my project, I'll appreciate if maybe you pass me a link related to displaying to a datagridview thanks again for your time.

  49. Henry,

    Let's take this offline. Email me at rogreen at microsoft dot com.

    Robert

  50. Marko says:

    I am getting this error:

    An exception of type 'System.BadImageFormatException' occurred in CalorieCalculator.exe but was not handled in user code

    Additional information: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)

    If there is a handler for this exception, the program may be safely continued.

    How to fix this???

  51. Marko says:

    I am using VS 2013 and selected x64 for Debug

  52. saravanan says:

    can anyone tell me how to sync this sqlite database back to sql server??

    i searched many site, not getting proper solution for this

  53. ankit says:

    how to make app package with sqlite database in windows store app using c#

  54. guddu says:

    I followed this process to create db tables but I am stuck here. My requirement is to execute  low level query like `select id, name from table  and all CRUD operations.

    so can you tell me What type of method should I declare? What should the return type be?

  55. guddu, did you check out the sample project I uploaded to MSDN Code Samples? The link to that is at the top of the page. That should show you how to do basic CRUD.

    Robert

  56. Earvin James says:

    Hello Robert! I followed the model you created on a Windows Phone App. I tried to bind data on a ListPicker control but there seems to be a problem I can't resolve.

    I defaulted the ListPicker on 'FullScreenOnly' expansion mode. The data binds okay on the control, it displays all the data from my model, but the moment I select an item from the full screen mode and goes back to normal mode the selected item never changes. Am I missing a control property or something?

  57. occorom12 says:

    Hi Robert,

    I can't find the SQLite extension, is that normal ?

  58. Eros says:

    Might spend the source code?

  59. yuan kai says:

    After its saved, when you close and re-run the app, the details change back? is there a way to permanently change the content?

  60. adi says:

    sir..

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

  61. Nick says:

    Is it necessary to use C# while using SQLite?  I am developing a C++/XAML app so that is why I ask.

  62. krishna says:

    hi Robert, I am trying to develop an app for windows 8 and I have followed what u have said when I execute and check the appdata folder for the SQLite db it is not getting created. I am using VS2013 express edition, windows 8.1 update 1, I have added  Microsoft Visual C++ 2013 Runtime Package for Windows and SQLite for windows runtime(windows 8.1).

    I have added the following code on onlaunced

    string DBPath = Path.Combine(

                       Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");

                   // Initialize the database if necessary

                   using (var db = new SQLite.SQLiteConnection(DBPath))

                   {

                       // Create the tables if they don't exist

                       db.CreateTable<employee>();

                        db.Insert(new employee()

                        {

                            id = 2,

                            name= "trued",

                        });

                        var customer = (db.Table<employee>().Where(

                            c => c.id == 2)).Single();

                   }

  63. Nick,

    You can use SQLite with C++. For example, check out visualstudiomagazine.com/…/using-sqlite-with-modern-c.aspx.

    Robert

  64. Krishna,

    What is the value of Windows.Storage.ApplicationData.Current.LocalFolder.Path? It should be something like C:UsersrogreenAppDataLocalPackagesf8905513-a12b-4a88-a462-6d0435296fdc_9n8k8qa6wx40cLocalState. If you look in that folder, do you not see the database file?

    If not, wrap the above in a try catch and see if there is an exception.

    Robert

  65. John Phillips says:

    Hey Robert, this looks like a great app.  I wonder if you have updated a version for windows 8.1?

  66. Simon says:

    Great explanation Robert. One problem now- Visual Studio 2013 has not StandardStyles in the Common folder on opening a new basic or other page. Do you know a way around it?

  67. Simon, the styles in StandardStyles.xaml are now built into the runtime, so you no longer need them in a separate resource file. Note that some of them have been renamed.

    Check out timheuer.com/…/remove-standardstyles-xaml-from-windows-8-1-winrt-projects.aspx for more information on this.

    Robert

  68. Migara says:

    Thanks robert for your valuable article .let me know,is there a way to db file(customers.db) apart from the app.i mean if its located in the appData folder as u mentioned,is that file secure?

  69. Daniel says:

    Hi! Your code is very helpful. I like to ask some out topic question. What is best practice for the database connection? I am thinking of maintaining one and open SQLiteConnection connection since Store apps are not multi-user.

  70. Heman123 says:

    hello.it was really helpful. thank you Robert.

    i created my project for class based on this. but i am unable to connect 2 tables- means unable to add foreign keys and primary keys. How should i do that please guide me through the problem.

  71. Heman123 says:

    hello.it was really helpful. thank you Robert.

    i created my project for class based on this. but i am unable to connect 2 tables- means unable to add foreign keys and primary keys. How should i do that please guide me through the problem.

  72. jjbarbas says:

    Excellent tutorial greetings

    A query because when quieron see MainPage.xaml page tells me the following:

    Compile Project to update the Design view.

    Design view can not be displayed correctly because they have not yet generated some custom items

    These pages are not visible in design view

    Thanking you in advance for your response

  73. jjbarbas says:

    Excellent tutorial greetings

    A query because when quieron see MainPage.xaml page tells me the following:

    Compile Project to update the Design view.

    Design view can not be displayed correctly because they have not yet generated some custom items

    These pages are not visible in design view

    Thanking you in advance for your response

  74. jjbarbas, Does the error go away if you build the project?

    Robert

  75. jjbarbas says:

    I am newbie apology now I get the following error when I open the MainPage.xaml page:

    The Design view is not available for target platforms x64 and ARM.

  76. jjbarbas says:

    I am newbie apology now I get the following error when I open the MainPage.xaml page:

    The Design view is not available for target platforms x64 and ARM.

    Thanking you in advance for your response

  77. jjbarbas says:

    If the error persists and compile the project

    Thanking you in advance for your response

  78. jjbarbas, Visual Studio is a 32-bit product. That is why the Design view is not available for x64 and ARM. You should develop targeting 32-bit. If you want to create packages for 64-bit and ARM, change the target and then build the project.

    Robert

  79. Heman123 says:

    Really Helpful tutorial. I am really thankful to u Robert….

    Can u Please help me how to add foreign key … I don't know how to connect both the tables. Means i want to add Foreign Key Constraint. can u please help me with it.

  80. Andrea says:

    Hi. I've a problem. I use your code in an application! I save the data correctly but if I  close the app when I open the app I can't load the data! Why?

  81. Niyaz says:

    hey when i added basic page.xaml the common folder added but its content is so different than your in figure above only 4 files are added 1-Navigationhelper.cs  2- Observable dictionary.cs 3-relayCommand and 4- SuspensionManager.cs files and its gived too much errors as there is no LayerAwarePage.cs

    needs help i amusing VS 2013.4 Ultimate

  82. xiaogezi says:

    I want a javascript demo??Please help me! thanks

  83. Ryan says:

    Adding a blank page created the "Common" folder for me once, but I made other mistakes so I deleted the project and started over.  This time, I don't get a "Common" folder automatically generated.  How can I force VS to make one?

  84. Ryan says:

    Would it be possible to get a copy of the Visual Studio project which opens without the errors that seem to follow a warning message about source control?  I suppose that might be easier than trying to troubleshoot all the various errors that will come with everyone doing the paint-by-numbers following the tutorial

  85. Lucas says:

    I Added a new Class  Named Wall to the project, this Wall class as Children's of Project

    how will I go about creating a master like so

    Customers

     Project

        Wall

    can you help me out here Thanks in advance

  86. Tomi Halonen says:

    This isn't working in VS 2013 update 4.

    using (var db = new SQLite.SQLiteConnection(this.DBPath))

    —> Error 'SQLite.Net.SQLiteConnection' does not contain a constructor that takes 1 arguments

    And I just finished reading step-by-step VS2013 C#

    oh boy I feel dummy now.. maybe I could resolve this with time. It's hard to find answers because everything changes so rapidly.

  87. Tomi Halonen says:

    Ok… found it. Having a lot of learning todo.

    stackoverflow.com/…/sqliteconnection-statement-error

  88. Tomi Halonen says:

    (I was trying to use this newer/cleaner) version of package 'sqlite-net':

    http://www.nuget.org/…/SQLite.Net-PCL )

    async here –>

    http://www.nuget.org/…/SQLite.Net.Async-PCL

    Got to get familiar with these newer packages as well… older package (sqlite-net) hasn't been updated since 2014.

  89. Hitesh Kulkarni says:

    In above code, if I want to add an image for perticular customer then same image I want to display in listview. How can I do that?

  90. santhosh says:

    In my project DataBase is not created,I installed which you recommended software ,but i am not finding the path which is "C:\Data\Users\DefApps\APPDATA\Local\Packages\99ddd0c9-0d68-4835-a251-23d959f05700_qq78v5e2147vr\LocalState\customers.sqlite"  ..What i have to do..Can you please suggest me.

  91. AmirElamine says:

    thank you Mr Robert..

    i'm wondering if there is a solution to make a report view (reporting) and print it.