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(); } } }
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 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(); } } }
private bool GetAndUpdateIndexedIndicators(string[] dbAvailableLanguage, DIConnection objDIConnection) { bool RetVal = false; List<System.Data.Common.DbParameter> DbParams = new List<System.Data.Common.DbParameter>(); DataSet DSIndicators = new DataSet(); string TableName = string.Empty; //string AdaptationURL = string.Empty; try { //Get all area tables from db foreach (string LanguageCode in dbAvailableLanguage) { TableName = "ut_indicator_" + LanguageCode; DbParams.Clear(); System.Data.Common.DbParameter Param1 = objDIConnection.CreateDBParameter(); Param1.ParameterName = "TAB_NAME"; Param1.DbType = DbType.String; Param1.Value = TableName; DbParams.Add(Param1); DataTable DtIndicator = objDIConnection.ExecuteDataTable("SP_GET_TABLE_DATA", CommandType.StoredProcedure, DbParams); DtIndicator.TableName = TableName; DtIndicator.Columns.Remove("Indicator_Info"); DSIndicators.Tables.Add(DtIndicator); } if (DSIndicators.Tables.Count > 0) { //AdaptationURL = Global.GetAdaptationUrl(); //call webservice method UpdateIndexedIndicators DIWorldwide.Catalog CatalogService = new DIWorldwide.Catalog(); CatalogService.Url = ConfigurationManager.AppSettings[Constants.WebConfigKey.DiWorldWide4] + Constants.WSQueryStrings.CatalogWebService; RetVal = CatalogService.UpdateIndexedIndicators(DSIndicators, Global.GetAdaptationGUID()); RetVal = true; } } catch (Exception ex) { Global.CreateExceptionString(ex, null); } return RetVal; }