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