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 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());
}
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);
}