Data Storage in Universal app (Windows Phone 8.1/Windows Store 8.1)

Learn how we can use the Sqlite in Windows Phone/Store for local database or offline storage. If you are developing a universal app (Phone & Store) , which requires a common database for both the projects, we should follow the below mechanism.

1) Setting up the Sqlite in App
2) Create CRUD operations
3) Create UI for interacting with CRUD operations

1) Setting up the Sqlite in App
By Creating a Universal app, you will get two projects, project for windows store and project for Windows Phone by sharing the shared project.

Note: Make sure you install VS 2013 with Update 2 for creating a Universal app project.

Click on Tools->Extension and Updates, then search and install the Sqlite for Windows Phone 8.1 and Sqlite for Windows Runtime (Windows 8.1) as shown below.
Now add the Sqlite for Windows Phone dll as reference to Windows Phone project, you can find this library under extensions. Similarly add the Sqlite for Windows Runtime library for Windows Store project as below.

If you are running the app in emulator, select the configuration manager and target the Active Solution Platform to x86. If you are running the app in Phone, then target to ARM. Sqlite libraries will only be compiled when you target to either x86/ARM.

Add Nuget package Sqlite-net for both of the projects which will add SQLite.cs and SQLiteAsync.cs files to the project, you just need to add them to shared project, so that code can be shared across both the projects.

2)
Create CRUD operations
Create a model which can be used as Table for Sqlite db..
[Table(
"Contacts")]
    
public class Contact
    {
        [PrimaryKey, AutoIncrement]
        
public int Id { get; set; }


         
public string Name
        {
             get;
            set;
        }

         
public string FirstName { get; set; }

         
public string LastName { get; set; }

         
public string Gender { get; set; }

         
public string PhoneNumber { get; set; }

         
public string Email { get; set; }

         [Ignore]
        
public string Link { get; set; }

    }

From the above model, I have defined the Table name as Table(“Contacts”),  added the Primary Key for Id column and is also a AutoIncrement column nothing but a Identity column.
Define the path where you want to persist the database as below. Here, I am creating a helper class for adding the CRUD operations.

public static string DB_PATH = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "SqliteUniversal.db"));

Create a connection for the Database.

dbConn =
new SQLiteAsyncConnection(DB_PATH);

Create Table:
Before creating any table, we should check whether the Database exists in the given path.
private async Task<bool> CheckFileExists(string fileName)
         {
             
try
            {
                var store = await Windows.Storage.ApplicationData.
Current.LocalFolder.GetFileAsync(fileName);
                 
return true;
            }
            catch
            {
                 
return false;
            }
        }

This function creates the Database if not been created. Now look at the code for creating a Table as below.

public async Task<bool> CreateTable<T>() where T : new()
        {
             
try
            {
                 
if (!await CheckFileExists(DB_PATH))
                {
                    await dbConn.CreateTableAsync<T>();

                 }
                  
return true;
            }
            catch
            {
                 
return false;
            }
        }

I have created this as a generic function which can be used to create a Table; you just need to pass the T type model. In this case, model is Contact.

Insert:
If you want to insert the multiple records at a time, you just need to call the InsertAllAsync function by passing the collection of records as below.

public async void InsertAllAsync(IEnumerable items)
        {
            await dbConn.InsertAllAsync(items);
        }
If you want to insert record by record, below is the code.
public async Task<object> Insert(object item)
        {
            await dbConn.InsertAsync(item);

             
return item;
        }
Update:
public void UpdateContact(Contact contact)
         {
             
using (var dbConn = new SQLiteConnection(DB_PATH))
            {
                var existingconact = dbConn.Table<Contact>().FirstOrDefault(faq => faq.Id == contact.Id);
                 
if (existingconact != null)
                {
                    existingconact.FirstName = contact.FirstName;
                    existingconact.LastName = contact.LastName;
                    existingconact.Name = contact.Name;
                    existingconact.PhoneNumber = contact.PhoneNumber;
                    existingconact.Gender = contact.Gender;
                    existingconact.Email = contact.Email;
                    dbConn.RunInTransaction(() =>
                     {
                          dbConn.Update(existingconact);
                     });
                 }
             }
         }
Delete:
  public void DeleteContact(int id)
        {
            
using (var dbConn = new SQLiteConnection(DB_PATH))
            {
                var existingconact = dbConn.Table<Contact>().FirstOrDefault(faq => faq.Id == id);
                 
if (existingconact != null)
                {
                    dbConn.RunInTransaction(() =>
                   {
                       dbConn.Delete(existingconact);
                   });
                }
            }
        }
I am done with the SQLite changes, now I will create an UI to interact with these operations.

3) Create UI for interacting with CRUD operations
I have create a simple UI for creating a new contact, updating existing contact and delete
.

In Create click event, call the SQLite functions CreateTable() for creating a Table, Insert() for inserting a new record.

private async void btnCreate_Click(object sender, RoutedEventArgs e)
        {
            var contact =
this.CreateContact();
             
if (contact != null)
            {
                await
this.helper.CreateTable<Contact>();
                var result = await
this.helper.Insert(contact);
                 
this.contactId = ((Contact)result).Id;
            }
        }

In Update click event, call the Update function for updating the existing record.

private void btnUpdate_Click(object sender, RoutedEventArgs e)
         {
             
if (this.contactId > 0)
            {
                var contact =
this.CreateContact();
                 
if (contact != null)
                {
                    contact.Id =
this.contactId;
                     
this.helper.UpdateContact(contact);
                }
            }
        }

In Delete click event, call the Delete function by passing the record ID
private void btnDelete_Click(object sender, RoutedEventArgs e)
         {
             
if (this.contactId > 0)
             {
                 
this.helper.DeleteContact(this.contactId);
                txtFirstName.Text = string.
Empty;
                txtLastName.Text = string.
Empty;
                txtGender.Text = string.
Empty;
                txtEmail.Text = string.
Empty;
                txtPhone.Text = string.
Empty;
            }
        }

Please download the sample from below location.
http://
nullskull.com/FileUpload/-407123783_SqliteUniversal.zip      

By Siva Jagan Dhulipalla   Popularity  (2458 Views)