Introduction

We know that every smartphone handle is lightweight data to handle their data. Like all, the Windows Phone OS favors SQLite. Moving to a demonstration, we have this.

By CRUD, I mean Create, Read, Update and Delete. These are the fundamental operation we try on any DBMS. As prerequites, I suppose you have prior knowledge of SQL query. Although, I have a brief intro in their respective section.

SQLite

Table of Contents

Environment Setup

Step 1

Create an Empty Windows Phone 8 Project. Now, before proceeding to anything, you need to update your Extensions (and, yes, it is mandatory).

Tools > Extension and Updates

behaviors SDK

In the Online tab, search for “sqlite for windows phone” and download the update.

SQLite for windows

Note: If your project belongs to Windows Phone 8.1, then update for SQLite for Windows Phone 8.1 else do as shown.

After downloading, you get a confirmation. Click on Close and move on.

Step 2

After installing the library for SQLite, we need the sqlite-net-wp8 NuGet package. Actually, it a helper-class file.

So, move to Tools > Manage NuGet Packet Manager > Manages NuGet Packages for Solution and search for sqlite-net-wp8 in the search tab.

SQLite prototype

Step 3

Until now, we have included the desired library, helper class. Now, we define the CPU architecture of the app,

Build > Configuration Manager. And, change the Active Solution Platform to x86 (for the emulator) or ARM (for a Windows Phone Device).

Code Illustration

Step 1

We have now completed all the requirements. So, let's start the coding part. First, we will design a look-alike prototype for this demonstration.

xmlcode

And try to mock the XAML layout window.

Step 2

As you know, every database has its schema in other words definition of its table.
So, here we will create a class file that has table's schema.
For this, create an empty class file in your project.

task

I have Task.cs that defines the schema of my table.

So, our table will be like something this.

Fig: Task Table

table

In this layout, we have three attributes and id is the Primary Key for the table. And we want our id to be incremented automatically.

  1. using SQLite;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Text;
  6. using System.Threading.Tasks;
  8. namespace SQLiteApp2
  9. {
  10. public sealed class Task
  11. {
  12. // Schema Of The Table
  14. [PrimaryKey, AutoIncrement]
  15. public int id { get; set; }
  16. public string name { get; set; }
  17. public string platform { get; set; }
  20. }
  21. }
With this code, you can relate your layout better. Here also, we have three fields of respective data types that have get and set properties.

Step 3

Before moving to the code part, add the desired namespaces to your project as in the following:
  1. using System.IO;
  2. using Sqlite;
  3. using Windows.Storage;
  4. using SQLite;
We will now set the database path and its connection. Since the path will be a global field and the database connection will be in the OnNavigatedTo event.
  1. // Path of The Database
  2. public static string DB_PATH = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path,"sample.sqlite"));
  3. private SQLiteConnection dbConnection;
Be sure to follow the same rule to create the path of the database. And, in the next line, we created a reference of the SQL connection.

And, in the On_NavigatedTo event.
  1. protected override void OnNavigatedTo(NavigationEventArgs e)
  2. {
  3. // When it Enters into Page
  5. dbConnection = new SQLiteConnection(DB_PATH);
  6. dbConnection.CreateTable<Task>();
  8. }
Unlike in OnNavigatedFrom(), we will release the database connection resource. Being exact, we will close the database connection to what we have started in OnNavigatedTo.
  1. protected override void OnNavigatedFrom(NavigationEventArgs e)
  2. {
  3. // When it Leaves from
  5. if(dbConnection!=null)
  6. {
  7. dbConnection.Close();
  8. }
  9. }
We can now start our CRUD operations. And, we will go in a systematic order for a better understanding.

We begin with INSERTION and then SELECTION.
  1. private void btnInsert_Click(object sender, RoutedEventArgs e)
  2. {
  3. // INSERT into Table
  5. Task task = new Task()
  6. {
  7. name = nameTextBox.Text.ToString(),
  8. platform = platformTextBox.Text.ToString()
  9. };
  11. // Now, Insert
  12. dbConnection.Insert(task);
  14. // Confirmation
  15. MessageBox.Show("Successfully Inserted .","Done",MessageBoxButton.OK);
  17. // Reset The Fields
  18. nameTextBox.Text = "";
  19. platformTextBox.Text = "";
  21. }
In the beginning, we have created an instance of Task (actually, it is a record for the table). And we have predefined the Insert() method for insertion. So, there is no need for a query. As the parameter we have ed the task's instance.

Unlike Insert, we don't have any exact method for Selection. So, we need the query() method.
  1. private void btnSelect_Click(object sender, RoutedEventArgs e)
  2. {
  3. // Retrive Data
  5. var data = dbConnection.Query<Task>("select platform from task where name='"+nameTextBox.Text.ToString()+"'").FirstOrDefault();
  7. if(data != null)
  8. {
  10. platformTextBox.Text = data.platform.ToString();
  11. }
  12. else
  13. {
  14. MessageBox.Show("Sorry!!","Error",MessageBoxButton.OK);
  15. }
  16. }
And "data", which is of var type, stores the returned value, since we ed the generic Query which is of type Task. So, in the return we will get the output as a Task type.

That's why we have written "data.platform" to access its value. And what if the data is Null. Then, the table is empty.

Moving to updating the table, you can do it in a similar way.
  1. private void btnUpdate_Click(object sender, RoutedEventArgs e)
  2. {
  3. // Upadte Button
  5. if(nameTextBox.Text != "")
  6. {
  7. Task temp = dbConnection.Query<Task> ("update task set platform='"+platformTextBox.Text.ToString()+"' where name='"+nameTextBox.T
  8. ext.ToString()+"'").FirstOrDefault();
  9. dbConnection.Update(temp);
  10. MessageBox.Show("Succeffully Updated !!");
  13. }
  14. }
Regarding this code, I have checked whether the name TextBox is not empty and if it is then we moved to the main part of the code. Where we executed the desired query and then ed the instance of that query in the Update() method. Finally, we want to update the table.

We will do exactly the same thing do for the deletion.
  1. private void btnDelete_Click(object sender, RoutedEventArgs e)
  2. {
  3. // Delete
  5. Task temp = dbConnection.Query<Task>("select platform from task where name='"+nameTextBox.Text.ToString()+"'").FirstOrDefault();
  7. if (temp != null)
  8. {
  9. dbConnection.Delete(temp);
  10. MessageBox.Show("Succeffully Deleted !!");
  11. }
  12. else
  13. {
  14. MessageBox.Show("No Row Selected","Error",MessageBoxButton.OK);
  15. }
  16. }
Emulator Snapshots

Emulator Snapshots

Conclusion

SQLite provides great support for creating highly durable static apps. With a database, you can handle the data efficiently and easily.

For any issue, feel free to ask and try to resolve it from the enclosed solution file.

For SQL queries you better learn about them from: SQL Tutorial.