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]); }
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 void AddSelected( DataTable Dt_Target , DataTable Dt_Selected , string Query_Selected_Source , string Query_Selected_Key , string Target_Key , List <Common.Constants.Str_AddSelectedFields> Obj_Fields = null , List <Common.Constants.Str_AddSelectedFieldsDefault> Obj_FieldsDefault = null) { if (!(Dt_Selected.Rows.Count > 0)) { return; } ClsPreparedQuery Pq = new ClsPreparedQuery(); Pq.pQuery = @"Select * From " + Query_Selected_Source + @" Where " + Query_Selected_Key + @" = @ID"; Pq.pParameters.Add("ID", SqlDbType.BigInt); Pq.Prepare(); foreach (DataRow Dr_Selected in Dt_Selected.Rows) { Pq.pParameters["ID"].Value = (Int64)Methods.IsNull(Dr_Selected["ID"], 0); DataTable Inner_Dt_Selected = Pq.ExecuteQuery().Tables[0]; if (Inner_Dt_Selected.Rows.Count > 0) { DataRow Inner_Dr_Selected = Inner_Dt_Selected.Rows[0]; DataRow[] Inner_ArrDr; DataRow Inner_Dr_Target = null; bool Inner_IsFound = false; Inner_ArrDr = Dt_Target.Select(Target_Key + " = " + Convert.ToInt64(Inner_Dr_Selected[Query_Selected_Key])); if (Inner_ArrDr.Length > 0) { Inner_Dr_Target = Inner_ArrDr[0]; if ((bool)Methods.IsNull(Inner_Dr_Target["IsDeleted"], false)) { Inner_Dr_Target["IsDeleted"] = DBNull.Value; Inner_IsFound = true; } } if (!Inner_IsFound) { Int64 Ct = 0; Inner_ArrDr = Dt_Target.Select("", "TmpKey Desc"); if (Inner_ArrDr.Length > 0) { Ct = (Int64)Inner_ArrDr[0]["TmpKey"]; } Ct++; DataRow Nr = Dt_Target.NewRow(); Nr["TmpKey"] = Ct; Nr["Item_Style"] = ""; Nr[Target_Key] = (Int64)Inner_Dr_Selected[Query_Selected_Key]; Dt_Target.Rows.Add(Nr); Inner_Dr_Target = Nr; } if (Obj_Fields != null) { foreach (Constants.Str_AddSelectedFields Inner_Obj in Obj_Fields) { Inner_Dr_Target[Inner_Obj.Field_Target] = Inner_Dr_Selected[Inner_Obj.Field_Selected]; } } if (Obj_FieldsDefault != null) { foreach (Constants.Str_AddSelectedFieldsDefault Inner_Obj in Obj_FieldsDefault) { Inner_Dr_Target[Inner_Obj.Field_Target] = Inner_Obj.Value; } } } } }