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…".





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

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".





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

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.

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.

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.





” 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

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

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

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 { [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(); } }

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 {}

This is an

. 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; } }

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

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

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

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

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

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) { dbConn = new SQLiteConnection(DB_PATH); dbConn.CreateTable<Task>(); } protected override void OnNavigatedFrom(NavigationEventArgs e) { if (dbConn != null ) dbConn.Close(); }

The

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

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.

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" />

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) { Task task = new Task() { Title = TitleField.Text, Text = TextField.Text, CreationDate = DateTime.Now }; dbConn.Insert(task); Referesh_ListBox(); }

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) { 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; }

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) { var testdata = dbConn.Query<Task>( "select * from task where title='" +TitleField.Text + "'" ).FirstOrDefault(); 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(); dbConn.Update(tp); Referesh_ListBox(); } }

In this operation we delete the entire row by comparing the Title TextBox.

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

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

private void Referesh_ListBox() { List<Task> retrievedTasks = dbConn.Table<Task>().ToList<Task>(); TaskListBox.Items.Clear(); foreach (var t in retrievedTasks) { TaskListBox.Items.Add(t); } }

Save it and test it.

.