SQLite For Windows Phone App

Storing data in a variable is not always useful in app development, because it will be automatically reset or deleted when the app is closed. But as a app developer, we need to store more than one variable to do something like maintaining a user profile, a game score, game levels information, and so on. Hence to store data in well-structured manner I suggest use of SQLite for Windows Phone.

                              

SQLite is open source and it's available for nearly every platform on the market (mobile, web, client, and so on). With a simple SQL query we can insert, update, retrieve and delete data from the app storage.

So here I'm showing you a simple procedure for using SQLite for Windows Phone: You can use Video Tutorial for SQLite for Windows Phone.

1. We need to install SQLite for Windows Phone SDK in Visual Studio, so we can add reference to it. Open your project in Visual Studio and go to "Tools" >> "Extensions and Updates…".



2. Search “SQLite for Windows Phone” or "sqlite" [1] and Install it [2]. After installation Visual Studio will ask for a restart. Click on Restart to add new installed Extensions into Visual Studio.



3. Now we have to add this new installed extension. For that, right-click "References" in the project in the Solution Explorer then select "Add References…".

4. Select Windows Phone [1] then Extenstions [2] and mark on SQLite for Windows Phone [3] and then click on OK.

5. After adding the extensions we have to add some NuGet Packages. For that, right-click "References" in the project in the Solution Explorer then select "Manage NuGet Packages".

6. Search for “sqlite-net” [1] and install both packages, sqlite-net and sqlite-net-wp8 [2].

SQlite-net:

It's a wrapper for the native SQLite engine and it supports both the Windows 8 platform and Windows Phone 7 or 8.0. sqlite-net provides helper code that you will use to handle the SQLite database. It is written in C# and is meant to be simply compiled in with projects.

SQlite-net-wp8:

This provides the code between your source code and the SQLite for Windows Phone SDK. It's a C++/CX wrapper for SQLite functions that depend on sqlite-net. It can be used as an alternative library to charp-sqlite on Windows Phone 8.



7. SQLite does not support all platforms so we have to change the Build Configurations.

 

• Go to Solution Explorer and double-click on Properties.
  
  
• Select the Build Tab [1], change the Platform [2] using the following table and finally add one extra Conditional Compilation Symbols [3] “USE_WP8_NATIVE_SQLITE” and build the solution.
  
  
• It is required to set the proper native build engine.
  
  

  Deploying and Testing On	Choose Platform
  Emulator	X86
  Windows Phone Device	ARM

8. Now you are ready with the SQLite environment, you can store (in other words insert), update, retrieve and delete data.

9. Create a class for representing the table. Each attribute in a class becomes an attribute in the database table.

Note: You can create a separate class with the same namespace for this or can paste in the same namespace.

By using the following code we are creating an ID, Title, Text and CreationDate that are 4 columns in a table.

public sealed class Task
{
     // You can create an integer primary key and let the SQLite control it.
     [PrimaryKey, AutoIncrement]
     public int Id { get; set; }
     public string Title { get; set; }
     public string Text { get; set; }
     public DateTime CreationDate { get; set; }
     public override string ToString()
     {
          return Title + ":" + Text + " << " + CreationDate.ToShortDateString() + " " +CreationDate.ToShortTimeString();
     }
}

Sealed Class:

When sealed is applied to a class, the sealed modifier prevents other classes from inheriting from it.

In the following example, class B inherits from class A, but no class can inherit from class B.

 class A {}
 sealed class B : A {}
 // Attempting to inherit causes compiler error CS0239.
 // class C : B {}

get;set; :

This is an Auto-Implemented Property. It's basically a shorthand way of creating properties for a class in C#, without having to define private variables for them. They are normally used when no extra logic is required when getting or setting the value of a variable.

Instead of the following code we can write only: public string Name { get; set; }.

 public string Name
 {
    get
    {
       return name;
    }
    set
    {
       name = value;
    }
 }

10. Go to MainPage.xaml.cs and add the SQLite and Windows Storage namespaces.

 using SQLite;
 using System.IO;
 using Windows.Storage;

11. Now set the Database Path and declare a SQLite Connection variable.

DB_PATH: It's a database path and we create the database in an app storage area so that another app can't modify this database.

 // The database path.
 public static string DB_PATH =Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "sample.sqlite"));
 // The sqlite connection.
 private SQLiteConnection dbConn;

12. To open and close a Database Connection [with Class Table] on the starting and closing of the app page respectively we have to override the OnNavigatedTo and OnNavigatedFrom methods.

 protected override void OnNavigatedTo(NavigationEventArgs e)
 {
    // Create the database connection.
    dbConn = new SQLiteConnection(DB_PATH);
    // Create the table Task, if it doesn't exist.
    dbConn.CreateTable<Task>();
 }
 protected override void OnNavigatedFrom(NavigationEventArgs e)
 {
    if (dbConn != null)
    dbConn.Close(); // Close the database connection.
 }

The OnNavigatedTo method is preferable because it is only called once for each time the page becomes active. You can override theOnNavigatedTo method to load the requested data and enable or disable visual elements, and so on.

The OnNavigatedFrom is called just before a page is no longer the active page. We can override the OnNavigatingFrom method to perform any actions on the page just before it becomes inactive like closing the database connection.



13. In this example we are covering four main operations, in other words Insert, Retrieve, Update and Delete.

• Add a single List box for showing the current DataBase Table values
• Add 4 buttons for each single SQLite operation into the Mainpage.xaml. 

  <ListBox HorizontalAlignment="Left" Height="287" VerticalAlignment="Top" Width="456"Name="TaskListBox"/>
         <TextBox HorizontalAlignment="Left" Height="76" Margin="10,292,0,0"TextWrapping="Wrap" Text="Title" VerticalAlignment="Top" Width="436" Name="TitleField"/>
         <TextBox HorizontalAlignment="Left" Height="134" Margin="10,363,0,0"TextWrapping="Wrap" Text="Note" VerticalAlignment="Top" Width="436" Name="TextField"/>
         <Button Content="Insert" HorizontalAlignment="Left" Height="92"Margin="10,497,0,0" VerticalAlignment="Top" Width="216" Click="Insert_Click"/>
         <Button Content="Delete" HorizontalAlignment="Left" Height="92"Margin="230,497,0,0" VerticalAlignment="Top" Width="216" Click="Delete_Click"/>
         <Button Content="Update" HorizontalAlignment="Left" Height="92"Margin="10,594,0,0" VerticalAlignment="Top" Width="216" Click="Update_Click"/>
         <Button Content="Retrieve" HorizontalAlignment="Left" Height="92"Margin="230,594,0,0" VerticalAlignment="Top" Width="216" Click="Retrieve_Click"/>

14. Insert Operation: In this operation we are taking Title and Notes from the TextBox and current date from system and inserting it into the Task Table.

 private void Insert_Click(object sender, RoutedEventArgs e)
 {
       // Create a new task.
       Task task = new Task()
       {
             Title = TitleField.Text,
             Text = TextField.Text,
             CreationDate = DateTime.Now
       };
       // Insert the new task in the Task table.
       dbConn.Insert(task);
       Referesh_ListBox();
 }

15. Retrieve Operation: In this operation we are comparing Title with the existing Title column data and if a record is found in the DB then it returns the Notes written with that Title Filed.

 private void Retrieve_Click(object sender, RoutedEventArgs e)
 {
    // Retriving Data
    var tp = dbConn.Query<Task>("select * from task where title='" +TitleField.Text + "'").FirstOrDefault();
    if (tp == null)
    MessageBox.Show("Title Not Present in DataBase");
    else
    TextField.Text= tp.Text;
 }

16. Update Operation: In this operation we update the Note Field (in other words the Text) of the DB that has the same Title compared with the Title TextBox.

Before updating we have to check whether or not the Title is present in the DB.

 private void Update_Click(object sender, RoutedEventArgs e)
 {
    // here we updating Notes i.e. Text Filed using Title filed. before that we have to check row present or not
    var testdata = dbConn.Query<Task>("select * from task where title='" +TitleField.Text + "'").FirstOrDefault();
    // Check result is empty or not
    if (testdata == null)
    MessageBox.Show("Title Not Present in DataBase");
    else
    {
       var tp = dbConn.Query<Task> ("update task set Text='" + TextField.Text + "' where title = '" + TitleField.Text + "'")
       .FirstOrDefault();
       // Update Database
       dbConn.Update(tp);
       Referesh_ListBox();
    }
 }

17. Delete Operation: In this operation we delete the entire row by comparing the Title TextBox.

 private void Delete_Click(object sender, RoutedEventArgs e)
 {
    // Deleting Entire Row from DB by matching Title Filed
    var tp = dbConn.Query<Task>("select * from task where title='" +TitleField.Text + "'").FirstOrDefault();
    // Check result is empty or not
    if (tp == null)
    MessageBox.Show("Title Not Present in DataBase");
    else
    {
       //Delete row from database
       dbConn.Delete(tp); //you can delete single column e.g. dbConn.Delete(tp.Text);
       Referesh_ListBox();
    }
 }

18. Refresh ListBox: After each successful operation we need to refresh and update the ListBox.

 private void Referesh_ListBox()
 {
    // Retrieve the task list from the database.
    List<Task> retrievedTasks = dbConn.Table<Task>().ToList<Task>();
    // Clear the list box that will show all the tasks.
    TaskListBox.Items.Clear();
    foreach (var t in retrievedTasks)
    {
       TaskListBox.Items.Add(t);
    }
 }

19. Save it and test it.

20. Download Source Code SQLite Temp from Here.