Esempio n. 1
0
        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!!");
            }
        }