WindowsPhone 8.1 SQLite: How to Store Data in DataBase for Beginners Tutorial(C#-XAML)

Introduction:

If you want to implement sqlite database application for windowsphone 8.0,you may helped from my previous article.Now from this article we will learn about 'Sqlite support in WindowsPhone Store 8.1'.This article is compared with my previous article,so that we can easily understand the changes in WP8.0 & WP8.1 to setup Sqlite environment.

Requirements:

• This sample is targeted for windowsphone store 8.1 OS,So make sure you’ve downloaded and installed the Windows Phone 8.1 SDK. For more information, see Get the SDK.
• I assumes that you’re going to test your app on the Windows Phone emulator. If you want to test your app on a phone, you have to take some additional steps. For more info, see Register your Windows Phone device for development.
• This post assumes you’re using Microsoft Visual Studio Express 2013 for Windows.

Table of Contents: 

Some extensions to be installed in order to interact with Sqlite from your WP8.1 app.And now it is very easy to setup sqlite environment in our apps.So this post covers all about sqlite with beginners level like this.

1)How can i setup SQLite environment?

2)How to perform all SQLite CRUD operations ?

3)How to bind SQLite  data to listbox?

4)How to explore my"SQLite"  database data?

Description:

SQLite is a very light weight database. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.So let's start with above hierarchy which is earlier discussed in "Table of Contents".

1)How can i setup SQLite environment?

Sqlite is directly not available in windowsphone ,and don't worry now it is very easy to setup sqlite in windowsphone apps.So we need to follow two steps only (i.e Installing SQLite for Windows Phone SDK,Installing sqlite-net-wp8 package)

1.1)Installing SQLite for Windows Phone SDK:

It’s worth noting that the current version (3.8.8.0) has a flaw in the package metadata that prevent it to show up in the Visual Studio 2013 "Tools | Extensions and Updates" page. You need to go to the web page, download and install the VSIX package by yourself:

Click on Download and You can directly open it while downloading, authorize installation and click install when ready.

Open Microsoft Visual Studio Express 2013 for Windows and then create new project type Blank App(Ex:SQLiteWp8.1)

Open Solution explorer,Right click on 'References' folder of your current project.

Then add the reference to the "SQLite for WindowsPhone 8.1" library:

When done, you can see that the proper references to SQLite and Visual C++ 2013 Runtime have been added to respective project:

You may have already noticed, that the references show a warning symbols,to resolve that the next thing to do, before compiling the solution is changing the architecture of the target platform. This is the fact that the engine of Sqlite is written in C ++, and the default target platform set in the project is Any CPU. This mode is not supported. To do this in the main menu of VisualStudio have the command "Compile", then "Configuration Manager", as shown in the figure.

In the next dialog, we note that we have several choices of platforms, Any CPU (the default), ARM, x64 and x86.

We have to select the target platform second, where we are trying the application. If we use a tablet, or a phone with ARM processor, we have to select the ARM platform. If we are using the emulator or in the case of Windows Phone, a PC in the case of Windows, we have to select x86 or x64, everything depends on your processor if 32 or 64 bits. In my case, I tried the sample application on a emulator  with Windows Phone OS 8.1, so I chose the way of configuration x86.

1.2)Installing sqlite-net package:

After installing the library for SQLite, we need sqlite-net NuGet package.With this library, we will be able to perform all the operations that you normally do in a database, such as Insert, Delete, Update and run search queries. Sqlite-net also offers an approach typical ORM. This package provides two helper classes (SQLite.cs and SQLiteAsync.cs),So to install it right click on your project (i.e in my case project name is SQLiteWp8.1) =>Click on "Manage NuGet Packages" and search for "sqlite-net"  => Click on "Install" button.After that we should found below dialog:

***Wow now whole SQLite set up process is completed,so now we are going to work with all CRUD operations***

2)How to perform all SQLite CRUD operations ?

Note: From this step on-wards,most of content will be same as my  previous article.Please be note a few changes made in this post.

So its time to perform all SQLite CRUD(Create,Read,Update,Delete) operations.So my thought is in this sample i made one  single "DatabaseHelperClass.cs" class for whole application and handling the SQLite operations with this helper class. Lets see first my table structure first 

Here i am trying to create Table name is "Contacts" in "ContactsManager.sqlite" database.So my class "Contacts" with all getter and setter methods(Id,Name,PhoneNumber,CreatedDate) to maintain single contact as an object.

C#

public class Contacts
    {
        //The Id property is marked as the Primary Key
        [SQLite.PrimaryKey, SQLite.AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        public string PhoneNumber { get; set; }
        public string CreationDate { get; set; }
        public Contacts()
        {
            //empty constructor
        }
        public Contacts(string name, string phone_no)
        {
            Name = name;
            PhoneNumber = phone_no;
            CreationDate = DateTime.Now.ToString();
        }
    }

2.1)Writing DatabaseHelper Class to handle database operations(i.e CRUD):

