コード例 #1
0
        /*public static XResponse TableDelete(string alias, int uid)
         * {
         *  Dictionary<string, dynamic> datos = new Dictionary<string, dynamic>();
         *  datos.Add("deleted", "1");
         *  string where = "uid = " + uid.ToString();
         *  Tabla send = new Tabla();
         *  send.alias = alias;
         *  send.datos = datos;
         *  send.where = where;
         *  return TableUpdate(send);
         * }*/
        /*public static XResponse KillData(string alias, string where)
         * {
         *  if (string.IsNullOrEmpty(alias)) { return CrsCore.ResponseError(600, "alias esta vacio"); }
         *  if (string.IsNullOrEmpty(where)) { return CrsCore.ResponseError(600, "where esta vacio"); }
         *  //ARMANDO QUERY
         *  string lcquery = "BEGIN;DELETE FROM " + alias + " WHERE " + where + ";COMMIT;";
         *  //enviando query
         *  XtrDataConnect mysql = new XtrDataConnect();
         *  if (!connect(ref mysql)) { return CrsCore.ResponseError(200, mysql.msg); }
         *  MySqlCommand mycomand = new MySqlCommand(lcquery, mysql.connection);
         *  int colaf = 0;
         *  try
         *  {
         *      colaf = mycomand.ExecuteNonQuery();
         *  }
         *  catch (MySqlException ex)
         *  {
         *      mysql.connection.Close();
         *      return CrsCore.ResponseError(201, "Query:" + lcquery + " sqlsay:" + ex.Message);
         *  }
         *  mysql.connection.Close();
         *  return CrsCore.Response();
         * }*/
        public static XResponse TableUpdate(string alias, Dictionary <string, dynamic> campos, string where)
        {
            if (campos.Count == 0)
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("TableUpdatecampos esta vacia")));
            }
            if (string.IsNullOrEmpty(alias))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableupdate alias esta vacia")));
            }
            if (string.IsNullOrEmpty(where))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableupdate where esta vacia")));
            }

            //ARMANDO QUERY
            string lcquery = "BEGIN;";

            where    = "where " + where;
            lcquery += "update " + alias + " set ";
            string valor = "";

            foreach (KeyValuePair <string, dynamic> colum in campos)
            {
                object rigival = TraslateFields(colum.Value);
                valor = Convert.ToString(rigival);
                if (rigival == null)
                {
                    continue;
                }
                lcquery += colum.Key + " = '" + valor + "',";
            }//compatibilidad con datos importados de carevi
            lcquery  = lcquery.Substring(0, lcquery.Length - 1);//quitando ultima coma
            lcquery += " " + where + ";";//cerrando instruccion

            lcquery += " COMMIT;";
            //enviando query
            XtrDataConnect mysql = new XtrDataConnect();

            if (!connect(ref mysql))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableupdate connect fail:" + mysql.msg)));
            }
            MySqlCommand mycomand = new MySqlCommand(lcquery, mysql.connection);
            int          colaf    = 0;

            try
            {
                colaf = mycomand.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                mysql.connection.Close();
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableupdate catch:" + lcquery + " sqlsay:" + ex.Message)));
            }
            mysql.connection.Close();
            return(new XResponse {
                response = colaf.ToString()
            });
        }
コード例 #2
0
        public static XResponse CreateTable(CodexDBF.TableCode codex, string name = "")
        {
            //ARMANDO QUERY
            XResponse resp = CodexDBF.GetStringTable(codex, name);

            if (resp.error)
            {
                return(resp);
            }
            string lcquery = resp.response;
            //enviando query
            XtrDataConnect mysql = new XtrDataConnect();

            if (!connect(ref mysql))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("CreateTable connect fail:" + mysql.msg)));
            }
            MySqlCommand mycomand = new MySqlCommand(lcquery, mysql.connection);

            try
            {
                mycomand.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                mysql.connection.Close();
                return(CrsCore.ResponseError(CrsCore.SaveLog("CreateTable catch:" + lcquery + " " + ex.Message)));
            }
            mysql.connection.Close();
            return(new XResponse());
        }
コード例 #3
0
        public static bool?ChkTable(string table)
        {
            if (string.IsNullOrEmpty(table))
            {
                CrsCore.SaveLog("ChkTable Falta dbase o tabla"); return(null);
            }
            XtrDataConnect mysql = new XtrDataConnect();

            if (!connect(ref mysql))
            {
                CrsCore.SaveLog("ChkTable connect fail:" + mysql.msg); return(null);
            }
            //armando query
            string       lcquery  = string.Format("SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '{1}'", constantes.database, table);
            MySqlCommand mycomand = new MySqlCommand(lcquery, mysql.connection);

            try
            {
                MySqlDataReader myreader = mycomand.ExecuteReader();
                if (!myreader.HasRows)
                {
                    mysql.connection.Close(); return(false);
                }
                List <Dictionary <string, dynamic> > data = (List <Dictionary <string, dynamic> >)BuildOut(BuildType.LstDctStrDyn, myreader);
                mysql.connection.Close();
                return(data[0]["count"] > 0);
            }
            catch (Exception e)
            {
                CrsCore.SaveLog("ChkTable catch:" + e.Message);
                mysql.connection.Close();
                return(null);
            }
        }
