public int Delete <T>(T dbobj, string TableName, params KeyValuePair <string, object>[] parameters)
where T : class
{
if (dbobj == null)
{
throw new ArgumentNullException(nameof(dbobj));
}
var SQL = "DELETE FROM " + TableName;
var SqlParams = new List <KeyValuePair <string, object> >();
if (parameters != null && parameters.Length > 0)
{
var lkeys = (string[])parameters.Clone();
for (int i = 0; i < lkeys.Length; i++)
{
lkeys[i] = lkeys[i].ToUpperInvariant();
}
SQL += " WHERE ";
var type = dbobj.GetType();
var properties = type.GetProperties();
foreach (var prp in properties)
{
if (prp.GetCustomAttribute <PDBIgnoreAttribute>() != null)
{
continue;
}
if (ORMLite.IsSupportedProperyType(prp))
{
var fname = prp.Name;
var idx = Array.IndexOf(parameters, fname.ToUpperInvariant());
if (idx < 0)
{
var fname2 = ORMLite.ConvertNameToDB(fname);
idx = Array.IndexOf(lkeys, fname2.ToUpperInvariant());
if (idx >= 0)
{
fname = fname2;
}
}
if (idx >= 0)
{
SQL += fname + "=:" + fname + " AND ";
SqlParams.Add(new KeyValuePair <string, object>(fname, GetPropValue(prp, dbobj)));
}
}
}
SQL = SQL.Remove(SQL.Length - 5);
}
var res = mth.ExecuteNonQuery(SQL, SqlParams.ToArray());
return(res);
}
public void GenerateSchema(string SchemaName)
{
Logger?.Info("Database Schema check/generation started");
var schema = db.GetSchema("Tables");
foreach (DataRow?row in schema.Rows)
{
if (row == null)
{
continue;
}
var dbName = row[0]?.ToString();
var schName = row[1].ToString();
var tblName = row[2].ToString();
if (schName == SchemaName && tblName != null)
{
ExistedTables.Add(tblName);
}
}
foreach (var c in tableClasses)
{
if (!c.IsClass)
{
continue;
}
var orm = new ORMLite(db);
CreateTable(c);
}
var sqls = PostGenerationScript.Split(";");
foreach (var sql in sqls)
{
var lsql = inlineCommentsRgx.Replace(sql, "");
if (!string.IsNullOrEmpty(lsql))
{
try
{
db.Transaction((th) => th.ExecuteNonQuery(lsql));
}
#pragma warning disable CA1031 // Do not catch general exception types
catch (Exception ex)
{
Logger?.Error(ex, $"DBSchemaGenerator. Operation: {lsql.Trim()} ");
}
#pragma warning restore CA1031 // Do not catch general exception types
}
}
Logger?.Info("Database Schema check/generation done");
}
public void Insert <T>(T dbobj, string TableName, bool ConvertFieldNames = true, params KeyValuePair <string, object>[] parameters)
where T : class
{
if (dbobj == null)
{
throw new ArgumentNullException(nameof(dbobj));
}
var SQL = "INSERT INTO " + TableName + "(";
var SQLV = "VALUES(";
var type = dbobj.GetType();
var properties = type.GetProperties();
var SqlParams = new List <KeyValuePair <string, object> >();
foreach (var prp in properties)
{
if (prp.GetCustomAttribute <PDBIgnoreAttribute>() != null)
{
continue;
}
if (ORMLite.IsSupportedProperyType(prp))
{
var fname = prp.Name;
if (ConvertFieldNames)
{
fname = ORMLite.ConvertNameToDB(fname);
}
var value = GetPropValue(prp, dbobj);
SQL += fname + ",";
SQLV += ":" + fname + ",";
SqlParams.Add(new KeyValuePair <string, object>(fname, value));
}
}
if (parameters != null)
{
foreach (var k in parameters)
{
SQL += k.Key + ",";
SQLV += ":" + k.Key + ",";
SqlParams.Add(new KeyValuePair <string, object>(k.Key, k.Value));
}
}
SQL = SQL.TrimEnd(',');
SQLV = SQLV.TrimEnd(',');
SQL += ")" + SQLV + ")";
mth.ExecuteNonQuery(SQL, SqlParams.ToArray());
}
private void CreateTable(Type typ)
{
var nattr = typ.GetCustomAttribute <TableNameAttribute>();
var tname = "";
if (nattr != null)
{
tname = nattr.TableName;
}
else
{
tname = ORMLite.ConvertNameToDB(typ.Name);
}
if (ExistedTables.Exists((n) => n == tname))
{
return;
}
Logger?.Info($"Table {tname} not found in database. Creating...");
var colCnt = 0;
var SQL = "CREATE TABLE " + tname + "(";
#region Reflection returns properties of parent class after child. Here we reverse it.
List <KeyValuePair <Type, List <PropertyInfo> > > pTypes = new List <KeyValuePair <Type, List <PropertyInfo> > >();
foreach (var p in typ.GetProperties())
{
KeyValuePair <Type, List <PropertyInfo> > pType;
if (!pTypes.Exists((pp) => pp.Key == p.DeclaringType) && p.DeclaringType != null)
{
pType = new KeyValuePair <Type, List <PropertyInfo> >(p.DeclaringType, new List <PropertyInfo>());
pTypes.Add(pType);
}
else
{
pType = pTypes.Find((pp) => pp.Key == p.DeclaringType);
}
pType.Value.Add(p);
}
pTypes.Reverse();
#endregion
foreach (var pType in pTypes)
{
foreach (var p in pType.Value)
{
var cName = ORMLite.ConvertNameToDB(p.Name);
var aColDef = p.GetCustomAttribute <ColumnDefAttribute>();
var colDef = "";
if (aColDef != null)
{
colDef = aColDef.ColumnDef;
}
else
{
continue;
}
SQL += cName + " " + colDef + ",";
colCnt++;
}
}
SQL = SQL.Trim(',') + ")";
if (colCnt == 0)
{
throw new Exception(
$"No any DB column definitions found for class {typ.Name}");
}
db.Transaction((th) => {
th.ExecuteNonQuery(SQL);
var types = new List <Type>()
{
typ
};
while (typ != typeof(object) && typ.GetTypeInfo().BaseType != typeof(object))
{
types.Insert(0, typ.GetTypeInfo().BaseType ?? typeof(object));
typ = typ.GetTypeInfo().BaseType ?? typeof(object);
}
foreach (var t in types)
{
var atPostCreate = t.GetCustomAttribute <PostTableCreateScriptAttribute>();
if (atPostCreate != null)
{
var sqls = atPostCreate.Script.Split(";");
foreach (var sql in sqls)
{
if (!string.IsNullOrEmpty(sql?.Trim()))
{
var lsql = sql.Replace("{TNAME}", tname, StringComparison.InvariantCultureIgnoreCase);
th.ExecuteNonQuery(lsql);
}
}
}
}
Logger?.Info($"Created table {tname}");
},
IsolationLevel.ReadCommitted,
true // Perform DDL out of transaction
);
}
public int Update <T>(T dbobj, string TableName, bool ConvertFieldNames = true, params string[] keys)
where T : class
{
if (dbobj == null)
{
throw new ArgumentNullException(nameof(dbobj));
}
var lkeys = (string[])Array.CreateInstance(typeof(string), 0);
if (keys != null)
{
lkeys = (string[])keys.Clone();
}
for (int i = 0; i < lkeys.Length; i++)
{
lkeys[i] = lkeys[i].ToUpperInvariant();
}
var SQL = "UPDATE " + TableName + " SET ";
var SQLW = " WHERE ";
var type = dbobj.GetType();
var properties = type.GetProperties();
var keyscnt = 0;
var SqlParams = new List <KeyValuePair <string, object> >();
var SqlParamsW = new List <KeyValuePair <string, object> >();
foreach (var prp in properties)
{
if (prp.GetCustomAttribute <PDBIgnoreAttribute>() != null)
{
continue;
}
if (ORMLite.IsSupportedProperyType(prp))
{
var fname = prp.Name;
if (ConvertFieldNames)
{
fname = ORMLite.ConvertNameToDB(fname);
}
var value = GetPropValue(prp, dbobj);
var idx = keys == null ? -1
: Array.IndexOf(keys, fname.ToUpperInvariant());
if (idx < 0)
{
idx = Array.IndexOf(lkeys, fname.ToUpperInvariant());
}
if (idx < 0)
{
SQL += (fname + " = :" + fname + ",");
SqlParams.Add(new KeyValuePair <string, object>(fname, GetPropValue(prp, dbobj)));
}
if (idx >= 0)
{
keyscnt++;
SQLW += (fname + " = :" + fname + " AND ");
SqlParamsW.Add(new KeyValuePair <string, object>(fname, value));
}
}
}
SQL = SQL.TrimEnd(',');
SqlParams.AddRange(SqlParamsW);
if (keyscnt > 0)
{
SQL += SQLW.Remove(SQLW.Length - 5);// Remove trailing ' AND '
}
var res = mth.ExecuteNonQuery(SQL, SqlParams.ToArray());
return(res);
}