C#

//This class for perform all database CRUD operations 
    public class DatabaseHelperClass 
    { 
        SQLiteConnection dbConn; 
        
        //Create Tabble 
        public async Task<bool> onCreate(string DB_PATH) 
        { 
            try 
            { 
                if (!CheckFileExists(DB_PATH).Result) 
                { 
                    using (dbConn = new SQLiteConnection(DB_PATH)) 
                    { 
                        dbConn.CreateTable<Contacts>(); 
                    } 
                }  
                return true; 
            } 
            catch 
            { 
                return false; 
            } 
        } 
        private async Task<bool> CheckFileExists(string fileName) 
        { 
            try 
            { 
                var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName); 
                return true; 
            } 
            catch 
            { 
                return false; 
            } 
        } 
 
        // Retrieve the specific contact from the database. 
        public Contacts ReadContact(int contactid) 
        { 
            using (var dbConn = new SQLiteConnection(App.DB_PATH)) 
            { 
                var existingconact = dbConn.Query<Contacts>("select * from Contacts where Id =" + contactid).FirstOrDefault(); 
                return existingconact; 
            } 
        } 
        // Retrieve the all contact list from the database. 
        public ObservableCollection<Contacts> ReadContacts() 
        { 
            using (var dbConn = new SQLiteConnection(App.DB_PATH)) 
            { 
                List<Contacts> myCollection  = dbConn.Table<Contacts>().ToList<Contacts>(); 
                ObservableCollection<Contacts> ContactsList = new ObservableCollection<Contacts>(myCollection); 
                return ContactsList; 
            } 
        } 
         
        //Update existing conatct 
        public void UpdateContact(Contacts contact) 
        { 
            using (var dbConn = new SQLiteConnection(App.DB_PATH)) 
            { 
                var existingconact = dbConn.Query<Contacts>("select * from Contacts where Id =" + contact.Id).FirstOrDefault(); 
                if (existingconact != null) 
                { 
                    existingconact.Name = contact.Name; 
                    existingconact.PhoneNumber = contact.PhoneNumber; 
                    existingconact.CreationDate = contact.CreationDate; 
                    dbConn.RunInTransaction(() => 
                    { 
                        dbConn.Update(existingconact); 
                    }); 
                } 
            } 
        } 
        // Insert the new contact in the Contacts table. 
        public void Insert(Contacts newcontact) 
        { 
            using (var dbConn = new SQLiteConnection(App.DB_PATH)) 
            { 
                dbConn.RunInTransaction(() => 
                    { 
                        dbConn.Insert(newcontact); 
                    }); 
            } 
        } 
        
        //Delete specific contact 
        public void DeleteContact(int Id) 
        { 
            using (var dbConn = new SQLiteConnection(App.DB_PATH)) 
            { 
                var existingconact = dbConn.Query<Contacts>("select * from Contacts where Id =" + Id).FirstOrDefault(); 
                if (existingconact != null) 
                { 
                    dbConn.RunInTransaction(() => 
                    { 
                        dbConn.Delete(existingconact); 
                    }); 
                } 
            } 
        } 
        //Delete all contactlist or delete Contacts table 
        public void DeleteAllContact() 
        { 
            using (var dbConn = new SQLiteConnection(App.DB_PATH)) 
            { 
                //dbConn.RunInTransaction(() => 
                //   { 
                       dbConn.DropTable<Contacts>(); 
                       dbConn.CreateTable<Contacts>(); 
                       dbConn.Dispose(); 
                       dbConn.Close(); 
                   //}); 
            } 
        } 
    }

3)How to bind SQLite  data to listbox?

In above step 2.1 i created one Database helper class name is "DatabaseHelperClass.cs" which is main head for this sample to perform all SQlite operations.Lets first see my project hierarchy like this

In the app.xaml class lets create a database.  In the constructor we check if the database exists and if it does not we create it.  Since if there is no file exists ,it will get an exception. 

C#

public partial class App : Application 
{ 
    public static string DB_PATH = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "ContactsManager.sqlite"));//DataBase Name 
public App() 
  { 
      if (!CheckFileExists("ContactsManager.sqlite").Result) 
            { 
                using (var db = new SQLiteConnection(DB_PATH)) 
                { 
                    db.CreateTable<Contacts>(); 
                } 
            }  
  } 
 
private async Task<bool> CheckFileExists(string fileName) 
        { 
            try 
            { 
                var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName); 
                return true; 
            } 
            catch 
            { 
            } 
            return false; 
        }

