/*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 TableInsert(List<Tabla> lista, bool retUid)
* {
* if (lista.Count == 0) { return CrsCore.ResponseError(600, "tabla esta vacia"); }
* //ARMANDO QUERY
* string lcquery = "BEGIN;";
* string lccampos = "";
* string lcvalues = "";
* //for (int r = 0; r < lista.Length; r++)
* foreach (Tabla tabla in lista)
* {
* if (string.IsNullOrEmpty(tabla.alias)) { continue; }
* lcquery += "insert into " + tabla.alias;
* lccampos = " (";
* lcvalues = " values(";
* string valor = "";
* //agregando camps
* foreach (KeyValuePair<string, dynamic> colum in tabla.datos)
* {
* 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
* }
* //agregando consulta para devolver el uid
* if (retUid)
* {
* lcquery += "SELECT uid FROM " + lista[0].alias + " ORDER BY uid DESC LIMIT 1;";
* }
* 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();
* MySqlDataReader myreader = mycomand.ExecuteReader();
* while (myreader.Read()) { colaf = (int)myreader.GetValue(0); }
* }
* catch (MySqlException ex)
* {
* mysql.connection.Close();
* return CrsCore.ResponseError(201, "Query:" + lcquery + " " + ex.Message);
* }
* mysql.connection.Close();
* return CrsCore.Response(colaf, "termino!!" + lcquery);
* }*/
/*public static XResponse TableInsUpd(List<Tabla> lista)
* {
* if (lista.Count == 0) { return CrsCore.ResponseError(600, "tabla esta vacia"); }
* //ARMANDO QUERY
* string lcquery = "BEGIN;";
* string lccampos = "";
* string lcvalues = "";
* string lccampos2 = "";
* //for (int r = 0; r < lista.Length; r++)
* foreach (Tabla tabla in lista)
* {
* if (string.IsNullOrEmpty(tabla.alias)) { continue; }
* lcquery += "insert into " + tabla.alias;
* lccampos = " (";
* lccampos2 = " ";
* lcvalues = " values(";
* string valor = "";
* //agregando camps
* foreach (KeyValuePair<string, dynamic> colum in tabla.datos)
* {
* object rigival = TraslateFields(colum.Value);
* if (rigival == null) { continue; }
* valor = Convert.ToString(rigival);
* lccampos += colum.Key + ",";
* lcvalues += "'" + valor + "',";
* lccampos2 += colum.Key + " = '" + valor + "',"; ;
* }
* lccampos = lccampos.Substring(0, lccampos.Length - 1) + ")";//quitando ultima coma
* lccampos2 = lccampos2.Substring(0, lccampos2.Length - 1);//quitando ultima coma
* lcvalues = lcvalues.Substring(0, lcvalues.Length - 1) + ")";//quitando ultima coma
* lcquery += lccampos + lcvalues + " ON DUPLICATE KEY UPDATE" + lccampos2 + ";";//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 + " " + ex.Message);
* }
* mysql.connection.Close();
* return CrsCore.Response(colaf, "termino!!" + lcquery);
* }*/
public static bool connect(ref XtrDataConnect datacon)
{
datacon.connected = false;
try
{
string lc = "Server=" + constantes.server +
";Database=" + constantes.database +
";Uid= " + constantes.uuser +
";Pwd= " + constantes.password +
";Port=3306;SslMode=none";
MySqlConnection con = new MySqlConnection(lc);
con.Open();
datacon.connection = con;
datacon.connected = true;
return(datacon.connected);
}
catch (MySqlException ex)
{
datacon.msg = ex.Message;
return(false);
}
}
/* 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()
});
}
