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