Local Database using SQLite

In this article I’m going to show how you can use SQLite as a Local database in windows phone 8.1 without the help of LINQ.

 

We have written some new posts for the SQLite Database. You can view them using the links given below. They provide a more easy and clear descripition on how to set up, install and use SQLite Database in your Windows app.

 

SQLite Database in Windows phone app – Part 1 (Installation and Setup)

SQLite Database in Windows phone app – Part 2 (SQLite Operations)

SQLite Database in Windows phone app – Part 3 (SQLite Exploration)

 

Prerequisite

Data Binding concept, Basic SQL query knowledge.

If you’re not familiar with Data Binding concept please check out our previous article

How to bind ListBox to data in Windows phone application – Introduction

Here’s how you can build the app,

Step 1: Create an empty project

Create a Windows Phone 8.1 XAML application.

Before we can use SQLite in our project, there are some things that you need to have in your project reference.

References

SQLite SDK for Windows Phone 8.1

The SQLite SDK, wp8.1 version. You can download it from here. Then add a reference to the SQLite SDK in your app.

 

add_reference_to_sqllite_wp81

SQLitePCL

SQLitePCL nuget package. You need search for Portable Class Library for Sqlite in nuget search box.
How to integrate a Toolkit or Third party SDK with windows phone app using NuGet Package Manager

 

11

Once the SDK is installed, add it as your project reference and the SQLitePCL nuget package also.

Note

There are several nuget packages (wrapper class) available that can manipulate the SQLite database. One of the most common is sqlite-net. We need to know LINQ query to use that wrapper. But in this article we’re going use SQLitePCL. In another article I’ll show the use of sqlite-net.

 

The advantage of this wrapper is that we can write ‘traditional’ query to use the database. And disadvantage of this wrapper is that it has no official documentation. Basically you’ve use it on a ‘test & trial’ basis. But this wrapper has many functions available that is easy to code.

Note

Once you’re done with adding the reference, you might see a triangle yellow tiny button on the SQLite reference. Change the project configuration to either ‘x86’ or ‘ARM’. Alert should be gone.

 

Queries

To illustrate all the queries and functions, we’ll use a separate class for handling the database related executions.

So create a class Database_Controller. We’ll now write several static function for our project. The first function should be in our class to create the Database table. As you can see from the code that it’s pretty straight forward. You just need to create a connection and pass the query. Step() method basically executes the query.

Create

 


 

Insert

Another common query is to insert data into database. So the next function will does the job of adding data.

The only thing that I need to explain for this function is ‘Bind’. You can see that in query instead of real value I put ‘question mark’. This is how database system is kept protected from SQL Injection virus. Database can keep track of how many ‘?’ is put in the query. Then this mark are replaced the by the original value with help of Bind function. And the index for ‘?’ starts from 1.

Select

Now another common queries of database system is the SELECT query. There’s one thing new here. Let’s take a look.

 

After executing queries, all the results are stored in a class SQLiteResult. You need traverse to get the results. And SQLiteResult class don’t requires to be initialized. When traversing the pointer in the Result set. There remains two state of the class SQLiteResult. When it becomes SQLiteResult.DONE, it means that the pointer has traversed all its data. And when the state is SQLiteResult.ROW, it means the pointer is at the start of any row. I kept the return of this function as a list because I want to bind the data with the xaml. If you’re familiar with data binding you should know why I created Student class inspite of having student table in database. 😉

Delete/Update

Deleting a row and editing a row is pretty much similar in a sense that we need pass a unique attribute to delete or to edit. I’ll demonstrate only deleting part. You’ll learn only when you struggle. And do it efficiently (hint: use queryString concept to use a same page for two different purpose 😉 )

Though I did not make the ID attribute as primary key, I’m assuming it’s unique.

Now that we’re done with queries, we’ll put into our xaml pages.

Wait, before we put it into any xaml, we need to create the table first. The best way to create the table in on the launch time of the app. So we’ll put these two lines in the App.xaml.cs file.

 

Rest of the work is easy for you. Create ListView, Bind it to Student ObservableCollection. And see the result. For the demonstration, I implemented the delete function the ItemClick event.

 

The ListView uses the StudentDataTemplate which is defined as Resources at the top of the page.

abc

You should implement in your real project with a proper way. The code is behind the function is below.

 

 

DONE! Congratulations again for completing this task successfully. You can now make the most use of local database. I’d love to get your feedback on this. Download the full source code of this project and use it as an reference.

 

Download SqliteTutorial.zip

ObservableCollection in C#

MSDN describes ObservableCollection as

Represents a dynamic collection of data that provides notifications when items get added, removed or when the whole list is refreshed

This tip is dedicated to experienced WP developer who had to suffer a lot earlier for INotifyPropertyChanged.

An ObservableCollection can be updated from UI exactly like any collection. The true difference is rather straightforward:

ObservableCollection implements INotifyCollectionChanged which provides notification when the collection is changed. It allows the binding engine to update the UI when the ObservableCollection is updated.

Windows Phone developers who are relatively new, it is suggested to use ObservableCollection instead of List. It has the capabilities of a List in addition to extended capabilities.

