public static DataTable GetQuery(ClsDataAccess Da, string ViewObject, string Fields, ClsQueryCondition Condition, string Sort = "", Int64 Top = 0, Int32 Page = 0)
{
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 (ViewObject.Trim() != "") ViewObject = " From " + ViewObject + " ";
if (Fields.Trim() == "") Fields = " * ";
if (Sort.Trim() != "") Sort = " Order By " + Sort;
ClsPreparedQuery Pq = new ClsPreparedQuery(Da);
Pq.Add_Parameter("ViewObject", SqlDbType.VarChar, 8000, 0, 0, ViewObject);
Pq.Add_Parameter("Fields", SqlDbType.VarChar, 8000, 0, 0, Fields);
Pq.Add_Parameter("Sort", SqlDbType.VarChar, 8000, 0, 0, Sort);
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 + " " + ViewObject + " " + Query_Condition + @" ) As [Tb] Where [Tb].RowNumber > " + PageCondition + " " + Sort;
Pq.Prepare();
return Pq.ExecuteQuery().Tables[0];
}
public static DataTable GetQueryWithPage(ClsDataAccess Da, string ViewObject, string Fields, string Condition, string Sort, Int64 Top, Int32 Page)
{
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 (ViewObject.Trim() != "") ViewObject = " From " + ViewObject + " ";
if (Fields.Trim() == "") Fields = " * ";
if (Condition.Trim() != "") Condition = " Where " + Condition;
if (Sort.Trim() != "") Sort = " Order By " + Sort;
ClsPreparedQuery Pq = new ClsPreparedQuery(Da);
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 + ' ' + @Sort + ' ) As [Tb] Where [Tb].RowNumber >= ' + @PageCondtion + ''; Exec(@Query)";
Pq.Add_Parameter("ViewObject", SqlDbType.VarChar, 8000, 0, 0, ViewObject);
Pq.Add_Parameter("Top", SqlDbType.VarChar, 8000, 0, 0, Query_Top);
Pq.Add_Parameter("RowNumberSort", SqlDbType.VarChar, 8000, 0, 0, Query_RowNumberSort);
Pq.Add_Parameter("PageCondtion", SqlDbType.BigInt, 0, 0, 0, PageCondition);
Pq.Add_Parameter("Fields", SqlDbType.VarChar, 8000, 0, 0, Fields);
Pq.Add_Parameter("Condition", SqlDbType.VarChar, 8000, 0, 0, Condition);
Pq.Add_Parameter("Sort", SqlDbType.VarChar, 8000, 0, 0, Sort);
Pq.Prepare();
return Pq.ExecuteQuery().Tables[0];
}
public static DataTable GetQuery(ClsDataAccess Da, string ViewObject, string Fields, string Condition, string Sort)
{
if (ViewObject.Trim() != "") ViewObject = " From " + ViewObject + " ";
if (Fields.Trim() == "") Fields = " * ";
if (Condition.Trim() != "") Condition = " Where " + Condition;
if (Sort.Trim() != "") Sort = " Order By " + Sort;
ClsPreparedQuery Pq = new ClsPreparedQuery(Da);
Pq.pQuery = @"Declare @Query As VarChar(Max); Set @Query = 'Select ' + @Fields + ' ' + @ViewObject + ' ' + @Condition + ' ' + @Sort; Exec(@Query)";
Pq.Add_Parameter("ViewObject", SqlDbType.VarChar, 8000, 0, 0, ViewObject);
Pq.Add_Parameter("Fields", SqlDbType.VarChar, 8000, 0, 0, Fields);
Pq.Add_Parameter("Condition", SqlDbType.VarChar, 8000, 0, 0, Condition);
Pq.Add_Parameter("Sort", SqlDbType.VarChar, 8000, 0, 0, Sort);
Pq.Prepare();
return Pq.ExecuteQuery().Tables[0];
}