/// <summary> /// Imports records from source database to target database/template /// </summary> /// <param name="selectedNids"></param> /// <param name="allSelected">Set true to import all records</param> public override void ImportValues(List <string> selectedNids, bool allSelected) { UnitBuilder UnitBuilderObj = new UnitBuilder(this._TargetDBConnection, this._TargetDBQueries); UnitInfo SourceDBUnit; DataRow Row; int ProgressBarValue = 0; foreach (string Nid in selectedNids) { try { //get unit from source table Row = this.SourceTable.Select(Unit.UnitNId + "=" + Nid)[0]; SourceDBUnit = new UnitInfo(); SourceDBUnit.Name = DICommon.RemoveQuotes(Row[Unit.UnitName].ToString()); SourceDBUnit.GID = Row[Unit.UnitGId].ToString(); SourceDBUnit.Global = Convert.ToBoolean(Row[Unit.UnitGlobal]); SourceDBUnit.Nid = Convert.ToInt32(Row[Unit.UnitNId]); //import into target database UnitBuilderObj.ImportUnit(SourceDBUnit, SourceDBUnit.Nid, this.SourceDBQueries, this.SourceDBConnection); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } this.RaiseIncrementProgessBarEvent(ProgressBarValue); ProgressBarValue++; } }
/// <summary> /// Get NotesDataInfo for a Notes_Data_nid /// </summary> /// <param name="notesDataNid"></param> /// <returns></returns> public NotesDataInfo GetNotesDataInfo(int notesDataNid) { NotesDataInfo RetVal = null; string SqlQuery = string.Empty; DbDataReader DBReader = null; try { SqlQuery = this.DBQueries.Notes.GetNoteData(notesDataNid.ToString()); DBReader = (DbDataReader)this.DBConnection.ExecuteReader(SqlQuery); if (DBReader.HasRows) { RetVal = new NotesDataInfo(); while (DBReader.Read()) { RetVal.Notes_Data_NId = (int)DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Data.NotesDataNId]; RetVal.Notes_NId = Convert.ToInt32(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Data.NotesNId]); RetVal.Data_NId = Convert.ToInt32(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Data.DataNId]); break; } } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (!DBReader.IsClosed) { DBReader.Close(); } } return(RetVal); }
private void UpdateTextField(LanguageTableInfo languageTable) { string SqlQuery = string.Empty; try { if (languageTable.TextColumnSize == -1) { SqlQuery = " UPDATE " + languageTable.TableName + " SET " + languageTable.ColumnName + " = '#' & " + languageTable.ColumnName; } else { SqlQuery = " UPDATE " + languageTable.TableName + " SET " + languageTable.ColumnName + " = '#' &" + " iif( len(" + languageTable.ColumnName + ")>" + languageTable.TextColumnSize + " ,left(" + languageTable.ColumnName + ", " + languageTable.TextColumnSize + " ) ," + languageTable.ColumnName + ") "; } this.DBConnection.ExecuteNonQuery(SqlQuery); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
private void UpdateRecommendedSourcesTable(string ICIUSLabel, int dataNId, int recommendedSourcesNId) { DataRow[] Rows; DataRow NewRow; try { Rows = this.RecommendedSourcesTable.Select(RecommendedSources.NId + "=" + recommendedSourcesNId); if (Rows.Length > 0) { //update label Rows[0][RecommendedSources.ICIUSLabel] = ICIUSLabel; } else { // add new row NewRow = this.RecommendedSourcesTable.NewRow(); NewRow[RecommendedSources.NId] = recommendedSourcesNId; NewRow[RecommendedSources.ICIUSLabel] = ICIUSLabel; NewRow[RecommendedSources.DataNId] = dataNId; this.RecommendedSourcesTable.Rows.Add(NewRow); } // update changes this.RecommendedSourcesTable.AcceptChanges(); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Updates ElementXslt table /// </summary> /// <param name="elementNid"></param> /// <param name="mdElementType"></param> /// <returns></returns> protected bool UpdateElementXsltTable(int elementNid, MetadataElementType mdElementType, string mdXslTtype) { bool RetVal = false; int XsltNid = 0; int ElementXsltNid = 0; try { // 1. get xslt nid from XSLT table XsltNid = this.GetXslNidFrmXSLTTable(mdXslTtype); if (XsltNid > 0) { // 2. check record exists in Element_Xslt table or not ElementXsltNid = this.GetElementXsltNidFrmDB(mdElementType, elementNid); // 3. If not then insert record if (ElementXsltNid <= 0) { this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Xslt.Insert.InsertElementXSLT(this.DBQueries.DataPrefix, elementNid, mdElementType, XsltNid)); } else { // 4. if exists then update record this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Xslt.Update.UpdateElementXSLT(this.DBQueries.DataPrefix, XsltNid, elementNid, mdElementType)); } RetVal = true; } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }
/// <summary> /// Imports records from source database to target database/template /// </summary> /// <param name="selectedNids"></param> /// <param name="allSelected">Set true to import all records</param> public override void ImportValues(List <string> selectedNids, bool allSelected) { DI6SubgroupTypeBuilder SGTypeBuilderObj = new DI6SubgroupTypeBuilder(this._TargetDBConnection, this._TargetDBQueries); DI6SubgroupTypeInfo SourceDBSubgroupType; DataRow Row; int ProgressBarValue = 0; foreach (string Nid in selectedNids) { try { //get subgroup Type from source table Row = this.SourceTable.Select(SubgroupTypes.SubgroupTypeNId + "=" + Nid)[0]; SourceDBSubgroupType = new DI6SubgroupTypeInfo(); SourceDBSubgroupType.Name = DICommon.RemoveQuotes(Row[SubgroupTypes.SubgroupTypeName].ToString()); SourceDBSubgroupType.GID = Row[SubgroupTypes.SubgroupTypeGID].ToString(); SourceDBSubgroupType.Global = Convert.ToBoolean(Row[SubgroupTypes.SubgroupTypeGlobal]); SourceDBSubgroupType.Nid = Convert.ToInt32(Row[SubgroupTypes.SubgroupTypeNId]); SourceDBSubgroupType.Order = Convert.ToInt32(Row[SubgroupTypes.SubgroupTypeOrder]); //import into target database SGTypeBuilderObj.ImportSubgroupType(SourceDBSubgroupType, SourceDBSubgroupType.Nid, this.SourceDBQueries, this.SourceDBConnection); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } this.RaiseIncrementProgessBarEvent(ProgressBarValue); ProgressBarValue++; } }
private void AddColumnIntoTimeperiodTable(bool forOnlineDB, DITables tablesName, DIServerType serverType) { try { // Add StartDate Column if (!this.ISColumnExist(Timeperiods.StartDate, tablesName.TimePeriod)) { this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Timeperiod.Insert.InsertDI7StartDateColumn(this._DBQueries.DataPrefix)); } // Add EndDate Column if (!this.ISColumnExist(Timeperiods.EndDate, tablesName.TimePeriod)) { this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Timeperiod.Insert.InsertDI7EndDateColumn(this._DBQueries.DataPrefix)); } // Add Perodicity Column if (!this.ISColumnExist(Timeperiods.Periodicity, tablesName.TimePeriod)) { this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Timeperiod.Insert.InsertDI7PerodicityColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
public override void Import(string selectedNids) { DataTable Table = null; int ProgressCounter = 0; DI6SubgroupTypeBuilder SGBuilderObj = null; DI6SubgroupTypeInfo SGInfoObj = null; Dictionary <string, DataRow> FileWithNids = new Dictionary <string, DataRow>(); DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; DI6SubgroupTypeBuilder SourceSGTypeBuilder = null; //-- Step 1: Get TempTable with Sorted SourceFileName Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportSubgroupDimensions(selectedNids)); //-- Step 2:Initialise Indicator Builder with Target DBConnection SGBuilderObj = new DI6SubgroupTypeBuilder(this.TargetDBConnection, this.TargetDBQueries); // Initialize progress bar this.RaiseProgressBarInitialize(selectedNids.Split(',').GetUpperBound(0) + 1); //-- Step 3: Import Nids for each SourceFile foreach (DataRow Row in Table.Copy().Rows) { try { string SourceFileWPath = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SourceFileWPath, String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection); // get subgroup type info from source SourceSGTypeBuilder = new DI6SubgroupTypeBuilder(SourceDBConnection, SourceDBQueries); SGInfoObj = SourceSGTypeBuilder.GetSubgroupTypeInfoByNid(Convert.ToInt32(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID])); // import subgroup type only if doesnt exist SGBuilderObj.ImportSubgroupType(SGInfoObj, Convert.ToInt32(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]), SourceDBQueries, SourceDBConnection); ProgressCounter += 1; this.RaiseProgressBarIncrement(ProgressCounter); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) { SourceDBConnection.Dispose(); } if (SourceDBQueries != null) { SourceDBQueries.Dispose(); } } } this._AvailableTable = this.GetAvailableTable(); this._UnmatchedTable = this.GetUnmatchedTable(); // Close ProgressBar this.RaiseProgressBarClose(); }
private void AddColumnIntoDataTable(bool forOnlineDB, DITables tablesName, DIServerType serverType) { try { if (!this.ISColumnExist(Data.TextualDataValue, tablesName.Data)) { // Add Textual_Data_Value Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7TextualDataValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.IsTextualData, tablesName.Data)) { // Add IsTextualData Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IsTextualDataColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.IsMRD, tablesName.Data)) { // Add IsMRD Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IsMRDColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.IsPlannedValue, tablesName.Data)) { // Add IsPlannedValue Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IsPlannedValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.IUNId, tablesName.Data)) { // Add IUNId Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IUNIdColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.ConfidenceIntervalUpper, tablesName.Data)) { // Add ConfidenceIntervalUpper Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7ConfidenceIntervalUpperColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.ConfidenceIntervalLower, tablesName.Data)) { // Add ConfidenceIntervalLower Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7ConfidenceIntervalLowerColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } if (!this.ISColumnExist(Data.MultipleSource, tablesName.Data)) { // Add MultipleSource Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7MultipleSourceColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType)); } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Set Default Subgroup for I-U /// </summary> /// <param name="IndicatorNId"></param> /// <param name="UnitNId"></param> /// <returns>SubgroupVal Nid which is set default</returns> public int UpdateISDefaultSubgroup(int IndicatorNId, int UnitNId) { string SqlQuery = string.Empty; DataTable Table = null; int RetVal = 0; try { SqlQuery = this.DBQueries.IUS.GetIUSByI_U_S(IndicatorNId.ToString(), UnitNId.ToString(), "", true); Table = this.DBConnection.ExecuteDataTable(SqlQuery); if (Table.Select(Indicator_Unit_Subgroup.IsDefaultSubgroup + "=1").Length == 0) { // IUSTable = this.DBConnection.ExecuteDataTable(this.DBQueries.IUS.GetIUS(FilterFieldType.None, "", FieldSelection.Light, true)); DataRow[] Rows = Table.Select(Indicator.IndicatorNId + "=" + Convert.ToString(IndicatorNId) + " AND " + Unit.UnitNId + "=" + Convert.ToString(UnitNId) + " AND " + SubgroupVals.SubgroupVal + " = 'Total'"); //-- Update IUS if Default Subgroup Not exists if (Rows.Length == 0) { DataRow[] SGIURows = Table.Select(Indicator.IndicatorNId + "=" + Convert.ToString(IndicatorNId) + " AND " + Unit.UnitNId + "=" + Convert.ToString(UnitNId) + " AND " + SubgroupVals.SubgroupVal + " like 'Total%'"); //-- Set Default Subgroup for start with Total if (SGIURows.Length > 0) { this.UpdateIUSISDefaultSubgroup(Convert.ToString(SGIURows[0][Indicator_Unit_Subgroup.IUSNId]), true); RetVal = Convert.ToInt32(SGIURows[0][Indicator_Unit_Subgroup.SubgroupValNId]); } else { DataRow[] IUSRows = Table.Select("1=1", Indicator_Unit_Subgroup.IUSNId + " Asc"); //-- Set Default Subgroup for first subgroup if not find for total if (IUSRows.Length > 0) { this.UpdateIUSISDefaultSubgroup(Convert.ToString(IUSRows[0][Indicator_Unit_Subgroup.IUSNId]), true); RetVal = Convert.ToInt32(IUSRows[0][Indicator_Unit_Subgroup.SubgroupValNId]); } } } else { this.UpdateIUSISDefaultSubgroup(Convert.ToString(Rows[0][Indicator_Unit_Subgroup.IUSNId]), true); RetVal = Convert.ToInt32(Rows[0][Indicator_Unit_Subgroup.SubgroupValNId]); } } else { RetVal = Convert.ToInt32(Table.Select(Indicator_Unit_Subgroup.IsDefaultSubgroup + "=1")[0][Indicator_Unit_Subgroup.SubgroupValNId].ToString()); } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }
public override void Import(string selectedNids) { DataTable Table = null; int ProgressCounter = 0; AreaBuilder AreaBuilderObj = null; AreaInfo AreaInfoObj = null; Dictionary <string, DataRow> FileWithNids = new Dictionary <string, DataRow>(); DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; // Initialize progress bar this.RaiseProgressBarInitialize(selectedNids.Split(',').GetUpperBound(0) + 1); //////-- Step 1: Get TempTable with Sorted SourceFileName ////Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportAreas(this._CurrentTemplateFileNameWPath,selectedNids)); //-- Step 2:Initialise Indicator Builder with Target DBConnection AreaBuilderObj = new AreaBuilder(this.TargetDBConnection, this.TargetDBQueries); ////-- Step 3: Import Nids for each SourceFile //foreach (DataRow Row in Table.Copy().Rows) //{ try { SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, this._CurrentTemplateFileNameWPath, String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection); // AreaInfoObj = this.GetIndicatorInfo(Row); //AreaBuilderObj.ImportArea(selectedNids, 1, SourceDBConnection, SourceDBQueries); //AreaBuilderObj.ImportAreaMaps(selectedNids, 1, SourceDBConnection, SourceDBQueries); AreaBuilderObj.ImportArea(selectedNids, DICommon.SplitString(selectedNids, ",").Length, SourceDBConnection, SourceDBQueries, true); ProgressCounter += 1; this.RaiseProgressBarIncrement(ProgressCounter); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) { SourceDBConnection.Dispose(); } if (SourceDBQueries != null) { SourceDBQueries.Dispose(); } } //} this._UnmatchedTable = this.GetUnmatchedTable(); this._AvailableTable = this.GetAvailableTable(); // Close ProgressBar this.RaiseProgressBarClose(); }
public override void Import(string selectedNids) { DataTable Table = null; int ProgressCounter = 0; UnitBuilder TrgUnitBuilderObj = null; UnitBuilder SourceUnitBuilderObj = null; UnitInfo SrcUnitInfoObj = null; DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; //-- Step 1: Get TempTable with Sorted SourceFileName Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportUnits(selectedNids)); //-- Step 2:Initialise Indicator Builder with Target DBConnection TrgUnitBuilderObj = new UnitBuilder(this.TargetDBConnection, this.TargetDBQueries); // Initialize progress bar this.RaiseProgressBarInitialize(selectedNids.Split(',').GetUpperBound(0) + 1); //-- Step 3: Import Nids for each SourceFile foreach (DataRow Row in Table.Copy().Rows) { try { string SourceFileWPath = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SourceFileWPath, String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection); SourceUnitBuilderObj = new UnitBuilder(SourceDBConnection, SourceDBQueries); SrcUnitInfoObj = SourceUnitBuilderObj.GetUnitInfo(FilterFieldType.NId, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID])); // Import Unit from Source TrgUnitBuilderObj.ImportUnit(SrcUnitInfoObj, Convert.ToInt32(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]), SourceDBQueries, SourceDBConnection); ProgressCounter += 1; this.RaiseProgressBarIncrement(ProgressCounter); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) { SourceDBConnection.Dispose(); } if (SourceDBQueries != null) { SourceDBQueries.Dispose(); } } } this._AvailableTable = this.GetAvailableTable(); this._UnmatchedTable = this.GetUnmatchedTable(); // Close ProgressBar this.RaiseProgressBarClose(); }
private void CreateSDMXUserTable(bool forOnlineDB) { try { this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.SDMXUser.Insert.CreateTable(this.DBQueries.TablesName.SDMXUser, forOnlineDB, this.DBConnection.ConnectionStringParameters.ServerType)); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
private void AddColumnIntoAreaTable(bool forOnlineDB, DITables tablesName, DIServerType serverType) { try { // Add Data_Exist Column this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Area.Insert.InsertDataExistColumn(tablesName, forOnlineDB, serverType)); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
private void CreateDocumentTable(bool forOnlineDB, DITables tablesName, DIServerType serverType) { try { // create document table this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.DIDocument.Insert.CreateTable(tablesName.Document, forOnlineDB, serverType)); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Imports records from source database to target database/template /// </summary> /// <param name="selectedNids"></param> /// <param name="allSelected">Set true to import all records</param> private void ImportSectors(List <string> selectedNids, bool allSelected) { int ProgressBarValue = 0; IndicatorClassificationInfo SrcClassification; DataRow Row; List <string> ImportedNids = new List <string>(); foreach (string Nid in selectedNids) { try { //get ic from source table Row = this.SourceTable.Select(this.TagValueColumnName + "=" + Nid)[0]; if (!ImportedNids.Contains(Row[IndicatorClassifications.ICNId].ToString())) { SrcClassification = new IndicatorClassificationInfo(); SrcClassification.Name = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICName].ToString()); SrcClassification.GID = Row[IndicatorClassifications.ICGId].ToString(); SrcClassification.IsGlobal = Convert.ToBoolean(Row[IndicatorClassifications.ICGlobal]); SrcClassification.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]); if (!Information.IsDBNull(Row[IndicatorClassifications.ICInfo])) { SrcClassification.ClassificationInfo = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICInfo].ToString()); } SrcClassification.Parent = new IndicatorClassificationInfo(); SrcClassification.Parent.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICParent_NId]); SrcClassification.Type = this._IndicatorClassificationType; //import into target database Utility.CreateClassificationChainFromExtDB( SrcClassification.Nid, SrcClassification.Parent.Nid, SrcClassification.GID, SrcClassification.Name, SrcClassification.Type, SrcClassification.ClassificationInfo, SrcClassification.IsGlobal, this.SourceDBQueries, this.SourceDBConnection, this._TargetDBQueries, this._TargetDBConnection); ImportedNids.Add(Row[IndicatorClassifications.ICNId].ToString()); } this.RaiseIncrementProgessBarEvent(ProgressBarValue); ProgressBarValue++; } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } } }
/// <summary> /// Insert DBMetadata information /// </summary> /// <param name="description"></param> /// <param name="publisherName"></param> /// <param name="publisherDate"></param> /// <param name="publisherCountry"></param> /// <param name="publisherRegion"></param> /// <param name="publisherOffice"></param> /// <param name="areaCount"></param> /// <param name="indicatorCount"></param> /// <param name="IUSCount"></param> /// <param name="timeperiodCount"></param> /// <param name="sourceCount"></param> /// <param name="dataCount"></param> public void InsertRecord(string description, string publisherName, string publisherDate, string publisherCountry, string publisherRegion, string publisherOffice, string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount) { string SqlQuery = string.Empty; DITables TablesName; string DataPrefix = this.DBConnection.DIDataSetDefault(); try { if (string.IsNullOrEmpty(areaCount)) { areaCount = "0"; } if (string.IsNullOrEmpty(indicatorCount)) { indicatorCount = "0"; } if (string.IsNullOrEmpty(IUSCount)) { IUSCount = "0"; } if (string.IsNullOrEmpty(timeperiodCount)) { timeperiodCount = "0"; } if (string.IsNullOrEmpty(sourceCount)) { sourceCount = "0"; } if (string.IsNullOrEmpty(dataCount)) { dataCount = "0"; } foreach (DataRow Row in this.DBConnection.DILanguages(DataPrefix).Rows) { TablesName = new DITables(DataPrefix, "_" + Row[Language.LanguageCode].ToString()); SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.InsertRecord(TablesName.DBMetadata, DICommon.RemoveQuotes(description), DICommon.RemoveQuotes(publisherName), publisherDate, DICommon.RemoveQuotes(publisherCountry), DICommon.RemoveQuotes(publisherRegion), DICommon.RemoveQuotes(publisherOffice), areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount); this.DBConnection.ExecuteNonQuery(SqlQuery); } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
private void UpdateSubgroupNSubgroupType() { int SexNidInDI6 = 1; int AgeNidInDI6 = 2; int LocationNidInDI6 = 3; int OthersNidInDI6 = 4; string DataPrefix = string.Empty; string LangaugeCode = string.Empty; DataTable SubgroupTable; DITables TableNames = null; try { // Step1: get sex,age,location and other's nid from DI6 database SexNidInDI6 = this.GetSubgroupTypeNid("SEX"); OthersNidInDI6 = this.GetSubgroupTypeNid("OTHERS"); AgeNidInDI6 = this.GetSubgroupTypeNid("AGE"); LocationNidInDI6 = this.GetSubgroupTypeNid("LOCATION"); // Step2: replace age, sex, others and location Nids in subgroup table with predefined nids DataPrefix = this.DBConnection.DIDataSetDefault(); foreach (DataRow Row in this.DBConnection.DILanguages(DataPrefix).Rows) { LangaugeCode = "_" + Row[Language.LanguageCode].ToString(); this.DBConnection.ExecuteNonQuery(UpdateQueries.Subgroup.Update.UpdateSubgroupTypeInSubgroupTable(DataPrefix, LangaugeCode, SexNidInDI6, SexNId)); this.DBConnection.ExecuteNonQuery(UpdateQueries.Subgroup.Update.UpdateSubgroupTypeInSubgroupTable(DataPrefix, LangaugeCode, AgeNidInDI6, AgeNId)); this.DBConnection.ExecuteNonQuery(UpdateQueries.Subgroup.Update.UpdateSubgroupTypeInSubgroupTable(DataPrefix, LangaugeCode, LocationNidInDI6, LocationNId)); this.DBConnection.ExecuteNonQuery(UpdateQueries.Subgroup.Update.UpdateSubgroupTypeInSubgroupTable(DataPrefix, LangaugeCode, OthersNidInDI6, OthersNId)); // Step3: replace remaining nids with subgroup_other_Nid (subgroup) this.DBConnection.ExecuteNonQuery(UpdateQueries.Subgroup.Update.UpdateOtherTypesInSubgroupTable(DataPrefix, LangaugeCode, AgeNId, SexNId, LocationNId, OthersNId)); // Step3: delete subgroup_type table TableNames = new DITables(DataPrefix, LangaugeCode); this.DBConnection.DropTable(TableNames.SubgroupType); } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// To import metadata from RTF file for all indicator classifications except source /// </summary> /// /// <param name="metadataInfo"></param> /// <param name="metaDataType"></param> /// <param name="elementNid"></param> public void ImportMetadataFromRTFFile(string metadataInfo, MetaDataType metadataType, int elementNId) { IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); try { ICBuilder.UpdateICInfo(elementNId, metadataInfo); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
private void UpdateDefaultSubgroupValue() { try { IUSBuilder IUSbuilderObj = new IUSBuilder(this.DBConnection, this.DBQueries); IUSbuilderObj.UpdateISDefaultSubgroups(); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Update Default Subgroup for IUS /// Set Total as default subgroup else Set subgroup start with 'Total' else Set first subgroup in ius /// </summary> public void UpdateISDefaultSubgroups() { bool ColumnExist = true; DataTable IUSTable = null; DataTable IUTable = null; DataView IUSTableView = null; string SqlQuery = string.Empty; try { SqlQuery = this.DBQueries.Update.IUS.UpdateISDefaultSubgroup(true, "'Total'"); this.DBConnection.ExecuteNonQuery(SqlQuery); IUSTable = this.DBConnection.ExecuteDataTable(this.DBQueries.IUS.GetIUS(FilterFieldType.None, "", FieldSelection.Light)); IUTable = IUSTable.DefaultView.ToTable(true, Indicator.IndicatorNId, Indicator.IndicatorName, Unit.UnitNId, Unit.UnitName); foreach (DataRow IURow in IUTable.Rows) { DataRow[] Rows = IUSTable.Select(Indicator.IndicatorNId + "=" + Convert.ToString(IURow[Indicator.IndicatorNId]) + " AND " + Unit.UnitNId + "=" + Convert.ToString(IURow[Unit.UnitNId])); if (Rows.Length == 1) { this.UpdateIUSISDefaultSubgroup(Convert.ToString(Rows[0][Indicator_Unit_Subgroup.IUSNId]), true); } else if (Rows.Length > 1) { DataRow[] NewRows = IUSTable.Select(Indicator.IndicatorNId + "=" + Convert.ToString(IURow[Indicator.IndicatorNId]) + " AND " + Unit.UnitNId + "=" + Convert.ToString(IURow[Unit.UnitNId]) + " AND " + SubgroupVals.SubgroupVal + " = 'Total'"); //-- Update IUS if Default Subgroup Not exists if (NewRows.Length == 0) { DataRow[] SGIURows = IUSTable.Select(Indicator.IndicatorNId + "=" + Convert.ToString(IURow[Indicator.IndicatorNId]) + " AND " + Unit.UnitNId + "=" + Convert.ToString(IURow[Unit.UnitNId]) + " AND " + SubgroupVals.SubgroupVal + " like 'Total%'"); //-- Set Default Subgroup for start with Total if (SGIURows.Length > 0) { this.UpdateIUSISDefaultSubgroup(Convert.ToString(SGIURows[0][Indicator_Unit_Subgroup.IUSNId]), true); } else { //-- Set Default Subgroup for first subgroup if not find for total this.UpdateIUSISDefaultSubgroup(Convert.ToString(Rows[0][Indicator_Unit_Subgroup.IUSNId]), true); } } } } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Update DefaultSubgroup for IUSNid /// </summary> /// <param name="iusNIds"></param> /// <param name="isDefaultSG"></param> public void UpdateIUSISDefaultSubgroup(string IUSNIds, bool isDefaultSG) { string SqlQuery = string.Empty; try { SqlQuery = this.DBQueries.Update.IUS.UpdateIUSISDefaultSubgroup(IUSNIds, isDefaultSG); this.DBConnection.ExecuteNonQuery(SqlQuery); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Updates data exists values /// </summary> public void UpdateDataExistValues() { try { // 1. set all IUS's data_exist value to false in default language table this.DBConnection.ExecuteNonQuery(this.DBQueries.Update.IUS.UpdateDataExistsToFalse(this.DBConnection.ConnectionStringParameters.ServerType)); // 2. set data_exist to true but where data exists this.DBConnection.ExecuteNonQuery(this.DBQueries.Update.IUS.UpdateDataExistsValues()); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Create new language table like UT_Indicator_ar,UT_Unit_ar,..etc.. /// </summary> /// <param name="tableName">Table Name</param> private void CreateTable(string newTableName, string existingTableName) { string SqlQuery = string.Empty; try { //query for new language table. SqlQuery = " SELECT * INTO " + newTableName + " FROM " + existingTableName; this.DBConnection.ExecuteNonQuery(SqlQuery); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
public override DataTable GetAllRecordsFrmData(int NId) { DataTable RetVal = null; string Query = string.Empty; try { Query = this._DBQueries.RecommendedSources.GetAllRecordsByIUSTimeperiodAreaNSource(UserSelections.IndicatorNIds, NId.ToString(), UserSelections.TimePeriodNIds, UserSelections.SourceNIds, UserSelections.ICNIds, this.SeletectedICType); RetVal = this._DBConnection.ExecuteDataTable(Query); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }
/// <summary> /// Imports records from source database to target database/template /// </summary> /// <param name="selectedNids"></param> /// <param name="allSelected">Set true to import all records</param> public override void ImportValues(List <string> SelectedNids, bool allSelected) { int SelectedCount; try { DI7MetadataCategoryBuilder TargetMetaData = new DI7MetadataCategoryBuilder(this._TargetDBConnection, this._TargetDBQueries); DI7MetadataCategoryBuilder SrcMetaDataBuilder = new DI7MetadataCategoryBuilder(this._TargetDBConnection, this._TargetDBQueries); if (this.InputFileType != DataSourceType.Database && this.InputFileType != DataSourceType.Template) { // Import data from xml or rtf file this.RaiseInitializeProgessBarEvent(1); //if (this.InputFileType == DataSourceType.SDMXWebService) //{ // import from database TargetMetaData.ImportAllMetadataCategories(this.SourceDBConnection, this.SourceDBQueries, this.MDElementType); //} this.RaiseIncrementProgessBarEvent(1); } else { //TargetMetaData.ProcessInfo += new ProcessInfoDelegate(MetaData_ProcessInfo); //TargetMetaData.BeforeProcess += new ProcessInfoDelegate(MetaData_BeforeProcess); //improt data from database/template SelectedCount = SelectedNids.Count; if (allSelected) { TargetMetaData.ImportAllMetadataCategories(this.SourceDBConnection, this.SourceDBQueries, this.MDElementType); } else { SrcMetaDataBuilder = new DI7MetadataCategoryBuilder(this.SourceDBConnection, this.SourceDBQueries); foreach (string CategoryNId in SelectedNids) { DI7MetadataCategoryInfo MDCatInfo = SrcMetaDataBuilder.GetMedataCategoryInfo(Convert.ToInt32(CategoryNId)); TargetMetaData.CheckNInsertCategory(MDCatInfo); } } } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Get DataTable of Notes Data for DataNid or ProfileNid or classificationNid and for approved or all Comments Data /// </summary> /// <returns></returns> public DataTable GetNotesData(string notesNId, string dataNId, string profileNIds, string classificationNIds, CheckedStatus notesApproved) { DataTable RetVal = null; string SqlQuery = string.Empty; try { SqlQuery = this.DBQueries.Notes.GetNotes_Data(notesNId, dataNId, profileNIds, classificationNIds, notesApproved); RetVal = this.DBConnection.ExecuteDataTable(SqlQuery); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }
/// <summary> /// Update Notes Profiles and return Count Of Records Updated /// </summary> /// <param name="notesProfileInfo"></param> /// <returns >Count Of Records Updated</returns> public int UpdateNotesProfiles(NotesProfilesInfo notesProfileInfo) { int RetVal = 0; string SqlQuery = string.Empty; try { SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Notes.Update.UpdateNotesProfiles(this.DBQueries.TablesName.NotesProfile, notesProfileInfo.Profile_NId, notesProfileInfo.Profile_Name, notesProfileInfo.Profile_EMail, notesProfileInfo.Profile_Country, notesProfileInfo.ProfileOrganization, notesProfileInfo.Profile_Org_Type); RetVal = this.DBConnection.ExecuteNonQuery(SqlQuery); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }
private void UpdateDBSchema(bool forOnlineDB) { string DataPrefix = string.Empty; string LanguageCode = string.Empty; DITables TablesName = this.DBQueries.TablesName; DIServerType ServerType; try { ServerType = this.DBConnection.ConnectionStringParameters.ServerType; // step1: add columns in all dataset foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows) { DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_"; // add columns in all available langauges foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows) { LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString(); // create tables name TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode); // insert new columns into indicator_classification table this.AddColumnsIntoIndicatorClassificationsTable(forOnlineDB, TablesName, ServerType); // insert new columns into indicator table this.AddColumnsIntoIndicatorTable(forOnlineDB, TablesName, ServerType); // insert new column "Data_Exist" into area table this.AddColumnIntoAreaTable(forOnlineDB, TablesName, ServerType); } // add columns into indicator_unit_subgroup table this.AddColumnsIntoIUSTable(forOnlineDB, TablesName, ServerType); // add document table this.CreateDocumentTable(forOnlineDB, TablesName, ServerType); } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Delete Notes Data for NotesNId /// </summary> /// <param name="notesNId">Comma Seperated Notes_NId</param> public int DeleteNotesData(string notesNId) { int RetVal = 0; string SqlQuery = string.Empty; try { SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Notes.Delete.DeleteFrmNotesData(this.DBQueries.TablesName.NotesData, notesNId); RetVal = this.DBConnection.ExecuteNonQuery(SqlQuery); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }