In this blog, we learn how to make UI and add a lite database that perform insert update or delete operation.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Linq;
  5. using System.Text;
  6. using SQLite;
  7. using sqliteapp;
  8. using sqliteapp.ViewModels;
  9. using sqliteapp.Models;
  10. using Windows.UI.Xaml;
  11. using System.Threading.Tasks;
  13. namespace sqliteapp.ViewModels
  14. {
  15. public class CusotmerViewModel
  16. {
  17. public event PropertyChangedEventHandler PropertyChange;
  18. protected virtual void RaisePropertyChanged(string propertyName)
  19. {
  20. var handler = this.PropertyChange;
  21. if (handler != null)
  22. {
  23. handler(this, new PropertyChangedEventArgs(propertyName));
  24. }
  25. }
  27. //============================================
  29. private int id = 0;
  30. public int Id
  31. {
  32. get { return id; }
  33. set
  34. {
  35. if (id == value)
  36. {
  37. return;
  38. }
  39. id = value;
  40. RaisePropertyChanged("Id");
  42. }
  43. }
  45. private string name = string.Empty;
  46. public string Name
  47. {
  48. get { return name; }
  49. set {if(name == value)
  50. {return;}
  51. name = value;
  52. RaisePropertyChanged("Name");
  53. }
  54. }
  56. private string city = string.Empty;
  57. public string City
  58. {
  59. get { return city; }
  60. set
  61. {
  62. if(city == value)
  63. {return;}
  64. city = value;
  65. RaisePropertyChanged("City");
  66. }
  67. }
  69. private string contact = string.Empty;
  70. public string Contact
  71. {
  72. get { return contact; }
  73. set
  74. {
  75. if(contact == value)
  76. {return; }
  77. contact = value;
  78. RaisePropertyChanged("Contact");
  79. }
  80. }
  82. private sqliteapp.App app = (Application.Current as App);
  84. public CusotmerViewModel GetCustomer(int customerId)
  85. {
  86. var customer = new CusotmerViewModel();
  87. using (var db=new SQLiteConnection(app.dbPath))
  88. {
  89. var _customer = (db.Table<Customer>().Where(
  90. c => c.Id == customerId)).AsEnumerable();
  91. foreach (var a in _customer)
  92. {
  93. customer.Id = a.Id;
  94. customer.Name = a.Name;
  95. customer.City = a.City;
  96. customer.Contact = a.Contact;
  97. }
  98. }
  99. return customer;
  100. }
  102. public CusotmerViewModel GetAllcustomer()
  103. {
  104. var customer = new CusotmerViewModel();
  105. using (var db= new SQLiteConnection(app.dbPath))
  106. {
  107. var result = (from t in db.Table<Customer>()
  108. select t
  109. ).ToList();
  110. if (result != null)
  111. {
  112. foreach (var customer1 in result)
  113. {
  114. customer.Id = customer1.Id;
  115. customer.Name = customer1.Name;
  116. customer.City = customer1.City;
  117. customer.Contact = customer1.Contact;
  118. }
  119. }
  120. }
  121. return customer;
  122. }
  124. public string SaveCustomer(CusotmerViewModel cusotmer)
  125. {
  126. string result = string.Empty;
  127. using (var db=new SQLiteConnection(app.dbPath))
  128. {
  129. string change = string.Empty;
  130. try
  131. {
  132. var existingCustomer = (db.Table<Customer>().Where(c => c.Id == cusotmer.Id)).SingleOrDefault();
  133. if (existingCustomer != null)
  134. {
  135. existingCustomer.Name = cusotmer.Name;
  136. existingCustomer.City = cusotmer.City;
  137. existingCustomer.Contact = cusotmer.Contact;
  138. int success = db.Update(existingCustomer);
  139. }
  140. else
  141. {
  142. int success = db.Insert(new Customer()
  143. {
  144. Id = cusotmer.id,
  145. Name = cusotmer.name,
  146. City = cusotmer.city,
  147. Contact = cusotmer.contact
  148. });
  149. }
  150. result = "Success";
  151. }
  152. catch (Exception ea)
  153. {
  155. result = "This customer was not saved";
  156. }
  157. return result;
  159. }
  160. }
  162. public string DeleteCustomer(int customerId)
  163. {
  164. string result = string.Empty;
  165. using (var db= new SQLite.SQLiteConnection(app.dbPath))
  166. {
  167. var projects = db.Table<Customer>().Where(
  168. p => p.Id == customerId);
  169. foreach (var project in projects)
  170. {
  171. db.Delete(project);
  172. }
  173. var existingCustomer = (db.Table<Customer>().Where(
  174. c => c.Id == customerId)).Single();
  175. if (db.Delete(existingCustomer) > 0)
  176. {
  177. result = "Success";
  178. }
  179. else
  180. {
  181. result = "This customer was not removed";
  182. }
  183. }
  184. return result;
  185. }
  187. }
  188. }