public void Load(ClsDataAccess Da, string Condition = "")
{
string OtherCondition = "";
if (this.mOtherLoadCondition != "")
{
OtherCondition = " And " + this.mOtherLoadCondition;
}
DataTable Dt;
DataRow Dr;
if (Condition == "")
{
Dt = Methods_Query.GetQuery(Da, this.mViewName, "*", "1 = 0");
Dr = Dt.NewRow();
}
else
{
Dt = Methods_Query.GetQuery(Da, this.mViewName, "*", Condition + OtherCondition);
if (Dt.Rows.Count > 0)
{
Dr = Dt.Rows[0];
}
else
{
Dr = Dt.NewRow();
}
}
this.mDr = Dr;
}
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 void Save(ClsDataAccess Da)
{
foreach (string Header_Key in this.mObj_Base.pHeader_Key)
{
Int64 Inner_ID = (Int64)Methods.IsNull(this.mObj_Base.pDr[Header_Key], 0);
this.mDr[Header_Key] = Inner_ID;
}
Da.SaveDataRow(this.mDr, this.mTableName);
}
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 void Load(string Condition = "")
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
this.Load(Da, Condition);
}
catch (Exception ex)
{ throw ex; }
finally
{ Da.Close(); }
}
public String InsertValue(BusinessObject objBOL)
{
ClsDataAccess objDAL = new ClsDataAccess();
try
{
return(objDAL.RegistrationDetails(objBOL));
}
catch
{
throw;
}
finally
{
objDAL = null;
}
}
public virtual bool Save()
{
bool IsSave = false;
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
Da.BeginTransaction();
Da.SaveDataRow(this.mHeader_Dr, this.mHeader_TableName);
//[-]
if (this.mBase_TableDetail != null)
{
foreach (ClsBaseTableDetail Inner_Obj in this.mBase_TableDetail)
{
Inner_Obj.Save(Da);
}
}
//[-]
if (this.mBase_RowDetail != null)
{
foreach (ClsBaseRowDetail Inner_Obj in this.mBase_RowDetail)
{
Inner_Obj.Save(Da);
}
}
//[-]
Da.CommitTransaction();
IsSave = true;
}
catch (Exception ex)
{
Da.RollbackTransaction();
throw ex;
}
finally
{ Da.Close(); }
return(IsSave);
}
public static DataTable GetQuery(string ViewObject, string Fields, string Condition, string Sort)
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
return(GetQuery(Da, ViewObject, Fields, Condition, Sort));
}
catch (Exception ex)
{
throw ex;
}
finally
{
Da.Close();
}
}
public static DataSet ExecuteQuery(string ProcedureName, ClsDataAccess.Struct_Parameters[] ProcedureParameters)
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
return(Da.ExecuteQuery(ProcedureName, ProcedureParameters));
}
catch (Exception ex)
{
throw ex;
}
finally
{
Da.Close();
}
}
public static DataTable GetQuery(string ViewObject, string Fields, ClsQueryCondition Condition, string Sort = "", Int64 Top = 0, Int32 Page = 0)
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
return(GetQuery(Da, ViewObject, Fields, Condition, Sort, Top, Page));
}
catch (Exception ex)
{
throw ex;
}
finally
{
Da.Close();
}
}
public static DataSet ExecuteQuery(string Query)
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
return(Da.ExecuteQuery(Query));
}
catch (Exception ex)
{
throw ex;
}
finally
{
Da.Close();
}
}
public void Save(ClsDataAccess Da)
{
DataRow[] ArrDr = this.mDt.Select("", "", DataViewRowState.CurrentRows);
foreach (DataRow Dr in ArrDr)
{
if (Dr.RowState == DataRowState.Added || Dr.RowState == DataRowState.Modified)
{
foreach (string Header_Key in this.mObj_Base.pHeader_Key)
{
Int64 Inner_ID = (Int64)Methods.IsNull(this.mObj_Base.pDr[Header_Key], 0);
Dr[Header_Key] = Inner_ID;
}
Da.SaveDataRow(Dr, this.mTableName);
}
}
ArrDr = this.mDt.Select("", "", DataViewRowState.Deleted);
foreach (DataRow Dr in ArrDr)
{
DataRow Nr = Dr.Table.NewRow();
foreach (DataColumn Dc in Dr.Table.Columns)
{
Nr[Dc.ColumnName] = Dr[Dc.ColumnName, DataRowVersion.Original];
}
bool IsPKComplete = true;
foreach (string Key in this.mList_Key)
{
if (Information.IsDBNull(Dr[Key]))
{
IsPKComplete = false;
break;
}
}
if (IsPKComplete)
{
Nr["IsDeleted"] = true;
Da.SaveDataRow(Dr, this.mTableName);
}
}
}
public void Load(ClsDataAccess Da, string Condition = "")
{
string OtherCondition = "";
if (this.mOtherLoadCondition != "")
{
OtherCondition = " And " + this.mOtherLoadCondition;
}
DataTable Dt;
if (Condition == "")
{
Dt = Methods_Query.GetQuery(Da, this.mViewName, "*", "1 = 0");
}
else
{
Dt = Methods_Query.GetQuery(Da, this.mViewName, "*", Condition + OtherCondition);
}
this.mDt = Dt;
}
public static DataTable GetQueryWithPage(ClsDataAccess Da, string ViewObject, string Fields, string Condition, string Sort)
{
return(GetQueryWithPage(Da, ViewObject, Fields, Condition, Sort, 0, 0));
}
public static DataTable GetQueryWithPage(ClsDataAccess Da, string ViewObject)
{
return(GetQueryWithPage(Da, ViewObject, "", "", "", 0, 0));
}
//[-]
public virtual void Load(ClsKeys Keys = null)
{
ClsDataAccess Da = new ClsDataAccess();
StringBuilder Sb_Condition = new StringBuilder();
string Condition = "";
try
{
if (Keys != null)
{
if (Keys.Count() != this.mHeader_Key.Count)
{
throw new Exception("Keys not equal to required keys.");
}
string Inner_Condition_And = "";
bool IsStart = false;
foreach (string Inner_Key in this.mHeader_Key)
{
Sb_Condition.Append(Inner_Condition_And + " " + Inner_Key + " = " + Keys[Inner_Key]);
if (!IsStart)
{
Inner_Condition_And = " And ";
}
IsStart = true;
}
}
Condition = Sb_Condition.ToString();
Da.Connect();
DataTable Dt;
DataRow Dr;
if (Keys == null)
{
Dt = Methods_Query.GetQuery(Da, this.mHeader_ViewName, "*", "1 = 0");
Dr = Dt.NewRow();
}
else
{
Dt = Methods_Query.GetQuery(Da, this.mHeader_ViewName, "*", Condition);
Dr = Dt.Rows[0];
}
this.mHeader_Dr = Dr;
//[-]
if (this.mBase_TableDetail != null)
{
foreach (ClsBaseTableDetail Inner_Obj in this.mBase_TableDetail)
{
Inner_Obj.Load(Da, Condition);
}
}
//[-]
if (this.mBase_RowDetail != null)
{
foreach (ClsBaseRowDetail Inner_Obj in this.mBase_RowDetail)
{
Inner_Obj.Load(Da, Condition);
}
}
//[-]
this.AddRequired();
}
catch { }
}
public static DataTable GetQuery(ClsDataAccess Da, string ViewObject)
{
return(GetQuery(Da, ViewObject, "", "", ""));
}
public static DataTable GetQuery(ClsDataAccess Da, string ViewObject, string Fields, string Condition)
{
return(GetQuery(Da, ViewObject, Fields, Condition, ""));
}