/// <summary> /// Generate Cache Result tables on language basis /// </summary> /// <param name="databasePath"></param> public void GenerateCacheResults(string databasePath) { List<string> DbLangCodes = new List<string>(); DIConnection DbConnection = null; string DataFolerName = string.Empty; int ProgressCount = 20; try { //-- raise event to display progress form this.RaiseDisplayProgressFormEvent(); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); DbConnection = new DIConnection(DIServerType.MsAccess, "", "", databasePath, "", ""); DbLangCodes = DI7OfflineSPHelper.GetAllDbLangCodes(DbConnection); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Merge Textual_Data_Value and Data_value column into Data_value column DIDataValueHelper.MergeTextualandNumericDataValueColumn(databasePath); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Create new temp table and alter table schemas this.CreateNAlterSchemas(DbConnection); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Generate cache on language basis foreach (string LangCode in DbLangCodes) { this.CreateCacheResults(DbConnection, LangCode); } } catch (Exception) { throw; } finally { if (DbConnection != null) { DbConnection.Dispose(); DbConnection = null; } } }
/// <summary> /// Process Target Areas that matched with Source Areas /// </summary> public void ProcessMatchedAreas() { DataTable Table = null; AreaBuilder AreaBuilderObj = null; AreaInfo AreaInfoObj = null; Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>(); DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; //-- Step 1: Get TempTable with Sorted SourceFileName Table = this.DBConnection.ExecuteDataTable(this.TemplateQueries.GetMatchedAreas()); //-- Step 2:Initialise Indicator Builder with Target DBConnection AreaBuilderObj = new AreaBuilder(this.DBConnection, this.DBQueries); //-- Step 3: Import Nids for each SourceFile foreach (DataRow Row in Table.Copy().Rows) { try { SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]), String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection); AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaNId]), 1, SourceDBConnection, SourceDBQueries); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) SourceDBConnection.Dispose(); if (SourceDBQueries != null) SourceDBQueries.Dispose(); } } }
//Added to check gallery existence public int GalleryExistence(string requestParam) { int RetVal; DataTable dtPresentation; DIConnection DIConnection; string GetPresentationsQuery; string[] Params; int UserNId ; ; int AdminNId ; DIConnection = null; try { Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter); UserNId = Convert.ToInt32(Params[0].Trim()); AdminNId = Convert.ToInt32(this.Get_AdminNId()); DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); GetPresentationsQuery = "SELECT * FROM Presentations WHERE user_nid = " + UserNId + " OR user_nid = " + AdminNId + ""; dtPresentation = DIConnection.ExecuteDataTable(GetPresentationsQuery); RetVal = dtPresentation.Rows.Count; return RetVal; } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
public static string GetSearchedKeywords(string KeywordNIds, List<string> SearchWords, string databaseURL) { string RetVal; string GetKeywordsQuery, KeyWord; DataTable DtKeywords; DIConnection DIConnection; RetVal = string.Empty; GetKeywordsQuery = "SELECT * FROM keywords WHERE keyword_nid IN (" + KeywordNIds + ") And keyword_type='UDK'"; DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseURL, string.Empty, string.Empty); try { DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); if (DtKeywords.Rows.Count > 0) { foreach (DataRow DrKeyWord in DtKeywords.Rows) { KeyWord = DrKeyWord["keyword"].ToString(); RetVal += KeyWord + "||"; } } else { GetKeywordsQuery = "SELECT * FROM keywords WHERE keyword_nid IN (" + KeywordNIds + ")"; DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); if (DtKeywords.Rows.Count > 0) { foreach (DataRow DrKeyWord in DtKeywords.Rows) { KeyWord = DrKeyWord["keyword"].ToString(); RetVal += KeyWord + "||"; } } } if (RetVal.Length > 0) { RetVal = RetVal.Substring(0, RetVal.Length - 2); } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
public static void Create_Constraint_Artefact_For_Version_2_0_SDMLMLFile(string RegistrationId, string DbNId, string UserNId, string AgencyId, string FileURL) { string InsertQuery, OutputFolder; DIConnection DIConnection; XmlDocument SimpleDataFileXML; XmlNodeList ObjXmlNodeList; SDMXObjectModel.Message.StructureType ConstraintStructure; SDMXObjectModel.Structure.ContentConstraintType ContentConstraint; DataKeySetType DataKeySet; DataKeyValueType DataKeyValue; int KeyIndex; string SimpleDataFileUrl = string.Empty; string ConstraintFileName = string.Empty; string ConstraintFileLocation = string.Empty; InsertQuery = string.Empty; OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DbNId + "\\sdmx\\Constraints\\" + UserNId); ConstraintFileName = DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Prefix + RegistrationId + ".xml"; ConstraintFileLocation = OutputFolder + "\\" + ConstraintFileName; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty); SimpleDataFileXML = new XmlDocument(); if (!String.IsNullOrEmpty(FileURL)) { SimpleDataFileXML.Load(FileURL); } ObjXmlNodeList = SimpleDataFileXML.GetElementsByTagName("sts:Series"); ConstraintStructure = new SDMXObjectModel.Message.StructureType(); ConstraintStructure.Structures = new StructuresType(); ConstraintStructure.Structures.Constraints = new List<ConstraintType>(); ContentConstraint = new SDMXObjectModel.Structure.ContentConstraintType(); ContentConstraint.id = DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Prefix + RegistrationId; ContentConstraint.Name.Add(new TextType(null, DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Name + RegistrationId)); ContentConstraint.agencyID = AgencyId; ContentConstraint.version = DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Version; ContentConstraint.Description.Add(new TextType(null, DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Description)); ContentConstraint.Annotations = null; ContentConstraint.ReleaseCalendar = null; ContentConstraint.ConstraintAttachment = new ContentConstraintAttachmentType(); if (!String.IsNullOrEmpty(FileURL)) { ContentConstraint.ConstraintAttachment.Items = new object[1]; ContentConstraint.ConstraintAttachment.Items[0] = FileURL; ContentConstraint.ConstraintAttachment.ItemsElementName = new ConstraintAttachmentChoiceType[] { ConstraintAttachmentChoiceType.SimpleDataSource }; } DataKeySet = new DataKeySetType(); DataKeySet.isIncluded = true; ContentConstraint.Items.Add(DataKeySet); KeyIndex = 0; foreach (XmlNode SeriesNode in ObjXmlNodeList) { ((DataKeySetType)(ContentConstraint.Items[0])).Key.Add(new DataKeyType()); foreach (XmlAttribute SeriesAttribute in SeriesNode.Attributes) { DataKeyValue = new DataKeyValueType(); DataKeyValue.id = SeriesAttribute.Name; DataKeyValue.Items.Add(new SimpleKeyValueType()); ((SimpleKeyValueType)(DataKeyValue.Items[0])).Value = SeriesAttribute.Value; ((DataKeySetType)(ContentConstraint.Items[0])).Key[KeyIndex].KeyValue.Add(DataKeyValue); } KeyIndex = KeyIndex + 1; } ConstraintStructure.Structures.Constraints.Add(ContentConstraint); SDMXObjectModel.Serializer.SerializeToFile(typeof(SDMXObjectModel.Message.StructureType), ConstraintStructure, ConstraintFileLocation); InsertQuery = "INSERT INTO Artefacts (DBNId, Id, AgencyId, Version, URN, Type, FileLocation)" + " VALUES(" + DbNId + ",'" + ContentConstraint.id + "','" + ContentConstraint.agencyID + "','" + ContentConstraint.version + "','" + string.Empty + "'," + Convert.ToInt32(ArtefactTypes.Constraint).ToString() + ",'" + ConstraintFileLocation + "');"; DIConnection.ExecuteDataTable(InsertQuery); } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
/// <summary> /// Process Mapped Areas from MappedRows. /// </summary> public void ProcessMappedAreas() { DIConnection SrcDBConnection = null; DIQueries SrcDBQueries = null; AreaBuilder TrgAreaBuilder = null; AreaBuilder SourceAreaBuilder = null; AreaInfo SrcAreaInfo = null; DataTable Table = null; string SourceFileWPath = string.Empty; int TrgAreaNid = 0; if (this.MappedTables.ContainsKey(TemplateMergeControlType.Areas)) { TrgAreaBuilder = new AreaBuilder(this.DBConnection, this.DBQueries); foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Areas].MappedTable.MappedTable.Rows) { //todo: Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportAreas()); TrgAreaNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Area.AreaNId]); if (Table != null && Table.Rows.Count > 0) { try { SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty); SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection); // Get Source Area Info SourceAreaBuilder = new AreaBuilder(SrcDBConnection, SrcDBQueries); SrcAreaInfo= SourceAreaBuilder.GetAreaInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID])); // Import Mapped Area TrgAreaBuilder.ImportAreaFrmMappedArea(SrcAreaInfo, SrcAreaInfo.Nid, TrgAreaNid, SrcDBQueries, SrcDBConnection); } finally { if (SrcDBConnection != null) { SrcDBConnection.Dispose(); SrcDBQueries.Dispose(); } } } } } }
private void ProcessMappedSubgroupType() { DIConnection SrcDBConnection = null; DIQueries SrcDBQueries = null; DI6SubgroupTypeBuilder TrgSubgroupTypeBilder = null; DI6SubgroupTypeBuilder SourceSGTypeBuilder = null; DI6SubgroupTypeInfo SrcSubgroupTypeInfo = null; DataTable Table = null; string SourceFileWPath = string.Empty; int TrgSGDNid = 0; if (this.MappedTables.ContainsKey(TemplateMergeControlType.SubgroupDimensions)) { TrgSubgroupTypeBilder = new DI6SubgroupTypeBuilder(this.DBConnection, this.DBQueries); foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.SubgroupDimensions].MappedTable.MappedTable.Rows) { Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportSubgroupDimensions(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + SubgroupTypes.SubgroupTypeNId]))); TrgSGDNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + SubgroupTypes.SubgroupTypeNId]); if (Table != null && Table.Rows.Count > 0) { try { SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty); SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection); // get subgroup type info SourceSGTypeBuilder = new DI6SubgroupTypeBuilder(SrcDBConnection, SrcDBQueries); SrcSubgroupTypeInfo = SourceSGTypeBuilder.GetSubgroupTypeInfoByNid(Convert.ToInt32(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID])); // Import Mapped Subgroup Type Values TrgSubgroupTypeBilder.ImportSubgroupTypeFrmMappedSubgroupType(SrcSubgroupTypeInfo, SrcSubgroupTypeInfo.Nid, TrgSGDNid, SrcDBQueries, SrcDBConnection); } finally { if (SrcDBConnection != null) { SrcDBConnection.Dispose(); } } } } } }
public static void Delete_Registration_Artefact(string DbNId, string UserNId, string RegistrationId) { string Query, FileNameWPath; DIConnection DIConnection; Query = string.Empty; FileNameWPath = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DbNId + "\\sdmx\\Registrations\\" + UserNId + "\\" + RegistrationId + DevInfo.Lib.DI_LibSDMX.Constants.XmlExtension); DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty); DIConnection.ExecuteDataTable("DELETE FROM Artefacts WHERE Id = '" + RegistrationId + "' AND DBNId = " + DbNId + " AND Type = " + Convert.ToInt32(DevInfo.Lib.DI_LibSDMX.ArtefactTypes.Registration).ToString() + ";"); if (File.Exists(FileNameWPath)) { File.Delete(FileNameWPath); } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
/// <summary> /// Delete Presentaion from Gallery Database /// </summary> /// <param name="presentationPath"></param> /// <param name="dbPath"></param> /// <remarks>To be deleted</remarks> public void DeletePresentationFromGalleryDatabase_old(String presentationPath, string dbPath) { int PresNIdForDelete = 0; string PresFileNameForDelete = string.Empty; string PresGalleryPathForDelete = string.Empty; string sSql = string.Empty; string PDS_PresentationNIdTobeUpdated = string.Empty; //Get Presentaion filename and Gallery path PresGalleryPathForDelete = Path.GetDirectoryName(presentationPath); PresFileNameForDelete = Path.GetFileName(presentationPath); if (this.ValidateDB(dbPath)) { // Find Presention in PresMaster and get it's NId try { sSql = "SELECT " + PresentationMaster.Pres_NId + " FROM " + DBTable.UT_PresMst + " WHERE " + PresentationMaster.Pres_FileName + "= '" + PresFileNameForDelete + "'"; // Get pres nid to be deleted PresNIdForDelete = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(sSql)); //If pres nid found then delete this pres records from tables if (PresNIdForDelete != 0) { //DELETE FROM Pres keyword sSql = "DELETE FROM " + DBTable.UT_PresKeyword + " WHERE " + PresentationMaster.Pres_NId + "=" + PresNIdForDelete; this.DBConnection.ExecuteNonQuery(sSql); //DELETE FROM Pres Mst sSql = "DELETE FROM " + DBTable.UT_PresMst + " WHERE " + PresentationMaster.Pres_NId + "=" + PresNIdForDelete; this.DBConnection.ExecuteNonQuery(sSql); //Delete from presearch sSql = "DELETE FROM UT_PreSearches WHERE PDS_Presentation_NIds=" + PresNIdForDelete; this.DBConnection.ExecuteNonQuery(sSql); string ConnString = this.DBConnection.GetConnection().ConnectionString; DIServerType TempserverType = DBConnection.ConnectionStringParameters.ServerType; this.DBConnection.Dispose(); DIConnection TempDBConn = new DIConnection(ConnString, TempserverType); System.Data.Common.DbDataAdapter Adapter = TempDBConn.CreateDBDataAdapter(); System.Data.Common.DbCommand cmd = TempDBConn.GetCurrentDBProvider().CreateCommand(); cmd.CommandText = "Select * from " + DBTable.UT_PreSearches; cmd.Connection = TempDBConn.GetConnection(); Adapter.SelectCommand = cmd; System.Data.Common.DbCommandBuilder CmdBuilder = TempDBConn.GetCurrentDBProvider().CreateCommandBuilder(); CmdBuilder.DataAdapter = Adapter; DataSet TargetFileDataset = new System.Data.DataSet(); Adapter.Fill(TargetFileDataset, DBTable.UT_PreSearches); // Update foreach (DataRow DRow in TargetFileDataset.Tables[0].Rows) { if (DRow[PreSearches.PDS_Presentation_NIds].ToString().Contains(PresNIdForDelete.ToString())) { if (DRow[PreSearches.PDS_Presentation_NIds].ToString().EndsWith(PresNIdForDelete.ToString())) { DRow[PreSearches.PDS_Presentation_NIds] = DRow[PreSearches.PDS_Presentation_NIds].ToString().Replace(PresNIdForDelete.ToString(), " ").Trim(); } else { DRow[PreSearches.PDS_Presentation_NIds] = DRow[PreSearches.PDS_Presentation_NIds].ToString().Replace(PresNIdForDelete.ToString() + ",", "").Trim(); } } } TargetFileDataset.AcceptChanges(); //update TempDataTable into target database Adapter.Update(TargetFileDataset, DBTable.UT_PreSearches); System.Threading.Thread.Sleep(1000); TempDBConn.Dispose(); this.DBConnection = new DIConnection(ConnString, TempserverType); } } catch (Exception ex) { } } }
private DataTable GetASResultsTableFromIndexingDatabase(int DBNId, string SearchIndicatorICs, string SearchAreas, string SearchLanguage, bool HandleAsDIUAOrDIUFlag) { DataTable RetVal; string GetASResultsQuery; DIConnection DIConnection; System.Data.Common.DbParameter DbParam; List<System.Data.Common.DbParameter> DbParams; RetVal = null; GetASResultsQuery = string.Empty; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); DbParams = new List<System.Data.Common.DbParameter>(); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_DBNId"; DbParam.DbType = DbType.Int32; DbParam.Value = DBNId.ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SearchIndicatorICs"; DbParam.DbType = DbType.String; DbParam.Value = SearchIndicatorICs; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SearchAreas"; DbParam.DbType = DbType.String; if (HandleAsDIUAOrDIUFlag) { DbParam.Value = SearchAreas; } else { DbParam.Value = string.Empty; } DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SearchLanguage"; DbParam.DbType = DbType.String; DbParam.Value = SearchLanguage; DbParams.Add(DbParam); GetASResultsQuery = "SELECT * FROM Indexing_Table " + "WHERE AS_DBNId = @AS_DBNId AND AS_SearchIndicatorICs = @AS_SearchIndicatorICs " + "AND AS_SearchAreas = @AS_SearchAreas AND AS_SearchLanguage = @AS_SearchLanguage;"; RetVal = DIConnection.ExecuteDataTable(GetASResultsQuery, CommandType.Text, DbParams); for (int i = 1; i < RetVal.Columns.Count; i++) { RetVal.Columns[i].ColumnName = RetVal.Columns[i].ColumnName.Substring(3); } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
private static DataTable Get_User(int UserNId) { DataTable RetVal; DIConnection DIConnection; string Query; RetVal = null; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty); Query = "SELECT * FROM Users WHERE NId = " + UserNId.ToString() + ";"; RetVal = DIConnection.ExecuteDataTable(Query); } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
private DataTable GetGalleryTable(string keyword, string Language, int DBNId, string Type) { DataTable DTKeyword, RetVal = null; DIConnection DIConnection; string GetKeywordsQuery, GetPresentationsQuery; DataTable DtKeywords; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); RetVal = new DataTable(); if (!string.IsNullOrEmpty(keyword)) { GetKeywordsQuery = "SELECT * FROM Keywords WHERE keyword LIKE '%" + keyword.Trim() + "%' AND keyword_type = 'UDK'"; DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); if (DtKeywords.Rows.Count > 0) { foreach (DataRow KeywordRow in DtKeywords.Rows) { GetPresentationsQuery = "SELECT * FROM Presentations WHERE dbnid = '" + DBNId.ToString() + "' AND lng_code = '" + Language + "'"; GetPresentationsQuery += " AND keyword_nids LIKE '%," + KeywordRow["keyword_nid"].ToString() + ",%'"; if (Type != "A") { GetPresentationsQuery += " AND type = '" + Type + "';"; } DTKeyword = DIConnection.ExecuteDataTable(GetPresentationsQuery); RetVal.Merge(DTKeyword); } } } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
private string NGallery_GetMoreKeywordXML(string KeywordNIds, string LangCode) { string RetVal; string Areas, Indicators, UDKs; string GetKeywordsQuery; DataTable DtKeywords; DIConnection DIConnection; RetVal = "<results>"; Areas = string.Empty; Indicators = string.Empty; UDKs = string.Empty; GetKeywordsQuery = "SELECT * FROM keywords WHERE keyword_nid IN (" + KeywordNIds + ")"; DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); try { DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); foreach (DataRow DrKeyWord in DtKeywords.Rows) { switch (DrKeyWord["keyword_type"].ToString()) { case "A": Areas += DrKeyWord["keyword"].ToString() + ", "; break; case "I": Indicators += DrKeyWord["keyword"].ToString() + ", "; break; case "UDK": UDKs += DrKeyWord["keyword"].ToString() + ", "; break; default: break; } } if (Areas.Length > 0) { Areas = Areas.Substring(0, Areas.Length - 2); } if (Indicators.Length > 0) { Indicators = Indicators.Substring(0, Indicators.Length - 2); } if (UDKs.Length > 0) { UDKs = UDKs.Substring(0, UDKs.Length - 2); } XmlDocument LangSpecificWords; LangSpecificWords = null; string langAreas = "Areas"; string langIndicators = "Indicators"; string langUDKs = "Keywords"; try { LangSpecificWords = new XmlDocument(); LangSpecificWords.Load(Server.MapPath(@"~\stock\language\" + LangCode.ToString() + @"\Gallery.xml")); foreach (XmlNode Keyword in LangSpecificWords.GetElementsByTagName("lng")) { if (Keyword.Attributes["id"].Value.ToUpper() == "LANGMOREAREAS") { langAreas = Keyword.Attributes["val"].Value; } if (Keyword.Attributes["id"].Value.ToUpper() == "LANGMOREINDICATORS") { langIndicators = Keyword.Attributes["val"].Value; } if (Keyword.Attributes["id"].Value.ToUpper() == "LANGMOREUDKS") { langUDKs = Keyword.Attributes["val"].Value; } } } catch (Exception ex) { Global.CreateExceptionString(ex, null); } //RetVal += "<ar txt=\"Area\" val=\"" + Areas + "\"/>"; //RetVal += "<ind txt=\"Indicator\" val=\"" + Indicators + "\"/>"; //RetVal += "<udk txt=\"Keywords\" val=\"" + UDKs + "\"/>"; RetVal += "<ar txt=\"" + langAreas + "\" val=\"" + Areas + "\"/>"; RetVal += "<ind txt=\"" + langIndicators + "\" val=\"" + Indicators + "\"/>"; RetVal += "<udk txt=\"" + langUDKs + "\" val=\"" + UDKs + "\"/>"; RetVal += "</results>"; } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
internal void ImportAssistants() { DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; string DataPrefix = string.Empty; string LanguageCode = string.Empty; //get all source database name foreach (string SourceFileNameWPath in this.SourceDatabaseFileNamesWPath) { //for each source database, import notes try { SourceDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileNameWPath, string.Empty, Common.Constants.DBPassword)); DataPrefix = SourceDBConnection.DIDataSetDefault(); LanguageCode = SourceDBConnection.DILanguageCodeDefault(DataPrefix); SourceDBQueries = new DIQueries(DataPrefix, LanguageCode); this.ImportAssistant(SourceDBConnection, SourceDBQueries); } catch (Exception) { } finally { if (SourceDBConnection != null) { SourceDBConnection.Dispose(); this._TargetDBConnection.Dispose(); } if (SourceDBQueries != null) { SourceDBQueries = null; } } } }
private void ImportEBook(ref DIConnection Connection, ref DIQueries queries, string languageCode, DITables sourceTableNames, DITables targetTableNames) { string SqlString = string.Empty; string TablePrefix = this._TargetDBConnection.DIDataSetDefault(); DataTable SourceTopicTable = null; string TargetConnectionString = this._TargetDBConnection.GetConnection().ConnectionString; string SourceConnectionString = Connection.GetConnection().ConnectionString; string SourceDBName = Connection.ConnectionStringParameters.DbName; string TargetDBName = this._TargetDBConnection.ConnectionStringParameters.DbName; OleDbCommand InsertCommand; OleDbDataAdapter Adapter; OleDbCommandBuilder CmdBuilder; DataSet EbookDataset; DataRow Row; try { this._TargetDBConnection.ExecuteNonQuery(AssistantQueries.DeleteFrmEBook(targetTableNames.AssistanteBook)); // get record from source database SourceTopicTable = Connection.ExecuteDataTable(" Select * from " + sourceTableNames.AssistanteBook); if (SourceTopicTable.Rows.Count > 0) { //dispose target and source connection this._TargetDBConnection.Dispose(); Connection.Dispose(); InsertCommand = new OleDbCommand(); InsertCommand.Connection = new OleDbConnection(TargetConnectionString); Adapter = new OleDbDataAdapter("Select * from " + sourceTableNames.AssistanteBook, TargetConnectionString); CmdBuilder = new OleDbCommandBuilder(Adapter); EbookDataset = new DataSet(sourceTableNames.AssistanteBook); Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; Adapter.Fill(EbookDataset, targetTableNames.AssistanteBook); //Fill data adapter Row = EbookDataset.Tables[0].NewRow(); try { Row[Assistant_eBook.EBook] = SourceTopicTable.Rows[0][Assistant_eBook.EBook]; //ShpBuffer EbookDataset.Tables[0].Rows.Add(Row); Adapter.Update(EbookDataset, targetTableNames.AssistanteBook); // Save changes to the database } catch (Exception ex) { ExceptionHandler.ExceptionFacade.ThrowException(ex); } if (CmdBuilder != null) { CmdBuilder.Dispose(); CmdBuilder = null; } if (InsertCommand != null) { InsertCommand.Dispose(); InsertCommand = null; } if (Adapter != null) { Adapter.Dispose(); Adapter = null; } //reconnect the source and target database this._TargetDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess, string.Empty, string.Empty, TargetDBName, string.Empty, Common.Constants.DBPassword)); Connection = new DIConnection(SourceConnectionString, DIServerType.MsAccess); } } catch (Exception ex) { ExceptionHandler.ExceptionFacade.ThrowException(ex); } finally { if (SourceTopicTable != null) { SourceTopicTable.Dispose(); } } }
private void ProcessMappedSubgroupVals() { DIConnection SrcDBConnection = null; DIQueries SrcDBQueries = null; DI6SubgroupValBuilder TrgSubgroupValBuilder = null; DI6SubgroupValBuilder SrcSubgroupBuilder = null; DI6SubgroupValInfo SrcSubgroupValInfo = null; DataTable Table = null; string SourceFileWPath = string.Empty; int TrgSGNid = 0; if (this.MappedTables.ContainsKey(TemplateMergeControlType.Subgroups)) { TrgSubgroupValBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries); foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Subgroups].MappedTable.MappedTable.Rows) { Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportSubgroupVals(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + SubgroupVals.SubgroupValNId]))); TrgSGNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + SubgroupVals.SubgroupValNId]); if (Table != null && Table.Rows.Count > 0) { SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty); SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection); // Get SubgroupVal Info SrcSubgroupBuilder = new DI6SubgroupValBuilder(SrcDBConnection, SrcDBQueries); SrcSubgroupValInfo= SrcSubgroupBuilder.GetSubgroupValInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID])); // Import Mapped SubgroupVals TrgSubgroupValBuilder.ImportSubgroupValFrmMappedSubgroupVal(SrcSubgroupValInfo.Nid, TrgSGNid, SrcDBQueries, SrcDBConnection); SrcDBConnection.Dispose(); } } } }
private static void Create_Other_Artefacts_And_Update_Folder_Structures_For_Consumer_Per_Database(string UserNId) { DataTable DtRegisteredDatabases; DIConnection DIConnection; string OutputFolder; string Query; DtRegisteredDatabases = null; DIConnection = null; OutputFolder = string.Empty; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty); Query = "SELECT DISTINCT DBNId FROM Artefacts WHERE DBNId <> -1;"; DtRegisteredDatabases = DIConnection.ExecuteDataTable(Query); foreach (DataRow DrRegisteredDatabases in DtRegisteredDatabases.Rows) { #region "--Subscription--" OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DrRegisteredDatabases["DBNId"].ToString() + "\\sdmx\\Subscriptions\\" + UserNId); Create_Directory_If_Not_Exists(OutputFolder); #endregion "--Subscription--" } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
private DataTable GetGalleryThumbnailsTable(string Indicator, string Area, string Language, int DBNId, string Type) { DataTable RetVal; DIConnection DIConnection; string GetKeywordsQuery, GetPresentationsQuery; DataTable DtKeywords; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); GetPresentationsQuery = "SELECT * FROM Presentations WHERE dbnid = '" + DBNId.ToString() + "' AND lng_code = '" + Language + "'"; if (!string.IsNullOrEmpty(Indicator)) { GetKeywordsQuery = "SELECT * FROM Keywords WHERE keyword = '" + RemoveHtmlEscape(DevInfo.Lib.DI_LibBAL.Utility.DICommon.RemoveQuotes(Indicator)) + "' AND keyword_type = 'I'"; if (!string.IsNullOrEmpty(Area)) { GetKeywordsQuery += " UNION SELECT * FROM Keywords WHERE keyword = '" + RemoveHtmlEscape(DevInfo.Lib.DI_LibBAL.Utility.DICommon.RemoveQuotes(Area)) + "' AND keyword_type = 'A'"; DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); if (DtKeywords != null && DtKeywords.Rows.Count == 2) { GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[0]["keyword_nid"].ToString() + ",%'"; GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[1]["keyword_nid"].ToString() + ",%'"; } //else if (DtKeywords != null && DtKeywords.Rows.Count == 1) //{ // GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[0]["keyword_nid"].ToString() + ",%'"; //GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[1]["keyword_nid"].ToString() + ",%'"; //} else { GetPresentationsQuery += " AND keyword_nids LIKE '%,-1,%'"; } } else { DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); if (DtKeywords != null && DtKeywords.Rows.Count == 1) { GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[0]["keyword_nid"].ToString() + ",%'"; } else { GetPresentationsQuery += " AND keyword_nids LIKE '%,-1,%'"; } } } else { if (!string.IsNullOrEmpty(Area)) { GetKeywordsQuery = "SELECT * FROM Keywords WHERE keyword = '" + RemoveHtmlEscape(DevInfo.Lib.DI_LibBAL.Utility.DICommon.RemoveQuotes(Area)) + "' AND keyword_type = 'A'"; DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery); if (DtKeywords != null && DtKeywords.Rows.Count == 1) { GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[0]["keyword_nid"].ToString() + ",%'"; //GetPresentationsQuery += " AND keyword_nids LIKE '%," + DtKeywords.Rows[1]["keyword_nid"].ToString() + ",%'"; } else { GetPresentationsQuery += " AND keyword_nids LIKE '%,-1,%'"; } } } if (Type != "A") { GetPresentationsQuery += " AND type = '" + Type + "';"; } RetVal = DIConnection.ExecuteDataTable(GetPresentationsQuery); } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
private static Dictionary<string, SubscriptionType> Get_Subscriptions_Dictionary(string DbNId, bool IsMetadata) { Dictionary<string, SubscriptionType> RetVal; string Query, FileNameWPath; DIConnection DIConnection; DataTable DtSubscriptions; SDMXObjectModel.Message.RegistryInterfaceType RegistryInterface; SDMXObjectModel.Registry.SubscriptionType Subscription; RetVal = new Dictionary<string, SubscriptionType>(); Query = string.Empty; FileNameWPath = string.Empty; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty); Query = "SELECT * FROM Artefacts WHERE DBNId = " + DbNId + " AND Type = " + Convert.ToInt32(ArtefactTypes.Subscription).ToString() + ";"; DtSubscriptions = DIConnection.ExecuteDataTable(Query); foreach (DataRow DrSubscriptions in DtSubscriptions.Rows) { FileNameWPath = DrSubscriptions["FileLocation"].ToString(); RegistryInterface = (SDMXObjectModel.Message.RegistryInterfaceType)Deserializer.LoadFromFile(typeof(SDMXObjectModel.Message.RegistryInterfaceType), FileNameWPath); Subscription = ((SDMXObjectModel.Registry.SubmitSubscriptionsRequestType)RegistryInterface.Item).SubscriptionRequest[0].Subscription; if (Subscription.EventSelector != null && Subscription.EventSelector.Count > 0) { if (IsMetadata == false) { if (Subscription.EventSelector[0] is DataRegistrationEventsType) { RetVal.Add(Subscription.RegistryURN, Subscription); } } else { if (Subscription.EventSelector[0] is MetadataRegistrationEventsType) { RetVal.Add(Subscription.RegistryURN, Subscription); } } } } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
private void InsertIntoIndexingDatabase(int DBNId, string SearchIndicatorICs, string SearchAreas, string SearchLanguage, DataTable DtASResults, bool HandleAsDIUAOrDIUFlag) { string InsertASResultsQuery; DIConnection DIConnection; System.Data.Common.DbParameter DbParam; List<System.Data.Common.DbParameter> DbParams; InsertASResultsQuery = string.Empty; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); DbParams = new List<System.Data.Common.DbParameter>(); if (DtASResults != null && DtASResults.Rows.Count > 0) { foreach (DataRow DrASResults in DtASResults.Rows) { InsertASResultsQuery = "INSERT INTO Indexing_Table (AS_DBNId, AS_SearchIndicatorICs, AS_SearchAreas, AS_SearchLanguage, " + "AS_IndicatorNId, AS_UnitNId, AS_AreaNId, AS_Indicator, AS_ICName, AS_Unit, AS_Area, AS_DefaultSG, AS_MRDTP, AS_MRD, " + "AS_AreaCount, AS_SGCount, AS_SourceCount, AS_TPCount, AS_DVCount, AS_AreaNIds, AS_SGNIds, AS_SourceNIds, AS_TPNIds, AS_DVNIds, AS_DVSeries) " + "VALUES(@DBNId, @SearchIndicatorICs, @SearchAreas, @SearchLanguage, @IndicatorNId, @UnitNId, @AreaNId, " + "@AS_Indicator, @AS_ICName, @AS_Unit, @AS_Area, @AS_DefaultSG, @AS_MRDTP, @AS_MRD, @AS_AreaCount, @AS_SGCount, @AS_SourceCount, " + "@AS_TPCount, @AS_DVCount, @AS_AreaNIds, @AS_SGNIds, @AS_SourceNIds, @AS_TPNIds, @AS_DVNIds, @AS_DVSeries);"; DbParams = new List<System.Data.Common.DbParameter>(); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_DBNId"; DbParam.DbType = DbType.Int32; DbParam.Value = DBNId.ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SearchIndicatorICs"; DbParam.DbType = DbType.String; DbParam.Value = SearchIndicatorICs; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SearchAreas"; DbParam.DbType = DbType.String; if (HandleAsDIUAOrDIUFlag) { DbParam.Value = SearchAreas; } else { DbParam.Value = string.Empty; } DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SearchLanguage"; DbParam.DbType = DbType.String; DbParam.Value = SearchLanguage; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_IndicatorNId"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["IndicatorNId"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_UnitNId"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["UnitNId"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_AreaNId"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["AreaNId"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_Indicator"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["Indicator"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_ICName"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["ICName"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_Unit"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["Unit"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_Area"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["Area"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_DefaultSG"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["DefaultSG"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_MRDTP"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["MRDTP"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_MRD"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["MRD"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_AreaCount"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["AreaCount"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SGCount"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["SGCount"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SourceCount"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["SourceCount"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_TPCount"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["TPCount"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_DVCount"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(DrASResults["DVCount"].ToString()); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_AreaNIds"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["AreaNIds"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SGNIds"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["SGNIds"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_SourceNIds"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["SourceNIds"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_TPNIds"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["TPNIds"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_DVNIds"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["DVNIds"].ToString(); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AS_DVSeries"; DbParam.DbType = DbType.String; DbParam.Value = DrASResults["DVSeries"].ToString(); DbParams.Add(DbParam); DIConnection.ExecuteDataTable(InsertASResultsQuery, CommandType.Text, DbParams); } } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
public static void Create_Other_Artefacts_And_Update_Folder_Structures_For_Provider_Per_Database(string UserNId) { DataTable DtRegisteredDatabases, DtTable; DIConnection DIConnection; string AgencyId, OutputFolder; string Query, InsertQuery; List<ArtefactInfo> PAArtefacts; DtRegisteredDatabases = null; DtTable = null; DIConnection = null; AgencyId = string.Empty; OutputFolder = string.Empty; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty); Query = "SELECT DISTINCT DBNId FROM Artefacts WHERE DBNId <> -1;"; DtRegisteredDatabases = DIConnection.ExecuteDataTable(Query); foreach (DataRow DrRegisteredDatabases in DtRegisteredDatabases.Rows) { #region "--PA--" AgencyId = Get_AgencyId_From_DFD(DrRegisteredDatabases["DBNId"].ToString()); OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DrRegisteredDatabases["DBNId"].ToString() + "\\sdmx\\Provisioning Metadata\\PAs"); PAArtefacts = SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, true, DevInfo.Lib.DI_LibSDMX.Constants.DFD.Id, DevInfo.Lib.DI_LibSDMX.Constants.DataProviderScheme.Prefix + UserNId, AgencyId, null, OutputFolder); if (!Global.IsDSDUploadedFromAdmin(Convert.ToInt32(DrRegisteredDatabases["DBNId"].ToString()))) { PAArtefacts.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DevInfo.Lib.DI_LibSDMX.Constants.MFD.Area.Id, DevInfo.Lib.DI_LibSDMX.Constants.DataProviderScheme.Prefix + UserNId, AgencyId, null, OutputFolder)); PAArtefacts.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DevInfo.Lib.DI_LibSDMX.Constants.MFD.Indicator.Id, DevInfo.Lib.DI_LibSDMX.Constants.DataProviderScheme.Prefix + UserNId, AgencyId, null, OutputFolder)); PAArtefacts.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DevInfo.Lib.DI_LibSDMX.Constants.MFD.Source.Id, DevInfo.Lib.DI_LibSDMX.Constants.DataProviderScheme.Prefix + UserNId, AgencyId, null, OutputFolder)); } else { Query = "SELECT DISTINCT Id FROM Artefacts WHERE DBNId = " + DrRegisteredDatabases["DBNId"].ToString() + " AND Type = " + Convert.ToInt32(ArtefactTypes.MFD).ToString(); DtTable = DIConnection.ExecuteDataTable(Query); foreach (DataRow DrTable in DtTable.Rows) { PAArtefacts.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DrTable["Id"].ToString(), DevInfo.Lib.DI_LibSDMX.Constants.DataProviderScheme.Prefix + UserNId, AgencyId, null, OutputFolder)); } } foreach (ArtefactInfo PAArtefact in PAArtefacts) { InsertQuery = "INSERT INTO Artefacts (DBNId, Id, AgencyId, Version, URN, Type, FileLocation) VALUES(" + DrRegisteredDatabases["DBNId"].ToString() + ",'" + PAArtefact.Id + "','" + PAArtefact.AgencyId + "','" + PAArtefact.Version + "','" + string.Empty + "'," + Convert.ToInt32(PAArtefact.Type) + ",'" + Path.Combine(OutputFolder, PAArtefact.FileName) + "');"; DIConnection.ExecuteDataTable(InsertQuery); } #endregion "--PA--" #region "--Registration--" OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DrRegisteredDatabases["DBNId"].ToString() + "\\sdmx\\Registrations\\" + UserNId); Create_Directory_If_Not_Exists(OutputFolder); #endregion "--Registration--" #region "--Constraint--" OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DrRegisteredDatabases["DBNId"].ToString() + "\\sdmx\\Constraints\\" + UserNId); Create_Directory_If_Not_Exists(OutputFolder); #endregion "--Constraint--" #region "--Subscription--" OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DrRegisteredDatabases["DBNId"].ToString() + "\\sdmx\\Subscriptions\\" + UserNId); Create_Directory_If_Not_Exists(OutputFolder); #endregion "--Subscription--" } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
private List<ArtefactInfo> Create_PAs_For_Database_Per_Provider(string DbNId, string AgencyId, string OutputFolder, string ProviderFileName, bool UploadedDSDFlag) { List<ArtefactInfo> RetVal; DataTable DtMFDs; DIConnection DIConnection; SDMXObjectModel.Message.StructureType Structure; string ProviderId; string MFDId; string Query; RetVal = new List<ArtefactInfo>(); DIConnection = null; Structure = null; ProviderId = string.Empty; MFDId = string.Empty; Query = string.Empty; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); Query = "SELECT * FROM Artefacts WHERE Type=" + Convert.ToInt32(ArtefactTypes.MFD).ToString() + " And DbNId = " + DbNId + ";"; DtMFDs = DIConnection.ExecuteDataTable(Query); if (File.Exists(ProviderFileName)) { Structure = (SDMXObjectModel.Message.StructureType)Deserializer.LoadFromFile(typeof(SDMXObjectModel.Message.StructureType), ProviderFileName); if (Structure != null && Structure.Structures != null && Structure.Structures.OrganisationSchemes != null && Structure.Structures.OrganisationSchemes.Count > 0 && Structure.Structures.OrganisationSchemes[0] is SDMXObjectModel.Structure.DataProviderSchemeType && Structure.Structures.OrganisationSchemes[0].Organisation != null && Structure.Structures.OrganisationSchemes[0].Organisation.Count > 0) { foreach (DataProviderType DataProvider in Structure.Structures.OrganisationSchemes[0].Organisation) { ProviderId = DataProvider.id; RetVal.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, true, DevInfo.Lib.DI_LibSDMX.Constants.DFD.Id, ProviderId, AgencyId, null, OutputFolder)); if (UploadedDSDFlag == false) { RetVal.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DevInfo.Lib.DI_LibSDMX.Constants.MFD.Area.Id, ProviderId, AgencyId, null, OutputFolder)); RetVal.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DevInfo.Lib.DI_LibSDMX.Constants.MFD.Indicator.Id, ProviderId, AgencyId, null, OutputFolder)); RetVal.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, DevInfo.Lib.DI_LibSDMX.Constants.MFD.Source.Id, ProviderId, AgencyId, null, OutputFolder)); } else { foreach (DataRow DrMFDs in DtMFDs.Rows) { MFDId = DrMFDs["Id"].ToString(); RetVal.AddRange(SDMXUtility.Generate_PA(SDMXSchemaType.Two_One, false, MFDId, ProviderId, AgencyId, null, OutputFolder)); } } } } } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
/// <summary> /// Check and create the IsMapServer column in Databases.mdb database /// </summary> private void CheckNCreateIsMapServerColumnInMDBDatabase() { DIConnection dIConnection = null; try { dIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("../../stock/Database.mdb"), string.Empty, string.Empty); try { DataTable table = dIConnection.GetConnection().GetSchema("Columns"); var v = from r in table.AsEnumerable() where (r["TABLE_NAME"].ToString() == "MappingInformation" && r["COLUMN_NAME"].ToString() == "mapserver_used") select r; if (v.Count() == 0) { dIConnection.AddColumn("mappinginformation", "mapserver_used", "Text", "False"); } } catch { } } catch (Exception ex) { Global.WriteErrorsInLogFolder("error in Registering new database"); Global.CreateExceptionString(ex, null); } finally { if (dIConnection != null) { dIConnection.Dispose(); dIConnection = null; } } }
private Dictionary<string, SubscriptionType> Get_Subscriptions_Dictionary_For_Structural_Metadata_Changes(string DbNId) { Dictionary<string, SubscriptionType> RetVal; string Query, FileNameWPath; DIConnection DIConnection; DataTable DtSubscriptions; SDMXObjectModel.Message.RegistryInterfaceType RegistryInterface; SDMXObjectModel.Registry.SubscriptionType Subscription; RetVal = new Dictionary<string, SubscriptionType>(); Query = string.Empty; FileNameWPath = string.Empty; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); Query = "SELECT * FROM Artefacts WHERE DBNId = " + DbNId + " AND Type = " + Convert.ToInt32(ArtefactTypes.Subscription).ToString() + ";"; DtSubscriptions = DIConnection.ExecuteDataTable(Query); foreach (DataRow DrSubscriptions in DtSubscriptions.Rows) { FileNameWPath = DrSubscriptions["FileLocation"].ToString(); RegistryInterface = (SDMXObjectModel.Message.RegistryInterfaceType)SDMXObjectModel.Deserializer.LoadFromFile(typeof(SDMXObjectModel.Message.RegistryInterfaceType), FileNameWPath); Subscription = ((SDMXObjectModel.Registry.SubmitSubscriptionsRequestType)RegistryInterface.Item).SubscriptionRequest[0].Subscription; if (Subscription.EventSelector[0] is StructuralRepositoryEventsType) { RetVal.Add(Subscription.RegistryURN, Subscription); } } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } return RetVal; }
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 ProcessMappedUnits() { DIConnection SrcDBConnection = null; DIQueries SrcDBQueries = null; UnitBuilder TrgUnitBilder = null; UnitInfo SrcUnitInfoObj = null; UnitBuilder SrcUnitBuilderObj = null; DataTable Table = null; string SourceFileWPath = string.Empty; int TrgUnitNid = 0; if (this.MappedTables.ContainsKey(TemplateMergeControlType.Unit)) { TrgUnitBilder = new UnitBuilder(this.DBConnection, this.DBQueries); foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Unit].MappedTable.MappedTable.Rows) { Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportUnits(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + Unit.UnitNId]))); TrgUnitNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Unit.UnitNId]); if (Table != null && Table.Rows.Count > 0) { SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty); SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection); // get unit info SrcUnitBuilderObj = new UnitBuilder(SrcDBConnection, SrcDBQueries); SrcUnitInfoObj = SrcUnitBuilderObj.GetUnitInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID])); // Import Mapped Unit Values TrgUnitBilder.ImportMappedUnitInformation(SrcUnitInfoObj, SrcUnitInfoObj.Nid, TrgUnitNid, SrcDBQueries, SrcDBConnection); SrcDBConnection.Dispose(); } } } }
private void Delete_SDMXArtefacts_Details_In_Database(string DbNId, string AdminNId) { string DeleteQuery; DIConnection DIConnection; DataTable DtTable; string RegistrationFolder = string.Empty; DeleteQuery = string.Empty; DIConnection = null; try { DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); DtTable = DIConnection.ExecuteDataTable("SELECT * FROM Artefacts WHERE DBNId = " + DbNId + " AND Type = " + Convert.ToInt32(ArtefactTypes.Registration).ToString() + ";"); foreach (DataRow DrTable in DtTable.Rows) { if (DrTable["FileLocation"].ToString().Contains(@"\" + DbNId + @"\" + Constants.FolderName.SDMX.Registrations + AdminNId + @"\")) { Global.Delete_Registration_Artefact(DbNId, AdminNId, DrTable["Id"].ToString()); Global.Delete_Constraint_Artefact(DbNId, AdminNId, DrTable["Id"].ToString()); } } DeleteQuery = "DELETE FROM Artefacts WHERE DBNId = " + DbNId + " AND (Type NOT IN (" + Convert.ToInt32(ArtefactTypes.Subscription).ToString() + "," + Convert.ToInt32(ArtefactTypes.Registration).ToString() + "," + Convert.ToInt32(ArtefactTypes.Constraint).ToString() + "," + Convert.ToInt32(ArtefactTypes.Mapping).ToString() + "));"; DIConnection.ExecuteDataTable(DeleteQuery); } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
public static bool CreateTempTables(List<string> sourceDatabaseFileWPaths, string targetDatabaseFileWPath) { bool RetVal = false; OleDbConnection Connection = null; OleDbCommand Command; ImportTableQueries ImportQueries = null; DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; for (int i = 0; i < sourceDatabaseFileWPaths.Count; i++) { try { string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + sourceDatabaseFileWPaths[i] + ";Jet OLEDB:Database Password="******";Persist Security Info=False;"; Connection = new OleDbConnection(ConnectionString); Command = new OleDbCommand(); Command.Connection = Connection; Command.CommandTimeout = 0; Connection.Open(); SourceDBConnection = new DevInfo.Lib.DI_LibDAL.Connection.DIConnection(DevInfo.Lib.DI_LibDAL.Connection.DIServerType.MsAccess, string.Empty, string.Empty, sourceDatabaseFileWPaths[i], String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DevInfo.Lib.DI_LibBAL.Utility.DataExchange.GetDBQueries(SourceDBConnection); ImportQueries = new ImportTableQueries(SourceDBQueries.DataPrefix, SourceDBQueries.LanguageCode, targetDatabaseFileWPath, sourceDatabaseFileWPaths[i]); if (i == 0) { Command.CommandText = ImportQueries.GetTempIndicatorTable(); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.GetTempUnitTable()); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.GetTempSubgroupValsTable()); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.GetTempSubgrouopDimensionsTable()); Command.ExecuteNonQuery(); Command.CommandText = ImportQueries.GetTempSubgroupDimValuesTable(); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.GetTempAreaTable()); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.GetTempICTable()); Command.ExecuteNonQuery(); } else { Command.CommandText = ImportQueries.InsertIntoIndicatorTable(); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.InsertIntoUnitTable()); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.InsertIntoSubgroupValsTable()); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.InsertIntoSubgrouopDimensionsTable()); Command.ExecuteNonQuery(); Command.CommandText = ImportQueries.InsertIntoSubgroupDimValuesTable(); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.InsertIntoAreaTable()); Command.ExecuteNonQuery(); Command.CommandText = (ImportQueries.InsertIntoTempICTable()); Command.ExecuteNonQuery(); } RetVal = true; } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) { SourceDBConnection.Dispose(); SourceDBQueries = null; } if (ImportQueries != null) ImportQueries = null; if (Connection != null) { Connection.Close(); Connection.Dispose(); } } } return RetVal; }
private void Save_Artefacts_Details_In_Database(List<ArtefactInfo> Artefacts, int DbNid) { string InsertQuery; DIConnection DIConnection; System.Data.Common.DbParameter DbParam; List<System.Data.Common.DbParameter> DbParams; InsertQuery = string.Empty; DIConnection = null; string AppPhysicalPath = string.Empty; string DbFolder = string.Empty; try { AppPhysicalPath = HttpContext.Current.Request.PhysicalApplicationPath; DbFolder = Constants.FolderName.Data + DbNid.ToString() + "\\"; DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); DbParams = new List<System.Data.Common.DbParameter>(); foreach (ArtefactInfo Artefact in Artefacts) { InsertQuery = "INSERT INTO Artefacts (DBNId, Id, AgencyId, Version, URN, Type, FileLocation)" + " VALUES(@DBNId, @Id, @AgencyId, @Version, @URN, @Type, @FileLocation);"; DbParams = new List<System.Data.Common.DbParameter>(); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "DBNId"; DbParam.DbType = DbType.Int32; DbParam.Value = DbNid; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "Id"; DbParam.DbType = DbType.String; DbParam.Value = Artefact.Id; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "AgencyId"; DbParam.DbType = DbType.String; DbParam.Value = Artefact.AgencyId; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "Version"; DbParam.DbType = DbType.String; DbParam.Value = Artefact.Version; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "URN"; DbParam.DbType = DbType.String; DbParam.Value = Artefact.URN; DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "Type"; DbParam.DbType = DbType.Int32; DbParam.Value = Convert.ToInt32(Artefact.Type); DbParams.Add(DbParam); DbParam = DIConnection.CreateDBParameter(); DbParam.ParameterName = "FileLocation"; DbParam.DbType = DbType.String; switch (Artefact.Type) { case ArtefactTypes.ConceptS: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.Concepts + Artefact.FileName); break; case ArtefactTypes.DSD: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.sdmx + Artefact.FileName); break; case ArtefactTypes.CL: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.Codelists + Artefact.FileName); break; case ArtefactTypes.CategoryS: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.Categories + Artefact.FileName); break; //case ArtefactTypes.Categorisation: // DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.Categorisations + Artefact.FileName); // break; case ArtefactTypes.DFD: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.ProvisioningMetadata + Artefact.FileName); break; case ArtefactTypes.Complete: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.sdmx + Artefact.FileName); break; case ArtefactTypes.Summary: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.sdmx + Artefact.FileName); break; case ArtefactTypes.Report: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.sdmx + Artefact.FileName); break; case ArtefactTypes.PA: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.PAs + Artefact.FileName); break; case ArtefactTypes.MSD: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.MSD + Artefact.FileName); break; case ArtefactTypes.MFD: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.ProvisioningMetadata + Artefact.FileName); break; case ArtefactTypes.Categorisation: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.Categorisations + Artefact.FileName); break; case ArtefactTypes.Header: DbParam.Value = Path.Combine(AppPhysicalPath, DbFolder + Constants.FolderName.SDMX.sdmx + Artefact.FileName); break; default: break; } DbParams.Add(DbParam); DIConnection.ExecuteDataTable(InsertQuery, CommandType.Text, DbParams); } } catch (Exception ex) { Global.CreateExceptionString(ex, null); throw ex; } finally { if (DIConnection != null) { DIConnection.Dispose(); } } }
/// <summary> /// Insert Newly added records from DES to database /// </summary> /// <param name="sDestDBPath">Destination Database Path</param> /// <param name="MsAccessPwd">Password</param> /// <param name="sLng_Suffix">Lang suffix</param> public void InsertNewRecords(String tempDESheetPath, string sDestDBPath, String MsAccessPwd) { DataView oDV = null; DataRowView oDVRow; string sDB_Prefix; string sQry = ""; DIConnection DBConnection; int i; int j; int iIndicatorNId = 0; int iUnitNId = 0; int[] iSubgroupNId = new int[this.DESheetInformation.Subgroup.Length]; int[] iIUSNId = new int[this.DESheetInformation.Subgroup.Length]; int iSectorNID = 0; //int[] iClassNId = new int[Strings.Split(this.DESheetInformation.ClassGUID, "{}").Length]; int[] iClassNId = new int[this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()).Length]; int iParentNId; //Used for Class and Source bool bNewIUS = false; Boolean bNewSector = false; bool bNewClass = false; //*** True if any of I / U / S is new bool SectorGlobal = false; // -- Create New DiConnection with db as sDestDBPath DBConnection = new DIConnection(DIServerType.MsAccess, "", "", sDestDBPath, "", MsAccessPwd); //--- Using DAL for getting Database default Prefix sDB_Prefix = DBConnection.DIDataSetDefault(); String sLng_Suffix = DBConnection.DILanguageCodeDefault(sDB_Prefix); DIQueries DBQueries = new DIQueries(sDB_Prefix, DBConnection.DILanguageCodeDefault(sDB_Prefix)); // Get SectorNId iSectorNID = GetSectorNId(DBConnection, DBQueries, out bNewSector, out bNewSector); //*** If Selected Class has not been inserted by export routine then insert new Class string[] sClassGuid; string[] sClassName; string[] sClassGlobal; iParentNId = iSectorNID; //sClassGuid = Strings.Split(this.DESheetInformation.ClassGUID,"{}"); sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()); sClassName = this.DESheetInformation.ClassName.Split("{}".ToCharArray()); sClassGlobal = this.DESheetInformation.ClassGlobal.Split("{}".ToCharArray()); //*** BugFix 29 Aug 2006 Do not try to insert class into database if indicator is directly associated with sector if (this.DESheetInformation.ClassGUID != "" & this.DESheetInformation.ClassName != "") { for (i = 0; i <= sClassGuid.Length - 1; i++) { iClassNId[i] = GetClassNId(DBConnection, DBQueries, out bNewClass, sClassGuid[i].ToString(), SectorGlobal, iParentNId, sClassName[i].ToString()); iParentNId = Convert.ToInt32(iClassNId[i]); } } //*** Indicator_NId sQry = DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + this.DESheetInformation.IndicatorGUID + "'", FieldSelection.NId); iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iIndicatorNId == 0) { bNewIUS = true; //-- Getting insert query using DAL sQry = DI_LibDAL.Queries.Indicator.Insert.InsertIndicator(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Indicator.Replace("'", "''"), this.DESheetInformation.IndicatorGUID, "", false); //-- Using DAL for query execution and getting identity value DBConnection.ExecuteNonQuery(sQry); iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } //*** Unit_NId //-- Using DAL for getting and executing query for getting unit NId sQry = DBQueries.Calculates.GetUnitNIdByGId(this.DESheetInformation.UnitGUID); iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iUnitNId == 0) { bNewIUS = true; //-- Getting insert query using DAL sQry = DI_LibDAL.Queries.Unit.Insert.InsertUnit(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Unit.Replace("'", "''"), this.DESheetInformation.UnitGUID, false); //-- Using DAL for query execution and getting identity value DBConnection.ExecuteNonQuery(sQry); iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } //*** Subgroup_Val_NId for (i = 0; i <= iSubgroupNId.Length - 1; i++) { sQry = DBQueries.Calculates.GetSubgroupValNIdByGId(this.DESheetInformation.SubgroupGUID[i]); iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iSubgroupNId[i] == 0) { bNewIUS = true; //-- Getting insert query using DAL sQry = DBQueries.Calculates.InsertSubgroupVal(this.DESheetInformation.Subgroup[0].Replace("'", "''"), this.DESheetInformation.SubgroupGUID[0], false); //-- Using DAL for query execution and getting identity value DBConnection.ExecuteNonQuery(sQry); iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } } //*** IUSNId if (bNewIUS) { //*** Insert New IUS for (i = 0; i <= iSubgroupNId.Length - 1; i++) { //-- Get query from DAL sQry = DBQueries.Calculates.InsertNIdsInIUSTable(iIndicatorNId, iUnitNId, iSubgroupNId[i]); //-- Using DAL for query execution and getting identity value DBConnection.ExecuteNonQuery(sQry); iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); //*** Create Relationship with IndicatorClassification string sICGuid; sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()); sICGuid = "'" + this.DESheetInformation.SectorGUID + "'"; for (j = 0; j <= sClassGuid.Length - 1; j++) { sICGuid += ",'" + sClassGuid[j].ToString() + "'"; } sQry = DBQueries.Calculates.GetICNIdByGId(sICGuid); //-- Get Dataview using DAL oDV = DBConnection.ExecuteDataTable(sQry).DefaultView; foreach (DataRowView oDVRow1 in oDV) { //-- Using DAL for inserting IC IUS relation sQry = DBQueries.Calculates.InsertIC_IUSRelation(Convert.ToInt32(oDVRow1["IC_NId"]), iIUSNId[i]); DBConnection.ExecuteNonQuery(sQry); } } } else if (bNewSector || bNewClass) { for (i = 0; i <= iSubgroupNId.Length - 1; i++) { sQry = DBQueries.IUS.GetIUSByI_U_S(iIndicatorNId.ToString(), iUnitNId.ToString(), iSubgroupNId[i].ToString()); iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (bNewSector) { //-- Using DAL for inserting IC IUS relation sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSectorNID, iIUSNId[i]); DBConnection.ExecuteNonQuery(sQry); } if (bNewClass) { for (j = 0; j <= iClassNId.Length - 1; j++) { //-- Using DAL for inserting IC IUS relation sQry = DBQueries.Calculates.InsertIC_IUSRelation(iClassNId[j], iIUSNId[i]); try { DBConnection.ExecuteNonQuery(sQry); } catch (Exception ex) { } } } } } else { int Index = 0; foreach (int SubgroupNId in iSubgroupNId) { //-- Using DAL Query for getting IUSNId sQry = DBQueries.Calculates.GetIUSNIdByIUS(iIndicatorNId, iUnitNId, SubgroupNId); iIUSNId[Index] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); Index += 1; } } string sTimePeriod = string.Empty; int iTimePeriod_NId = 0; Hashtable oTimePeriod = new Hashtable(); string sAreaId = string.Empty; int iArea_NId = 0; Hashtable oAreaId = new Hashtable(); string sDataValue = string.Empty; System.DateTime dtStartDate = System.DateTime.Now; System.DateTime dtEndDate = System.DateTime.Now; string sSubgroup = string.Empty; string sSource = string.Empty; int iSource_NId = 0; Hashtable oSource = new Hashtable(); string sFootNote = ""; int iFootNote_NId = -1; Hashtable oFootNote = new Hashtable(); string sData_Denominator = ""; IWorksheet DataSheet; DIDatabase DIdatabase = null; this.DIExcel = new DIExcel(tempDESheetPath); // this.DIExcel = new DIExcel(this.TempLogFilePath); DataSheet = DIExcel.GetWorksheet(0); { //For each record in excel sheet '*** Get TimePeriod_NId, Area_NId, Data Value, Subgroup, SourceNId for (i = 10; i <= miCtrFillTo; i++) { try { if (DataSheet.Cells[i, 0].Value.ToString() == "" || DataSheet.Cells[i, 1].Value.ToString() == "" || DataSheet.Cells[i, 3].Value.ToString() == "" || DataSheet.Cells[i, 4].Value.ToString() == "" || DataSheet.Cells[i, 5].Value.ToString() == "") { } //*** If TimePeriod, AreaID, DataValue,Subgroup, Source is blank leave this record else { //*** TimePeriod sTimePeriod = DataSheet.Cells[i, 0].Value.ToString(); iTimePeriod_NId = -1; if (CheckDate(sTimePeriod)) { //*** Check for Valid TimeFormat allowed in DE if (oTimePeriod.ContainsKey(sTimePeriod)) { iTimePeriod_NId = Convert.ToInt32(oTimePeriod[sTimePeriod]); } else { sQry = DBQueries.Calculates.GetTimeperiodNIdByTimePeriod(sTimePeriod); iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iTimePeriod_NId == 0) { // Using DAL for getting and executing Query for inserting timeperiod sQry = DI_LibDAL.Queries.Timeperiod.Insert.InsertTimeperiod(sDB_Prefix, sTimePeriod); DBConnection.ExecuteNonQuery(sQry); iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } oTimePeriod.Add(sTimePeriod, iTimePeriod_NId); } SetDate(sTimePeriod, ref dtStartDate, ref dtEndDate); } else { iTimePeriod_NId = -1; } //*** Area sAreaId = DataSheet.Cells[i, 1].Value.ToString(); iArea_NId = -1; if (oAreaId.ContainsKey(sAreaId)) { iArea_NId = Convert.ToInt32(oAreaId[sAreaId]); } else { //-- Using DAL for getting AreaNId Using AreaID sQry = DBQueries.Calculates.GetAreaNIdByAreaID(sAreaId); iArea_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iArea_NId == 0) { oAreaId.Add(sAreaId, -1); } //*** Don't add New areas to database else { oAreaId.Add(sAreaId, iArea_NId); } } //*** Data value sDataValue = DataSheet.Cells[i, 3].Value.ToString(); //*** Subgroup sSubgroup = DataSheet.Cells[i, 4].Value.ToString(); //*** Source sSource = DI_LibBAL.Utility.DICommon.RemoveQuotes(DataSheet.Cells[i, 5].Value.ToString()); iSource_NId = -1; if (oSource.ContainsKey(sSource)) { iSource_NId = Convert.ToInt32(oSource[sSource]); } else { sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSource + "'", ICType.Source, FieldSelection.NId); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iSource_NId == 0) { string[] sSourceArr; sSourceArr = sSource.Split('_'); //Publisher_ISOCode_Year: Parent= Publisher; Child= Abbr_Year if (sSourceArr.Length >= 2) { //*** Insert Parent sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSourceArr[0] + "'", ICType.Source, FieldSelection.NId); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iSource_NId == 0) { sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSourceArr[0], Guid.NewGuid().ToString(), false, -1, "", ICType.Source); DBConnection.ExecuteNonQuery(sQry); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } //*** Create relationship for (j = 0; j <= iIUSNId.Length - 1; j++) { sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]); //'sQry = "INSERT INTO " & sDB_Prefix & "Indicator_Classifications_IUS" & " (IC_NId,IUSNId) VALUES (" & iSource_NId & "," & iIUSNId(j) & ")" try { DBConnection.ExecuteNonQuery(sQry); } catch (Exception ex) { } } //*** Insert Source // Using DAL for inserting Source sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source); DBConnection.ExecuteNonQuery(sQry); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); //*** Create relationship for (j = 0; j <= iIUSNId.Length - 1; j++) { sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]); try { DBConnection.ExecuteNonQuery(sQry); } //' oDestDB.ExecuteNonQuery(sQry) catch (Exception ex) { } } } else { //iSource_NId = -1 if (sSource.Trim() != "") { //*** Insert Parent as "Global" sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, "Global", ICType.Source, FieldSelection.NId); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iSource_NId == 0) { sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global", Guid.NewGuid().ToString(), false, -1, "", ICType.Source); DBConnection.ExecuteNonQuery(sQry); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } //*** Create relationship for (j = 0; j <= iIUSNId.Length - 1; j++) { sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]); try { DBConnection.ExecuteNonQuery(sQry); } catch (Exception ex) { } } //*** Insert Source sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global_" + sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source); DBConnection.ExecuteNonQuery(sQry); iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); //*** Create relationship for (j = 0; j <= iIUSNId.Length - 1; j++) { sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]); try { DBConnection.ExecuteNonQuery(sQry); } catch (Exception ex) { } } } } } else { //*** If New IUS was created then Create Relationship with Source if (bNewIUS) { // using DAL query for getting parentNId sQry = DBQueries.Calculates.GetICParentNIdByICNId(iSource_NId, "SR"); iParentNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iParentNId != 0) { for (j = 0; j <= iIUSNId.Length - 1; j++) { sQry = DBQueries.Calculates.InsertIC_IUSRelation(iParentNId, iIUSNId[j]); try { DBConnection.ExecuteNonQuery(sQry); } catch (Exception ex) { //*** database maintains unique composite key for IC_NId and IUSNId - this will prevent duplicate entry if any } } } for (j = 0; j <= iIUSNId.Length - 1; j++) { sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]); try { DBConnection.ExecuteNonQuery(sQry); } catch (Exception ex) { Console.Write(ex.Message); } } } } oSource.Add(sSource, iSource_NId); } try { sFootNote = DataSheet.Cells[i, 6].Value.ToString(); } catch (Exception ex) { sFootNote = ""; } iFootNote_NId = -1; if (sFootNote != "") { if (oFootNote.ContainsKey(sFootNote)) { iFootNote_NId = Convert.ToInt32(oFootNote[sFootNote]); } else { sQry = DBQueries.Calculates.GetFootNoteNIdByFootNote(Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(sFootNote))); iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (iFootNote_NId == 0) { sQry = DI_LibDAL.Queries.Footnote.Insert.InsertFootnote(sDB_Prefix, sLng_Suffix, sFootNote, Guid.NewGuid().ToString()); DBConnection.ExecuteNonQuery(sQry); iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } oFootNote.Add(sFootNote, iFootNote_NId); } } try { if (Utility.DICommon.IsNumeric(DataSheet.Cells[i, 7].Value.ToString(), System.Threading.Thread.CurrentThread.CurrentCulture)) { sData_Denominator = DataSheet.Cells[i, 7].Value.ToString() + ","; } else { sData_Denominator = ""; } } catch (Exception ex) { sData_Denominator = ""; } if (iIUSNId[0] == -1 | iTimePeriod_NId == -1 | iArea_NId == -1 | iSource_NId == -1 | sDataValue == "") { } else { if (this.ApplicationWizardType == WizardType.HundredMinus) { sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "", iFootNote_NId, iSource_NId); DBConnection.ExecuteNonQuery(sQry); } else { sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", sData_Denominator, iFootNote_NId, iSource_NId); DBConnection.ExecuteNonQuery(sQry); } } } } catch (Exception ex) { } } // Update Indicator Unit subgroupNId DIdatabase = new DIDatabase(DBConnection, DBQueries); DIdatabase.UpdateIndicatorUnitSubgroupNIDsInData(); DBConnection.Dispose(); } //1.Time - 2.AreaId - 3.AreaName - 4.DataValue - 5.Subgroup - 6.Source - 7.Footnote - 8.Denominator - 12.SubgroupNId //DataSheet = null; if ((oDV != null)) { oDV.Dispose(); oDV = null; } }