Those who developed in WP 8.0 or before, will be glad to know that ObservableCollection will remove a huge pain when you’re going to implement any DataBinding. Earlier we had to use INotifyPropertyChanged interface to make the binding interactive and efficient. And truly that was a pain in the. This modern List like ObservableCollection takes care of all the things. According to Visual Studio Documentation.

Here an example of How to Create and Bind to an ObservableCollection.

This article gives a basic understanding of List, ObservableCollection, and INotifyPropertyChanged.

Fetch web data using HTML Agility Pack in Windows Phone 8

In this article I’m going to explain how you can fetch data from web. It’s a very powerful tool I must say. We can make any static website into ‘app’ and also use this tricks in dynamic website to some extent. You must have a basic knowledge of HTML and have at least understanding of LINQ query. I’ll be using a website popularly known as “songs.pk” to fetch the album names. And I’ll also use data binding. If you’re not familiar with Data Binding, please go to this link.

How to bind ListBox to data in Windows phone application – Introduction

Okay let’s get started. Create an Empty Project (Windows Phone 8.1). Create a new class Album and put these lines as shown below.



Basically these will be the data structure for the list that will be shown in the MainPage.

And the next step is to add the reference of HtmlAgilityPack. Now let me tell you that there is no official version of HtmlAgilityPack for windows phone 8.1. It was built for WP 8.0 version. As this is free of cost, the developer didn’t make the WP 8.1 version. Still the original version works for WP8.1 Silverlight version. But in windows phone 8.0 and 8.1 Silverlight version doesn’t support SD Card writing feature. This is why I choose to work on 8.1 version so that you can make the best use of Web Scraping like downloading file from internet. So the question is how do I add the reference of HtmlAgilityPack in 8.1 project solution?

A modified version of HtmlAgilityPack has been released by some developer. I got it from stackoverflow. You can download it from here.
HtmlAgilityPack.src
You will have to manually add it to your solution.

First copy the HtmlAgilityPack.src folder into your project folder. You’ll get a HtmlAgilityPack.dll file in this directory

HtmlAgilityPack.src è HtmlAgilityPack.Universal è bin è Debug

Now all you’ve to do is right click on your reference of your visual studio project solution and then click the Add Reference. A pop up will appear, click browse and select the HtmlAgilityPack.dll file.

Done! Now you are ready to code finally. Put these lines into the main Grid of your MainPage.xaml file.



Here you can see that Databinding is implemented in a TextBlock. albumName is basically the variable name you declared in you Album class.

Now go to the MainPage.xaml.cs file and write this line to use HtmlAgilityPack.

using HtmlAgilityPack;

Before you use HtmlAgilityPack, you need to have some basic knowledge about async and await.

When the keyword async is written in function, it means that this function will run in parallel in another thread. This function won’t block UI of the app. Most of the case it’s used to write large file into system or download some information from net so that the user don’t feel the app is frozen.

Await keyword is written before a method which basically invokes/calls the method which is responsible for downloading/writing.

This is very top level description. You can Google to know more about this. By the way these keywords works as a pair like ‘as ….. If’ phrase.

Now put this function in your MainPage.xaml.cs file. You’ll see some error, don’t worry, eventually everything will be fixed.




Here you can see the use of async and await pair. We declared ObservableCollection data type and called a method scrape with a parameter.

ObservableCollection is just another version of List. Google yourself to find out more. You can also use List here. Now paste the core function scrape in this file which basically does the scraping.



Lots of things are unfamiliar to you now. I’m going to simplify every bit of code. So basically to use HtmlAgilityPack, we need to have HtmlDocument which can be used only by HtmlWeb Class.

The above line actually download the whole source code of your desired URL.

Now you’ve to make use of the other available function of HtmlAgilityPack. If you go to the source of songspk.name and you’ll see that all the latest album names are kept under a class songs-list1 which has an unordered list <ul> node. So from the downloaded document we’ll select that portion only. To select a part of the Document, we need to use the class HtmlNode.

We’ll talk more about later. Now if we closely look at the source of the <ul> node, we’ll see that there are some <li> node into this node. And the first <li> node is of no use for our example. We’ll remove that. Another problem is that when we try to fetch data from net via HtmlAgilityPack, we get a garbage node for each of the internal node. We’ll have to remove that nodes also.

After getting the desired nodes, we’ll have to filter these nodes again. Because these <li> contain several internal nodes. But we only need the one that has URL and the name of the Album. Using a foreach loop we’ll create objects of Album class and put the objects into our ObservableCollection.

Another thing to look for is the parameter of the Descendants function. It is a LINQ query. If you’re not familiar with this, please search on the web to learn some basic LINQ query. And the scrape function has Task<…> keyword. It actually supports the async-await keywords. The Task means that this function will create a new thread and destroy this thread after the function completes its execution.

Download WebScraping.zip

Disclaimer: Windows App Tutorials doesn’t encourage you to use unauthorized content from any website without prior permissions. The demo is just to explain the usage of HTMLAgilityPack.