Next I divided my project into MVVM pattern for simplicity.So in Model folder i placed table class name is "Contacts.cs".In ViewModels i placed DB helpers classes(DatabaseHelperClass.cs & ReadAllContactsList.cs).And finally Views folder i placed All my three UI related pages

• AddConatct.xaml:This page for adding contact to database,when click on "Add Contact" button it will be add contact in database like this
  

C#

private async void AddContact_Click(object sender, RoutedEventArgs e) 
        { 
            DatabaseHelperClass Db_Helper = new DatabaseHelperClass();//Creating object for DatabaseHelperClass.cs from ViewModel/DatabaseHelperClass.cs  
            if (NametxtBx.Text != "" & PhonetxtBx.Text != "") 
            { 
                Db_Helper.Insert(new Contacts(NametxtBx.Text, PhonetxtBx.Text)); 
                Frame.Navigate(typeof(ReadContactList));//after add contact redirect to contact listbox page  
            } 
            else 
            { 
                MessageDialog messageDialog = new MessageDialog("Please fill two fields");//Text should not be empty  
                await messageDialog.ShowAsync(); 
            } 
        }

• ReadContactList.xaml:This page for displaying all DB contact list with listbox.And in this screen there is two buttons(Add Contact & DeleteAll) for correspondingly add contact to DB / Delete entire table data.Firstly i made following listbox datatemplate for binding database contacts

XAML

<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0"> 
            <Grid.RowDefinitions> 
                <RowDefinition Height="auto"/> 
                <RowDefinition Height="*"/> 
            </Grid.RowDefinitions> 
            <StackPanel Width="400" Orientation="Horizontal" Grid.Row="0"> 
                <Button  Margin="20,0,0,0" Content="Add Contact"  Click="AddContact_Click"/> 
                <Button Name="Btn_Delete" Margin="100,0,0,0" Content="DeleteAll" Click="DeleteAll_Click"/> 
            </StackPanel> 
            <ListBox Background="Transparent" Margin="6" Height="auto" BorderThickness="2" MaxHeight="580" Grid.Row="1" x:Name="listBoxobj" SelectionChanged="listBoxobj_SelectionChanged"> 
                <ListBox.ItemTemplate> 
                    <DataTemplate> 
                        <Grid Width="350" > 
                            <Border Margin="5" BorderBrush="White" BorderThickness="1"> 
                                <Grid> 
                                    <Grid.RowDefinitions> 
                                        <RowDefinition Height="Auto"/> 
                                        <RowDefinition Height="Auto"/> 
                                    </Grid.RowDefinitions> 
                                    <TextBlock Margin="5,0,0,0" Grid.Row="0" x:Name="NameTxt" TextWrapping="Wrap" Text="{Binding Name}" FontSize="28" Foreground="White"/> 
                                    <TextBlock Grid.Row="0" Text=">" FontSize="28"  HorizontalAlignment="Right" VerticalAlignment="Center" Foreground="White"/> 
                                    <TextBlock Margin="5,0,0,0" Grid.Row="1" x:Name="PhoneTxt"  TextWrapping="Wrap" Foreground="White" FontSize="18" Text="{Binding PhoneNumber}" /> 
                                    <TextBlock HorizontalAlignment="Right" Margin="0,0,35,0" Grid.Row="3" x:Name="CreateddateTxt" Foreground="White" FontSize="18" TextWrapping="Wrap" Text="{Binding CreationDate}" /> 
                                </Grid> 
                            </Border> 
                        </Grid> 
                    </DataTemplate> 
                </ListBox.ItemTemplate> 
            </ListBox> 
 
        </Grid>

So when page is loaded ,i done like this 

C#

private void ReadContactList_Loaded(object sender, RoutedEventArgs e) 
        { 
            ReadAllContactsList dbcontacts = new ReadAllContactsList(); 
            DB_ContactList = dbcontacts.GetAllContacts();//Get all DB contacts  
            if (DB_ContactList.Count > 0) 
            { 
                Btn_Delete.IsEnabled = true; 
            } 
            listBoxobj.ItemsSource = DB_ContactList.OrderByDescending(i => i.Id).ToList();//Binding DB data to LISTBOX and Latest contact ID can Display first.  
        }

When "Add Contact" button is clicked is navigated to "AddConatct.xaml" page to add contact in DB.

C#

private void AddContact_Click(object sender, RoutedEventArgs e) 
        { 
            Frame.Navigate(typeof(AddConatct)); 
        }

When "DeleteAll" button is clicked ,i done like this

C#

