SQLite in Metro Style App

In this blog we lean how to make UI and add lite database that preform insert update or delete operation.

using System;

using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using SQLite;
using sqliteapp;
using sqliteapp.ViewModels;
using sqliteapp.Models;
using Windows.UI.Xaml;
using System.Threading.Tasks; 

namespace sqliteapp.ViewModels
{
   public class CusotmerViewModel
   {
       public event PropertyChangedEventHandler PropertyChange; 
       protected virtual void RaisePropertyChanged(string propertyName)
       {
           var handler = this.PropertyChange;
           if (handler != null)
           {
               handler(this, new PropertyChangedEventArgs(propertyName));
           }
       } 

       //============================================

       private int id = 0;

       public int Id
       {
           get { return id; }
           set
           {
               if (id == value)
               {
                   return;
               }
               id = value;
               RaisePropertyChanged("Id");                         

           }

       }

       private string name = string.Empty;

       public string Name
       {
           get { return name; }
           set {if(name == value)
           {return;}
               name = value;
               RaisePropertyChanged("Name");
           }
       } 

       private string city = string.Empty;

       public string City
       {
           get { return city; }
           set
           {
             if(city == value)
             {return;}
               city = value;
               RaisePropertyChanged("City");
           }
       } 

       private string contact = string.Empty;

       public string Contact
       {
           get { return contact; }
           set
           {
               if(contact == value)
               {return; }
               contact = value;
               RaisePropertyChanged("Contact");
           }
       } 

       private sqliteapp.App app = (Application.Current as App);

       public CusotmerViewModel GetCustomer(int customerId)

       {
           var customer = new CusotmerViewModel();
           using (var db=new SQLiteConnection(app.dbPath))
           {
               var _customer = (db.Table<Customer>().Where(
                   c => c.Id == customerId)).AsEnumerable();
               foreach (var a in _customer)
               {
                   customer.Id = a.Id;
                   customer.Name = a.Name;
                   customer.City = a.City;
                   customer.Contact = a.Contact;
               }
           }
           return customer;
       } 

       public CusotmerViewModel GetAllcustomer()

       {
           var customer = new CusotmerViewModel();
           using (var db= new SQLiteConnection(app.dbPath))
           {
               var result = (from  t in db.Table<Customer>()
                             select  t
               ).ToList();
               if (result != null)
               {
                   foreach (var customer1 in result)
                   {
                       customer.Id = customer1.Id;
                       customer.Name = customer1.Name;
                       customer.City = customer1.City;
                       customer.Contact = customer1.Contact;
                   }
               }
           }
           return customer;
       } 

       public string SaveCustomer(CusotmerViewModel cusotmer)

       {
           string result = string.Empty;
           using (var db=new SQLiteConnection(app.dbPath))
           {
               string change = string.Empty;
               try
               {
                   var existingCustomer = (db.Table<Customer>().Where(c => c.Id == cusotmer.Id)).SingleOrDefault();
                   if (existingCustomer != null)
                   {
                       existingCustomer.Name = cusotmer.Name;
                       existingCustomer.City = cusotmer.City;
                       existingCustomer.Contact = cusotmer.Contact;
                       int success = db.Update(existingCustomer);
                   }
                   else
                   {
                       int success = db.Insert(new Customer()
                           {
                               Id = cusotmer.id,
                               Name = cusotmer.name,
                               City = cusotmer.city,
                               Contact = cusotmer.contact
                           });
                   }
                   result = "Success";
               }
               catch (Exception ea)
               { 

                   result = "This customer was not saved";
               }
               return result;

           }
       }

       public string DeleteCustomer(int customerId)

       {
           string result = string.Empty;
           using (var db= new SQLite.SQLiteConnection(app.dbPath))
           {
               var projects = db.Table<Customer>().Where(
                   p => p.Id == customerId);
               foreach (var project in projects)
               {
                   db.Delete(project);
               }
               var existingCustomer = (db.Table<Customer>().Where(
                   c => c.Id == customerId)).Single();
               if (db.Delete(existingCustomer) > 0)
               {
                   result = "Success";
               }
               else
               {
                   result = "This customer was not removed";
               }
           }
           return result;
       }                  

   }

}