public string insertRow(parametrosInsert insert)
{
try
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
//int row_id = Guid.NewGuid().GetHashCode();
string row_id = generateID();
foreach (valueXcolum valorXcolumna in insert.valueXcolumn)
{
var data_id = db.GetValueFunction("add_tupla('" + row_id + "', '" + valorXcolumna.column + "');");
string procedure = "get_connection(" + valorXcolumna.column + ");";
List <Dictionary <string, object> > conexion = db.CallProcedure(procedure);
switch (conexion[0]["database_type"].ToString())
{
case "mariaDB":
insertMaria(conexion[0], valorXcolumna.value, data_id);
break;
case "SQLServer":
insertServer(conexion[0], valorXcolumna.value, data_id);
break;
case "mongoDB":
insertMongo(conexion[0], valorXcolumna.value, data_id);
break;
}
}
return("{\"message\": \"" + "SE INSERTO LA FILA EXITOSAMENTE!" + "\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
public string JSONparametrosAddDatabase(parametrosAddDatabase addDatabase)
{
try {
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba");
db.NonQuery("CALL registrar_conexion('" + addDatabase.database_type + "','"
+ addDatabase.user + "','"
+ addDatabase.pass + "','"
+ addDatabase.server + "','"
+ addDatabase.protocol + "','"
+ addDatabase.port + "','"
+ addDatabase.alias + "');");
/*ArrayList filas = new ArrayList();
*
* string prueba = "";
*
* filas = db.Select2("db_connection");
*
* for (int i = 0; i < filas.Count; i++) prueba += filas[i];
*
*
* return prueba;*/
return("Conexion exitosa");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
public bool hasBeenDeletedMaria(Dictionary <string, object> datos, string condition)
{
string server = datos["server"].ToString();
string database = "multidb_datos";
string uid = datos["user"].ToString();
string pass = datos["pass"].ToString();
string port = datos["port"].ToString();
MariaConnect db = new MariaConnect(server, database, uid, pass, port);
try
{
List <Dictionary <string, object> > select = db.SelectListDictionary(datos["column_type"].ToString(), "data_id = " + datos["ID_data"] + " AND data " + condition + ";");
bool exist = false;
if (select.Count > 0)
{
exist = true;
}
/*if (exist)
* {
* db.NonQuery("DELETE FROM " + datos["column_type"].ToString() + " WHERE data_id = '" + datos["ID_data"].ToString() + "';");
* }*/
return(exist);
}
catch
{
return(false);
}
}
public string getDBConnections()
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba");
ArrayList result = new ArrayList();
result = db.Select("db_connection");
string resultados = result.ToJson();
return(resultados);
}
public string getDatabases()
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba");
//ArrayList result = new ArrayList();
string result;
result = db.Select2("data_bases");
string resultados = result.ToJson();
return(resultados);
}
public List <Dictionary <string, object> > executeQueryMaria(Dictionary <string, object> datos, string condition)
{
string server = datos["server"].ToString();
string database = "multidb_datos";
string uid = datos["user"].ToString();
string pass = datos["pass"].ToString();
string port = datos["port"].ToString();
MariaConnect db = new MariaConnect(server, database, uid, pass, port);
return(db.SelectListDictionary(datos["column_type"].ToString(), "data_id = " + datos["ID_data"] + condition + ";"));
}
public string createDatabase(parametrosCreateDatabase createDatabase)
{
try
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
db.NonQuery("CALL add_data_base('" + createDatabase.name + "');");
return("{\"message\": \"Base de datos creada exitosamente\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
public string dropTable(parametroID dropTable)
{
try
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
db.NonQuery("DELETE FROM TABLAS WHERE ID =" + dropTable.ID);
return("{\"message\": \"Tabla borrada exitosamente\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
public bool updateQueryMaria(Dictionary <string, object> datos, string condition, string value)
{
string server = datos["server"].ToString();
string database = "multidb_datos";
string uid = datos["user"].ToString();
string pass = datos["pass"].ToString();
string port = datos["port"].ToString();
MariaConnect db = new MariaConnect(server, database, uid, pass, port);
//update string set data = 'holi' WHERE data_id = 1 AND data = 'pi';
db.NonQuery("UPDATE " + datos["column_type"].ToString() + " SET data = '" + value +
"' WHERE data_id = " + datos["ID_data"] + " AND data " + condition + ";");
return(true);
}
public string addDatabase(parametrosAddDatabase addDatabase)
{
try {
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
db.NonQuery("CALL registrar_conexion('" + addDatabase.database_type + "','"
+ addDatabase.user + "','"
+ addDatabase.pass + "','"
+ addDatabase.server + "','"
+ addDatabase.protocol + "','"
+ addDatabase.port + "','"
+ addDatabase.alias + "');");
return("{\"message\": \"Conexion creada exitosamente\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
public bool insertMaria(Dictionary <string, object> conexion, object dato, int data_id)
{
string server = conexion["server"].ToString();
string database = "multidb_datos";
string uid = conexion["user"].ToString();
string pass = conexion["pass"].ToString();
string port = conexion["port"].ToString();
MariaConnect db = new MariaConnect(server, database, uid, pass, port);
try
{
db.NonQuery("INSERT INTO " + conexion["columna"] + "(data_id, data) VALUES ('" + data_id + "', '" + dato.ToString() + "');");
return(true);
}
catch
{
return(false);
}
}
public bool deleteDataMaria(Dictionary <string, object> datos)
{
string server = datos["server"].ToString();
string database = "multidb_datos";
string uid = datos["user"].ToString();
string pass = datos["pass"].ToString();
string port = datos["port"].ToString();
MariaConnect db = new MariaConnect(server, database, uid, pass, port);
Debug.WriteLine("DELETE FROM " + datos["column_type"].ToString() + " WHERE data_id = '" + datos["ID_data"].ToString() + "';");
try
{
db.NonQuery("DELETE FROM " + datos["column_type"].ToString() + " WHERE data_id = '" + datos["ID_data"].ToString() + "';");
return(true);
}
catch
{
return(false);
}
}
public string updateQuery(parametrosUpdate update)
{
try
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
string procedure = "get_tuplas(" + update.filter.column + ");";
List <Dictionary <string, object> > tuplas = db.CallProcedure(procedure);
foreach (Dictionary <string, object> resultado in tuplas)
{
object value = new object();
string database_type = resultado["database_type"].ToString();
string condition = update.filter.method + " '" + update.filter.byValue + "'";
switch (database_type)
{
case "mariaDB":
updateQueryMaria(resultado, condition, update.value);
break;
case "SQLServer":
updateQueryServer(resultado, condition, update.value);
break;
case "mongoDB":
updateQueryMongo(resultado, update.filter.byValue, update.value, update.filter.method);
break;
}
}
return("{\"message\": \"" + "SE ACTUALIZO LA FILA EXITOSAMENTE!" + "\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
public string createTable(parametrosCreateTable createTable)
{
try
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
var id_table = db.GetValueFunction("add_table('" + createTable.table_name + "'," +
"" + createTable.database_id + ");");
foreach (parametrosColumn column in createTable.columns)
{
db.NonQuery("CALL add_Column(" + id_table + "," +
"'" + column.DB_alias + "'," +
"'" + column.column_name + "'," +
"'" + column.Type + "'," +
"'" + column.Null + "'" +
");");
}
return("{\"message\": \"" + "SE CREO LA TABLA EXITOSAMENTE!" + "\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
public string getColumns()
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
return(db.ReaderQuery("get_columns(NULL)"));
}
public string executeQuery(parametrosQuery query)
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
string procedure;
Dictionary <string, Dictionary <string, object> > listaResultados = new Dictionary <string, Dictionary <string, object> >();
List <string> excludedRows = new List <string>();
foreach (tableXcolumn txc in query.tableXcolumn)
{
procedure = "get_tuplas(" + txc.column + ")";
List <Dictionary <string, object> > resultados = db.CallProcedure(procedure);
foreach (Dictionary <string, object> resultado in resultados)
{
List <Dictionary <string, object> > dataQuery = new List <Dictionary <string, object> >();
Dictionary <string, object> row = new Dictionary <string, object>();
string ID_tupla = resultado["ID_tupla"].ToString();
string column_name = resultado["table_name"].ToString() + "." + resultado["column_name"].ToString();
object value = new object();
string condition = "";
string mongoCondition = null;
string mongoMethod = null;
try
{ // Si la columna que se está obteniendo información coincide con la del where, se filtra
if (resultado["ID_column"].ToString() == query.filter.column)
{
condition = " AND data" + query.filter.method + " '" + query.filter.byValue + "'";
mongoCondition = query.filter.byValue;
mongoMethod = query.filter.method;
}
}
catch {}
//Si el Row del column a consultar ya se excluyó entonces no se consultan los datos
if (!excludedRows.Contains(resultado["ID_tupla"].ToString()))
{
//Si la entrada ID_tupla no se ha agregado al diccionario, se crea una entrada Dictionary<string, object> y se agrega
if (!listaResultados.TryGetValue(ID_tupla, out row))
{
Dictionary <string, object> valor = new Dictionary <string, object>();
listaResultados.Add(ID_tupla, valor);
}
switch (resultado["database_type"].ToString())
{
case "mariaDB":
dataQuery = executeQueryMaria(resultado, condition);
break;
case "SQLServer":
dataQuery = executeQueryServer(resultado, condition);
break;
case "mongoDB":
dataQuery = executeQueryMongo(resultado, mongoCondition, mongoMethod);
break;
}
if (dataQuery.Count > 0)
{
value = dataQuery[0]["data"];
listaResultados[ID_tupla][column_name] = value; //Se el resultado a la lista que se retornará
}
else
{
excludedRows.Add(resultado["ID_tupla"].ToString()); //Se agrega el ID de la tupla que se va a excluir del resultado
}
}
}
}
//Se borran los rows que no coinciden con el WHERE
foreach (var key in excludedRows)
{
listaResultados.Remove(key);
}
return(JsonConvert.SerializeObject(listaResultados));
}
public string getDBConnections()
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
return(db.Select2("db_connection"));
}
public string query_deleteRow(parametrosDelete delete)
{
try
{
MariaConnect db = new MariaConnect("localhost", "TEST", "prueba", "prueba", "3306");
string procedure = "get_tuplas(" + delete.filter.column + ");";
List <Dictionary <string, object> > tuplas = db.CallProcedure(procedure);
List <string> deletedRows = new List <string>();
foreach (Dictionary <string, object> resultado in tuplas)
{
object value = new object();
string database_type = resultado["database_type"].ToString();
string condition = delete.filter.method + " '" + delete.filter.byValue + "'";
Debug.WriteLine(condition);
bool hasBeenDeleted = false;
switch (database_type)
{
case "mariaDB":
hasBeenDeleted = hasBeenDeletedMaria(resultado, condition);
break;
case "SQLServer":
hasBeenDeleted = hasBeenDeletedServer(resultado, condition);
break;
case "mongoDB":
hasBeenDeleted = hasBeenDeletedMongo(resultado, delete.filter.byValue, delete.filter.method);
break;
}
if (hasBeenDeleted && !deletedRows.Contains(resultado["ID_tupla"].ToString()))
{
deletedRows.Add(resultado["ID_tupla"].ToString());
}
}
foreach (string id_tupla in deletedRows)
{
List <Dictionary <string, object> > dataTuplas = db.CallProcedure("get_tupla('" + id_tupla + "')");
Debug.WriteLine("tuplas = " + dataTuplas.Count.ToString());
bool validDelete = true;
foreach (Dictionary <string, object> column in dataTuplas)
{
Debug.WriteLine(column["database_type"].ToString());
switch (column["database_type"].ToString())
{
case "mariaDB":
validDelete = validDelete && deleteDataMaria(column);
break;
case "SQLServer":
validDelete = validDelete && deleteDataServer(column);
break;
case "mongoDB":
validDelete = validDelete && deleteDataMongo(column);
break;
}
}
if (validDelete)
{
db.NonQuery("DELETE FROM tuplas WHERE ID_tupla = '" + id_tupla + "';");
}
}
return("{\"message\": \"" + "SE BORRON " + deletedRows.Count + " FILAS EXITOSAMENTE!" + "\"}");
}
catch (Exception ex)
{
return("{\"messageError\": \"" + ex.ToString() + "\"}");
}
}
