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];
}