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