public override List <ConfigurationEntity> RetrieveAlliSeriesValues(string companyCode, string[] defaultKeys, string[] variableKeys, string selectedColumns = "") { DataTable outputDataTable = new DataTable(); TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); string columns = (selectedColumns != string.Empty ? selectedColumns : "*"); try { string commandText = string.Format("SELECT {0} FROM CD00A WHERE CONO0A = @CompanyCode", selectedColumns); talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Clear(); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); //Execute err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT); if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null))) { outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[0]; } } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(GetConfigurationData(outputDataTable, defaultKeys, variableKeys)); }
/// <summary> /// XML Comment /// </summary> /// <param name="businessUnit"></param> /// <param name="configs"></param> /// <returns></returns> /// <remarks></remarks> private Dictionary <string, string> RetrieveCurrentValuesData(System.Collections.Generic.List <ConfigurationItem> configs) { DataTable outputDataTable = new DataTable(); TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); try { string commandText = "SELECT * FROM CD00A WHERE CONO0A = @CompanyCode"; //Construct The Call talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Clear(); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT); if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null))) { outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[0]; } } catch (Exception) { } finally { talentDB2AccessDetail = null; } return(GetCurrentValues(outputDataTable, configs)); }
/// <summary> /// Deletes payment type information from MD501 /// </summary> /// <param name="type51"></param> /// <param name="code51"></param> /// <param name="givenTransaction"></param> /// <returns></returns> public int DeleteData(string type51, string code51, iDB2Transaction givenTransaction = null) { int affectedRows = 0; TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); try { string commandText = "UPDATE MD501 SET ACTR51 = 'D',USER51=@USER51,PGMD51=@PGMD51,UPDT51=@UPDT51 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51"; talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", type51, 4)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", code51, 15)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@USER51", settings.AgentName, 10)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@PGMD51", settings.StoredProcedure, 10)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDT51", settings.iSeriesTodaysDate, iDB2DbType.iDB2Decimal)); //Execute affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction); } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(affectedRows); }
/// <summary> /// XML Comment /// </summary> /// <param name="companyCode"></param> /// <param name="type51"></param> /// <returns></returns> /// <remarks></remarks> public DataTable SelectByCompAndType(string companyCode, string type51) { //how to get company code, type code and active flag string sqlStatement = string.Empty; sqlStatement = " SELECT CODE51, DESC51, VALU51 FROM MD501 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type AND ACTR51 = 'A' "; DataTable outputDataTable = new DataTable(); TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); try { talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet; talentDB2AccessDetail.CommandElements.CommandParameter.Clear(); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type", type51, 4)); ErrorObj err = new ErrorObj(); talentDB2AccessDetail.CommandElements.CommandText = sqlStatement; err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT); if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null))) { outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[0]; } } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(outputDataTable); }
/// <summary> /// XML Comment /// </summary> /// <param name="businessUnit"></param> /// <param name="variableKey1Value"></param> /// <param name="configs"></param> /// <param name="givenTransaction"></param> /// <returns></returns> /// <remarks></remarks> private int UpdateData(string businessUnit, string variableKey1Value, System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction) { int affectedRows = 0; TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); try { string[] setValue = new string[configs.Count - 1 + 1]; for (int i = 0; i <= configs.Count - 1; i++) { setValue[i] = configs[i].DefaultName + " = @UPDATED_VALUE" + i.ToString(); } string values = string.Join(" , ", setValue); var commandText = string.Format("UPDATE MD501 SET {0} WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51", values); talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Clear(); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", configs[0].DefaultKey1, 4)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", variableKey1Value, 15)); for (int i = 0; i <= configs.Count - 1; i++) { if (configs[i].DefaultName == "UPDT51" || configs[i].DefaultName == "VALU51") { talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDATED_VALUE" + i.ToString(), System.Convert.ToString(configs[i].UpdatedValue), iDB2DbType.iDB2Decimal)); } else { talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDATED_VALUE" + i.ToString(), System.Convert.ToString(configs[i].UpdatedValue))); } } //Execute affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction); } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(affectedRows); }
/// <summary> /// /// </summary> /// <param name="businessUnit"></param> /// <param name="variableKey1Value"></param> /// <param name="configs"></param> /// <param name="givenTransaction"></param> /// <returns></returns> private int InsertData(System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction) { int affectedRows = 0; TalentDB2Access talentDB2AccessDetail; ErrorObj err = new ErrorObj(); string commandText = string.Empty; StringBuilder columnNames, columnValues; try { talentDB2AccessDetail = new TalentDB2Access(); talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery; commandText = "INSERT INTO MD501({0}) VALUES({1})"; columnNames = new StringBuilder(); columnValues = new StringBuilder(); for (int i = 0; i <= configs.Count - 1; i++) { columnNames.Append(configs[i].DefaultName + ","); columnValues.Append("@" + configs[i].DefaultName + ","); if (configs[i].DefaultName == "UPDT51" || configs[i].DefaultName == "VALU51") { talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@" + configs[i].DefaultName, configs[i].UpdatedValue, iDB2DbType.iDB2Decimal)); } else { talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@" + configs[i].DefaultName, configs[i].UpdatedValue)); } } commandText = string.Format(commandText, columnNames.ToString().Substring(0, columnNames.ToString().Length - 1), columnValues.ToString().Substring(0, columnValues.ToString().Length - 1)); talentDB2AccessDetail.CommandElements.CommandText = commandText; //Execute affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction); } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(affectedRows); }
public bool DoesDescriptionItemExist(string type, string code) { int affectedRows = 0; TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); string commandText = "SELECT 1 FROM MD501 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51 AND ACTR51 = 'A'"; talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Clear(); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", type, 4)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", code, 15, iDB2DbType.iDB2Char)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); //Execute err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT); if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null))) { affectedRows = talentDB2AccessDetail.ResultDataSet.Tables[0].Rows.Count; } talentDB2AccessDetail = null; return(affectedRows > 0 ? false : true); }