Skip to content

Instantly share code, notes, and snippets.

@JerryNixon
Last active August 23, 2016 19:20
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save JerryNixon/f501f79688784b029abc9c6a19b57b09 to your computer and use it in GitHub Desktop.
Save JerryNixon/f501f79688784b029abc9c6a19b57b09 to your computer and use it in GitHub Desktop.
Potential Sqlite DAL for UWP
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);
}
}
}
@JerryNixon
Copy link
Author

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.

@ErikEJ
Copy link

ErikEJ commented Aug 23, 2016

Looks good to me, assume this is on top of sqlite-net?

@mbrdev
Copy link

mbrdev commented Aug 23, 2016

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