public override string ConvertSqlSelectToOrderBy(string sql, ColumnEnumList GroupByParams) { if (GroupByParams != null && GroupByParams.Count > 0) { return(sql + " ORDER BY " + GroupByParams.getOrderByParams(this)); } else { return(sql); } }
/// <summary> /// Permite obtener los registros de varias tablas filtrando por diferentes campos /// </summary> /// <param name="nDataTable">Resultado de la consulta</param> /// <param name="nDataBaseCod">Base de datos a la que pertenece la tabla</param> /// <param name="nSchemaName">Esquema al que pertenece la tabla</param> /// <param name="nDataTableName">Nombre de la tabla</param> /// <param name="nKeys">Llave primaria del registro</param> /// <param name="nTableRelationList">Lista de relaciones con otras tablas</param> /// <param name="nMaxRows">Número máximo de registros a retornar</param> /// <param name="nResult">Define si la instrucción se ejecutó correctamente o no</param> /// <param name="nException">Exepción ocurrida al ejecutar la instrucción</param> public void DBQueryFill(ref DataTable nDataTable, string nDataBaseCod, string nSchemaName, string nDataTableName, List <Parameter> nKeys, TableRelationCollection nTableRelationList, int nMaxRows, ColumnEnumList nOrderByParams) { try { bool nResult = true; Exception nException = null; int index = 0; //Ejecutar la consulta principal var primaryRelations = new TableRelationCollection(); while (index < nTableRelationList.Count) { if (nTableRelationList[index].ForeignDataBaseCod != nDataBaseCod) { break; } primaryRelations.Add((TableRelation)nTableRelationList[index].Clone()); index++; } ManagerPool[nDataBaseCod].DataBase.DBQueryFill(ref nDataTable, "form", nSchemaName, nDataTableName, nKeys, primaryRelations, nMaxRows, nOrderByParams, out nResult, out nException); if (!nResult) { throw nException; } //TODO: Agregar consultas a relaciones a base de datos externas while (index < nTableRelationList.Count) { index++; } } catch (Exception ex) { throw new Exception("Error al recuperar los registros de la tabla [" + nDataBaseCod + "] " + nSchemaName + "." + nDataTableName + ", " + ex.Message, ex); } }
/// <summary> /// Construye la sentencia SQL para obtener los registros de varias tablas filtrando por diferentes campos /// </summary> /// <param name="nTableAlias">Alias usado para la tabla principal</param> /// <param name="nSchemaName">Esquema al que pertenece la tabla</param> /// <param name="nDataTableName">Nombre de la tabla</param> /// <param name="nKeys">Campos que componen la llave primaria del registro</param> /// <param name="nTableRelationList">Lista de relaciones con otras tablas</param> /// <param name="nMaxRows">Número máximo de registros a retornar, 0 para devolver todas la filas</param> /// <param name="nOrderByParams">Parametros de ordenación, null para no rodenar</param> /// <returns>Sentencia SQL</returns> public string SqlQuery(string nTableAlias, string nSchemaName, string nDataTableName, List <Parameter> nKeys, TableRelationCollection nTableRelationList, int nMaxRows, ColumnEnumList nOrderByParams) { try { string filters = ""; string sql = ""; for (int i = 0; i < nKeys.Count; i++) { if (nKeys[i].Value != null && !DBNulls.IsNull(nKeys[i].Value)) { if (filters == "") { filters += " Where "; } else { filters += " And "; } filters += "main." + this.DataBase.FormatToDatabaseColumnName(nKeys[i].Name) + " " + this.DataBase.FormatToComparisonOperator(nKeys[i]) + " " + this.DataBase.FormatToDatabaseStringValue(nKeys[i], true); } } //Crear Joins var tablesToJoin = new TableRelationCollection(); if (nTableRelationList != null) { tablesToJoin = nTableRelationList; } var aliasList = new NameValueCollection(); int aliasIndex = 0; //Crear las lista de alias aliasList.Set(nTableAlias + nSchemaName + nDataTableName, "main"); foreach (var columnToJoin in nTableRelationList) { string alias = aliasList.Get(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName); if (alias == null) { alias = " tab" + (++aliasIndex); aliasList.Set(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName, alias); } } var joinList = new NameValueCollection(); var selectColumnList = new StringCollection(); selectColumnList.Add("main.*"); foreach (var columnToJoin in tablesToJoin) { string alias = aliasList.Get(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName); string pairJoinAlias = aliasList.Get(columnToJoin.MainRelationAlias + columnToJoin.MainSchemaName + columnToJoin.MainTableName); if (pairJoinAlias == null) { throw new Exception("No se encontró la tabla para crear el join, " + columnToJoin.MainSchemaName + "." + columnToJoin.MainTableName); } string strJoin = joinList.Get(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName); if (strJoin == null) { strJoin = " " + columnToJoin.GetJoinString() + " " + this.DataBase.FormatToDatabaseTableName(columnToJoin.ForeignSchemaName, columnToJoin.ForeignTableName) + " as " + alias + " on " + alias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.ForeignColumnName) + " = " + pairJoinAlias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.MainColumnName); } else { strJoin += " and " + alias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.ForeignColumnName) + " = " + pairJoinAlias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.MainColumnName); } if (columnToJoin.ForeignFilterTextValue != "") { var columnsToFilter = columnToJoin.ForeignColumnsToResult.Split(','); foreach (string textColumn in columnsToFilter) { strJoin += " and " + alias + "." + this.DataBase.FormatToDatabaseColumnName(textColumn) + " " + this.DataBase.FormatToComparisonOperator(columnToJoin.ForeignFilterTextValue) + " " + this.DataBase.FormatStringToDatabaseStringValue(columnToJoin.ForeignFilterTextValue, true); } } joinList.Set(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName, strJoin); var columnsToSelect = columnToJoin.MainColumnsToResult.Split(','); foreach (string textColumn in columnsToSelect) { if (pairJoinAlias != "main" && textColumn.Trim() != "") { string colName = pairJoinAlias + "." + this.DataBase.FormatToDatabaseColumnName(textColumn); if (!selectColumnList.Contains(", " + colName)) { selectColumnList.Add(", " + colName); } } } columnsToSelect = columnToJoin.ForeignColumnsToResult.Split(','); foreach (string textColumn in columnsToSelect) { if (textColumn.Trim() != "") { string colName = alias + "." + this.DataBase.FormatToDatabaseColumnName(textColumn); if (!selectColumnList.Contains(", " + colName)) { selectColumnList.Add(", " + colName); } } } } string joinString = ""; foreach (var join in joinList) { joinString += joinList[join.ToString()]; } string selectString = ""; foreach (var sel in selectColumnList) { selectString += sel; } sql = "Select " + selectString + " From " + this.DataBase.FormatToDatabaseTableName(nSchemaName, nDataTableName) + " As main " + joinString + filters; sql = this.DataBase.ConvertSqlSelectToMaxRows(sql, nMaxRows); sql = this.DataBase.ConvertSqlSelectToOrderBy(sql, nOrderByParams); this.DataBase.LastQuery = sql; return(sql); } catch (Exception ex) { throw new Exception("No fue posible obtener la sentencia de consulta, " + ex.Message); } }
/// <summary> /// Construye la sentencia SQL para obtener los registro de una tabla filtrando por diferentes campos /// </summary> /// <param name="nSchemaName">Esquema al que pertenece la tabla</param> /// <param name="nDataTableName">Nombre de la tabla</param> /// <param name="nKeys">Campos que componen la llave primaria del registro</param> /// <param name="nMaxRows">Número máximo de registros a retornar, 0 para devolver todas la filas</param> /// <param name="nOrderByParams">Parametros de ordenación, null para no rodenar</param> /// <returns>Sentencia SQL</returns> public string SqlFilter(string nSchemaName, string nDataTableName, List <Parameter> nKeys, int nMaxRows, ColumnEnumList nOrderByParams) { try { string filters = ""; string filterGroupString = ""; string sql = ""; int itemsByGroup = 0; int groupCount = GetGroupCount(nKeys); for (int group = 1; group <= groupCount; group++) { filterGroupString = ""; itemsByGroup = 0; for (int i = 0; i < nKeys.Count; i++) { if (nKeys[i].FilterGroup == group) { if (nKeys[i].Value != null && !DBNulls.IsNull(nKeys[i].Value)) { if (filterGroupString == "") { if (filters != "") { filterGroupString = " And ("; } else { filterGroupString = "Where ("; } itemsByGroup++; } else { filterGroupString += " " + nKeys[i].FilterOption.ToString() + " "; } filterGroupString += this.DataBase.FormatToDatabaseColumnName(nKeys[i].Name) + " " + this.DataBase.FormatToComparisonOperator(nKeys[i]) + " " + this.DataBase.FormatToDatabaseStringValue(nKeys[i], true); } } } if (filterGroupString != "") { filters = filters + filterGroupString + ")"; } } sql = "Select * From " + this.DataBase.FormatToDatabaseTableName(nSchemaName, nDataTableName) + filters; sql = this.DataBase.ConvertSqlSelectToMaxRows(sql, nMaxRows); sql = this.DataBase.ConvertSqlSelectToOrderBy(sql, nOrderByParams); this.DataBase.LastQuery = sql; return(sql); } catch (Exception ex) { throw new Exception("No fue posible obtener la sentencia de consulta, " + ex.Message); } }