public static string dxGrid_UpdateData(string pk, List <int> pkvals, OrderedDictionary newvalues, string db, string schema, string tbl)
{
SQL_utils sql = new SQL_utils(db);
List <SQL_utils.dbfield> flds = dxGrid_UpdateData_CreateDbFields(sql, pk, newvalues, tbl, schema);
string sqlcode = "update " + schema + "." + tbl + " set ";
List <string> numtypes = new List <string> {
"number", "int", "smallint", "bigint", "float", "decimal"
};
List <string> texttypes = new List <string> {
"text", "string", "char", "varchar", "nchar"
};
List <string> datetypes = new List <string> {
"date", "datetime", "smalldatetime"
};
foreach (SQL_utils.dbfield fld in flds)
{
if (fld.fieldname.ToLower() != pk.ToLower()) //don't add the PK
{
if (fld.value != "" & fld.value != null)
{
if (numtypes.Any(s => fld.sqldbtype.Contains(s)))
{
sqlcode += fld.fieldname + "= " + SQL_utils.CleanString(fld.value, false) + ",";
}
else if (texttypes.Any(s => fld.sqldbtype.Contains(s)))
{
sqlcode += fld.fieldname + "= " + SQL_utils.CleanString(fld.value, true) + ",";
}
else if (datetypes.Any(s => fld.sqldbtype.Contains(s)))
{
sqlcode += fld.fieldname + "= " + SQL_utils.CleanString(fld.value, true) + ",";
}
else
{
sqlcode += fld.fieldname + "= " + SQL_utils.CleanString(fld.value, true) + ",";
}
}
}
}
sqlcode = sqlcode.Substring(0, sqlcode.Length - 1);
//array of pkvals is when we update multiple records with the same values
string pkvals_csv = string.Join(",", pkvals);
sqlcode += " where " + pk + " in (" + pkvals_csv.ToString() + ")";
if (sqlcode.Contains(" where ") && pkvals.Count >= 1) //must have a where clause
{
try
{
sql.NonQuery_from_SQLstring(sqlcode);
sql.Close();
return(sqlcode + " pk:" + pk + " = " + pkvals_csv + " updated!"); // +sql;
}
catch (Exception ex)
{
sql.Close();
return(ex.Message);
}
}
else
{
sql.Close();
return("No Where Clause or no PK Value!!");
}
}