Working With SQLite in Windows Store Apps

Look at the following procedure.

1. Create a new “Windows Store App”. Here, this sample app targets Windows 8.1.

2. Right-click on the project and go to “Manage NuGet Package Installer”.



3. Install “SQLite-net”.



4. Check in the Solution Explorer for SQLite.cs and SQLiteAsync.cs files.


5. Add references to the project.



Right-click on the References to the project under solution, select Add References. This opens up the Reference Manager of the project.



In the Reference Manager click on Windows Version. My application targets Windows 8.1 and therefore I have selected “Windows 8.1”. Select Extensions from the list as shown in the preceding screen shot. Now you will get the list of SDKs applicable to your project. As could be seen in the screen, SQLite for Windows Runtime (Windows 8.1) is available in the list post installation of SQLite from NuGet Package Installer.



Now check/tick the two options Microsoft Visual C++ 2013 Runtime Package for Windows 8.1 and SQLite for Windows Runtime (Windows 8.1) as shown in the screen shot. Then click “Ok”.



Check whether or not the references are added. You can see references are added but it is showing an exclamatory mark on these added references. If you build the application now you will get some warning messages along with the error messages in all certainty and the build fails with some messages as shown below. This is because the SQLite we have installed will not support the “Any CPU” processor architecture.





Now change the target platform from “Any CPU” to your CPU Architecture. In my case, the CPU Architecture is x64. The procedure to check your CPU architecture is specified below.

6. Write code to create database in App.xaml file.

1. Add the “SQLite” namespace by adding the statement “Using SQLite”.
   
   
   
2. Create separate classes in the App.xaml.cs file for the tables that you would like to create. Here, in this example, I have created a table named “Appointments”.
   
   Let us consider the table structure of “Appointments”.
   
   

   appointmentId	appointmentDescription	appointmentPersonName	appointmentPlace
   1	Meeting with manager	Chetan	Global Village
   2	Meeting with HOD	Ajaykumar	RVCE
   3	Sync up with Deputy Manager	Anoop	UB City
   4	Meeting with close friend	Akshay	A2B, Satellite
   5	Meeting with client	RameshRavan	Electronic City

   The preceding table represents an “Appointments” table that contains 4 columns, each of which could become properties of the class “Appointments”; appointmentId, appointmentDescription, appointmentPersonName and appointmentPlace are the 4 properties in this class. You need to use [SQLite.AutoIncrement] above the property to make the column as an auto increment by one. You need to use [SQLite.PrimaryKey] above the property in the class to make it a primary key in the table.
   

3. Each class should contain properties and these properties represent the columns of the table. Create this Appointments class inside the App class.
   
   
   In the preceding example, appointmentId has an auto-increment column set and this field is also set as the primary key. So, as soon as we add rows to the table, appointmentId will be incremented automatically from an initially set value of "1".
   
   
4. Create a separate function to create a database and call it from the application class constructor, in other words “App( )”.
   
   
   
   CreateDataBase( ) is a separate function to create a database and the table “Appointments” within this database. Here “AppDB” is the database name. A database file named “AppDB” will be created when you deploy the application in an emulator or in your local machine. CreateTableAsync creates the table named Appointments asynchronously. During the asynchronous operation call, we need to put the “await” keyword prior to the line start. The asynchronous call can only be made through this function if we have put the “async” keyword prior to the return type of the actual function definition.
   
   
   
   The code above represents the call to the “CreateDataBase” function.
   
   
5. Create objects of the “Appointments” class, assign values to the properties of the object and then insert these objects into the table. This is akin to inserting a row into the “Appointments” table. Each object of the “Appointments” table created represents a row in the Appointments table. The properties set of the objects that are set represent the values of each cell.
   
   
   
   The code above represents the insertion of five rows into the table. The code is written in a separate function named “InsertData( )”. The function must be called from the application class constructor, in other words “App( )”.
   
   
   
6. Avoid reinsertion of data and recreation of the data by putting an appropriate condition before calling the “CreateDB” and “InsertData” functions.

7. Setup the controls in the page to bind the data coming from the database.



In this example application, I have taken a “ListView” control and I will be binding the data to the ListView. Drag and drop it from the toolbox on the Designer View of the XAML page that you are willing to view the Appointments table data upon.



Here, in the example application, the name of the ListView is “listOfAppointments”. The page name is MainPage.xaml.

8. Pull the data from database and bind them to the UI control.



9. See the results to confirm the binding of the data.



The preceding screen shot shows the ListView with data from the database.

Note: Put an appropriate condition in the App.xaml file when inserting rows into the table to avoid re-insertion of rows into the table.

10. See the database file created after running the application.

1. Click on the Package.appxmainfest under the project files list in the Solution Explorer.
   
   
   
   
2. Note down the Package Name, by clicking the Package tab on the right side.
   
   
   
   
3. Once you run the application in an emulator or a local machine, go to C:\Users\YourMachineUsername\AppData\Local\Packages” in the sample application created. In my machine, the path is “C:\Users\M1023130\AppData\Local\Packages”.
   
   
4. Search for the package name in the path “C:\Users\YourMachineUsername\AppData\Local\Packages” on your machine. Open the folder with the Package Name.
   
   
   In my project the Package Name is “185398f4-1ced-46c5-b81d-c147fdaca850” and you can see the folder with the same name in the preceding screenshot. Open this folder.
   
   You will find a list of folders. Open the folder named “LocalState”. In this LocalState folder you will get your database file, in other words AppDB that is required for my project. The following screenshot shows the database file present in the LocalState folder.
   
   
   

11. Download the SQLite Database browser from Database Browser for SQLite and install it. Open the SQLite Database browser application.

Click on the “opendatabase” option and copy & paste the path of this database file, to open this file in the SQLite Database browser.





The preceding screenshot shows the selection of database file through the file picker pop-up that appears after clicking the open database option in the SQLite Database Browser. Now click on “open” after selecting the database file, which in this case happens to be AppDB.



The preceding screen shot shows the tables present in the selected database through the use of the SQLite Database Browser. In my example project, “Appointments” is the table present in the database and the columns with the column definition is shown in the preceding screen. You can create and modify SQLite databases using this browser (SQLite DatabaseBrowser).