コード例 #4
0
        public static XResponse TableSelect(List <string> campos, string from, string where, BuildType output)
        {
            XResponse salida = new XResponse();

            if (string.IsNullOrEmpty(from))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("TableSelect Falta from")));
            }
            DataTable      dtstruct = new DataTable();
            XtrDataConnect mysql    = new XtrDataConnect();

            if (!connect(ref mysql))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("TableSelect connect fail:" + mysql.msg)));
            }
            //armando query
            string lcquery = "select ";
            string lcampos = "";
            string lcfrom  = " from " + from;
            string lcwhere = " where deleted = 0";

            lcwhere += (string.IsNullOrEmpty(where)) ? "" : " and " + where;
            for (int a = 0; a < campos.Count; a++)
            {
                lcampos += campos[a] + ",";
            }
            lcampos  = lcampos.Substring(0, lcampos.Length - 1);
            lcquery += lcampos + lcfrom + lcwhere + " LIMIT 0,100";
            MySqlCommand    mycomand = new MySqlCommand(lcquery, mysql.connection);
            MySqlDataReader myreader = mycomand.ExecuteReader();

            salida.empty = !myreader.HasRows;
            if (output == 0)
            {
                object[] sql = new object[2];
                sql[0]         = myreader;
                sql[1]         = mysql.connection;
                salida.objecto = sql;
            }
            else
            {
                salida.objecto = BuildOut(output, myreader);
            }
            if (!output.Equals(0))
            {
                myreader.Close();
                mysql.connection.Close();
            }
            return(salida);
        }
コード例 #5
0
        /*        public static XResponse TableUpdate(List<Tabla> tabla)
         *      {
         *          if (tabla.Count == 0) { return CrsCore.ResponseError(600, "tabla esta vacia"); }
         *          //ARMANDO QUERY
         *          string lcquery = "BEGIN;";
         *          string where = "";
         *          foreach (Tabla item in tabla)
         *          {
         *              if (string.IsNullOrEmpty(item.alias)) { continue; }
         *              where = (string.IsNullOrEmpty(item.where)) ? "" : "where " + item.where;
         *              lcquery += "update " + item.alias + " set ";
         *              string valor = "";
         *              foreach (KeyValuePair<string, dynamic> colum in item.datos)
         *              {
         *                  object rigival = TraslateFields(colum.Value);
         *                  valor = Convert.ToString(rigival);
         *                  if (rigival == null) { continue; }
         *                  lcquery += colum.Key + " = '" + valor + "',";
         *              }//compatibilidad con datos importados de carevi
         *              lcquery = lcquery.Substring(0, lcquery.Length - 1);//quitando ultima coma
         *              lcquery += " " + where + ";";//cerrando instruccion
         *          }
         *          lcquery += " COMMIT;";
         *          //enviando query
         *          XtrDataConnect mysql = new XtrDataConnect();
         *          if (!connect(ref mysql)) { return CrsCore.ResponseError(200, mysql.msg); }
         *          MySqlCommand mycomand = new MySqlCommand(lcquery, mysql.connection);
         *          int colaf = 0;
         *          try
         *          {
         *              colaf = mycomand.ExecuteNonQuery();
         *          }
         *          catch (MySqlException ex)
         *          {
         *              mysql.connection.Close();
         *              return CrsCore.ResponseError(201, "Query:" + lcquery + " sqlsay:" + ex.Message);
         *          }
         *          mysql.connection.Close();
         *          return CrsCore.Response(colaf, "termino!!");
         *      }*/
        /*public static XResponse TableUpdate(Tabla tabla)
         * {
         *  return TableUpdate(new List<Tabla> { tabla });
         * }*/
        /*public static XResponse TableInsUpd(Tabla tabla)
         * {
         *  return TableInsUpd(new List<Tabla>() { tabla });
         * }*/
        /*   public static XResponse TableInsert(Tabla tabla)
         * {
         *     return TableInsert(new List<Tabla>() { tabla }, true);
         * }*/
        /*  public static XResponse TableInsert(List<Tabla> lista)
         * {
         *    return TableInsert(lista, true);
         * }*/
        public static XResponse TableInsert(string alias, Dictionary <string, dynamic> campos)
        {
            if (campos.Count == 0)
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableinsert campos esta vacia")));
            }
            if (string.IsNullOrEmpty(alias))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableinsert alias esta vacia")));
            }

            //ARMANDO QUERY
            string lcquery  = "BEGIN;";
            string lccampos = "";
            string lcvalues = "";

            //for (int r = 0; r < lista.Length; r++)

            lcquery += "insert into " + alias;
            lccampos = " (";
            lcvalues = " values(";
            string valor = "";

            //agregando camps
            foreach (KeyValuePair <string, dynamic> colum in campos)
            {
                object rigival = TraslateFields(colum.Value);
                if (rigival == null)
                {
                    continue;
                }
                valor     = Convert.ToString(rigival);
                lccampos += colum.Key + ",";
                lcvalues += "'" + valor + "',";
            }
            lccampos = lccampos.Substring(0, lccampos.Length - 1) + ")"; //quitando ultima coma
            lcvalues = lcvalues.Substring(0, lcvalues.Length - 1) + ")"; //quitando ultima coma
            lcquery += lccampos + lcvalues + ";";                        //cerrando instruccion

            lcquery += "COMMIT;";
            //enviando query
            XtrDataConnect mysql = new XtrDataConnect();

            if (!connect(ref mysql))
            {
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableinsert connect fail:" + mysql.msg)));
            }
            MySqlCommand mycomand = new MySqlCommand(lcquery, mysql.connection);
            int          colaf    = 0;

            try
            {
                colaf = mycomand.ExecuteNonQuery();
                //MySqlDataReader myreader = mycomand.ExecuteReader();
                //while (myreader.Read()) { colaf = (int)myreader.GetValue(0); }
            }
            catch (MySqlException ex)
            {
                mysql.connection.Close();
                return(CrsCore.ResponseError(CrsCore.SaveLog("tableinsert catch:" + lcquery + " " + ex.Message)));
            }
            mysql.connection.Close();
            return(new XResponse {
                response = colaf.ToString()
            });
        }
