Using SQLite in Windows 8 Store Apps

Article
11/13/2012

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.

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.

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:

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.

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 databasethis.DBPath = Path.Combine(<br>    Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");// Initialize the database if necessaryusing (var db = new SQLite.SQLiteConnection(this.DBPath))<br>{<br>    // Create the tables if they don't exist    db.CreateTable<Customer>();<br>    db.CreateTable<Project>();<br>}

 // 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>();<br>    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.

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

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.

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

Anonymous
November 14, 2012
Thanks for the video; really enjoyed and waiting for the code to upload to get a quick start.
Anonymous
November 15, 2012
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.
Anonymous
November 15, 2012
Can someone provide an example using Javascript/HTML5 instead of C# or is that possible?
Anonymous
November 18, 2012
Very nice walkthrough, really inspires me to start using SQLite in a Windows Store app!
Anonymous
November 20, 2012
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.
Anonymous
November 28, 2012
Using prepopulated database - see my blog post here: erikej.blogspot.dk/.../getting-started-with-sqlite-in-windows.html
Anonymous
December 20, 2012
When is the Fabrikam Fiber demo coming out that you showed us in Redmond/BUILD?
Anonymous
January 10, 2013
Is it possible to ask a question here?
Anonymous
January 10, 2013
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)?
Anonymous
January 20, 2013
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 ??
Anonymous
February 03, 2013
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.
Anonymous
February 05, 2013
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.?
Anonymous
February 11, 2013
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
Anonymous
February 15, 2013
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.
Anonymous
February 15, 2013
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
Anonymous
February 15, 2013
Robert, Thanks for your answer. I resolved this problem. The error was in App.xaml.cs file in method onLaunched. Now everything is OK.
Anonymous
February 18, 2013
inte, Glad to hear that. Let me know if you have any questions or run into any other issues. Robert
Anonymous
February 19, 2013
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
Anonymous
February 27, 2013
Why when I close demo app and relaunch demo app, the customer and project that I added disappear?
Anonymous
February 27, 2013
The comment has been removed
Anonymous
February 27, 2013
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
Anonymous
March 07, 2013
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
Anonymous
March 19, 2013
The comment has been removed
Anonymous
March 29, 2013
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.
Anonymous
April 16, 2013
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.
Anonymous
April 16, 2013
Dean, Bill Burrows has a tutorial and code that shows how to use VB with SQLite. Check it out at www.myvbprof.com/.../WinRT_SQLite.aspx. Robert
Anonymous
April 16, 2013
Thanks Robert, I will check it out. Thanks for the video- I enjoyed it
Anonymous
April 23, 2013
I wanna share my SQLite database with WF8 and Windows store apps. How can I do this?
Anonymous
April 25, 2013
msdn.microsoft.com/.../hh335063.aspx Perfect!
Anonymous
May 18, 2013
The comment has been removed
Anonymous
May 26, 2013
This is the best tutorial in this topic. Thank you very much.
Anonymous
May 29, 2013
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
Anonymous
May 29, 2013
Perfecto !!
Anonymous
May 31, 2013
Is there any compatibility issues between Windows8 & SQL?
Anonymous
June 03, 2013
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
Anonymous
June 21, 2013
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
Anonymous
June 24, 2013
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())
Anonymous
July 16, 2013
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
Anonymous
July 16, 2013
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
Anonymous
July 16, 2013
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.
Anonymous
July 16, 2013
hacknsack, Excellent. Let me know if you have any other questions. Robert
Anonymous
August 23, 2013
Great walkthrough. but how to store Image into SQLite.
Anonymous
August 27, 2013
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
Anonymous
September 16, 2013
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.
Anonymous
September 16, 2013
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.
Anonymous
September 16, 2013
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.
Anonymous
September 29, 2013
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?
Anonymous
October 22, 2013
Hi Robert how can i display my data from sqlite database to datagridview? im really need help with that thanks
Anonymous
October 23, 2013
Henry, Are you having issues with the sample app or with a different app? Robert
Anonymous
October 23, 2013
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.
Anonymous
October 24, 2013
Henry, Let's take this offline. Email me at rogreen at microsoft dot com. Robert
Anonymous
December 09, 2013
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???
Anonymous
December 09, 2013
I am using VS 2013 and selected x64 for Debug
Anonymous
December 19, 2013
can anyone tell me how to sync this sqlite database back to sql server?? i searched many site, not getting proper solution for this
Anonymous
December 20, 2013
saravanan, Check out http://syncwinrt.codeplex.com/. Robert
Anonymous
March 06, 2014
how to make app package with sqlite database in windows store app using c#
Anonymous
March 18, 2014
The comment has been removed
Anonymous
March 19, 2014
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
Anonymous
March 23, 2014
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?
Anonymous
April 02, 2014
Hi Robert, I can't find the SQLite extension, is that normal ?
Anonymous
April 09, 2014
Might spend the source code?
Anonymous
April 23, 2014
After its saved, when you close and re-run the app, the details change back? is there a way to permanently change the content?
Anonymous
May 22, 2014
sir.. how to use collate no case in linq manner while creating sqlite table...
Anonymous
June 08, 2014
Is it necessary to use C# while using SQLite? I am developing a C++/XAML app so that is why I ask.
Anonymous
June 10, 2014
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(); }
Anonymous
June 11, 2014
Nick, You can use SQLite with C++. For example, check out visualstudiomagazine.com/.../using-sqlite-with-modern-c.aspx. Robert
Anonymous
June 11, 2014
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
Anonymous
June 29, 2014
Hey Robert, this looks like a great app. I wonder if you have updated a version for windows 8.1?
Anonymous
July 18, 2014
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?
Anonymous
July 18, 2014
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
Anonymous
October 13, 2014
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?
Anonymous
October 14, 2014
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.
Anonymous
November 02, 2014
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.
Anonymous
November 02, 2014
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.
Anonymous
November 25, 2014
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
Anonymous
November 25, 2014
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
Anonymous
November 25, 2014
jjbarbas, Does the error go away if you build the project? Robert
Anonymous
November 25, 2014
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.
Anonymous
November 25, 2014
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
Anonymous
November 26, 2014
If the error persists and compile the project Thanking you in advance for your response
Anonymous
December 03, 2014
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
Anonymous
December 11, 2014
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.
Anonymous
December 26, 2014
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?
Anonymous
January 27, 2015
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
Anonymous
February 04, 2015
I want a javascript demo??Please help me! thanks
Anonymous
February 14, 2015
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?
Anonymous
February 14, 2015
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
Anonymous
March 13, 2015
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
Anonymous
May 18, 2015
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.
Anonymous
May 18, 2015
Ok... found it. Having a lot of learning todo. stackoverflow.com/.../sqliteconnection-statement-error
Anonymous
May 18, 2015
(I was trying to use this newer/cleaner) version of package 'sqlite-net': www.nuget.org/.../SQLite.Net-PCL ) async here --> 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.
Anonymous
June 15, 2015
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?
Anonymous
June 28, 2015
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.
Anonymous
July 01, 2015
thank you Mr Robert.. i'm wondering if there is a solution to make a report view (reporting) and print it.

Share via

Using SQLite in Windows 8 Store Apps

Comments

Additional resources