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