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