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