コード例 #6
0
        private static object BuildOut(BuildType output, MySqlDataReader myreader)
        {
            DataTable dtstruct  = new DataTable();
            object    salida    = new object();
            DataTable datatable = new DataTable();

            Tabla[] tabla = new Tabla[0];
            switch (output)
            {
            case BuildType.LstDctStrStr:    //List<Dictionary<string, string>>
                List <Dictionary <string, string> > ltabla2 = new List <Dictionary <string, string> >();
                dtstruct = myreader.GetSchemaTable();
                Dictionary <string, string> datos2;
                //llenando la tabla
                while (myreader.Read())
                {
                    datos2 = new Dictionary <string, string>();
                    for (int a = 0; a < myreader.FieldCount; a++)
                    {
                        datos2.Add((dtstruct.Rows[a])["ColumnName"].ToString(), Convert.ToString(myreader.GetValue(a)));
                    }
                    ltabla2.Add(datos2);
                }
                salida = ltabla2;
                break;

            case BuildType.LstDctStrDyn:    //List<Dictionary<string, dynamic>>
                List <Dictionary <string, dynamic> > ltabla = new List <Dictionary <string, dynamic> >();
                dtstruct = myreader.GetSchemaTable();
                Dictionary <string, dynamic> datos;
                //llenando la tabla
                while (myreader.Read())
                {
                    datos = new Dictionary <string, dynamic>();
                    for (int a = 0; a < myreader.FieldCount; a++)
                    {
                        datos.Add((dtstruct.Rows[a])["ColumnName"].ToString(), myreader.GetValue(a));
                    }
                    ltabla.Add(datos);
                }
                salida = ltabla;
                break;

            case BuildType.TablaArray:    //Tabla[]
                dtstruct = myreader.GetSchemaTable();
                string[] fields = new string[myreader.FieldCount];
                int      count  = -1;
                string   valor  = "";
                foreach (DataRow myField in dtstruct.Rows)
                {
                    count++;
                    valor         = myField["ColumnName"].ToString();
                    fields[count] = valor;
                }
                //llenando la tabla
                int tmpi = 0;
                while (myreader.Read())
                {
                    tmpi = tabla.Length;
                    Array.Resize(ref tabla, tmpi + 1);
                    tabla[tmpi].datos = new Dictionary <string, dynamic>();
                    for (int a = 0; a < myreader.FieldCount; a++)
                    {
                        tabla[tmpi].datos.Add(fields[a], myreader.GetValue(a));
                    }
                }
                /*int p = tabla[0].datos["uid"] + 5;*/
                salida = tabla;
                break;

            case BuildType.DataTable:    //DataTable
                datatable.Load(myreader);
                salida = datatable;
                break;

            case BuildType.LstCrsseek:    //list<Crsseek> usado para buscador
                List <CrsSeek> lista = new List <CrsSeek>();
                //llenando la tabla
                while (myreader.Read())
                {
                    lista.Add(new CrsSeek()
                    {
                        codigo  = (string)myreader.GetValue(0),
                        descrip = (string)myreader.GetValue(1),
                    });
                }
                salida = lista;
                break;

            default:
                return(CrsCore.ResponseError(CrsCore.SaveLog("BuildOut Tipo de output invaldo")));
            }
            return(salida);
        }