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 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 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() + "\"}");
}
}
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));
}