public DataTable GetQuery(Interface_Connection Connection, Do_Constants.Str_QuerySource SourceObject, string Fields, QueryCondition Condition, string Sort = "", long Top = 0, int Page = 0) { Fields = Do_Methods.Convert_String(Fields); Sort = Do_Methods.Convert_String(Sort); string Query_RowNumberSort = Sort; if (Query_RowNumberSort.Trim() == "") { Query_RowNumberSort = "(Select 0)"; } string Query_Top = ""; if (Top > 0) { Query_Top = "Top " + Top.ToString(); } Int64 PageCondition = 0; if (Page > 0) { PageCondition = Top * (Page - 1); } if (SourceObject.ObjectName.Trim() != "") { SourceObject.ObjectName = @" From " + SourceObject.ObjectName + " "; } if (SourceObject.Fields.Trim() == "") { SourceObject.Fields = @" * "; } if (SourceObject.Condition.Trim() != "") { SourceObject.Condition = @" Where " + SourceObject.Condition; } if (Fields.Trim() == "") { Fields = " * "; } if (Sort.Trim() != "") { Sort = " Order By " + Sort; } PreparedQuery Pq = this.CreatePreparedQuery(); string Query_Condition = ""; if (Condition != null) { Query_Condition = " Where 1 = 1 "; Query_Condition += " And " + Condition.GetQueryCondition(); Pq.Add_Parameter(Condition.GetParameters()); } //Pq.pQuery = @"Select " + Query_Top + @" [Tb].* From ( Select Row_Number() Over (Order By " + Query_RowNumberSort + @") As [RowNumber], " + Fields + " " + SourceObject + " " + Query_Condition + @" ) As [Tb] Where [Tb].RowNumber > " + PageCondition + " " + Sort; //Pq.pQuery = @"Declare @Query As VarChar(Max); Set @Query = 'Select ' + @Top + ' [Tb].* From ( Select Row_Number() Over (Order By ' + @RowNumberSort + ') As [RowNumber], ' + @Fields + ' ' + @ViewObject + ' ' + @Condition + ' ) As [Tb] Where [Tb].RowNumber >= ' + @PageCondition + ' ' + @Sort; Exec(@Query)"; Pq.pQuery = @"Declare @ViewObject As VarChar(Max); Set @ViewObject = ' Select * ' + @Param_ViewObject_TableName + ' ' + @Param_ViewObject_Condition + ' '; Declare @Query As VarChar(Max); Set @Query = 'Select ' + @Param_Top + ' [Tb].* From ( Select Row_Number() Over (Order By ' + @Param_RowNumberSort + ') As [RowNumber] , ' + @Param_Fields + ' From ('+ @ViewObject + ') As [Source] '" + @Query_Condition + @"' ) As [Tb] Where [Tb].RowNumber >= ' + @Param_PageCondition + ' ' + @Param_Sort; Exec(@Query);"; Pq.Add_Parameter(new QueryParameter() { Name = "Param_ViewObject_TableName", Value = SourceObject.ObjectName, Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Add_Parameter(new QueryParameter() { Name = "Param_ViewObject_Condition", Value = SourceObject.Condition, Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Add_Parameter(new QueryParameter() { Name = "Param_Top", Value = Query_Top, Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Add_Parameter(new QueryParameter() { Name = "Param_RowNumberSort", Value = Query_RowNumberSort, Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Add_Parameter(new QueryParameter() { Name = "Param_PageCondition", Value = PageCondition.ToString(), Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Add_Parameter(new QueryParameter() { Name = "Param_Fields", Value = Fields, Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Add_Parameter(new QueryParameter() { Name = "Param_Sort", Value = Sort, Type = Do_Constants.eParameterType.VarChar, Size = 8000 }); Pq.Prepare(); return Pq.ExecuteQuery().Tables[0]; }
/// <summary> /// Fetches a result set from a data source object /// </summary> /// <param name="Connection"> /// An open connection object /// </param> /// <param name="SourceObject"> /// Data source object to fetch from /// </param> /// <param name="Fields"> /// List of fields tp fetch (SQL valid syntax) /// </param> /// <param name="Condition"> /// ClsQueryCondition Object to use the filter the result set /// </param> /// <param name="Sort"> /// Sort expression to use to sort the result set (SQL Order By valid syntax) /// </param> /// <param name="Top"> /// Limits the number of returned rows in the result set, /// used in pagination /// </param> /// <param name="Page"> /// Selects a section based on the Page and Top values in the result set, /// used in pagination /// </param> /// <returns></returns> public DataTable GetQuery(Interface_Connection Connection, String SourceObject, String Fields, QueryCondition Condition, String Sort = "", Int64 Top = 0, Int32 Page = 0) { SourceObject = Do_Methods.Convert_String(SourceObject); Fields = Do_Methods.Convert_String(Fields); Sort = Do_Methods.Convert_String(Sort); string Query_RowNumberSort = Sort; if (Query_RowNumberSort.Trim() == "") Query_RowNumberSort = "(Select 0)"; string Query_Top = ""; if (Top > 0) Query_Top = "Top " + Top.ToString(); Int64 PageCondition = 0; if (Page > 0) { PageCondition = Top * (Page - 1); } if (SourceObject.Trim() != "") SourceObject = " From " + SourceObject + " "; if (Fields.Trim() == "") Fields = " * "; if (Sort.Trim() != "") Sort = " Order By " + Sort; PreparedQuery Pq = this.CreatePreparedQuery(); string Query_Condition = ""; if (Condition != null) { Query_Condition = " Where 1 = 1 "; Query_Condition += " And " + Condition.GetQueryCondition(); Pq.Add_Parameter(Condition.GetParameters()); } Pq.pQuery = @"Select " + Query_Top + @" [Tb].* From ( Select Row_Number() Over (Order By " + Query_RowNumberSort + @") As [RowNumber], " + Fields + " " + SourceObject + " " + Query_Condition + @" ) As [Tb] Where [Tb].RowNumber > " + PageCondition + " " + Sort; Pq.Prepare(); return Pq.ExecuteQuery().Tables[0]; }