In Windows Store Apps there are variety of ways/methods to store your application data. Examples of such mechanisms would be Web Storage, IndexedDB etc. and even SkyDrive. Today I am going to write about my favorite data storage method in developing Windows Store Apps which is “SQLite”.
SQLite is a relational database management system contained in a small C programming library. SQLite stores the entire database as a single cross-platform file on a host machine. You can examine this file using tools such as SQLite Database Browser which you can download from here. SQLite does not support all the SQL features. Here is a list of features which are not supported in SQLite.
Now let’s see how we can use SQLite in a Windows Store App. I am creating a blank Windows Store App. Now I need to get SQLite binaries for Windows Runtime. You can download Precompiled Binaries for Windows Runtime from here.
Once the vsix file has completed installing, then you need to add a reference to "SQLite for Windows Runtime" from your project.
One important thing. If you want to publish a app with SQLite to Windows Store, make sure to add reference to Microsoft Visual C++ Runtime package as well. Now after adding the reference, when I compile the project, I am getting a error.
|Changing Target Plaform|
Now I am going to add a wrapper for SQLite which was written using C#. So I can write my code fairly easily. I am going to Nuget and searching online for “sqlite-net” and I am adding it to my project.
Once the package has completed configuring, you can see two new classes (“SQLite.cs” and “SQLiteAsync.cs”) added to your project. Now I am almost done configuring the project for SQLite. Now let's move into writing some codes.
Here I am using the Repository Pattern. Repository Pattern is a software design pattern where the entity classes and the entity functionalities are separated. To my project I am adding following folders,
- “DataModel” – Stores classes which model tables (entities) inside my SQLite database.
- “Repository” – Stores classes which has CRUD methods of Entity models.
- “DataAccess” – Stores classes for database connection.
First I am adding DbConnection.cs and IDbConnection.cs to “DataAccess” folder.
Here I am getting the path of the current application and creating my SQLite database there. Now I am adding two classes to “DataModel” folder which are “Employee” and “Department”.
In my “Repository”, I am going to have CRUD operations for above two entities. Here I am only pasting the code for Employee entity.
In my EmployeeRepository class I am implementing the above IEmployeeRepository interface.
Now I am moving to the UI. In my MainPage.xaml, I have a stack panel which contain controls for user to insert data. And then I have a list view to show data stored in the database.Now in my code behind, first I am initializing the database.
Now I can call methods in my Repository classes, through these objects. Here are some screenshots of the final application.
Appreciate your feedback.