public ClsPreparedQuery(ClsDataAccess pDa)
{
this.mDa = pDa;
this.mCmd = new SqlCommand();
this.mCmd.Connection = this.mDa.pConnection;
this.mCmd.Transaction = this.mDa.pTransaction;
this.mCmd.CommandType = System.Data.CommandType.Text;
}
public ClsPreparedQuery(ClsDataAccess pDa)
{
this.mDa = pDa;
this.mCmd = new SqlCommand();
this.mCmd.Connection = this.mDa.pConnection;
this.mCmd.Transaction = this.mDa.pTransaction;
this.mCmd.CommandType = System.Data.CommandType.Text;
}
public ClsPreparedQuery()
{
this.mDa = new ClsDataAccess();
this.mDa.Connect();
this.mCmd = new SqlCommand();
this.mCmd.Connection = this.mDa.pConnection;
this.mCmd.Transaction = this.mDa.pTransaction;
this.mCmd.CommandType = System.Data.CommandType.Text;
//this.IsDa = true;
}
public ClsPreparedQuery()
{
this.mDa = new ClsDataAccess();
this.mDa.Connect();
this.mCmd = new SqlCommand();
this.mCmd.Connection = this.mDa.pConnection;
this.mCmd.Transaction = this.mDa.pTransaction;
this.mCmd.CommandType = System.Data.CommandType.Text;
//this.IsDa = true;
}
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;
}
//bool IsDa = false;
#endregion
#region _Constructor
public ClsPreparedQuery(ClsDataAccess pDa, string pQuery, SqlParameter[] pArrSp)
{
this.mDa = pDa;
this.mCmd = new SqlCommand();
this.mCmd.Connection = this.mDa.pConnection;
this.mCmd.Transaction = this.mDa.pTransaction;
this.mCmd.CommandType = System.Data.CommandType.Text;
this.mCmd.CommandText = pQuery;
foreach (SqlParameter Sp in pArrSp)
{
this.mCmd.Parameters.Add(Sp);
}
}
public ClsPreparedQuery(ClsDataAccess pDa, string pQuery, SqlParameter[] pArrSp)
{
this.mDa = pDa;
this.mCmd = new SqlCommand();
this.mCmd.Connection = this.mDa.pConnection;
this.mCmd.Transaction = this.mDa.pTransaction;
this.mCmd.CommandType = System.Data.CommandType.Text;
this.mCmd.CommandText = pQuery;
foreach (SqlParameter Sp in pArrSp)
{
this.mCmd.Parameters.Add(Sp);
}
}
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 Int32 ExecuteNonQuery(string ProcedureName, ClsDataAccess.Struct_Parameters[] ProcedureParameters)
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
return Da.ExecuteNonQuery(ProcedureName, ProcedureParameters);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Da.Close();
}
}
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 static DataTable GetQueryWithPage(ClsDataAccess Da, string ViewObject)
{
return GetQueryWithPage(Da, ViewObject, "", "", "", 0, 0);
}
public static DataTable GetQueryWithPage(string ViewObject, string Fields, string Condition, string Sort, Int64 Top, Int32 Page)
{
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
return GetQueryWithPage(Da, ViewObject, Fields, Condition, Sort,Top,Page);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Da.Close();
}
}
public static DataTable GetQueryWithPage(ClsDataAccess Da, string ViewObject, string Fields, string Condition, string Sort, Int64 Top)
{
return GetQueryWithPage(Da, ViewObject, Fields, Condition, Sort, Top, 0);
}
public static DataTable GetQueryWithPage(ClsDataAccess Da, string ViewObject, string Fields, string Condition)
{
return GetQueryWithPage(Da, ViewObject, Fields, Condition, "", 0, 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 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)
{
return GetQuery(Da, ViewObject, "", "", "");
}
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 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 bool SaveDataRow(DataRow ObjDataRow, string TableName, string SchemaName, bool IsDelete)
{
if (SchemaName == "")
{ SchemaName = "dbo"; }
eProcess cProcess = eProcess.Process_Insert;
DataTable Dt_TableDef = new DataTable(TableName);
DataTable Dt_Def;
List<Struct_Parameters> List_Param;
//[Get Table Definition]
List_Param = new List<Struct_Parameters>();
List_Param.Add(new Struct_Parameters(@"@TableName", TableName));
List_Param.Add(new Struct_Parameters(@"@SchemaName", SchemaName));
Dt_Def = this.ExecuteQuery("usp_GetTableDef", List_Param).Tables[0];
foreach (DataRow Inner_Dr in Dt_Def.Rows)
{
System.Type Inner_Type = null;
switch (Methods.IsNull(Inner_Dr["DataType"], "").ToString().ToLower())
{
case "tinyint":
Inner_Type = typeof(System.Byte);
break;
case "smallint":
Inner_Type = typeof(System.Int16);
break;
case "int":
Inner_Type = typeof(System.Int32);
break;
case "bigint":
Inner_Type = typeof(System.Int64);
break;
case "bit":
Inner_Type = typeof(System.Boolean);
break;
case "decimal":
case "numeric":
Inner_Type = typeof(System.Double);
break;
case "datetime":
case "smalldatetime":
Inner_Type = typeof(System.DateTime);
break;
case "char":
case "varchar":
case "text":
case "nchar":
case "nvarchar":
case "ntext":
Inner_Type = typeof(System.String);
break;
}
Dt_TableDef.Columns.Add((string)Methods.IsNull(Inner_Dr["ColumnName"], ""), Inner_Type);
}
//[Check ObjDataRow Fields for PK Data]
bool IsFound = false;
Int32 PKsCt = 0;
Int32 PKsFoundCt = 0;
DataRow[] ArrDr_Dt_Def;
ArrDr_Dt_Def = Dt_Def.Select("IsPK = 1");
PKsCt = ArrDr_Dt_Def.Length;
foreach (DataRow Inner_Dr in ArrDr_Dt_Def)
{
foreach (DataColumn Inner_Dc in ObjDataRow.Table.Columns)
{
if ((string)Inner_Dr["ColumnName"] == Inner_Dc.ColumnName)
{
if (Convert.ToInt64(Methods.IsNull(ObjDataRow[Inner_Dc.ColumnName], 0)) != 0)
{
PKsFoundCt++;
if (PKsFoundCt >= PKsCt) break;
}
}
}
}
//Check Process
if (PKsFoundCt != PKsCt)
{
cProcess = eProcess.Process_Insert;
DataRow[] ArrDr_Dt_Def_Pks = Dt_Def.Select(@"IsPK = 1 And IsIdentity = 0");
foreach (DataRow Inner_Dr in ArrDr_Dt_Def_Pks)
{
//Check PK if there is already a value
//If there is, continue the loop
string Inner_ColumnName = (string)Methods.IsNull(Inner_Dr["ColumnName"], "");
if (Convert.ToInt64(Methods.IsNull(ObjDataRow[Inner_ColumnName], 0)) != 0)
{ continue; }
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
Da.BeginTransaction();
Int64 NewID;
List_Param = new List<Struct_Parameters>();
List_Param.Add(new Struct_Parameters(@"@TableName", TableName + "." + Inner_ColumnName));
NewID = (Int64)Methods.IsNull(Da.ExecuteQuery("usp_GetNextID", List_Param).Tables[0].Rows[0][0], 0);
ObjDataRow[Inner_ColumnName] = NewID;
Da.CommitTransaction();
}
catch (Exception ex)
{
Da.RollbackTransaction();
throw ex;
}
finally
{
Da.Close();
Da = null;
}
}
}
else
{
//Check if Row to be updated has rows to be updated
//If none the return the function true
DataRow[] Inner_ArrDr = Dt_Def.Select(@"IsPk = 0 And IsIdentity = 0");
if (Inner_ArrDr.Length == 0) return true;
cProcess = eProcess.Process_Update;
}
//Delete Removed, use Soft Delete
/*
if (IsDelete)
{
if (cProcess == eProcess.Process_Update) cProcess = eProcess.Process_Delete;
else return true;
}
*/
//Prepare SQL Statement
ClsPreparedQuery Pq = new ClsPreparedQuery(this);
string Query_InsertFields = "";
string Query_InsertFieldsValues = "";
string Query_UpdateFields = "";
string Query_Comma = "";
foreach (DataColumn Dc_ObjDataRow in ObjDataRow.Table.Columns)
{
IsFound = false;
foreach (DataColumn Dc_TableDef in Dt_TableDef.Columns)
{
if (Dc_ObjDataRow.ColumnName.ToLower() == Dc_TableDef.ColumnName.ToLower())
{
switch (cProcess)
{
case eProcess.Process_Insert:
IsFound = true;
break;
case eProcess.Process_Update:
DataRow[] Inner_ArrDr = Dt_Def.Select(@"ColumnName = '" + Dc_ObjDataRow.ColumnName + "' And IsPk = 1");
if (Inner_ArrDr.Length == 0) IsFound = true;
break;
}
if (IsFound) break;
}
}
if (IsFound)
{
DataRow[] Inner_ArrDr_Def = Dt_Def.Select(@"ColumnName = '" + Dc_ObjDataRow.ColumnName + "'" + " And IsIdentity = 0");
if (Inner_ArrDr_Def.Length == 0) continue;
switch (cProcess)
{
case eProcess.Process_Insert:
Query_InsertFields += " " + Query_Comma + @" [" + Dc_ObjDataRow.ColumnName + @"] ";
Query_InsertFieldsValues += " " + Query_Comma + @" @" + Dc_ObjDataRow.ColumnName.Replace(@" ", @"_") + " ";
break;
case eProcess.Process_Update:
Query_UpdateFields += " " + Query_Comma + @" [" + Dc_ObjDataRow.ColumnName + @"] = @" + Dc_ObjDataRow.ColumnName.Replace(@" ", @"_") + " ";
break;
}
Query_Comma = ",";
SqlParameter Inner_Sp = new SqlParameter(@"@" + Dc_ObjDataRow.ColumnName.Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_ArrDr_Def[0]["DataType"]), Convert.ToInt32(Inner_ArrDr_Def[0]["Length"]));
Inner_Sp.Direction = ParameterDirection.Input;
Inner_Sp.Precision = (byte)Inner_ArrDr_Def[0]["Precision"];
Inner_Sp.Scale = (byte)Inner_ArrDr_Def[0]["Scale"];
Pq.pParameters.Add(Inner_Sp);
}
}
DataRow[] Inner_ArrDr_Pk;
switch (cProcess)
{
case eProcess.Process_Insert:
StringBuilder Sb_Query_Output = new StringBuilder();
StringBuilder Sb_Query_Output_Table = new StringBuilder();
string Query_Output = "";
char Query_Output_Comma = ' ';
string Query_Output_Table = "";
string Query_Output_Table_Select = "";
Inner_ArrDr_Pk = Dt_Def.Select("IsPK = 1 And IsIdentity = 1");
foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
{
Sb_Query_Output.Append(@" " + Query_Output_Comma + @" Inserted.[" + (string)Methods.IsNull(Inner_Dr["ColumnName"], "") + @"] Into @Tb");
Sb_Query_Output_Table.Append(@" " + Query_Output_Comma + @" [" + (string)Methods.IsNull(Inner_Dr["ColumnName"], "") + @"] " + (string)Methods.IsNull(Inner_Dr["DataType"], ""));
Query_Output_Comma = ',';
}
Query_Output = Sb_Query_Output.ToString();
if (Query_Output.Trim() != "") Query_Output = " Output " + Query_Output;
Query_Output_Table = Sb_Query_Output_Table.ToString();
if (Query_Output_Table.Trim() != "")
{
Query_Output_Table = @" Declare @Tb As Table (" + Query_Output_Table + @"); ";
Query_Output_Table_Select = " Select * From @Tb ";
}
if (Query_InsertFields != "")
{
Query_InsertFields = "(" + Query_InsertFields + ")";
Query_InsertFieldsValues = " Values (" + Query_InsertFieldsValues + ") ";
}
else
{
//This path will be reached if the table to be inserted has only one field that is an identity field
Query_InsertFieldsValues = " Default Values ";
}
Pq.pQuery = Query_Output_Table + " Insert Into [" + SchemaName + "].[" + TableName + "] " + Query_InsertFields + " " + Query_Output + " " + Query_InsertFieldsValues + "; " + Query_Output_Table_Select;
break;
case eProcess.Process_Update:
string Query_UpdateCriteria = "";
Query_Comma = "";
Inner_ArrDr_Pk = Dt_Def.Select("IsPk = 1");
foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
{
DataRow[] Inner_ArrDr_TableDef = Dt_Def.Select(@"ColumnName = '" + (string)Inner_Dr["ColumnName"] + @"'");
Query_UpdateCriteria += " " + Query_Comma + " [" + Inner_Dr["ColumnName"] + "] = @" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_") + " ";
Query_Comma = "And";
SqlParameter Inner_Sp = new SqlParameter("@" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_Dr["DataType"]), Convert.ToInt32(Inner_Dr["Length"]));
Inner_Sp.Direction = ParameterDirection.Input;
Inner_Sp.Precision = (byte)Inner_Dr["Precision"];
Inner_Sp.Scale = (byte)Inner_Dr["Scale"];
Pq.pParameters.Add(Inner_Sp);
}
Pq.pQuery = "Update [" + SchemaName + "].[" + TableName + "] Set " + Query_UpdateFields + " Where " + Query_UpdateCriteria;
break;
case eProcess.Process_Delete:
string Query_DeleteCriteria = "";
Query_Comma = "";
Inner_ArrDr_Pk = Dt_Def.Select("IsPk = 1");
foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
{
DataRow[] Inner_ArrDr_TableDef = Dt_Def.Select(@"ColumnName = '" + (string)Inner_Dr["ColumnName"] + @"'");
Query_DeleteCriteria += " " + Query_Comma + " [" + Inner_Dr["ColumnName"] + "] = @" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_") + " ";
Query_Comma = "And";
SqlParameter Inner_Sp = new SqlParameter("@" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_Dr["DataType"]), (Int32)Inner_Dr["Length"]);
Inner_Sp.Direction = ParameterDirection.Input;
Inner_Sp.Precision = (byte)Inner_Dr["Precision"];
Inner_Sp.Scale = (byte)Inner_Dr["Scale"];
Pq.pParameters.Add(Inner_Sp);
}
Pq.pQuery = "Delete [" + SchemaName + "].[" + TableName + "] Where " + Query_DeleteCriteria;
break;
}
Pq.Prepare();
foreach (DataColumn Dc_ObjDataRow in ObjDataRow.Table.Columns)
{
foreach (SqlParameter Inner_Sp in Pq.pParameters)
{
if ("@" + Dc_ObjDataRow.ColumnName.Replace(" ", "_") == Inner_Sp.ParameterName)
{
if (Information.IsDBNull(Dc_ObjDataRow)) Inner_Sp.Value = DBNull.Value;
else Inner_Sp.Value = this.SqlConvertDataType(ObjDataRow[Dc_ObjDataRow], Inner_Sp.SqlDbType.ToString());
continue;
}
}
}
DataSet Ds_Output;
DataTable Dt_Output;
Ds_Output = Pq.ExecuteQuery();
if (Ds_Output.Tables.Count > 0)
{
Dt_Output = Ds_Output.Tables[0];
foreach (DataColumn Inner_Dc in Dt_Output.Columns)
{
ObjDataRow[Inner_Dc.ColumnName] = Dt_Output.Rows[0][Inner_Dc.ColumnName];
}
}
return true;
}
public bool SaveDataRow(DataRow ObjDataRow, string TableName, string SchemaName, bool IsDelete)
{
if (SchemaName == "")
{
SchemaName = "dbo";
}
eProcess cProcess = eProcess.Process_Insert;
DataTable Dt_TableDef = new DataTable(TableName);
DataTable Dt_Def;
List <Struct_Parameters> List_Param;
//[Get Table Definition]
List_Param = new List <Struct_Parameters>();
List_Param.Add(new Struct_Parameters(@"@TableName", TableName));
List_Param.Add(new Struct_Parameters(@"@SchemaName", SchemaName));
Dt_Def = this.ExecuteQuery("usp_GetTableDef", List_Param).Tables[0];
foreach (DataRow Inner_Dr in Dt_Def.Rows)
{
System.Type Inner_Type = null;
switch (Methods.IsNull(Inner_Dr["DataType"], "").ToString().ToLower())
{
case "tinyint":
Inner_Type = typeof(System.Byte);
break;
case "smallint":
Inner_Type = typeof(System.Int16);
break;
case "int":
Inner_Type = typeof(System.Int32);
break;
case "bigint":
Inner_Type = typeof(System.Int64);
break;
case "bit":
Inner_Type = typeof(System.Boolean);
break;
case "decimal":
case "numeric":
Inner_Type = typeof(System.Double);
break;
case "datetime":
case "smalldatetime":
Inner_Type = typeof(System.DateTime);
break;
case "char":
case "varchar":
case "text":
case "nchar":
case "nvarchar":
case "ntext":
Inner_Type = typeof(System.String);
break;
}
Dt_TableDef.Columns.Add((string)Methods.IsNull(Inner_Dr["ColumnName"], ""), Inner_Type);
}
//[Check ObjDataRow Fields for PK Data]
bool IsFound = false;
Int32 PKsCt = 0;
Int32 PKsFoundCt = 0;
DataRow[] ArrDr_Dt_Def;
ArrDr_Dt_Def = Dt_Def.Select("IsPK = 1");
PKsCt = ArrDr_Dt_Def.Length;
foreach (DataRow Inner_Dr in ArrDr_Dt_Def)
{
foreach (DataColumn Inner_Dc in ObjDataRow.Table.Columns)
{
if ((string)Inner_Dr["ColumnName"] == Inner_Dc.ColumnName)
{
if (Convert.ToInt64(Methods.IsNull(ObjDataRow[Inner_Dc.ColumnName], 0)) != 0)
{
PKsFoundCt++;
if (PKsFoundCt >= PKsCt)
{
break;
}
}
}
}
}
//Check Process
if (PKsFoundCt != PKsCt)
{
cProcess = eProcess.Process_Insert;
DataRow[] ArrDr_Dt_Def_Pks = Dt_Def.Select(@"IsPK = 1 And IsIdentity = 0");
foreach (DataRow Inner_Dr in ArrDr_Dt_Def_Pks)
{
//Check PK if there is already a value
//If there is, continue the loop
string Inner_ColumnName = (string)Methods.IsNull(Inner_Dr["ColumnName"], "");
if (Convert.ToInt64(Methods.IsNull(ObjDataRow[Inner_ColumnName], 0)) != 0)
{
continue;
}
ClsDataAccess Da = new ClsDataAccess();
try
{
Da.Connect();
Da.BeginTransaction();
Int64 NewID;
List_Param = new List <Struct_Parameters>();
List_Param.Add(new Struct_Parameters(@"@TableName", TableName + "." + Inner_ColumnName));
NewID = (Int64)Methods.IsNull(Da.ExecuteQuery("usp_GetNextID", List_Param).Tables[0].Rows[0][0], 0);
ObjDataRow[Inner_ColumnName] = NewID;
Da.CommitTransaction();
}
catch (Exception ex)
{
Da.RollbackTransaction();
throw ex;
}
finally
{
Da.Close();
Da = null;
}
}
}
else
{
//Check if Row to be updated has rows to be updated
//If none the return the function true
DataRow[] Inner_ArrDr = Dt_Def.Select(@"IsPk = 0 And IsIdentity = 0");
if (Inner_ArrDr.Length == 0)
{
return(true);
}
cProcess = eProcess.Process_Update;
}
//Delete Removed, use Soft Delete
/*
* if (IsDelete)
* {
* if (cProcess == eProcess.Process_Update) cProcess = eProcess.Process_Delete;
* else return true;
* }
*/
//Prepare SQL Statement
ClsPreparedQuery Pq = new ClsPreparedQuery(this);
string Query_InsertFields = "";
string Query_InsertFieldsValues = "";
string Query_UpdateFields = "";
string Query_Comma = "";
foreach (DataColumn Dc_ObjDataRow in ObjDataRow.Table.Columns)
{
IsFound = false;
foreach (DataColumn Dc_TableDef in Dt_TableDef.Columns)
{
if (Dc_ObjDataRow.ColumnName.ToLower() == Dc_TableDef.ColumnName.ToLower())
{
switch (cProcess)
{
case eProcess.Process_Insert:
IsFound = true;
break;
case eProcess.Process_Update:
DataRow[] Inner_ArrDr = Dt_Def.Select(@"ColumnName = '" + Dc_ObjDataRow.ColumnName + "' And IsPk = 1");
if (Inner_ArrDr.Length == 0)
{
IsFound = true;
}
break;
}
if (IsFound)
{
break;
}
}
}
if (IsFound)
{
DataRow[] Inner_ArrDr_Def = Dt_Def.Select(@"ColumnName = '" + Dc_ObjDataRow.ColumnName + "'" + " And IsIdentity = 0");
if (Inner_ArrDr_Def.Length == 0)
{
continue;
}
switch (cProcess)
{
case eProcess.Process_Insert:
Query_InsertFields += " " + Query_Comma + @" [" + Dc_ObjDataRow.ColumnName + @"] ";
Query_InsertFieldsValues += " " + Query_Comma + @" @" + Dc_ObjDataRow.ColumnName.Replace(@" ", @"_") + " ";
break;
case eProcess.Process_Update:
Query_UpdateFields += " " + Query_Comma + @" [" + Dc_ObjDataRow.ColumnName + @"] = @" + Dc_ObjDataRow.ColumnName.Replace(@" ", @"_") + " ";
break;
}
Query_Comma = ",";
SqlParameter Inner_Sp = new SqlParameter(@"@" + Dc_ObjDataRow.ColumnName.Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_ArrDr_Def[0]["DataType"]), Convert.ToInt32(Inner_ArrDr_Def[0]["Length"]));
Inner_Sp.Direction = ParameterDirection.Input;
Inner_Sp.Precision = (byte)Inner_ArrDr_Def[0]["Precision"];
Inner_Sp.Scale = (byte)Inner_ArrDr_Def[0]["Scale"];
Pq.pParameters.Add(Inner_Sp);
}
}
DataRow[] Inner_ArrDr_Pk;
switch (cProcess)
{
case eProcess.Process_Insert:
StringBuilder Sb_Query_Output = new StringBuilder();
StringBuilder Sb_Query_Output_Table = new StringBuilder();
string Query_Output = "";
char Query_Output_Comma = ' ';
string Query_Output_Table = "";
string Query_Output_Table_Select = "";
Inner_ArrDr_Pk = Dt_Def.Select("IsPK = 1 And IsIdentity = 1");
foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
{
Sb_Query_Output.Append(@" " + Query_Output_Comma + @" Inserted.[" + (string)Methods.IsNull(Inner_Dr["ColumnName"], "") + @"] Into @Tb");
Sb_Query_Output_Table.Append(@" " + Query_Output_Comma + @" [" + (string)Methods.IsNull(Inner_Dr["ColumnName"], "") + @"] " + (string)Methods.IsNull(Inner_Dr["DataType"], ""));
Query_Output_Comma = ',';
}
Query_Output = Sb_Query_Output.ToString();
if (Query_Output.Trim() != "")
{
Query_Output = " Output " + Query_Output;
}
Query_Output_Table = Sb_Query_Output_Table.ToString();
if (Query_Output_Table.Trim() != "")
{
Query_Output_Table = @" Declare @Tb As Table (" + Query_Output_Table + @"); ";
Query_Output_Table_Select = " Select * From @Tb ";
}
if (Query_InsertFields != "")
{
Query_InsertFields = "(" + Query_InsertFields + ")";
Query_InsertFieldsValues = " Values (" + Query_InsertFieldsValues + ") ";
}
else
{
//This path will be reached if the table to be inserted has only one field that is an identity field
Query_InsertFieldsValues = " Default Values ";
}
Pq.pQuery = Query_Output_Table + " Insert Into [" + SchemaName + "].[" + TableName + "] " + Query_InsertFields + " " + Query_Output + " " + Query_InsertFieldsValues + "; " + Query_Output_Table_Select;
break;
case eProcess.Process_Update:
string Query_UpdateCriteria = "";
Query_Comma = "";
Inner_ArrDr_Pk = Dt_Def.Select("IsPk = 1");
foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
{
DataRow[] Inner_ArrDr_TableDef = Dt_Def.Select(@"ColumnName = '" + (string)Inner_Dr["ColumnName"] + @"'");
Query_UpdateCriteria += " " + Query_Comma + " [" + Inner_Dr["ColumnName"] + "] = @" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_") + " ";
Query_Comma = "And";
SqlParameter Inner_Sp = new SqlParameter("@" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_Dr["DataType"]), Convert.ToInt32(Inner_Dr["Length"]));
Inner_Sp.Direction = ParameterDirection.Input;
Inner_Sp.Precision = (byte)Inner_Dr["Precision"];
Inner_Sp.Scale = (byte)Inner_Dr["Scale"];
Pq.pParameters.Add(Inner_Sp);
}
Pq.pQuery = "Update [" + SchemaName + "].[" + TableName + "] Set " + Query_UpdateFields + " Where " + Query_UpdateCriteria;
break;
case eProcess.Process_Delete:
string Query_DeleteCriteria = "";
Query_Comma = "";
Inner_ArrDr_Pk = Dt_Def.Select("IsPk = 1");
foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
{
DataRow[] Inner_ArrDr_TableDef = Dt_Def.Select(@"ColumnName = '" + (string)Inner_Dr["ColumnName"] + @"'");
Query_DeleteCriteria += " " + Query_Comma + " [" + Inner_Dr["ColumnName"] + "] = @" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_") + " ";
Query_Comma = "And";
SqlParameter Inner_Sp = new SqlParameter("@" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_Dr["DataType"]), (Int32)Inner_Dr["Length"]);
Inner_Sp.Direction = ParameterDirection.Input;
Inner_Sp.Precision = (byte)Inner_Dr["Precision"];
Inner_Sp.Scale = (byte)Inner_Dr["Scale"];
Pq.pParameters.Add(Inner_Sp);
}
Pq.pQuery = "Delete [" + SchemaName + "].[" + TableName + "] Where " + Query_DeleteCriteria;
break;
}
Pq.Prepare();
foreach (DataColumn Dc_ObjDataRow in ObjDataRow.Table.Columns)
{
foreach (SqlParameter Inner_Sp in Pq.pParameters)
{
if ("@" + Dc_ObjDataRow.ColumnName.Replace(" ", "_") == Inner_Sp.ParameterName)
{
if (Information.IsDBNull(Dc_ObjDataRow))
{
Inner_Sp.Value = DBNull.Value;
}
else
{
Inner_Sp.Value = this.SqlConvertDataType(ObjDataRow[Dc_ObjDataRow], Inner_Sp.SqlDbType.ToString());
}
continue;
}
}
}
DataSet Ds_Output;
DataTable Dt_Output;
Ds_Output = Pq.ExecuteQuery();
if (Ds_Output.Tables.Count > 0)
{
Dt_Output = Ds_Output.Tables[0];
foreach (DataColumn Inner_Dc in Dt_Output.Columns)
{
ObjDataRow[Inner_Dc.ColumnName] = Dt_Output.Rows[0][Inner_Dc.ColumnName];
}
}
return(true);
}
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 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 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 { }
}
