public T Read <T>(Int64 id)
{
var tmpList = ReadList <T>(new DbQueryItem("Id", CompareOp.EqualTo, id));
if (!DictionaryUtils.IsNullOrEmpty(tmpList))
{
return(tmpList.FirstOrDefault());
}
return(default(T));
}
public T Read <T>(params DbQueryItem[] args)
{
if (String.IsNullOrEmpty(this.ConString))
{
throw new ArgumentNullException("ConnectionString");
}
var tmpList = ReadList <T>(args);
if (!DictionaryUtils.IsNullOrEmpty(tmpList))
{
return(tmpList.FirstOrDefault());
}
return(default(T));
}
public IList <T> ReadList <T>(params DbQueryItem[] args)
{
if (String.IsNullOrEmpty(this.ConString))
{
throw new MissingFieldException("ConnectionString");
}
Type dstType = typeof(T);
var propList = dstType.GetProperties();
if (GetTableInfo(dstType, out string tblName, out List <Tuple <string, Type> > colNameList))
{
using (var con = new SqliteConnection(this.ConString)) {
using (var cmd = con.CreateCommand()) {
string cmdText = $"SELECT {String.Join(',', colNameList.Select(t => t.Item1))} FROM {tblName}";
if (!DictionaryUtils.IsNullOrEmpty(args))
{
List <string> whereItemList = null;
foreach (var arg in args)
{
if (whereItemList == null)
{
whereItemList = new List <string>();
}
var prop = propList.FirstOrDefault(obj => obj.Name.Equals(arg.PropertyName));
if (prop != null)
{
string colName = null;
var attrList = prop.GetCustomAttributes(true);
if (!DictionaryUtils.IsNullOrEmpty(attrList))
{
foreach (var attr in attrList)
{
if (attr is AppColumnAttribute)
{
colName = ((AppColumnAttribute)attr).Name;
}
}
}
if (!String.IsNullOrEmpty(colName))
{
whereItemList.Add(
$"{colName} {ConvertCompareOp(arg.Op)} @{colName}");
cmd.Parameters.Add(new SqliteParameter($"@{colName}", arg.Value));
}
}
}
if (!DictionaryUtils.IsNullOrEmpty(whereItemList))
{
cmdText += $" WHERE {String.Join(" AND ", whereItemList)}";
}
}
cmd.CommandText = cmdText;
con.Open();
this._logger.LogDebug(cmd.CommandText);
using (var sqlReader = cmd.ExecuteReader()) {
var schemaInfo = sqlReader.GetColumnSchema();
List <List <Tuple <string, object> > > rows = new List <List <Tuple <string, object> > >();
while (sqlReader.Read())
{
List <Tuple <string, object> > row = new List <Tuple <string, object> >();
foreach (var col in schemaInfo)
{
row.Add(
new Tuple <string, object>(col.ColumnName, sqlReader[col.ColumnName]));
}
rows.Add(row);
}
if (!DictionaryUtils.IsNullOrEmpty(rows))
{
List <T> result = new List <T>();
foreach (var row in rows)
{
object dto = null;
if (dstType == typeof(Category))
{
dto = new Category();
}
else if (dstType == typeof(CategoryItem))
{
dto = new CategoryItem();
}
else if (dstType == typeof(ValueItem))
{
dto = new ValueItem();
}
if (dto != null)
{
foreach (var prop in propList)
{
var attrList = prop.GetCustomAttributes(true);
if (!DictionaryUtils.IsNullOrEmpty(attrList))
{
foreach (var attr in attrList)
{
if (attr is AppColumnAttribute)
{
var collAttr = attr as AppColumnAttribute;
if (!String.IsNullOrEmpty(collAttr.Name))
{
var tmp = row.FirstOrDefault(obj => obj.Item1.Equals(collAttr.Name));
if (tmp != null)
{
if (prop.PropertyType == typeof(String))
{
prop.SetValue(dto, tmp.Item2);
}
else if (prop.PropertyType == typeof(Int32))
{
if (tmp.Item2 != null &&
Int32.TryParse(tmp.Item2.ToString(), out Int32 intVal))
{
prop.SetValue(dto, intVal);
}
}
else if (prop.PropertyType == typeof(Int64))
{
if (tmp.Item2 != null &&
Int64.TryParse(tmp.Item2.ToString(), out Int64 intVal))
{
prop.SetValue(dto, intVal);
}
}
else if (prop.PropertyType == typeof(DateTime) ||
prop.PropertyType == typeof(DateTime?))
{
if (tmp.Item2 != null &&
DateTime.TryParse(tmp.Item2.ToString(), out DateTime dtVal))
{
prop.SetValue(dto, dtVal);
}
}
else
{
prop.SetValue(dto, tmp.Item2);
}
}
}
}
}
}
}
result.Add((T)dto);
}
}
return(result as List <T>);
}
}
}
}
}
return(default(IList <T>));
}
bool GetTableInfo(Type type, out string table, out List <Tuple <string, Type> > colNameList)
{
table = null;
colNameList = null;
Type typeObj = null;
if (type == typeof(Category))
{
typeObj = typeof(Category);
}
else if (type == typeof(CategoryItem))
{
typeObj = typeof(CategoryItem);
}
else if (type == typeof(ValueItem))
{
typeObj = typeof(ValueItem);
}
if (typeObj != null)
{
var tmpClassAttrList = typeObj.GetCustomAttributes(true);
if (!DictionaryUtils.IsNullOrEmpty(tmpClassAttrList))
{
foreach (var attr in tmpClassAttrList)
{
if (attr is TableAttribute)
{
table = ((TableAttribute)attr).Name;
break;
}
}
if (!String.IsNullOrEmpty(table))
{
var propList = typeObj.GetProperties();
if (!DictionaryUtils.IsNullOrEmpty(propList))
{
foreach (var prop in propList)
{
var fieldAttrList = prop.GetCustomAttributes(true);
if (!DictionaryUtils.IsNullOrEmpty(fieldAttrList))
{
foreach (var fieldAttr in fieldAttrList)
{
if (fieldAttr is AppColumnAttribute)
{
if (colNameList == null)
{
colNameList = new List <Tuple <string, Type> >();
}
colNameList.Add(
new Tuple <string, Type>(((AppColumnAttribute)fieldAttr).Name, prop.PropertyType));
}
}
}
}
}
return(!DictionaryUtils.IsNullOrEmpty(colNameList));
}
}
}
return(false);
}
public int Update(AbstractDataItem item)
{
if (String.IsNullOrEmpty(this.ConString))
{
throw new MissingFieldException("ConnectionString");
}
if (item == null)
{
throw new ArgumentNullException("IdItem");
}
if (item.Id < 0)
{
throw new ArithmeticException("InvalidId");
}
item.Updated = DateTime.UtcNow;
Type dstType = item.GetType();
var propList = dstType.GetProperties();
if (GetTableInfo(dstType, out string tblName, out List <Tuple <string, Type> > colNameList))
{
List <Tuple <string, object> > valueList = null;
foreach (var prop in propList.Where(
obj => !obj.Name.Equals("Id") &&
!obj.Name.Equals("Created")))
{
var attrList = prop.GetCustomAttributes(true);
if (attrList != null)
{
foreach (var attr in attrList)
{
if (attr is AppColumnAttribute)
{
var appAttr = attr as AppColumnAttribute;
if (appAttr.CanUpdate)
{
if (valueList == null)
{
valueList = new List <Tuple <string, object> >();
}
valueList.Add(new Tuple <string, object>(
appAttr.Name, prop.GetValue(item)));
break;
}
}
}
}
}
if (!DictionaryUtils.IsNullOrEmpty(valueList))
{
using (var con = new SqliteConnection(this.ConString)) {
using (var cmd = con.CreateCommand()) {
var tmpList = new List <string>();
foreach (var kvItem in valueList)
{
tmpList.Add($"{kvItem.Item1}=@{kvItem.Item1}");
cmd.Parameters.Add(new SqliteParameter($"@{kvItem.Item1}", kvItem.Item2));
}
cmd.CommandText = $"UPDATE {tblName} SET {String.Join(',', tmpList)} " +
$"WHERE id={item.Id}";
con.Open();
this._logger.LogDebug(cmd.CommandText);
return(cmd.ExecuteNonQuery());
}
}
}
}
return(0);
}
public Int64 Create(AbstractDataItem item)
{
if (String.IsNullOrEmpty(this.ConString))
{
throw new MissingFieldException("ConnectionString");
}
if (item == null)
{
throw new ArgumentNullException("IdItem");
}
item.State = (int)DataItemState.Default;
item.Created = DateTime.UtcNow;
item.Updated = null;
Type dstType = item.GetType();
var propList = dstType.GetProperties();
if (GetTableInfo(dstType, out string tblName, out List <Tuple <string, Type> > colNameList))
{
List <Tuple <string, object> > valueList = null;
foreach (var prop in propList)
{
var attrList = prop.GetCustomAttributes(true);
if (attrList != null)
{
foreach (var attr in attrList)
{
if (attr is AppColumnAttribute)
{
var appAttr = attr as AppColumnAttribute;
if (appAttr.CanInsert)
{
if (valueList == null)
{
valueList = new List <Tuple <string, object> >();
}
valueList.Add(new Tuple <string, object>(
appAttr.Name, prop.GetValue(item)));
}
break;
}
}
}
}
if (!DictionaryUtils.IsNullOrEmpty(valueList))
{
using (var con = new SqliteConnection(this.ConString)) {
using (var cmd = con.CreateCommand()) {
cmd.CommandText = $"INSERT INTO {tblName} ({String.Join(',', valueList.Select(obj => obj.Item1))}) " +
$"VALUES ({String.Join(',', valueList.Select(obj => "@" + obj.Item1))})";
foreach (var kvItem in valueList)
{
cmd.Parameters.Add(new SqliteParameter($"@{kvItem.Item1}", kvItem.Item2));
}
con.Open();
this._logger.LogDebug(cmd.CommandText);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
cmd.CommandText = "select last_insert_rowid()";
this._logger.LogDebug(cmd.CommandText);
return((Int64)cmd.ExecuteScalar());
}
}
}
}
}
return(0);
}
public int Delete <T>(params DbQueryItem[] args)
{
if (String.IsNullOrEmpty(this.ConString))
{
throw new MissingFieldException("ConnectionString");
}
Type dstType = typeof(T);
var propList = dstType.GetProperties();
if (GetTableInfo(dstType, out string tblName, out List <Tuple <string, Type> > colNameList))
{
using (var con = new SqliteConnection(this.ConString)) {
using (var cmd = con.CreateCommand()) {
string cmdText = $"DELETE FROM {tblName}";
if (!DictionaryUtils.IsNullOrEmpty(args))
{
List <string> whereItemList = null;
foreach (var arg in args)
{
if (whereItemList == null)
{
whereItemList = new List <string>();
}
var prop = propList.FirstOrDefault(obj => obj.Name.Equals(arg.PropertyName));
if (prop != null)
{
string colName = null;
var attrList = prop.GetCustomAttributes(true);
if (!DictionaryUtils.IsNullOrEmpty(attrList))
{
foreach (var attr in attrList)
{
if (attr is AppColumnAttribute)
{
colName = ((AppColumnAttribute)attr).Name;
}
}
}
if (!String.IsNullOrEmpty(colName))
{
whereItemList.Add(
$"{colName} {ConvertCompareOp(arg.Op)} @{colName}");
cmd.Parameters.Add(new SqliteParameter($"@{colName}", arg.Value));
}
}
}
if (!DictionaryUtils.IsNullOrEmpty(whereItemList))
{
cmdText += $" WHERE {String.Join(" AND ", whereItemList)}";
}
}
cmd.CommandText = cmdText;
con.Open();
this._logger.LogDebug(cmd.CommandText);
return(cmd.ExecuteNonQuery());
}
}
}
return(0);
}
static void Main(string[] args)
{
var serviceCollection = new ServiceCollection()
.AddLogging(builder => {
builder.SetMinimumLevel(LogLevel.Trace);
builder.AddConsole();
});
var serviceProvider = serviceCollection.BuildServiceProvider();
var loggerFactory = serviceProvider.GetService <ILoggerFactory>();
var logger = loggerFactory.CreateLogger <Program>();
try {
var dbFileInfo = new FileInfo(@$ "{Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)}\secure_dev.db");
if (!dbFileInfo.Exists)
{
logger.LogWarning($"Database {dbFileInfo.Name} not found.");
return;
}
var db = new Database(loggerFactory,
new FileInfo(@$ "{Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)}\secure_dev.db"));
logger.LogInformation($"Database {dbFileInfo.Name} initialized.");
// Read collection
var catList = db.ReadList <Category>(
new List <DbQueryItem>()
{
new DbQueryItem("Id", CompareOp.GreaterThan, 0)
}.ToArray());
if (!DictionaryUtils.IsNullOrEmpty(catList))
{
logger.LogInformation($"ReadList categories received {catList.Count} rows");
// Create new Category
var catId = db.Create(
new Category()
{
Name = $"CategoryName {catList.Count + 1}"
});
if (catId > 1)
{
logger.LogInformation($"New category [Id {catId}] created");
// Update category
var cat = db.Read <Category>(catId);
if (cat != null)
{
cat.Name = $"CategoryName {catList.Count +1}";
int numRowsUpdated = db.Update(cat);
if (numRowsUpdated == 1)
{
logger.LogInformation($"Category [Id {catId}] updated");
// Delete newly create item
if (db.Delete <Category>(cat.Id) == 1)
{
logger.LogInformation($"Category [Id {catId}] deleted");
}
}
}
}
}
Console.ReadKey();
}
catch (Exception ex) {
logger.LogError(ex.Message);
}
}
