Last active
August 23, 2016 19:20
-
-
Save JerryNixon/f501f79688784b029abc9c6a19b57b09 to your computer and use it in GitHub Desktop.
Potential Sqlite DAL for UWP
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public static class SqliteDAL | |
{ | |
public static class Database | |
{ | |
public static string FileName { get; set; } = "Storage.sqlite"; | |
public static StorageFolder Folder { get; set; } = ApplicationData.Current.LocalFolder; | |
public static async Task DropAsync(string path = null) | |
{ | |
if (string.IsNullOrEmpty(path)) | |
{ | |
path = Path.Combine(Folder.Path, FileName); | |
} | |
new DebugTraceListener().Receive($"Delete file {path}"); | |
await (await StorageFile.GetFileFromPathAsync(path)).DeleteAsync(); | |
} | |
public static SQLiteConnection Open(string path = null) | |
{ | |
if (string.IsNullOrEmpty(path)) | |
{ | |
path = Path.Combine(Folder.Path, FileName); | |
} | |
return new SQLiteConnection(new SQLitePlatformWinRT(), path) | |
{ | |
TraceListener = new DebugTraceListener() | |
}; | |
} | |
} | |
public static class Table<T> where T : class, IItemWithKey | |
{ | |
public static void Create(SQLiteConnection connection = null) | |
{ | |
using (var db = connection ?? Database.Open()) | |
{ | |
db.CreateTable<T>(); | |
} | |
} | |
public static void Drop(SQLiteConnection connection = null) | |
{ | |
using (var db = connection ?? Database.Open()) | |
{ | |
db.DropTable<T>(); | |
} | |
} | |
public static bool Exists(SQLiteConnection connection = null) | |
{ | |
using (var db = connection ?? Database.Open()) | |
{ | |
return db.Execute($"SELECT name FROM sqlite_master WHERE type='table' AND name='{typeof(T).ToString()}';") > 0; | |
} | |
} | |
public static T Select(params string[] key) | |
{ | |
if (key?.Any() == false) | |
{ | |
throw new ArgumentException("At least one key is required", nameof(key)); | |
} | |
using (var db = Database.Open()) | |
{ | |
return db.Table<T>().Where(x => key.Contains(x.Key)).FirstOrDefault(); | |
} | |
} | |
public static IEnumerable<T> SelectAll() | |
{ | |
using (var db = Database.Open()) | |
{ | |
return db.Table<T>().ToObservableCollection(); | |
} | |
} | |
public static IEnumerable<T> SelectAll(Func<T, bool> predicate) | |
{ | |
using (var db = Database.Open()) | |
{ | |
return db.Table<T>().Where(predicate).ToObservableCollection(); | |
} | |
} | |
public static void Insert(params T[] items) | |
{ | |
if (items?.Any() == false) | |
{ | |
throw new ArgumentException("At least one item is required", nameof(items)); | |
} | |
using (var db = Database.Open()) | |
{ | |
db.InsertAll(items); | |
} | |
} | |
public static void Update(params T[] items) | |
{ | |
if (items?.Any() == false) | |
{ | |
throw new ArgumentException("At least one item is required", nameof(items)); | |
} | |
using (var db = Database.Open()) | |
{ | |
db.UpdateAll(items); | |
} | |
} | |
public static void Upsert(params T[] items) | |
{ | |
if (items?.Any() == false) | |
{ | |
throw new ArgumentException("At least one item is required", nameof(items)); | |
} | |
using (var db = Database.Open()) | |
{ | |
db.InsertOrReplaceAll(items); | |
} | |
} | |
public static void Delete(params T[] items) | |
{ | |
if (items?.Any() == false) | |
{ | |
throw new ArgumentException("At least one item is required", nameof(items)); | |
} | |
using (var db = Database.Open()) | |
{ | |
items.ForEach(item => db.Delete(item)); | |
} | |
} | |
public static void DeleteAll(SQLiteConnection connection = null) | |
{ | |
using (var db = connection ?? Database.Open()) | |
{ | |
db.DeleteAll<T>(); | |
} | |
} | |
} | |
public static T Select<T>(params string[] key) where T : class, IItemWithKey | |
=> Table<T>.Select(key); | |
public static IEnumerable<T> SelectAll<T>(Func<T, bool> predicate) where T : class, IItemWithKey | |
=> Table<T>.SelectAll(predicate); | |
public static IEnumerable<T> SelectAll<T>() where T : class, IItemWithKey | |
=> Table<T>.SelectAll(); | |
public static void Insert<T>(params T[] items) where T : class, IItemWithKey | |
=> Table<T>.Insert(items); | |
public static void Update<T>(params T[] items) where T : class, IItemWithKey | |
=> Table<T>.Update(items); | |
public static void Upsert<T>(params T[] items) where T : class, IItemWithKey | |
=> Table<T>.Upsert(items); | |
public static void Delete<T>(params T[] items) where T : class, IItemWithKey | |
=> Table<T>.Delete(items); | |
public static void DeleteAll<T>() where T : class, IItemWithKey | |
=> Table<T>.DeleteAll(); | |
public static T Execute<T>(string sql, params object[] args) where T : class, IItemWithKey | |
{ | |
if (string.IsNullOrEmpty(sql)) | |
{ | |
throw new ArgumentException("Sql is required", nameof(sql)); | |
} | |
using (var db = Database.Open()) | |
{ | |
return db.ExecuteScalar<T>(sql, args); | |
} | |
} | |
public interface IItemWithKey | |
{ | |
[SQLite.Net.Attributes.PrimaryKey] | |
string Key { get; set; } | |
} | |
private class DebugTraceListener : ITraceListener | |
{ | |
public void Receive(string message) | |
{ | |
Debug.WriteLine(message); | |
} | |
} | |
} |
Looks good to me, assume this is on top of sqlite-net?
Does the key have to be a string? After testing this out a bit I think I would much prefer an integer primary key.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This DAL is intended to cover the 90% use case how most developers would use SQLite in their apps. There are several complex scenarios missing, but the core feature-set should meet the needs of most developers. I think.