sqlite-net
sqlite-net copied to clipboard
praeclarum
Is creating a generic data-access class an option?
I started off by writing something like this:
using SQLite;
public class Database<T>
{
public Database(string dataFilePath)
{
this._connection = new SQLiteAsyncConnection(dataFilePath);
this._connection.CreateTablesAsync(CreateFlags.None, typeof(T));
}
private readonly SQLiteAsyncConnection _connection;
}
... but then I quickly became stuck noticing how Table<>() (and other) methods requires a type that has a default constructor, making it impossible to create a class that could handle arbitrary types. Is there a solution, or would you consider adding Type arguments to all these methods?
Thanks.
I found a solution to my question this way:
namespace Storage
{
using SQLite;
using System.Linq;
// class: [Table("")]
// property: [Column(""), PrimaryKey, AutoIncrement, Indexed, Ignore, Unique, MaxLength(1), Collation(""), NotNull]
// enum: [StoreAsText]
public class DataItemBase
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
}
public class DataTable<T> where T : DataItemBase
{
public DataTable(string dataFilePath)
{
this.DataFilePath = dataFilePath;
this._connection = new SQLiteConnection(dataFilePath, true);
this._connection.CreateTable<T>();
}
public readonly string DataFilePath;
private readonly SQLiteConnection _connection;
public void Close()
{
this._connection.Dispose();
}
public IQueryable<T> GetAll()
{
var query = new TableQuery<T>(this._connection);
return query.AsQueryable<T>();
}
public void Save(T item)
{
this._connection.Insert(item);
}
public void Delete(int id)
{
this._connection.Delete<T>(id);
}
}
}
Now I can define arbitrary classes to represent tables, and use this little class to persist them, without having to write a separate implementation for each object/table. This can get more complex, of course, if I wanted to create a class that manages all tables, but it'll do for now. Thanks for the great library!
I like this better:
namespace Storage
{
using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
// class: [Table("")]
// property: [Column(""), PrimaryKey, AutoIncrement, Indexed, Ignore, Unique, MaxLength(1), Collation(""), NotNull]
// enum: [StoreAsText]
public abstract class DataItemBase
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
}
public class Database : IDisposable
{
public Database(string dataFilePath)
{
this.DataFilePath = dataFilePath;
this._connection = new SQLiteConnection(dataFilePath, true);
this._tables = new List<Type>();
}
private readonly SQLiteConnection _connection;
public readonly string DataFilePath;
private readonly List<Type> _tables;
private void EnsureTable<T>()
{
var type = typeof(T);
if (!this._tables.Contains(type))
{
this._connection.CreateTable<T>();
this._tables.Add(type);
}
}
public void Dispose()
{
this._connection.Dispose();
}
public IQueryable<T> GetAll<T>()
{
this.EnsureTable<T>();
var query = new TableQuery<T>(this._connection);
return query.AsQueryable<T>();
}
public void Save<T>(T item) where T:DataItemBase
{
this.EnsureTable<T>();
if (item.Id == 0)
this._connection.Insert(item);
else
this._connection.Update(item);
}
public void Delete<T>(int id)
{
this.EnsureTable<T>();
this._connection.Delete<T>(id);
}
}
}
And few quick tests:
namespace Tests
{
using Microsoft.VisualStudio.TestTools.UnitTesting;
using SQLite;
using Storage;
using System;
using System.IO;
using System.Linq;
[TestClass]
public class StorageTests
{
private Database _database;
[Table("t")]
class DataItem : DataItemBase
{
[Column("s")]
public string String { get; set; }
public int Int32 { get; set; }
public long Int64 { get; set; }
public DateTimeOffset Date { get; set; }
public Guid Guid { get; set; }
public int? NullableInt32 { get; set; }
public long? NullableInt64 { get; set; }
public DateTimeOffset? NullableDate { get; set; }
public Guid? NullableGuid { get; set; }
}
[TestInitialize]
public void Initialize()
{
this._database = new Database(Path.GetTempFileName());
}
[TestCleanup()]
public void Cleanup()
{
string path = this._database.DataFilePath;
this._database.Dispose();
this._database = null;
File.Delete(path);
}
[TestMethod]
public void CRUD()
{
var item = new DataItem
{
String = "test",
Int32 = 2,
Int64 = long.MaxValue,
Guid = Guid.NewGuid(),
Date = DateTimeOffset.Now,
NullableInt32 = 4,
NullableInt64 = null,
NullableDate = null,
NullableGuid = Guid.Empty
};
this._database.Save(item);
Assert.AreEqual(item.Id, 1);
var items = this._database.GetAll<DataItem>().ToArray();
Assert.IsNotNull(items);
Assert.IsTrue(items.Length == 1);
item = new DataItem
{
String = "test",
Int32 = 2,
Int64 = long.MaxValue,
Guid = Guid.NewGuid(),
Date = DateTimeOffset.Now,
NullableInt32 = 4,
NullableInt64 = null,
NullableDate = null,
NullableGuid = Guid.Empty
};
this._database.Save(item);
Assert.AreEqual(item.Id, 2);
item.Id = 0;
this._database.Save(item);
Assert.AreEqual(item.Id, 3);
items = this._database.GetAll<DataItem>().Where(i => i.Id > 1).ToArray();
Assert.IsNotNull(items);
Assert.IsTrue(items.Length == 2);
Assert.IsNotNull(items[0]);
Assert.AreEqual(items[0].Id, 2);
Assert.IsNotNull(items[1]);
Assert.AreEqual(items[1].Id, 3);
this._database.Delete<DataItem>(int.MaxValue);
this._database.Delete<DataItem>(2);
items = this._database.GetAll<DataItem>().ToArray();
Assert.IsNotNull(items);
Assert.IsTrue(items.Length == 2);
Assert.IsNotNull(items[0]);
Assert.AreEqual(items[0].Id, 1);
Assert.IsNotNull(items[1]);
Assert.AreEqual(items[1].Id, 3);
}
}
}
Hello,
Congratulations on the excellent example. I tried replicating it with SQLiteAsyncConnection and it doesn't work, making it impossible to create a class that could handle arbitrary types. For example: Before: connection.CreateTable<TEntity>();
Later: connection.CreateTableAsync<TEntity>();
Exception: Error CS0310 "TEntity" must be a non-abstract type with a public parameterless constructor so it can be used as a "T" parameter in the generic type or method
Hello,
Congratulations on the excellent example. I tried replicating it with SQLiteAsyncConnection and it doesn't work, making it impossible to create a class that could handle arbitrary types. For example: Before: connection.CreateTable();
Later: connection.CreateTableAsync();
Exception: Error CS0310 "TEntity" must be a non-abstract type with a public parameterless constructor so it can be used as a "T" parameter in the generic type or method
This is true.
Have you found a solution?