using System.Collections.Generic;
public static void Main()
Console.WriteLine("Hello World");
var note1 = new Note { Name = "Kris", Notes = "Hello from the developer!" };
MyRepo.Singleton.Save(note1, new Note.NoteCRUD());
var note2 = new Note { Name = "Rick", Notes = "Oh geez" };
MyRepo.Singleton.Save(note2, new Note.NoteCRUD());
Console.WriteLine(string.Join(Environment.NewLine, MyRepo.Singleton.GetAll(new Note.NoteCRUD())));
public class SimpleSqlLiteDatabase
private static readonly object Locker = new object();
internal SqliteConnection Connection;
internal SimpleSqlLiteDatabase(string dbPath, params string[] createTableSql)
var exists = File.Exists(dbPath);
Connection = new SqliteConnection("Data Source=" + dbPath);
var commands = createTableSql.ToArray();
foreach (var command in commands)
using (var c = Connection.CreateCommand())
var i = c.ExecuteNonQuery();
#region CRUD for the SQLLiteRepository
internal IEnumerable<T> GetAll<T>(ISqlLiteDataObjectCRUD<T> crud) where T: class, ISqlLiteDataObject
Connection = new SqliteConnection("Data Source=" + Path);
using (var contents = Connection.CreateCommand())
contents.CommandText = crud.ReadAll();
var r = contents.ExecuteReader();
tl.Add(crud.FromReader(r));
internal T Get<T>(int id, ISqlLiteDataObjectCRUD<T> crud) where T : class, ISqlLiteDataObject
Connection = new SqliteConnection("Data Source=" + Path);
using (var command = Connection.CreateCommand())
command.CommandText = crud.Read();
command.Parameters.Add(new SqliteParameter(DbType.Int32) {Value = id});
var r = command.ExecuteReader();
var thing = crud.FromReader(r);
internal int Update<T>(T item, ISqlLiteDataObjectCRUD<T> crud) where T: class, ISqlLiteDataObject
Connection = new SqliteConnection("Data Source=" + Path);
using (var command = Connection.CreateCommand())
crud.Update(item, command);
r = command.ExecuteNonQuery();
Connection = new SqliteConnection("Data Source=" + Path);
using (var command = Connection.CreateCommand())
crud.Insert(item, command);
r = command.ExecuteNonQuery();
internal int Delete<T>(int id, ISqlLiteDataObjectCRUD<T> crud) where T: class, ISqlLiteDataObject
Connection = new SqliteConnection("Data Source=" + Path);
using (var command = Connection.CreateCommand())
crud.Delete(id, command);
r = command.ExecuteNonQuery();
public abstract class SimpleSqlLiteRepository
protected static string DbLocation;
protected SimpleSqlLiteDatabase Db;
internal static string DatabaseFilePath { get { return GetFilePathFormattedForPlatform(); } }
internal virtual ISqlLiteDataObject Get<T>(int id, ISqlLiteDataObjectCRUD<T> crud) where T : class, ISqlLiteDataObject { return Db.Get(id, crud); }
internal virtual IEnumerable<T> GetAll<T>(ISqlLiteDataObjectCRUD<T> crud) where T : class, ISqlLiteDataObject { return Db.GetAll(crud); }
internal virtual int Save<T>(T item, ISqlLiteDataObjectCRUD<T> crud) where T : class, ISqlLiteDataObject { return Db.Update(item, crud); }
internal virtual int Delete<T>(int id, ISqlLiteDataObjectCRUD<T> crud) where T : class, ISqlLiteDataObject { return Db.Delete(id, crud); }
protected static string GetFilePathFormattedForPlatform(string sqlLiteDbFileName = "SimpleSqlLiteDatabase")
sqlLiteDbFileName = string.Join(sqlLiteDbFileName, ".db3");
var libraryPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var path = System.IO.Path.Combine(libraryPath, sqlLiteDbFileName);
public interface ISqlLiteDataObject
ISqlLiteDataObject Get(SimpleSqlLiteRepository repository, int id);
IList<ISqlLiteDataObject> GetAll(SimpleSqlLiteRepository repository);
int Save(SimpleSqlLiteRepository repository, ISqlLiteDataObject item);
int Delete(SimpleSqlLiteRepository repository, int id);
public interface ISqlLiteDataObjectCRUD<T> where T: ISqlLiteDataObject
string CreateSqlLiteTable();
T FromReader(IDataRecord r);
void Update(T item, SqliteCommand command);
void Insert(T item, SqliteCommand command);
void Delete(int id, SqliteCommand command);
#region Type Safety Guards for this Repository
public abstract class MyReproSqlLiteObject : ISqlLiteDataObject
public abstract int Id { get; set; }
public abstract ISqlLiteDataObject Get(SimpleSqlLiteRepository repository, int id);
public abstract IList<ISqlLiteDataObject> GetAll(SimpleSqlLiteRepository repository);
public abstract int Save(SimpleSqlLiteRepository repository, ISqlLiteDataObject item);
public abstract int Delete(SimpleSqlLiteRepository repository, int id);
public abstract ISqlLiteDataObjectCRUD<MyReproSqlLiteObject> CRUD { get; }
public class Note : MyReproSqlLiteObject
public override int Id { get; set; }
public string Name { get; set; }
public string Notes { get; set; }
public bool Done { get; set; }
NoteCRUD _crud = new NoteCRUD();
public override ISqlLiteDataObjectCRUD<MyReproSqlLiteObject> CRUD { get { return (ISqlLiteDataObjectCRUD<MyReproSqlLiteObject>)_crud; } }
public override ISqlLiteDataObject Get(SimpleSqlLiteRepository repository, int id) { return repository.Get(id, CRUD); }
public override IList<ISqlLiteDataObject> GetAll(SimpleSqlLiteRepository repository) { return new List<ISqlLiteDataObject>(repository.GetAll(CRUD)); }
public override int Save(SimpleSqlLiteRepository repository, ISqlLiteDataObject item) { return repository.Save(item as Note, CRUD); }
public override int Delete(SimpleSqlLiteRepository repository, int id) { return repository.Delete(id, CRUD); }
#region SQL for Data Access
internal class NoteCRUD : ISqlLiteDataObjectCRUD<Note>
public string CreateSqlLiteTable()
return string.Format("{0}{1}{2}{3}{4}",
string.Format("CREATE TABLE [{0}] ", Constants.TableName),
string.Format("({0} INTEGER PRIMARY KEY ASC, ", Constants.Id),
string.Format("{0} NTEXT, ", Constants.Name),
string.Format("{0} NTEXT, ", Constants.Notes),
string.Format("{0} INTEGER);", Constants.Done));
#region The GetMethods are strongly-coupled to the FromReader method
return string.Format("SELECT {0}{1}{2}{3}{4}",
string.Format("[{0}], ", Constants.Id),
string.Format("[{0}], ", Constants.Name),
string.Format("[{0}], ", Constants.Notes),
string.Format("[{0}] ", Constants.Done),
string.Format("FROM [{0}]", Constants.TableName));
return string.Format("SELECT {0}{1}{2}{3}{4}{5}",
string.Format("[{0}], ", Constants.Id),
string.Format("[{0}], ", Constants.Name),
string.Format("[{0}], ", Constants.Notes),
string.Format("[{0}] ", Constants.Done),
string.Format("FROM [{0}] ", Constants.TableName),
string.Format("WHERE [{0}] = ?", Constants.Id));
public Note FromReader(IDataRecord r)
Id = Convert.ToInt32(r["_id"]),
Name = r["Name"].ToString(),
Notes = r["Notes"].ToString(),
Done = Convert.ToInt32(r["Done"]) == 1
public void Update(Note item, SqliteCommand command)
command.CommandText = string.Format("{0}SET {1}{2}{3}{4}",
string.Format("UPDATE [{0}] ", Constants.TableName),
string.Format("[{0}] = ?, ", Constants.Name),
string.Format("[{0}] = ?, ", Constants.Notes),
string.Format("[{0}] = ? ", Constants.Done),
string.Format("WHERE [{0}] = ?;", Constants.Id));
command.Parameters.Add(new SqliteParameter(DbType.String) { Value = item.Name });
command.Parameters.Add(new SqliteParameter(DbType.String) { Value = item.Notes });
command.Parameters.Add(new SqliteParameter(DbType.Int32) { Value = item.Done });
command.Parameters.Add(new SqliteParameter(DbType.Int32) { Value = item.Id });
public void Insert(Note item, SqliteCommand command)
command.CommandText = string.Format("{0}{1}{2}{3}) VALUES (? ,?, ?)",
string.Format("INSERT INTO [{0}] (", Constants.TableName),
string.Format("[{0}], ", Constants.Name),
string.Format("[{0}], ", Constants.Notes),
string.Format("[{0}]", Constants.Done));
command.Parameters.Add(new SqliteParameter(DbType.String) { Value = item.Name });
command.Parameters.Add(new SqliteParameter(DbType.String) { Value = item.Notes });
command.Parameters.Add(new SqliteParameter(DbType.Int32) { Value = item.Done });
public void Delete(int id, SqliteCommand command)
string.Format("DELETE FROM [{0}] ", Constants.TableName),
string.Format("WHERE [{0}] = ?;", Constants.Id));
command.Parameters.Add(new SqliteParameter(DbType.Int32) { Value = id });
private static class Constants
private const string PluralName = "Notes";
public const string TableName = PluralName;
internal const string Id = "_id";
internal const string Name = "Name";
internal const string Done = "Done";
internal const string Notes = "Notes";
public class MyRepo : SimpleSqlLiteRepository
public static MyRepo Singleton = new MyRepo();
DbLocation = GetFilePathFormattedForPlatform("MyRepo");
Db = new SimpleSqlLiteDatabase(DbLocation, new Note.NoteCRUD().CreateSqlLiteTable());
internal new ISqlLiteDataObject Get<T>(int id, ISqlLiteDataObjectCRUD<T> crud) where T : MyReproSqlLiteObject { return Singleton.Db.Get(id, crud); }
internal new IEnumerable<T> GetAll<T>(ISqlLiteDataObjectCRUD<T> crud) where T : MyReproSqlLiteObject { return Singleton.Db.GetAll(crud); }
internal new int Save<T>(T item, ISqlLiteDataObjectCRUD<T> crud) where T : MyReproSqlLiteObject { return Singleton.Db.Update(item, crud); }
internal new int Delete<T>(int id, ISqlLiteDataObjectCRUD<T> crud) where T : MyReproSqlLiteObject { return Singleton.Db.Delete(id, crud); }