· 8 years ago · Apr 09, 2018, 10:54 AM
1using System;
2using Xamarin.Forms;
3using System.Diagnostics;
4using System.Collections.Generic;
5using System.Linq;
6using SQLiteNetExtensions.Extensions;
7using System.Linq.Expressions;
8using SQLite.Net;
9using System.Threading.Tasks;
10
11namespace Eboard
12{
13 public interface ISQLite
14 {
15 SQLiteConnection GetConnection();
16 }
17
18 public class SQLiteTable
19 {
20 public string name { get; set; }
21 }
22
23 public class DatabaseManager
24 {
25 SQLiteConnection connection;
26 static readonly DatabaseManager _singleton = new DatabaseManager();
27 readonly object Lock = new object();
28
29 public static DatabaseManager SharedInstance()
30 {
31 return _singleton;
32 }
33
34 DatabaseManager()
35 {
36 this.connection = DependencyService.Get<ISQLite>().GetConnection();
37 Debug.WriteLine(this.connection.DatabasePath);
38 }
39
40 public void ClearDatabase()
41 {
42 const string sql = "SELECT name FROM sqlite_master WHERE type='table'";
43 var allTables = this.connection.Query<SQLiteTable>(sql);
44 foreach (var table in allTables)
45 {
46 string cmdText = "delete from '" + table.name + "'";
47 var cmd = this.connection.CreateCommand(cmdText);
48 cmd.ExecuteScalar<int>();
49 }
50 }
51
52 public void CreateTable<T>() where T : new()
53 {
54 try
55 {
56 //Executes a "create table if not exists" on the database
57 Debug.WriteLine("creating table: " + typeof(T).Name);
58 this.connection.CreateTable<T>();
59 }
60 catch (Exception e)
61 {
62 Debug.WriteLine(e);
63 }
64 }
65
66 bool TableExists<T>()
67 {
68 const string cmdText = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
69 var cmd = this.connection.CreateCommand(cmdText, typeof(T).Name);
70 return cmd.ExecuteScalar<string>() != null;
71 }
72
73 public int GetTableCount<T>() where T : class
74 {
75 return this.connection.Table<T>().Count();
76 }
77
78 public void DeleteEntity<T>(T objToDelete)
79 {
80 //Deletes the given object from the database using its primary key.
81 if (TableExists<T>())
82 this.connection.Delete(objToDelete);
83 }
84
85 public void DropTable<T>()
86 {
87 if (TableExists<T>())
88 this.connection.DropTable<T>();
89 }
90
91 public T GetEntity<T>(long _id) where T : class
92 {
93 try
94 {
95 return this.connection.GetWithChildren<T>(_id, true);
96 }
97 catch (Exception ex)
98 {
99 if (ex is InvalidOperationException)
100 {
101 //object not found
102 }
103 else
104 {
105 Debug.WriteLine(ex);
106 }
107 return null;
108 }
109 }
110
111 public async Task<List<T>> GetEntities<T>(Expression<Func<T, bool>> filter = null) where T : class
112 {
113 try
114 {
115 //Filter is passed to the Where clause when fetching objects from the database
116 //No relationship properties are allowed in this filter as they are loaded afterwards
117
118 if (TableExists<T>())
119 {
120 var result = await Task.Run(() =>
121 {
122 //TODO: if the filter needs to work on relationship properties, need to refactor like this:
123 // this.connection.GetAllWithChildren(null, true).Where(filter);
124 lock (this.Lock)
125 {
126 var res = connection.GetAllWithChildren(filter, true);
127 return res;
128 }
129 });
130 return result;
131 }
132 else
133 {
134 return new List<T>();
135 }
136 }
137 catch (Exception e)
138 {
139 Debug.WriteLine(e);
140 return null;
141 }
142 }
143
144 public async Task SaveEntities<T>(IEnumerable<T> items) where T : class
145 {
146 try
147 {
148 if (items != null)
149 {
150 await Task.Run(() =>
151 {
152 connection.RunInTransaction(() =>
153 {
154 var watch = Stopwatch.StartNew();
155 foreach (var item in items)
156 {
157 var currentEntityPrimaryKeyProperty = item.GetType().GetPrimaryKey();
158 int itemPrimaryKey = (int)Utils.GetProperty(item, currentEntityPrimaryKeyProperty.Name);
159
160 var existinEntity = GetEntity<T>(itemPrimaryKey);
161 if (existinEntity != null)
162 {
163 Debug.WriteLine("updating " + typeof(T).Name + " with id: " + itemPrimaryKey);
164 connection.UpdateWithChildren(item);
165 watch.Stop();
166 }
167 else
168 {
169 Debug.WriteLine("inserting " + typeof(T).Name + " with id: " + itemPrimaryKey);
170 connection.InsertOrReplaceWithChildren(item, true);
171 }
172 }
173 watch.Stop();
174 var elapsedMs = watch.ElapsedMilliseconds;
175 Debug.WriteLine(">>>>>>>saving to db took " + elapsedMs / 1000.0 + " for " + items.Count() + " items");
176 });
177 });
178 }
179 }
180 catch (Exception e)
181 {
182 Debug.WriteLine(e);
183 }
184 }
185
186 public void SaveEntity<T>(T item) where T : class
187 {
188 try
189 {
190 lock (this.Lock)
191 {
192 var watch = Stopwatch.StartNew();
193
194 var currentEntityPrimaryKeyProperty = item.GetType().GetPrimaryKey();
195 int itemPrimaryKey = (int)Utils.GetProperty(item, currentEntityPrimaryKeyProperty.Name);
196
197 var existinEntity = GetEntity<T>(itemPrimaryKey);
198 if (existinEntity != null)
199 {
200 Debug.WriteLine("updating " + typeof(T).Name + " with id: " + itemPrimaryKey);
201 connection.UpdateWithChildren(item);
202 var elapsedMs = watch.ElapsedMilliseconds;
203 Debug.WriteLine(">>>>>>>updating to db took " + elapsedMs / 1000.0 + " for 1 ");
204 }
205 else
206 {
207 Debug.WriteLine("inserting " + typeof(T).Name + " with id: " + itemPrimaryKey);
208 connection.InsertOrReplaceWithChildren(item, true);
209 var elapsedMs = watch.ElapsedMilliseconds;
210 Debug.WriteLine(">>>>>>>inserting to db took " + elapsedMs / 1000.0 + " for 1 ");
211 }
212 }
213 }
214 catch (Exception e)
215 {
216 Debug.WriteLine(e);
217 }
218 }
219 }
220}