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;

       }                  

   }

}