/// <summary> /// Returns the database language code /// </summary> /// <param name="dbConnection"></param> /// <returns></returns> public static string GetDBLanguageCode(DIConnection dbConnection) { string RetVal = string.Empty; string DataPrefix = dbConnection.DIDataSetDefault(); if (dbConnection.IsValidDILanguage(DataPrefix, DataAdmin.DAAppliationLangCode)) { RetVal = "_" + DataAdmin.DAAppliationLangCode; } else { RetVal = dbConnection.DILanguageCodeDefault(DataPrefix); } return RetVal; }
/// <summary> /// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue /// and merge both column value into new column Data_Value /// </summary> /// <param name="dbConnection"></param> /// <param name="dbQueries"></param> public static void MergeTextualandNumericDataValueColumn(string databaseFileNameWPath) { try { using (DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, string.Empty)) { DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); MergeTextualandNumericDataValueColumn(DBConnection, DBQueries); } } catch (Exception ex) { throw ex; } }
public DatabaseIncrementBuilder(string sourceDBLocation, string TargetDBLocation) { string DataPrefix, LangCode = string.Empty; SourceConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, sourceDBLocation, string.Empty, "unitednations2000"); TargetConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TargetDBLocation, string.Empty, "unitednations2000"); DataPrefix = SourceConnection.DIDataSetDefault(); LangCode = SourceConnection.DILanguageCodeDefault(DataPrefix); SourceQueries = new DIQueries(DataPrefix, LangCode); DataPrefix = TargetConnection.DIDataSetDefault(); LangCode = TargetConnection.DILanguageCodeDefault(DataPrefix); TargetQueries = new DIQueries(DataPrefix, LangCode); this.SourceDbFile = sourceDBLocation; this.TargetDbFile = TargetDBLocation; }
/// <summary> /// Returns the DIQueries object /// </summary> /// <param name="dbConnection"></param> /// <returns></returns> public static DIQueries GetDBQueries(DIConnection dbConnection) { DIQueries RetVal = null; string LanguageCode = string.Empty; string DataPrefix = dbConnection.DIDataSetDefault(); if (dbConnection.IsValidDILanguage(DataPrefix, DataAdmin.DAAppliationLangCode)) { LanguageCode = "_" + DataAdmin.DAAppliationLangCode; } else { LanguageCode = dbConnection.DILanguageCodeDefault(DataPrefix); } RetVal = new DIQueries(DataPrefix, LanguageCode); return RetVal; }
/// <summary> /// Generate SDMX Files from DES /// </summary> /// <param name="DESFileNameWithPath"></param> /// <param name="DSDFileNameWPath"></param> /// <param name="outputFolder"></param> /// <returns></returns> public static bool Generate_SDMXML_Data(string DESFileNameWithPath, string DSDFileNameWPath, string outputFolder) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries = null; int count = 0; // Utility.TemporaryFileNamePath DataTable Table = DevInfo.Lib.DI_LibBAL.Import.DAImport.DES.DataEntrySpreadsheets.GetDataTableForAllDESSheets(DESFileNameWithPath); string TempDatabaseName = string.Empty; string TempDatabaseNameCompact = string.Empty; TempDatabaseName = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template)); Dictionary<string, int> IndicatorList = new Dictionary<string, int>(); Dictionary<string, int> UnitList = new Dictionary<string, int>(); Dictionary<string, int> AreaList = new Dictionary<string, int>(); Dictionary<string, int> SgValList = new Dictionary<string, int>(); Dictionary<string, int> TimepeirodList = new Dictionary<string, int>(); Dictionary<string, int> SourceList = new Dictionary<string, int>(); try { DevInfo.Lib.DI_LibDAL.Resources.Resource.GetBlankDevInfoDBFile(TempDatabaseName); DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseName, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); DIDataValueHelper.MergeTextualandNumericDataValueColumn(DBConnection, DBQueries); IndicatorBuilder IndBuilder = new IndicatorBuilder(DBConnection, DBQueries); UnitBuilder UBuilder = new UnitBuilder(DBConnection, DBQueries); DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(DBConnection, DBQueries); AreaBuilder AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries); IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(DBConnection, DBQueries); TimeperiodBuilder TBuilder = new TimeperiodBuilder(DBConnection, DBQueries); SourceBuilder SrcBuilder = new SourceBuilder(DBConnection, DBQueries); DIDatabase DatabaseObj = new DIDatabase(DBConnection, DBQueries); IUSBuilder IUSBuilderObj = new IUSBuilder(DBConnection, DBQueries); foreach (DataRow Row in Table.Rows) { int IndicatorNId = 0; int UnitNId = 0; int SGValNId = 0; int AreaNId = 0; int TimeperiodNId = 0; int SourceNid = 0; int IUSNID = 0; //-- Import Indicator if (IndicatorList.ContainsKey(Convert.ToString(Row[Indicator.IndicatorGId]))) { IndicatorNId = IndicatorList[Convert.ToString(Row[Indicator.IndicatorGId])]; } else { IndicatorNId = IndBuilder.ImportIndicator(Convert.ToString(Row[Indicator.IndicatorName]), Convert.ToString(Row[Indicator.IndicatorGId]), false); IndicatorList.Add(Convert.ToString(Row[Indicator.IndicatorGId]), IndicatorNId); } //-- Import Unit if (UnitList.ContainsKey(Convert.ToString(Row[Unit.UnitGId]))) { UnitNId = UnitList[Convert.ToString(Row[Unit.UnitGId])]; } else { UnitNId = UBuilder.ImportUnit(Convert.ToString(Row[Unit.UnitGId]), Convert.ToString(Row[Unit.UnitName]).Trim(), false); UnitList.Add(Convert.ToString(Row[Unit.UnitGId]), UnitNId); } //-- Import SubgroupVal if (!string.IsNullOrEmpty(Convert.ToString(Row[SubgroupVals.SubgroupVal]))) { if (SgValList.ContainsKey(Convert.ToString(Row[SubgroupVals.SubgroupValGId]))) { SGValNId = SgValList[Convert.ToString(Row[SubgroupVals.SubgroupValGId])]; } else { SGValNId = SGBuilder.ImportSubgroupVal(Convert.ToString(Row[SubgroupVals.SubgroupVal]), Convert.ToString(Row[SubgroupVals.SubgroupValGId]), false); SgValList.Add(Convert.ToString(Row[SubgroupVals.SubgroupValGId]), SGValNId); } } //-- Import Area if (AreaList.ContainsKey(Convert.ToString(Row[Area.AreaID]))) { AreaNId = AreaList[Convert.ToString(Row[Area.AreaID])]; } else { AreaNId = AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaName]), Convert.ToString(Row[Area.AreaID]), String.Empty, false); AreaList.Add(Convert.ToString(Row[Area.AreaID]), AreaNId); } //-- Import Timeperiod if (TimepeirodList.ContainsKey(Convert.ToString(Row[Timeperiods.TimePeriod]))) { TimeperiodNId = TimepeirodList[Convert.ToString(Row[Timeperiods.TimePeriod])]; } else { TimeperiodNId = TBuilder.CheckNCreateTimeperiod(Convert.ToString(Row[Timeperiods.TimePeriod])); TimepeirodList.Add(Convert.ToString(Row[Timeperiods.TimePeriod]), TimeperiodNId); } //-- Import Source if (SourceList.ContainsKey(Convert.ToString(Row[IndicatorClassifications.ICName]))) { SourceNid = SourceList[Convert.ToString(Row[IndicatorClassifications.ICName])]; } else { SourceNid = SrcBuilder.CheckNCreateSource(Convert.ToString(Row[IndicatorClassifications.ICName])); SourceList.Add(Convert.ToString(Row[IndicatorClassifications.ICName]), SourceNid); } IUSNID = IUSBuilderObj.ImportIUS(IndicatorNId, UnitNId, SGValNId, 0, 0, DBQueries, DBConnection); if (IUSNID > 0 && AreaNId > 0 && TimeperiodNId > 0 && !string.IsNullOrEmpty(Convert.ToString(Row[Data.DataValue]))) { DatabaseObj.CheckNCreateData(AreaNId, IUSNID, SourceNid, TimeperiodNId, Convert.ToString(Row[Data.DataValue])); } } DatabaseObj.UpdateIndicatorUnitSubgroupNIDsInData(); //-- Compact Database into TempFile TempDatabaseNameCompact = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template)); DIDatabase.CompactDataBase(ref DBConnection, DBQueries, TempDatabaseNameCompact, true); DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseNameCompact, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); if (!Directory.Exists(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_')))) { Directory.CreateDirectory(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_'))); } System.Xml.XmlDocument XmlDoc = SDMXUtility.Get_Query(SDMXSchemaType.Two_One, null, QueryFormats.StructureSpecificTS, DataReturnDetailTypes.Full, Guid.NewGuid().ToString().Replace("-", "").Replace("_", ""), DBConnection, DBQueries); RetVal = SDMXUtility.Generate_Data(SDMXSchemaType.Two_One, XmlDoc, DevInfo.Lib.DI_LibSDMX.DataFormats.StructureSpecificTS, DBConnection, DBQueries, outputFolder); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return RetVal; }
public static DIQueries GetMapServerQueries(string DataPrefix_UI, string LanguageCode_UI, DIConnection connection) { //TO Do GetConnection from web service DIQueries RetVal = null; try { if (connection.DIDataSets().Select(DBAvailableDatabases.AvlDBPrefix + "='" + DataPrefix_UI.Trim('_') + "'").Length == 0) { DataPrefix_UI = connection.DIDataSetDefault(); } if (connection.DILanguages(DataPrefix_UI).Select(Language.LanguageCode + "='" + LanguageCode_UI + "'").Length == 0) { LanguageCode_UI = connection.DILanguageCodeDefault(DataPrefix_UI); } RetVal = new DIQueries(DataPrefix_UI, LanguageCode_UI); } catch (Exception ex) { throw ex; } 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; } } } }
/// <summary> /// Register database in db.xml file /// </summary> /// <param name="requestParam"></param> /// <returns></returns> public string AdminRegisterDatabase(string requestParam) { string RetVal = string.Empty; string[] Params; string DBConnectionsFile = string.Empty; XmlDocument XmlDoc; XmlNode xmlNode; string CategoryName = string.Empty; string ConnName = string.Empty; string DbConn = string.Empty; string DefArea = string.Empty; string Description = string.Empty; string IsDefDb = string.Empty; string IsRegCatalog = string.Empty; XmlElement NewNode; int NewId = 0; string[] DBConnArr; DIConnection ObjDIConnection = null; string ServerType = string.Empty; string ServerName = string.Empty; string DbName = string.Empty; string UserName = string.Empty; string Password = string.Empty; string QryStr = string.Empty; DataTable DTCounts; string CountStr = string.Empty; XmlElement NewCategoryNode; XmlNodeList ObjXmlNodeList; int CategoryId = 0; string DbConnWithEncryptPassword = string.Empty; string DefAreaJSon = string.Empty; string DefAreaCount = "0"; string[] DefAreaStr; string[] DefIndStr; string DefInd = string.Empty; string DefIndJSon = string.Empty; string strDefaultLanguage = string.Empty; DBConverterDecorator objDBConverterDecorator = null; DBVersionBuilder VersionBuilder = null; string DbDefaultLanguage = string.Empty; string[] DbAvailableLanguage = null; string DbAvailableLanguageStr = string.Empty; string isGlobalAllow = ConfigurationManager.AppSettings[Constants.WebConfigKey.IsGlobalAllow]; try { requestParam = HttpUtility.UrlDecode(requestParam); Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter); CategoryName = Params[0]; ConnName = Params[1]; DbConn = Params[2]; if (Params.Length > 3) { DefArea = Params[3]; } if (Params.Length > 4) { Description = Params[4]; } if (Params.Length > 6) { IsRegCatalog = Params[6]; } DBConnectionsFile = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DBConnectionsFile]); XmlDoc = new XmlDocument(); XmlDoc.Load(DBConnectionsFile); xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']"); if (xmlNode != null) { RetVal = "exists"; } else { xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "[@" + Constants.XmlFile.Db.Tags.CategoryAttributes.Name + "='" + CategoryName + "']"); if (xmlNode == null) { NewCategoryNode = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Category); NewCategoryNode.SetAttribute(Constants.XmlFile.Db.Tags.CategoryAttributes.Name, CategoryName); xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).AppendChild(NewCategoryNode); } // Get old higher id ObjXmlNodeList = XmlDoc.SelectNodes("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + "child::node()"); foreach (XmlNode data in ObjXmlNodeList) { CategoryId = int.Parse(data.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Id].Value); if (NewId < CategoryId) { NewId = CategoryId; } } NewId++; // Increase 1 for new id // Split connection details in variables DBConnArr = Global.SplitString(DbConn, "||"); ServerType = DBConnArr[0]; ServerName = DBConnArr[1]; DbName = DBConnArr[2]; UserName = DBConnArr[3]; if (DBConnArr.Length > 4) { Password = DBConnArr[4]; } ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(ServerType)), ServerName, "", DbName, UserName, Password); // Get default language for that db QryStr = "SELECT Language_Code FROM ut_language WHERE Language_Default = 1"; DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase)); if (DTCounts.Rows.Count == 1) strDefaultLanguage = DTCounts.Rows[0]["Language_Code"].ToString(); else strDefaultLanguage = "en"; // Get counts from db QryStr = "select DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt from ut_dbmetadata_" + strDefaultLanguage; DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase)); DataRow Row = DTCounts.Rows[0]; CountStr = string.Format("{0:0,0}", Row[0]) + "_" + string.Format("{0:0,0}", Row[1]) + "_" + string.Format("{0:0,0}", Row[2]) + "_" + string.Format("{0:0,0}", Row[3]); DbConnWithEncryptPassword = ServerType + "||" + ServerName + "||" + DbName + "||" + UserName; if (!string.IsNullOrEmpty(Password)) { DbConnWithEncryptPassword += "||" + Global.EncryptString(Password); } else { DbConnWithEncryptPassword += "||"; } //get default language code in database DbDefaultLanguage = ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()); //get all avalilable language code in database DbAvailableLanguage = Global.GetAllAvailableLanguageCode(ObjDIConnection); if (DbAvailableLanguage.Length > 0) { DbAvailableLanguageStr = string.Join(",", DbAvailableLanguage); } if (Global.standalone_registry != "true") { this.RunDBScripts(ObjDIConnection, NewId.ToString(), DbDefaultLanguage.Substring(1)); VersionBuilder = new DBVersionBuilder(ObjDIConnection, new DIQueries(ObjDIConnection.DIDataSetDefault(), ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()))); VersionBuilder.InsertVersionInfo(Constants.DBVersion.DI7_0_0_0, Constants.DBVersion.VersionsChangedDatesDI7_0_0_0, Constants.DBVersion.VersionCommentsDI7_0_0_0); #region -- Get default values of indicator, area and language -- #region -- get default indicator and their Json data -- DefIndStr = GetDefaultIndicators(ObjDIConnection, strDefaultLanguage); DefInd = DefIndStr[0]; DefIndJSon = DefIndStr[1]; #endregion #region --get default area of level 1 and 2 (L1 + L2) by stored procedure with their json and counts -- DefAreaStr = GetDefaultAreas(ObjDIConnection, strDefaultLanguage); DefArea = DefAreaStr[0]; DefAreaJSon = DefAreaStr[1]; DefAreaCount = DefAreaStr[2]; #endregion #endregion #region "Catalog" if (IsRegCatalog == "true") { // Set description filed to update in service database this.DataBaseDescription = Description.Replace("'", "''").Replace("\n", "<br />").Replace(@"""", @"\"""); if (InsertIntoCatalog(ObjDIConnection, Row, DbAvailableLanguageStr)) { GetAndUpdateIndexedAreas(DbAvailableLanguage, ObjDIConnection); GetAndUpdateIndexedIndicators(DbAvailableLanguage, ObjDIConnection); } } #endregion "Catalog" } #region -- Set xml tag attribute values and save it in db.xml file -- //Create new element node and set its attributes NewNode = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Database); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.Id, NewId.ToString()); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.Name, ConnName); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.SDMXDb, "false"); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.Count, CountStr); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage, DbDefaultLanguage.Substring(1)); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator, DefInd); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON, DefIndJSon); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea, DefArea); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON, DefAreaJSon); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount, DefAreaCount); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection, DbConnWithEncryptPassword); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage, DbAvailableLanguageStr); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified, string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date)); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.LanguageCodeCSVFiles, string.Empty); NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.IsSDMXHeaderCreated, "false"); if (DbAvailableLanguage != null) { foreach (string LanguageCode in DbAvailableLanguage) { NewNode.SetAttribute("desc_" + LanguageCode, Description); } } xmlNode.AppendChild(NewNode); XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = NewId.ToString(); //Save xml file File.SetAttributes(DBConnectionsFile, FileAttributes.Normal); XmlDoc.Save(DBConnectionsFile); #endregion GenerateAllPagesXML(); DeleteSitemapFiles(); RetVal = NewId.ToString(); } } catch (Exception ex) { Global.WriteErrorsInLogFolder("error in Registering new database"); Global.CreateExceptionString(ex, null); } finally { if (ObjDIConnection != null) { ObjDIConnection.Dispose(); ObjDIConnection = null; } } return RetVal; }
/// Update connection details by selected db /// </summary> /// <param name="requestParam"></param> /// <returns></returns> public string AdminUpdateDbConnection(string requestParam) { string RetVal = string.Empty; string[] Params; string DBConnectionsFile = string.Empty; XmlDocument XmlDoc; XmlNode xmlNode; string DbNId = string.Empty; string ConnName = string.Empty; string DbConn = string.Empty; string Password = string.Empty; string DefArea = string.Empty; string Description = string.Empty; string IsDefDb = string.Empty; string CategoryName = string.Empty; XmlElement NewCategoryElement; XmlNode NewXmlNode; XmlNode CategoryNode; DIConnection ObjDIConnection = null; string[] DbAvailableLanguage; string strDefaultLanguage = string.Empty; string CountStr = string.Empty; DataTable DTCounts; string QryStr = string.Empty; string DbDefaultLanguage = string.Empty; string DbAvailableLanguageStr = string.Empty; string DefAreaJSon = string.Empty; string DefAreaCount = "0"; string[] DefAreaStr; string[] DefIndStr; string DefInd = string.Empty; string DefIndJSon = string.Empty; DBConverterDecorator objDBConverterDecorator = null; DBVersionBuilder VersionBuilder = null; ///Variables for creatin XLSLogfile string[] DatabaseParams; string XLSFileMsg = string.Empty; string Server_HostName = string.Empty; string DatabaseName = string.Empty; string DataBaseUserName = string.Empty; string[] DBConnArr; try { requestParam = HttpUtility.UrlDecode(requestParam); Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter); DbNId = Params[0]; ConnName = Params[1]; DbConn = Params[2]; Password = Params[3]; Description = Params[4]; IsDefDb = Params[5]; CategoryName = Params[6]; DBConnectionsFile = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DBConnectionsFile]); XmlDoc = new XmlDocument(); XmlDoc.Load(DBConnectionsFile); DBConnArr = DbConn.Split(new string[] { "||" }, StringSplitOptions.None); ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(DBConnArr[0])), DBConnArr[1].ToString(), string.Empty, DBConnArr[2].ToString(), DBConnArr[3].ToString(), Password); if (!string.IsNullOrEmpty(Password)) { Password = Global.EncryptString(Password); DbConn = DbConn + Password; } QryStr = "SELECT Language_Code FROM ut_language WHERE Language_Default = 1"; DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase)); if (DTCounts.Rows.Count == 1) strDefaultLanguage = DTCounts.Rows[0]["Language_Code"].ToString(); else strDefaultLanguage = "en"; // Get counts from db QryStr = "select DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt from ut_dbmetadata_" + strDefaultLanguage; DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase)); DataRow Row = DTCounts.Rows[0]; CountStr = string.Format("{0:0,0}", Row[0]) + "_" + string.Format("{0:0,0}", Row[1]) + "_" + string.Format("{0:0,0}", Row[2]) + "_" + string.Format("{0:0,0}", Row[3]); //get default language code in database DbDefaultLanguage = ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()); //get all avalilable language code in database DbAvailableLanguage = Global.GetAllAvailableLanguageCode(ObjDIConnection); if (DbAvailableLanguage.Length > 0) { DbAvailableLanguageStr = string.Join(",", DbAvailableLanguage); } #region -- get default indicator and their Json data -- DefIndStr = GetDefaultIndicators(ObjDIConnection, strDefaultLanguage); DefInd = DefIndStr[0]; DefIndJSon = DefIndStr[1]; #endregion #region --get default area of level 1 and 2 (L1 + L2) by stored procedure with their json and counts -- DefAreaStr = GetDefaultAreas(ObjDIConnection, strDefaultLanguage); DefArea = DefAreaStr[0]; DefAreaJSon = DefAreaStr[1]; DefAreaCount = DefAreaStr[2]; #endregion //Check connection name already exists or not if (XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']") != null && XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']").Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Id].Value != DbNId.ToString()) { RetVal = "exists"; } else { xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Id + "=" + DbNId + "]"); if (xmlNode.Attributes["langcode_csvfiles"] == null) { XmlAttribute attrCSV = XmlDoc.CreateAttribute("langcode_csvfiles"); attrCSV.Value = string.Empty; xmlNode.Attributes.Append(attrCSV); } else { xmlNode.Attributes["langcode_csvfiles"].Value = string.Empty; } if (xmlNode.ParentNode.Attributes[Constants.XmlFile.Db.Tags.CategoryAttributes.Name].Value == CategoryName) { //Update for same category name xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Name].Value = ConnName; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection].Value = DbConn; if (xmlNode.Attributes["desc" + DbDefaultLanguage] == null) { XmlAttribute attrDesc = XmlDoc.CreateAttribute("desc" + DbDefaultLanguage); attrDesc.Value = Description; xmlNode.Attributes.Append(attrDesc); } else { xmlNode.Attributes["desc" + DbDefaultLanguage].Value = Description; } xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Count].Value = CountStr; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified].Value = string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date); xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage].Value = DbAvailableLanguageStr; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage].Value = DbDefaultLanguage.Substring(1); xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator].Value = DefInd; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON].Value = DefIndJSon; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea].Value = DefArea; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON].Value = DefAreaJSon; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount].Value = DefAreaCount; //Update default dbNId if (IsDefDb == "true") { XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = DbNId; } File.SetAttributes(DBConnectionsFile, FileAttributes.Normal); XmlDoc.Save(DBConnectionsFile); //Update Metadata Description in the database ObjDIConnection.ExecuteNonQuery("UPDATE " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString() + " SET DBMtd_Desc = '" + Description.Replace("'", "''") + "'"); RetVal = "true"; } else if (xmlNode.ParentNode.Attributes[Constants.XmlFile.Db.Tags.CategoryAttributes.Name].Value != CategoryName) { //Update when category name has changed for same id //Update values in node xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Name].Value = ConnName; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection].Value = DbConn; xmlNode.Attributes["desc_en"].Value = Description; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Count].Value = CountStr; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified].Value = string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date); xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage].Value = DbAvailableLanguageStr; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage].Value = DbDefaultLanguage.Substring(1); xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator].Value = DefInd; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON].Value = DefIndJSon; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea].Value = DefArea; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON].Value = DefAreaJSon; xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount].Value = DefAreaCount; //Update default dbNId if (IsDefDb == "true") { XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = DbNId; } //Copy updated node values in new node NewXmlNode = xmlNode; //Remove current node xmlNode.ParentNode.RemoveChild(xmlNode); //Read the category node CategoryNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "[@" + Constants.XmlFile.Db.Tags.CategoryAttributes.Name + "='" + CategoryName + "']"); //Create a category node if not exists if (CategoryNode == null) { NewCategoryElement = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Category); NewCategoryElement.SetAttribute(Constants.XmlFile.Db.Tags.CategoryAttributes.Name, CategoryName); CategoryNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).AppendChild(NewCategoryElement); } //Append the new node in category CategoryNode.AppendChild(NewXmlNode); File.SetAttributes(DBConnectionsFile, FileAttributes.Normal); XmlDoc.Save(DBConnectionsFile); RetVal = "true"; //Update Metadata Description in the database ObjDIConnection.ExecuteNonQuery("UPDATE " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString() + " SET DBMtd_Desc = '" + Description.Replace("'", "''") + "'"); } if (Global.standalone_registry != "true") { this.RunDBScripts(ObjDIConnection, DbNId, DbDefaultLanguage.Substring(1)); // Generate language file because RunDBScripts method deletes existing language file, so user will not be able to execute furter functionality. GenerateAllPagesXML(); VersionBuilder = new DBVersionBuilder(ObjDIConnection, new DIQueries(ObjDIConnection.DIDataSetDefault(), ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()))); VersionBuilder.InsertVersionInfo(Constants.DBVersion.DI7_0_0_0, Constants.DBVersion.VersionsChangedDatesDI7_0_0_0, Constants.DBVersion.VersionCommentsDI7_0_0_0); } // Set description filed to update in service database this.DataBaseDescription = Description.Replace("'", "''").Replace("\n", "<br />").Replace(@"""", @"\"""); GetAndUpdateIndexedAreas(DbAvailableLanguage, ObjDIConnection); GetAndUpdateIndexedIndicators(DbAvailableLanguage, ObjDIConnection); UpdateAdaptations(ObjDIConnection); DeleteSitemapFiles(); } #region "Call method to write log in XLS file" if (RetVal == "true") { DatabaseParams = DbConn.Split(new string[] { "||" }, StringSplitOptions.None); if (!string.IsNullOrEmpty(DatabaseParams[1].ToString())) { Server_HostName = DatabaseParams[1].ToString(); } if (!string.IsNullOrEmpty(DatabaseParams[2].ToString())) { DatabaseName = DatabaseParams[2].ToString(); } if (!string.IsNullOrEmpty(DatabaseParams[3].ToString())) { DataBaseUserName = DatabaseParams[3].ToString(); } //"Connection Name:{0}, Server/Host Name:{1}, Database Name:{2}, User Name:{3}, Description:{4}"; XLSFileMsg = string.Format(Constants.CSVLogMessage.UpdateConnection, ConnName, Server_HostName, DatabaseName, DataBaseUserName, Description); WriteLogInXLSFile(Constants.AdminModules.DatabaseSettings.ToString(), XLSFileMsg); } #endregion } catch (Exception ex) { Global.CreateExceptionString(ex, null); } return RetVal; }
/// <summary> /// Returns true if template or database is DevInfo compatible /// </summary> /// <param name="dbConnection"></param> /// <param name="fileNameWPath"></param> /// <returns></returns> /// <remarks>It will validate on the basis of default langauge and dataset</remarks> public static bool ISVaildDevInfoDatabase(string fileNameWPath) { bool RetVal = false; bool IsForOnlineDB; DIConnection DBConnection = null; DIQueries DbQueries; try { DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty); //AvailableDB DbQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); // check DB_Available_Databases table exists if (!string.IsNullOrEmpty(DBConnection.DIDataSetDefault())) { RetVal = true; } } catch (Exception ex) { System.Diagnostics.Debug.Print(ex.Message); RetVal = false; } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
/// <summary> /// Returns the DIQueries object /// </summary> /// <param name="dbConnection"></param> /// <param name="dataPrefix"></param> /// <returns></returns> public static DIQueries GetDBQueries(DIConnection dbConnection, string dataPrefix) { DIQueries RetVal = null; string LanguageCode = string.Empty; //-- Set Default dataprefix if not passed if (string.IsNullOrEmpty(dataPrefix)) dataPrefix = dbConnection.DIDataSetDefault(); if (dbConnection.IsValidDILanguage(dataPrefix, DataAdmin.DAAppliationLangCode)) { LanguageCode = "_" + DataAdmin.DAAppliationLangCode; } else { LanguageCode = dbConnection.DILanguageCodeDefault(dataPrefix); } RetVal = new DIQueries(dataPrefix, LanguageCode); return RetVal; }
private void ConnectToDatabaseNCreateConverter(string fileNameWPath) { DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty); DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); this.CreateDBConverter(DBConnection, DBQueries); }
public static bool CompactNUpdateOnlineDataBase(ref DIConnection sourceDBConnection, DIQueries dbQueries) { bool RetVal = false; DIConnectionDetails SourceDBConnectionDetails; DBMetadataTableBuilder DBMetadataTable; DIDatabase DBDatabase; string DataPrefix = string.Empty; try { if (sourceDBConnection != null) { // update counts in DBMetadata table( only if database/template is in DI6 format) if (dbQueries == null) { DataPrefix = sourceDBConnection.DIDataSetDefault(); dbQueries = new DIQueries(DataPrefix, sourceDBConnection.DILanguageCodeDefault(DataPrefix)); } DBMetadataTable = new DBMetadataTableBuilder(sourceDBConnection, dbQueries); if (DBMetadataTable.IsDBMetadataTableExists()) { DBMetadataTable.GetNUpdateCounts(); } if (DIDatabase.SeperateDataValueColumn) { // Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column. DIDataValueHelper.SeparateTextualandNemericData(sourceDBConnection, dbQueries); } // update indicator unit and subgroup nids in Data table DBDatabase = new DIDatabase(sourceDBConnection, dbQueries); DBDatabase.UpdateIndicatorUnitSubgroupNIDsInData(); // remove FootnoteNId inconsistency. (replace 0 or null FootnoteNId by -1 in UT_data table). DBDatabase.RemoveFootnoteNIdsInconsistencyInData(); RetVal = true; } } catch (Exception ex) { RetVal = false; ExceptionFacade.ThrowException(ex); } return RetVal; }
/// <summary> /// Compact the database mainly Access database(.mdb) and save as specified destination file. /// </summary> ///<param name="sourceDBConnection"> Source database connection</param> /// <param name="destFilePath">destination file name with path.</param> /// <param name="disposeConnection"> Set true when calling this methods for finish process otherwise false.For only saving the database, pass False and for finish , pass true. </param> /// <returns>true, if success.</returns> /// <remarks>Before calling this function,dont dispose source database connection </remarks> public static bool CompactDataBase(ref DIConnection sourceDBConnection, DIQueries dbQueries, string destFilePath, bool disposeConnection) { bool RetVal = false; bool IsFileOpen = false; DIConnectionDetails SourceDBConnectionDetails; DBMetadataTableBuilder DBMetadataTable; DIDatabase DBDatabase; string SourceDBNameWPath; JRO.JetEngine je; string DataPrefix = string.Empty; DI7MetadataCategoryBuilder DI7MetadataCategory; // -- NOTE: USE sDestFile only when sDestFile <> sSourceDB try { if (sourceDBConnection != null && !string.IsNullOrEmpty(destFilePath)) { //Drop index for ut_data table for IUSNID, areanid column DIDatabase.DropDefaultIndex(sourceDBConnection); // update counts in DBMetadata table( only if database/template is in DI6 format) if (dbQueries == null) { DataPrefix = sourceDBConnection.DIDataSetDefault(); dbQueries = new DIQueries(DataPrefix, sourceDBConnection.DILanguageCodeDefault(DataPrefix)); } DBMetadataTable = new DBMetadataTableBuilder(sourceDBConnection, dbQueries); if (DBMetadataTable.IsDBMetadataTableExists()) { DBMetadataTable.GetNUpdateCounts(); } //Updating XSLT from Resourse File into database. DI7MetadataCategory = new DI7MetadataCategoryBuilder(sourceDBConnection, dbQueries); DI7MetadataCategory.UpdateXSLT(dbQueries.DataPrefix); // update database name in DB_Available table sourceDBConnection.InsertNewDBFileName(dbQueries.DataPrefix, DICommon.RemoveQuotes(System.IO.Path.GetFileName(destFilePath))); if (DIDatabase.SeperateDataValueColumn) { // Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column. DIDataValueHelper.SeparateTextualandNemericData(sourceDBConnection, dbQueries); } // update indicator unit and subgroup nids in Data table DBDatabase = new DIDatabase(sourceDBConnection, dbQueries); DBDatabase.UpdateIndicatorUnitSubgroupNIDsInData(); // remove FootnoteNId inconsistency. (replace 0 or null FootnoteNId by -1 in UT_data table). DBDatabase.RemoveFootnoteNIdsInconsistencyInData(); // Update auto calculated columns ( IC table - Publisher, Year & title ,Indicator table- Data_Exists, area table - data_exist, IUS table - subgroup_nids & data_exist) into the database/template DBDatabase.UpdateAutoCalculatedFieldsInTables(); // Update auto calculated column of DI7 DBDatabase.UpdateAutoCalculatedDI7FieldsInTables(); //Update those subgroupVals which is not associated with any subgroup type.Then insert association with others for those subgroup DBDatabase.UpdateSubgroupValsInOthersSGDimensionInTables(); //Create index for ut_data table for IUSNID, areanid column DIDatabase.CreateDefaultIndex(sourceDBConnection); // dispose source database connection SourceDBConnectionDetails = sourceDBConnection.ConnectionStringParameters; SourceDBNameWPath = SourceDBConnectionDetails.DbName; sourceDBConnection.Dispose(); System.Threading.Thread.Sleep(10); try { if (File.Exists(destFilePath)) { File.SetAttributes(destFilePath, FileAttributes.Normal); File.Delete(destFilePath); } } catch { } //-- Copy SourceFile to temp file so that any existing connection on database shall not stop compact database process. //string TempFile = DICommon.GetValidFileName(DateTime.Now.ToString()) + Path.GetExtension(SourceDBNameWPath); //File.Copy(SourceDBNameWPath, TempFile, true); try { // compacting the database je = new JRO.JetEngine(); je.CompactDatabase("Data Source=\"" + SourceDBNameWPath + "\";Jet OLEDB:Database Password="******"Data Source=\"" + destFilePath + "\";Jet OLEDB:Database Password="******"Database already exists")) IsFileOpen = true; } // reconnect to source database if (!disposeConnection) { sourceDBConnection = new DIConnection(SourceDBConnectionDetails); } if (IsFileOpen == false) RetVal = true; } } catch (Exception ex) { RetVal = false; ExceptionFacade.ThrowException(ex); } return RetVal; }
/// <summary> /// check is DI7 database /// </summary> /// <returns></returns> public static bool IsDI7Database(string databaseFileNameWPath) { bool RetVal = false; string DataPrefix = string.Empty; DIConnection diConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, "unitednations2000"); DIQueries diQueries; if (diConnection != null) { DataPrefix = diConnection.DIDataSetDefault(); diQueries = new DIQueries(DataPrefix, diConnection.DILanguageCodeDefault(DataPrefix)); if (diConnection.ExecuteDataTable(diQueries.DBVersion.GetRecords(DevInfo.Lib.DI_LibBAL.Converter.Database.Constants.Versions.DI7_0_0_0)).Rows.Count > 0) { RetVal = true; } } return RetVal; }
/// <summary> /// Returns true if template or database is DevInfo compatible /// </summary> /// <param name="dbConnection"></param> /// <param name="isForOnlineDB"></param> /// <returns></returns> /// <remarks>It will validate on the basis of default langauge and dataset</remarks> public static bool ISVaildDevInfoDatabase(DIConnectionDetails connectionDetails) { bool RetVal = false; bool IsForOnlineDB; DIConnection DBConnection = null; DIQueries DbQueries; DI5SP2DBConverter SP2Converter = null; try { DBConnection = new DIConnection(connectionDetails); //AvailableDB DbQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); // check DB_Available_Databases table exists if (!string.IsNullOrEmpty(DBConnection.DIDataSetDefault())) { RetVal = true; ////// if exists then check for SP2 database ////SP2Converter = new DI5SP2DBConverter(DBConnection, DbQueries); ////switch (connectionDetails.ServerType) ////{ //// case DIServerType.Excel: //// case DIServerType.MsAccess: //// IsForOnlineDB = false; //// break; //// default: //// IsForOnlineDB = true; //// break; ////} ////RetVal = SP2Converter.IsValidDB(IsForOnlineDB); } } catch (Exception ex) { System.Diagnostics.Debug.Print(ex.Message); RetVal = false; } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
/// <summary> /// DevInfo_5_0 Area Spreadsheet (should have 5 columns only staring the value from 6th Row - AreaID, AreaName, AreaLevel, AreaGID, ParentGID) /// </summary> /// <param name="templateFileName"></param> /// <param name="xlsFilenames"></param> /// <param name="tempFolderPath"></param> /// <param name="trgDBQueries">Instance of target queries object</param> /// <param name="languageName">languageName</param> /// <returns></returns> public bool CreateTemplateFrmAreaSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgDBQueries, string languageName) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries; AreaBuilder AreaBuilderObj; DIDatabase TempTemplateFile; string DataPrefix = string.Empty; string LanguageCode = string.Empty; LanguageBuilder DILanguageBuilder; try { //create temp template file TempTemplateFile = new DIDatabase(templateFileName); TempTemplateFile.Dispose(); //create DIConnection, queries and area objects DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty); if (trgDBQueries != null) { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgDBQueries.LanguageCode); DataPrefix = DBQueries.DataPrefix; LanguageCode = trgDBQueries.LanguageCode.Replace("_", ""); // create langauge dependent tables if not exists in the new template this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode); } else { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); } AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries); //get areas from excel files foreach (string XlsFileName in xlsFilenames) { try { // insert area into template DIExcel AreaXlsFile = new DIExcel(XlsFileName); AreaInfo NewArea; DataTable TempTable = AreaXlsFile.GetDataTableFromSheet(AreaXlsFile.GetSheetName(0)); //check it is a valid area excel file or not if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1) { if (TempTable.Rows[2][0].ToString() == DITemplate.AreaIdString & TempTable.Rows[2][1].ToString() == DITemplate.AreaNameString & TempTable.Rows[2][2].ToString() == DITemplate.AreaLevelString & TempTable.Rows[2][3].ToString() == DITemplate.AreaGIdString & TempTable.Rows[2][4].ToString() == DITemplate.AreaParentGIdString) { // delete empty rows for (int i = 0; i < 4; i++) { TempTable.Rows[0].Delete(); } TempTable.AcceptChanges(); // sort the table by level TempTable.DefaultView.Sort = TempTable.Columns[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex].ColumnName; foreach (DataRowView Row in TempTable.DefaultView) { try { // get area info from temp table NewArea = new AreaInfo(); NewArea.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaID].ColumnIndex].ToString()); NewArea.Name = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaName].ColumnIndex].ToString()); NewArea.Level = Convert.ToInt32(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex]); NewArea.GID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaGID].ColumnIndex].ToString()); NewArea.Parent = new AreaInfo(); NewArea.Parent.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.PareaGID].ColumnIndex].ToString()); //insert area into template // Step 1: If Area_Parent_ID is blank then set Area_Parent_NID to -1 and Area_Level=1 if (string.IsNullOrEmpty(NewArea.Parent.ID)) { NewArea.Parent.Nid = -1; NewArea.Level = 1; } else { // Step 2: If Area_Parent_ID is not blank then get Area_Parent_NID NewArea.Parent.Nid = AreaBuilderObj.GetAreaNidByAreaID(NewArea.Parent.ID); if (NewArea.Parent.Nid <= 0) { // Step 2.1: If Area_Parent_NID <=0 then set Area_Parent_NID to -1 and Area_Level=1 NewArea.Parent.Nid = -1; NewArea.Level = 1; } else { // Step 2.1: If Area_Parent_NID >0 then Area_Level=Area_Parent_Level+1 //get parent area level NewArea.Parent.Level = AreaBuilderObj.GetAreaLevelByAreaID(NewArea.Parent.ID); NewArea.Level = NewArea.Parent.Level + 1; } } // insert area into template AreaBuilderObj.InsertIntoDatabase(NewArea.Name, NewArea.ID, NewArea.GID, NewArea.Level, NewArea.Parent.Nid); } catch (Exception ex) { // do nothing } } } } } catch (Exception) { // } } RetVal = true; } catch (Exception ex) { RetVal = false; throw new ApplicationException(ex.ToString()); } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
/// <summary> /// Set the selected connection details on the basis of DBinfo /// </summary> /// <param name="dbInfo"></param> /// <remarks> In case of online connection, Dbinfo contains : ServerType{[]}ConnectionIndex{[]}DatasetPrefix /// In case of offline connection, ServerType{[]}AcessdbPath (Applicable for Dekstop application) </remarks> public void SetSelectedConnectionDetail(string dbInfo) { DIConnection DIConnection; string[] Values = new string[0]; Values = Utility.DICommon.SplitString(dbInfo, DELIMITER); if (Values.Length == 3) { //-- In case of Web, it return the connection for all the servert type. this.SelectedConnectionDetail = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].DIConnectionDetails; this.SelectedConnectionName = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].Connection; for (int i = 0; i <= this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo.Count - 1; i++) { if (this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].Prefix.ToLower() == Values[2].ToLower()) { //-- set the selected dataset name and its prefix. this.SelectedDatasetName = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].DatasetName; this.SelectedDatasetPrefix = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].Prefix + "_"; break; } } this.SelectedDbInfo = dbInfo; DIConnection = new DIConnection(this.SelectedConnectionDetail); //-- Seleted dataset language if (DIConnection.DILanguages(this.SelectedDatasetPrefix).Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Language.LanguageCode + " = '" + this._DatabaseLanguage.Substring(1) + "'").Length > 0) { //-- If the database language exists in the database this.DatabaseLanguage = this._DatabaseLanguage; } else { //-- Set the default language as databaseLanguage, if the last selected language does not exists in the database. this.DatabaseLanguage = DIConnection.DILanguageCodeDefault(this.SelectedDatasetPrefix); } } else if (Values.Length == 2) { //-- Set the connection detail of access db (only for dekstop) this.SelectedConnectionDetail = new DIConnectionDetails(DIServerType.MsAccess, "", "", Values[1], USERNAME, PASSWORD); this.SelectedConnectionName = string.Empty; this.SelectedDatasetName = System.IO.Path.GetFileName(Values[1]); DIConnection = new DIConnection(this.SelectedConnectionDetail); this.SelectedDatasetPrefix = DIConnection.DIDataSetDefault(); this.SelectedDbInfo = dbInfo; //-- Seleted dataset language this.DatabaseLanguage = DIConnection.DILanguageCodeDefault(this.SelectedDatasetPrefix); } }
/// <summary> /// DevInfo_5_0 Indicator Spreadsheet (should have 2 columns only staring the value from 6th Row - Indicator,Indicator_GId) /// </summary> /// <param name="templateFileName"></param> /// <param name="xlsFilenames"></param> /// <param name="tempFolderPath"></param> /// <returns></returns> public bool CreateTemplateFrmIndicatorSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgQueries, string languageName) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries; IndicatorBuilder IndicatorBuilderObj; DIDatabase TempTemplateFile; string DataPrefix = string.Empty; string LanguageCode = string.Empty; try { //create temp template file TempTemplateFile = new DIDatabase(templateFileName); TempTemplateFile.Dispose(); //create DIConnection, queries and indicator objects DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty); if (trgQueries == null) { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); } else { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgQueries.LanguageCode); DataPrefix = DBQueries.DataPrefix; LanguageCode = trgQueries.LanguageCode.Replace("_", ""); this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode); } IndicatorBuilderObj = new IndicatorBuilder(DBConnection, DBQueries); //get indicators from excel files foreach (string XlsFileName in xlsFilenames) { try { // insert indicators into template DIExcel IndicatorXlsFile = new DIExcel(XlsFileName); IndicatorInfo NewIndicator; DataTable TempTable = IndicatorXlsFile.GetDataTableFromSheet(IndicatorXlsFile.GetSheetName(0)); //check it is a valid indicator excel file or not if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1) { if (TempTable.Rows[2][0].ToString() == DITemplate.IndicatorString) { //starting index should be 5 for (int i = 5; i < TempTable.Rows.Count; i++) { NewIndicator = new IndicatorInfo(); //indicator Name NewIndicator.Name = DICommon.RemoveQuotes(TempTable.Rows[i][0].ToString()); //indicator GId NewIndicator.GID = DICommon.RemoveQuotes(TempTable.Rows[i][1].ToString()); //insert indicator into template IndicatorBuilderObj.CheckNCreateIndicator(NewIndicator); } } } } catch (Exception) { // } } RetVal = true; } catch (Exception ex) { RetVal = false; throw new ApplicationException(ex.ToString()); } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
public string GetDataDescription(string requestParam) { string RetVal = string.Empty; DataTable DTMetadata = null; string[] DBDetails = requestParam.Split(new string[] { "[~]" }, StringSplitOptions.None); DIConnection ObjDIConnection = null; ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(0)), DBDetails[0].ToString(), string.Empty, DBDetails[1].ToString(), DBDetails[2].ToString(), DBDetails[3].ToString()); DTMetadata = ObjDIConnection.ExecuteDataTable("SELECT DBMtd_Desc FROM " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString()); if (DTMetadata.Rows.Count > 0) { RetVal = DTMetadata.Rows[0]["DBMtd_Desc"].ToString(); } return RetVal; }
/// <summary> /// Initializes a new instance of the Freetext class with the specified database connection details. /// </summary> /// <param name="serverType"></param> /// <param name="serverName"></param> /// <param name="portNo"></param> /// <param name="databaseName"></param> /// <param name="userName"></param> /// <param name="password"></param> /// <param name="languageFilePath"></param> /// <remarks> /// Use this constructor when hosting application is itself not using an instance of DAL /// Hosting application needs to additionally set DatabasePrefix and LanguageCode properties /// </remarks> public FreeText(DIServerType serverType, string serverName, string portNo, string databaseName, string userName, string password) { try { DIConnection = new DIConnection(serverType, serverName, portNo, databaseName, userName, password); } catch (Exception ex) { throw ex; } // Get Default DataSet Prefix for database. this._DatabasePrefix = DIConnection.DIDataSetDefault(); // Get Default Language Code for Default DataSet. this._LanguageCode = DIConnection.DILanguageCodeDefault(_DatabasePrefix); DIQueries = new DIQueries(_DatabasePrefix, _LanguageCode); //add Advance search fields (Indicator,Unit,Subgroup,AreaName,AreaId and Timeperiod)to collection (_AdvanceSearchFields) SetAdvanceSearchFields(); }
/// <summary> /// Create and alter tables schema in database /// </summary> /// <param name="dbConnection"></param> private void CreateNAlterSchemas(DIConnection dbConnection) { string StrQry = string.Empty; DITables TableNames; string DataPrefix = string.Empty; try { //-- Get default data prefix in database DataPrefix = dbConnection.DIDataSetDefault(); //-- Get all table names TableNames = new DITables(dbConnection.DIDataSetDefault(), dbConnection.DILanguageCodeDefault(dbConnection.DIDataSetDefault())); //-- Add timeperiod column into data StrQry = "Alter table "+ TableNames.Data +" add column " + QDSConstants.QDSTables.Data.Columns.TimePeriod + " varchar(100)"; dbConnection.ExecuteNonQuery(StrQry); //-- Add IsDefaultSG column into data StrQry = "Alter table "+ TableNames.Data +" add column " + QDSConstants.QDSTables.Data.Columns.ISDefaultSG + " bit"; dbConnection.ExecuteNonQuery(StrQry); //-- Create TempMRDRecords table CacheUtility.CreateTempMRDRecordsTable(dbConnection); //-- Update timeperiod values into data table StrQry = "UPDATE "+ TableNames.Data +" AS d INNER JOIN "+ TableNames.TimePeriod +" AS t ON d."+ Data.TimePeriodNId +" = t."+ Timeperiods.TimePeriodNId +" SET d."+ QDSConstants.QDSTables.Data.Columns.TimePeriod +" = t." + Timeperiods.TimePeriod; dbConnection.ExecuteNonQuery(StrQry); //-- Update default sg into data table StrQry = "UPDATE "+ TableNames.Data +" AS D INNER JOIN "+ TableNames.IndicatorUnitSubgroup +" AS IUS ON D." + Data.IUSNId + " = IUS." + Indicator_Unit_Subgroup.IUSNId + " SET D."+ QDSConstants.QDSTables.Data.Columns.ISDefaultSG +" = IUS." + Indicator_Unit_Subgroup.IsDefaultSubgroup; dbConnection.ExecuteNonQuery(StrQry); } catch (Exception ex) { throw ex; } }
/// <summary> /// Get all languge codes exists in database /// </summary> /// <param name="dbConnection"></param> /// <returns></returns> public static List<string> GetAllDbLangCodes(DIConnection dbConnection) { List<string> RetVal = new List<string>(); string StrQry = string.Empty; DataTable DtAllLanguages = null; DITables TableNames; string DataPrefix = string.Empty; try { //-- Get default data prefix in database DataPrefix = dbConnection.DIDataSetDefault(); //-- Get all table names TableNames = new DITables(DataPrefix, dbConnection.DILanguageCodeDefault(DataPrefix)); StrQry = "SELECT " + Language.LanguageCode + " FROM " + TableNames.Language; DtAllLanguages = dbConnection.ExecuteDataTable(StrQry); foreach (DataRow Row in DtAllLanguages.Rows) { RetVal.Add(Convert.ToString(Row[0])); } } catch (Exception) { throw; } return RetVal; }
private bool IsAssistantTblExists(DIConnection dbConnection) { bool RetVal = false; DIQueries DBQueries; try { DBQueries = new DIQueries(dbConnection.DIDataSetDefault(), dbConnection.DILanguageCodeDefault(dbConnection.DIDataSetDefault())); // Check availability of table if (dbConnection.ExecuteScalarSqlQuery("SELECT count(*) FROM " + DBQueries.TablesName.Assistant + " WHERE 1=1") != null) { RetVal = true; } } catch (Exception) { } return RetVal; }
/// <summary> /// Returns true if template or database is in current DevInfo 6.0 format /// </summary> /// <param name="fileNameWPath"></param> /// <returns></returns> /// <remarks>For MsAccess</remarks> public static bool ISDevInfoLatestDatabase(string fileNameWPath, ref string dbFilePostFix) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries = null; DBConverterDecorator DBConverterObject; DI7_0_0_0DBConverter DI7_DatabaseConvertor; try { DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); DI7_DatabaseConvertor = new DI7_0_0_0DBConverter(DBConnection, DBQueries); RetVal = DI7_DatabaseConvertor.IsValidDB(false); dbFilePostFix = DI7_DatabaseConvertor.DBFilePostfix; if (RetVal) { // if database/tempalte already in DI7 format then convert it into latest format DBConverterObject = new DBConverterDecorator(DBConnection, DBQueries); DBConverterObject.DoConversion(false); } } catch (Exception) { RetVal = false; // Do NOT throw any exception. } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
/// <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; } }
/// <summary> /// Returns true if template or database is in DevInfo SP2 format /// </summary> /// <param name="fileNameWPath"></param> /// <returns></returns> /// <remarks>For MsAccess</remarks> public static bool ISDevInfoSP2Database(string fileNameWPath) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries = null; try { DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); RetVal = DICommon.ISDevInfoSP2Database(DBConnection, DBQueries, false); } catch (Exception) { RetVal = false; // Do NOT throw any exception. } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
internal bool ExportMDB(string destinationDBNameWPath, string tempFolderPath, bool createNewDatabase) { bool RetVal = false; int ProgressCount = 0; try { if (createNewDatabase) { this.ExportMDB(destinationDBNameWPath, tempFolderPath); } else { DIExport.RaiseExportProgressBarInitialize(100); //- Establish Connection with Destination Database in temp folder this.DestDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, destinationDBNameWPath, string.Empty, string.Empty); this.DestDBQueries = new DIQueries(DestDBConnection.DIDataSetDefault(), DestDBConnection.DILanguageCodeDefault(DestDBConnection.DIDataSetDefault())); this.DBTableNames = new DITables(DestDBConnection.DIDataSetDefault(), DestDBConnection.DILanguageCodeDefault(DestDBConnection.DIDataSetDefault())); this.SourceTableNames = this.SourceDBQueries.TablesName; // if database/tempalte already in DI6 format then convert it into latest format DBConverterDecorator DBConverter = new DBConverterDecorator(this.DestDBConnection, this.DestDBQueries); DBConverter.DoConversion(false); //-- Get LanguageTable this.LanguageTable = this.DestDBConnection.DILanguages(this.DestDBConnection.DIDataSetDefault()); DIExport.RaiseExportProgressBarIncrement(1); //- Export Indicator, Unit, Subgroupval, IUS this.ProcessIndicators(); DIExport.RaiseExportProgressBarIncrement(10); // Export linked IC information this.ProcessIndicatorClassifications(); DIExport.RaiseExportProgressBarIncrement(20); //- Export Area this.ProcessAreas(); DIExport.RaiseExportProgressBarIncrement(30); //- Export TimePeriod this.ProcessTimePeriods(); DIExport.RaiseExportProgressBarIncrement(40); //- Export Data this.ExportData(); DIExport.RaiseExportProgressBarIncrement(80); this.ProcessOtherTables(); DIExport.RaiseExportProgressBarIncrement(90); DIExport.RaiseExportProgressBarIncrement(ProgressCount++); this.DeleteTable(TEMP_TABLE_Data, this.DestDBConnection); DIExport.RaiseExportProgressBarIncrement(100); } } catch { throw; } return RetVal; }
/// <summary> /// Updates specified DataBase name as parameter, in DB_Available Table. /// </summary> /// <param name="availableDbName"></param> public void UpdateAvialableDBInSavedFile(string availableDbWName) { DIConnectionDetails ConnectionDetails; DIConnection AvailableDBConnection; string DatasetPrefix; string LanguageCode; DIQueries AvailableDBQuery; ImportQueries Query; //' --Create connection with saved file ConnectionDetails = new DIConnectionDetails(DIServerType.MsAccess, string.Empty, string.Empty, availableDbWName, string.Empty, DAImportCommon.Constants.DBPassword); AvailableDBConnection = new DIConnection(ConnectionDetails); DatasetPrefix = AvailableDBConnection.DIDataSetDefault(); LanguageCode = AvailableDBConnection.DILanguageCodeDefault(DatasetPrefix); AvailableDBQuery = new DIQueries(DatasetPrefix, LanguageCode); Query = new ImportQueries(AvailableDBQuery); //' - Update Avialble DB fileName in DB_Available_Database table. AvailableDBConnection.ExecuteNonQuery(Query.UpdateDB_Available_DatabaseTable(Path.GetFileNameWithoutExtension(availableDbWName))); //' -- Dispose Connection AvailableDBConnection.Dispose(); AvailableDBQuery = null; Query = null; }