private async void DeleteAll_Click(object sender, RoutedEventArgs e) 
        { 
            var dialog = new MessageDialog("Are you sure you want to remove all your data ?"); 
            dialog.Commands.Add(new UICommand("No", new UICommandInvokedHandler(Command))); 
            dialog.Commands.Add(new UICommand("Yes", new UICommandInvokedHandler(Command))); 
            await dialog.ShowAsync(); 
        }
 
        private void Command(IUICommand command) 
        { 
            if (command.Label.Equals("Yes")) 
            { 
                DatabaseHelperClass Db_Helper = new DatabaseHelperClass(); 
                Db_Helper.DeleteAllContact();//delete all DB contacts  
                DB_ContactList.Clear();//Clear collections  
                Btn_Delete.IsEnabled = false; 
                listBoxobj.ItemsSource = DB_ContactList; 
            } 
        }

When selected listbox item ,i navigate to "Delete_UpdateContacts.xaml" page for delete/update corresponding contact details on listbox SelectionChanged event  like this.

C#

private void listBoxobj_SelectionChanged(object sender, SelectionChangedEventArgs e) 
        { 
            int SelectedContactID = 0; 
            if (listBoxobj.SelectedIndex != -1) 
            { 
                Contacts listitem = listBoxobj.SelectedItem as Contacts;//Get slected listbox item contact ID  
                Frame.Navigate(typeof(Delete_UpdateContacts),SelectedContactID=listitem.Id);         
 
            } 
        }

• Delete_UpdateContacts.xaml:This page is for updating/delete selected contact details

See in above when listbox item is selected ,i passed selected contact Id as query string,so in this i get that id in "OnNavigatedTo" method like this

C#

protected override void OnNavigatedTo(NavigationEventArgs e) 
        { 
            Selected_ContactId = int.Parse(e.Parameter.ToString()); 
            currentcontact = Db_Helper.ReadContact(Selected_ContactId);//Read selected DB contact 
            NametxtBx.Text = currentcontact.Name;//get contact Name 
            PhonetxtBx.Text = currentcontact.PhoneNumber;//get contact PhoneNumber 
        }

When "Delete" button is pressed i done like this.

C#

private void DeleteContact_Click(object sender, RoutedEventArgs e) 
        { 
            Db_Helper.DeleteContact(Selected_ContactId);//Delete selected DB contact Id. 
            Frame.Navigate(typeof(ReadContactList)); 
        }

When "Update" button is pressed i done like this.

C#

private void UpdateContact_Click(object sender, RoutedEventArgs e) 
        { 
            currentcontact.Name = NametxtBx.Text; 
            currentcontact.PhoneNumber = PhonetxtBx.Text; 
            Db_Helper.UpdateContact(currentcontact);//Update selected DB contact Id 
            Frame.Navigate(typeof(ReadContactList)); 
        }

4)How to explore my SQLite  database data?

Isolated Storage Explorer tool to get the database file.This tool is installed under folder path is Program Files (x86)\Microsoft SDKs\Windows Phone\v8.1\Tools\IsolatedStorageExplorerTool

To use Isolated Storage Explorer, the following things must be true:

• The app that you want to test must be installed on the emulator or device.
• The emulator or device must be running, but the app doesn’t have to be running.

You cannot do the following things with Isolated Storage Explorer:

• You can’t view isolated storage for apps that you’ve installed from the Windows Phone Store.
• You can’t view app settings that are stored in the local folder in Isolated Storage Explorer. These are settings that the app saved by using the IsolatedStorageSettings class.

After that execute following commands from command prompt.

1)First change command prompt directory path to Program Files (x86)\Microsoft SDKs\Windows Phone\v8.1\Tools\IsolatedStorageExplorerTool

2)Get Product Id from project Package.appxmanifest file Packaging Tab under Package name attribute (i.e 9f68177c-0add-437b-8b43-95ec429ee5b5)

3)if your app is run on emulator excute this command: ISETool.exe ts xd 9f68177c-0add-437b-8b43-95ec429ee5b5 c:\data\myfiles

4)if your app is run on device excute this command: ISETool.exe ts de 9f68177c-0add-437b-8b43-95ec429ee5b5 c:\data\myfiles

Now we will be found DB content in your computer at c:\data\myfiles like this.

Note:This content may be change in future.

Summary:

In this article, we have seen the basics of Sqlite, how to install the engine, the library Sqlite-net, and saw the most common operations (also known as code first), such as inserting, updating, and deleting data from a table of a database.

FeedBack Note: Please share your thoughts,what you think about this post,Is this post really helpful for you?otherwise it would be very happy ,if you have any thoughts for to implement this requirement in any another way?I always welcome if you drop comments on this post and it would be impressive.

Follow me always at  @Subramanyam_B 
Have a nice day by Subramanyam Raju :)