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.

In this blog, we learn how to make UI and add a lite database that perform 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;
       }

   }
}