/// <summary> /// Main Constructor /// </summary> /// <param name="databaseFactory"></param> public AbstractQueryBuilder(Gale.Db.IDataActions databaseFactory, Type TModel) { //Setting the Database Factory Type this._databaseFactory = databaseFactory; //Entity Type for the CRUD Type entityType = TModel; //Field List this._reflectedModel = ReflectModel(entityType); }
/// text public static Models.Config GetConfiguration(String NameConfiguration, Gale.Db.IDataActions dataActions) { using (Gale.Db.DataService svc = new Gale.Db.DataService("PA_MAE_SEL_Configuracion")) { svc.Parameters.Add("@CONF_Nombre", NameConfiguration); Gale.Db.EntityRepository rep = dataActions.ExecuteQuery(svc); Models.Config result = rep.GetModel <Models.Config>().FirstOrDefault(); return(result); } }
/// text public static Models.Config GetConfiguration(Nullable <Guid> token, Gale.Db.IDataActions dataActions) { using (Gale.Db.DataService svc = new Gale.Db.DataService("PA_MAE_SEL_Configuracion")) { if (token != null) { svc.Parameters.Add("@DOCU_Token", token); } svc.Parameters.Add("@CONF_Nombre", DBNull.Value); Gale.Db.EntityRepository rep = dataActions.ExecuteQuery(svc); Models.Config result = rep.GetModel <Models.Config>().FirstOrDefault(); return(result); } }
public HttpQueryBuilder(Gale.Db.IDataActions databaseFactory, HttpRequestMessage request, GQLConfiguration configuration) : base(databaseFactory, request, configuration) { System.Collections.Specialized.NameValueCollection query = System.Web.HttpUtility.ParseQueryString(request.RequestUri.Query); //------------------------------------------------------------------------------------------ //--[ ORDER BY PARSER (FOR SQL SERVER WE NEED TO ENGAGE FIRST) this.RegisterParser <OData.Builders.SQLServer.Parsers.OrderBy>(this.Kql); //------------------------------------------------------------------------------------------ //------------------------------------------------------------------------------------------ //--[ SELECT PARSER this.RegisterParser <OData.Builders.SQLServer.Parsers.Select>(this.Kql); //------------------------------------------------------------------------------------------ //------------------------------------------------------------------------------------------ //--[ FROM PARSER (Send null query , the model , has converted automatically by constraint's) this.RegisterParser <OData.Builders.SQLServer.Parsers.From>(this.Kql); //------------------------------------------------------------------------------------------ //------------------------------------------------------------------------------------------ //--[ WHERE PARSER (Filter's) this.RegisterParser <OData.Builders.SQLServer.Parsers.Where>(this.Kql); //------------------------------------------------------------------------------------------ //------------------------------------------------------------------------------------------ //--[ APPEND THE ANSI SQL STATEMENTS (SELECT , FROM , WHERE, ORDER BY) String orderBy = ""; this.OnExecutedParser += new ExecutedParserEventHandler((args) => { #region OVERRIDE FRAGMENT //ORDER BY APPEND (CAPTURE AND SET NULL) if (args.Parser.GetType() == typeof(OData.Builders.SQLServer.Parsers.OrderBy)) { orderBy = args.ResultQueryFragment; args.ResultQueryFragment = ""; } //SELECT APPEND if (args.Parser.GetType() == typeof(OData.Builders.SQLServer.Parsers.Select)) { //INCLUDE PAGINATOR SQL SENTENCE String format = String.Format("SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS ROWNUM, {1}", orderBy, args.ResultQueryFragment ); args.ResultQueryFragment = format;; } //WHERE APPEND if (args.Parser.GetType() == typeof(OData.Builders.SQLServer.Parsers.Where)) { if (args.ResultQueryFragment.Length > 0) { args.ResultQueryFragment = String.Concat(" WHERE ", args.ResultQueryFragment); } } #endregion }); //------------------------------------------------------------------------------------------ //------------------------------------------------------------------------------------------ //--[ REGISTER OPERATOR'S (CAN BE EXECUTED IN ANY PARSER FOR THE "CALLOPERATOR" METHOD) IEnumerable <Type> operators = (from t in this.GetType().Assembly.GetTypes() where t.IsSubclassOf(typeof(OData.Builders.SQLServer.Operators.Operator)) select t); foreach (Type Qoperator in operators) { System.Reflection.MethodInfo register = this.GetType().GetMethod("RegisterOperator").MakeGenericMethod(Qoperator); register.Invoke(this, new object[] { }); } //------------------------------------------------------------------------------------------ }
private Result _Execute(String query, Gale.Db.IDataActions databaseFactory) { System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch(); timer.Start(); //------------------------------------------------------------------------------------- //---[ DATABASE CALL using (Gale.Db.DataService svc = new Gale.Db.DataService(query)) { System.Data.DataTable db_data = null; System.Data.DataTable db_pagination = null; Gale.Db.EntityRepository rep = null; try { //Create the repository rep = this.DatabaseFactory.ExecuteSql(svc); db_data = rep.GetRawTable(0); db_pagination = rep.GetRawTable(1); } catch (System.Exception ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; throw new Gale.Exception.GaleException("API_DB_ERROR", message); } //--------[ GET ALL DATA IN AN ARRAY var data = new List <List <Object> >(); SortedList <int, Field> ordinalsFields = new SortedList <int, Field>(); var columns_count = db_data.Columns.Count; #region ORDINAL COLUMN SEARCH //SEARCH FOR ORDINAL'S foreach (Field field in _reflectedModel.SelectedFields) { for (var column_index = 0; column_index < columns_count; column_index++) { if (field.Key.ToLower() == db_data.Columns[column_index].ColumnName.ToLower()) { ordinalsFields.Add(db_data.Columns[column_index].Ordinal, field); break; } } } #endregion if (db_pagination.Rows.Count != 1) { throw new Gale.Exception.GaleException("API003"); } int total = Convert.ToInt32(db_pagination.Rows[0]["total"]); #region TABLE FORMAT //Save all data from the foreign table for the descriptor's SortedList <Type, Gale.Db.IEntityTable> _foreignTableDatas = new SortedList <Type, Gale.Db.IEntityTable>(); //Starting Fetching Data for (var row_index = 0; row_index < db_data.Rows.Count; row_index++) { List <Object> item = new List <Object>(); foreach (int ordinal in ordinalsFields.Keys) { Field field = ordinalsFields[ordinal]; Object db_value = db_data.Rows[row_index][ordinal]; if (db_value is DateTime) { db_value = DateTime.SpecifyKind((DateTime)db_value, DateTimeKind.Local); } //If is FK , try to get the Descriptor, if not have descriptor, send Encripted Value :S if (field.Specification == Field.SpecificationEnum.Fk) { Table table = _reflectedModel.Constraints.First(constraint => constraint.ThisField == field).Table; if (table.Descriptor != null) { Gale.Db.IEntityTable tableData; _foreignTableDatas.TryGetValue(table.Type, out tableData); #region CREATE DATA TABLE FROM THE CURRENT SOURCE IF NOT EXIST YET if (tableData == null) { //Create Constraint Table Data System.Reflection.MethodInfo baseMethod = (from t in rep.GetType().GetMethods() where t.GetGenericArguments().Count() > 0 && t.Name == "GetModel" && t.GetParameters().Count() == 0 select t).FirstOrDefault(); System.Reflection.MethodInfo GetModelMethod = baseMethod.MakeGenericMethod(table.Type); tableData = (Gale.Db.IEntityTable)GetModelMethod.Invoke(rep, null); _foreignTableDatas.Add(table.Type, tableData); } #endregion //GET Constraint Function Expression to Get the FK Descriptor Object _item = tableData.GetType().GetMethod("get_Item").Invoke(tableData, new object[] { row_index }); db_value = table.Descriptor.DynamicInvoke(_item).ToString(); } } item.Add(db_value); //Column Value } data.Add(item); } #endregion timer.Stop(); var response = new Gale.REST.Queryable.Primitive.Result(total, timer.Elapsed, _reflectedModel.SelectedFields, data); return(response); } //------------------------------------------------------------------------------------- }
public AbstractQueryBuilder(Gale.Db.IDataActions databaseFactory) : base(databaseFactory, typeof(TModel)) { }
public HttpQueryBuilder(Gale.Db.IDataActions databaseFactory, HttpRequestMessage request, GQLConfiguration configuration) : base(databaseFactory) { Request = request; System.Collections.Specialized.NameValueCollection query = System.Web.HttpUtility.ParseQueryString(Request.RequestUri.Query); Kql = new GQLConfiguration(); #region OFFSET if (query.AllKeys.Contains("$offset")) { int _offset = 0; int.TryParse(query["$offset"], out _offset); Kql.offset = _offset; } #endregion #region LIMIT if (query.AllKeys.Contains("$limit")) { int _limit = 0; int.TryParse(query["$limit"], out _limit); if (_limit > 0) { Kql.limit = _limit; } } else { //default Kql.limit = 10; } #endregion #region ORDER BY if (query.AllKeys.Contains("$orderBy")) { String[] values = query["$orderBy"].Split(' '); Exception.GaleException.Guard(() => { return(values.Length != 2); }, "API017"); String fieldName = values[0].ToLower(); String order = values[1].Trim().ToLower(); Exception.GaleException.Guard(() => { return(order != "asc" && order != "desc"); }, "API016"); this.Kql.orderBy = new GQLConfiguration.OrderBy() { name = fieldName, order = (order == "asc") ? GQLConfiguration.OrderBy.orderEnum.asc : GQLConfiguration.OrderBy.orderEnum.desc }; } #endregion #region SELECT if (query.AllKeys.Contains("$select")) { string fields = query["$select"].Trim(); if (fields.Length > 0) { List <string> selectedFields = fields.Split(',').ToList(); Exception.GaleException.Guard(() => { return(selectedFields.Count == 0); }, "API011"); foreach (var fieldName in selectedFields) { Exception.GaleException.Guard(() => { return(fieldName.Trim().Length == 0); }, "API011"); } this.Kql.fields = selectedFields; } } #endregion #region FILTERS if (query.AllKeys.Contains("$filter")) { List <string> filters = query["$filter"].Trim().Split(',').ToList(); foreach (string filterString in filters) { string text = filterString; Func <string> reduceFragment = new Func <string>(() => { string _fragment; int space_ordinal = text.IndexOf(" "); //Gale Exception Exception.GaleException.Guard(() => { return(space_ordinal == 0); }, "API011"); _fragment = text.Substring(0, space_ordinal); text = text.Substring(space_ordinal + 1); //Reduce Filter return(_fragment); }); //FILTER FORMAT => {field} {operatorAlias} {value} string field = reduceFragment(); string operatorAlias = reduceFragment(); string value = text; this.Kql.filters.Add(new GQLConfiguration.Filter() { field = field, operatorAlias = operatorAlias, value = text }); } } #endregion #region SETUP Manual GQLConfiguration if (configuration != null) { configuration.filters.ForEach((filter) => { this.Kql.filters.Add(filter); }); configuration.fields.ForEach((fields) => { this.Kql.fields.Add(fields.ToLower()); }); if (configuration.limit > 0) { this.Kql.limit = configuration.limit; } if (configuration.offset > 0) { this.Kql.offset = configuration.offset; } if (configuration.orderBy != null && !String.IsNullOrEmpty(configuration.orderBy.name)) { this.Kql.orderBy = configuration.orderBy; } } #endregion }