/// <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(DIConnection dbConnection, DIQueries dbQueries) { try { if (!ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data)) { //1. Rename Textual_Data_Value & Data_value to orgTextual_Data_value & orgData_Value respectively RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.TextualDataValue, Constants.Data.Orginal_Textual_Data_valueColumn, " Memo"); RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.DataValue, Constants.Data.Orginal_Data_valueColumn, " Double "); System.Threading.Thread.Sleep(100); //2. Create new column Data_Value of memo data type string SqlQuery = "ALTER Table " + dbQueries.TablesName.Data + " Add Column " + Data.DataValue + " Memo NULL"; dbConnection.ExecuteNonQuery(SqlQuery); System.Threading.Thread.Sleep(10); //3. Merge all data values into Data_Value column SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Data_valueColumn; dbConnection.ExecuteNonQuery(SqlQuery); SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Textual_Data_valueColumn + " WHERE " + Data.IsTextualData + "=" + true; dbConnection.ExecuteNonQuery(SqlQuery); } } catch (Exception ex) { throw ex; } }
public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType) { try { dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString())); } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
public static int CreateDISearchResultsTable(DIConnection dbConnection, string languageCode) { int RetVal = -1; StringBuilder SBQry = new StringBuilder(); string TblName = string.Empty; try { TblName = QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode; dbConnection.DropTable(TblName); SBQry.Append("Create Table " + TblName); SBQry.Append("("); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.NId + "] COUNTER, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + "] Text(50) NOT NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + "] YESNO NOT NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + "] Text(255) NOT NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Unit + "] Text(128) NOT NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Area + "] Text(255) NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + "] Text(255) NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.MRD + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + "] Text(255) NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + "] Text NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IsBlockAreaRecord + "] YESNO"); SBQry.Append(")"); RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString()); } catch (Exception) { throw; } return RetVal; }
/// <summary> /// Create and insert into TmpDI7SearchAreas table /// </summary> /// <param name="dbConnection"></param> /// <param name="searchAreas"></param> /// <param name="isSearchForQS"></param> public static void CreateTmpAreaSearchTbl(DIConnection dbConnection, string searchAreas, bool isSearchForQS) { StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; string SearchLanguage = string.Empty; try { #region "-- Create table schema --" if (isSearchForQS) { CacheUtility.CreateSearchAreasTable(dbConnection, false); } else { CacheUtility.CreateSearchAreasTable(dbConnection, true); } #endregion if (!string.IsNullOrEmpty(searchAreas)) { CacheUtility.GetSplittedList(dbConnection, searchAreas, ",", false); SBQry.Remove(0, SBQry.Length); SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.SearchAreas.TableName); SBQry.Append(" SELECT List." + QDSConstants.QDSTables.SplittedList.Columns.Value + " as AreaNId"); SBQry.Append(" FROM " + QDSConstants.QDSTables.SplittedList.TableName + " List"); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); } } catch (Exception) { throw; } }
public bool CreateMetadataFromSDMX(string templateFileName,string tempFolderPath, string languageCode,string sdmxRegistryUrl) { bool RetVal = true; DIConnection DBConnection = null; DIQueries DBQueries; try { //create temp template file DIDatabase TempTemplateFile = new DIDatabase(templateFileName, "UT_", languageCode); // create database object DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), languageCode); // delete default categories from blank template DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Delete.DeleteMetadataCategory(DBQueries.TablesName.MetadataCategory, string.Empty)); // import data from SDMX BaseSDMXHelper HelperObj = new BaseSDMXHelper(DBConnection, DBQueries); string TempXmlFile = tempFolderPath + "\\IMPORT_MSDXML_" + this.GetDateTimeStamp() + DICommon.FileExtension.XML; HelperObj.GenerateMetadataXMLFromSDMXWebservice(sdmxRegistryUrl, TempXmlFile); } catch (Exception ex) { RetVal = false; } finally { // dispose database object if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
public static int CreateSplittedListTable(DIConnection dbConnection, bool isNumericValues) { int RetVal = -1; StringBuilder SBQry = new StringBuilder(); string TblName = string.Empty; string ColumnDataType = "Text(255)"; try { TblName = QDSConstants.QDSTables.SplittedList.TableName; dbConnection.DropTable(TblName); if (isNumericValues) { ColumnDataType = "Long"; } SBQry.Append("Create Table " + TblName); SBQry.Append("("); SBQry.Append("[" + QDSConstants.QDSTables.SplittedList.Columns.Value + "] " + ColumnDataType); SBQry.Append(")"); RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString()); } catch (Exception) { throw; } return RetVal; }
public static int CreateTempMRDRecordsTable(DIConnection dbConnection) { int RetVal = -1; StringBuilder SBQry = new StringBuilder(); string TblName = string.Empty; try { TblName = QDSConstants.QDSTables.TempMRDRecords.TableName; dbConnection.DropTable(TblName); SBQry.Append("Create Table " + TblName); SBQry.Append("("); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + "] Text(255) NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount + "] Long NULL, "); SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + "] Text(255) NULL "); SBQry.Append(")"); RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString()); } catch (Exception) { throw; } return RetVal; }
public static int CreateSearchIndicatorsTable(DIConnection dbConnection) { int RetVal = -1; StringBuilder SBQry = new StringBuilder(); string TblName = string.Empty; try { TblName = QDSConstants.QDSTables.SearchIndicators.TableName; dbConnection.DropTable(TblName); SBQry.Append("Create Table " + TblName); SBQry.Append("("); SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + "] Long"); SBQry.Append(")"); RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString()); } catch (Exception) { throw; } return RetVal; }
public static int CreateSearchIndicatorsTable1(DIConnection dbConnection) { int RetVal = -1; StringBuilder SBQry = new StringBuilder(); string TblName = string.Empty; try { TblName = QDSConstants.QDSTables.SearchIndicators.TableName; dbConnection.DropTable(TblName); SBQry.Append("Create Table " + TblName); SBQry.Append("("); SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.Id + "] COUNTER CONSTRAINT PKey PRIMARY KEY, "); SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + "] Long DEFAULT 0, "); SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorName + "] Varchar(255) DEFAULT '' NOT NULL , "); SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.ICName + "] Varchar DEFAULT '' NOT NULL "); SBQry.Append(")"); RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString()); } catch (Exception) { throw; } return RetVal; }
/// <summary> /// Creates Icon tables for all available language /// </summary> /// <param name="dbConnection"></param> /// <param name="dbQueries"></param> /// <param name="forOnlineDB"></param> public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB) { if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false) { try { //-- create Icon table dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB)); } catch (Exception ex) { throw new ApplicationException(ex.Message); } } }
/// <summary> /// Create and insert into TmpDI7SearchIndicators table /// </summary> /// <param name="dbConnection"></param> /// <param name="tableNames"></param> /// <param name="searchIndicators"></param> /// <param name="isSearchForQS"></param> public static void CreateTmpIndSearchTbl(DIConnection dbConnection, DITables tableNames, string searchIndicators, bool isSearchForQS) { StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; string SearchLanguage = string.Empty; try { CacheUtility.CreateSearchIndicatorsTable(dbConnection); if (!string.IsNullOrEmpty(searchIndicators)) { CacheUtility.GetSplittedList(dbConnection, searchIndicators, ",", true); SBQry.Remove(0, SBQry.Length); SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.SearchIndicators.TableName); SBQry.Append(" (" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + ")"); SBQry.Append(" SELECT Ind."+ Indicator.IndicatorNId +" As IndicatorNId FROM "+ tableNames.Indicator +" As Ind"); SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SplittedList.TableName + " L"); SBQry.Append(" ON Ind." + Indicator.IndicatorNId + " = L." + QDSConstants.QDSTables.SplittedList.Columns.Value); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); } } catch (Exception) { throw; } }
public static void DeleteTempTables(DIConnection connection) { try { connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Indicator); connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Unit); connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Subgroup); connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.SubgroupType); connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.SubgroupVals); connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Area); connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.IndicatorClassification); } catch (Exception) { } }
/// <summary> /// Create new table schema of DI_Search_Result table /// </summary> /// <param name="dbConnection"></param> /// <param name="tableName"></param> /// <param name="languageCode"></param> /// <returns></returns> private static int CreateSearchResultCopyTable(DIConnection dbConnection, string tableName, string languageCode) { int RetVal = -1; StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; try { dbConnection.DropTable(tableName); SBQry.Remove(0, SBQry.Length); SBQry.Append("SELECT * INTO " + tableName); SBQry.Append(" FROM " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode); SBQry.Append(" WHERE 1=2"); StrQry = SBQry.ToString(); RetVal = dbConnection.ExecuteNonQuery(StrQry); } catch (Exception) { } return RetVal; }
private void UpdateMRDTable(DIConnection ObjDIConnection) { string Query = string.Empty; string TempTableName = "temp1"; string DTData = ObjDIConnection.DIDataSetDefault() + "Data"; // 1. drop temp1 table try { ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName); } catch (Exception) { } // 2. Create Temp1 table for IsMRD calculation Query = "Select MRDTable.*, T2.Timeperiod_nid into " + TempTableName + " from ( SELECT d.IUSNId, d.Area_NId, MAX(t.TimePeriod) AS timeperiod FROM " + ObjDIConnection.DIDataSetDefault() + "Data" + " d," + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " t WHERE d.TimePeriod_NId= t.TimePeriod_NId GROUP BY d.IUSNId,d.Area_NId) AS MRDTable , " + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " T2 where MRDTable.timeperiod=T2.Timeperiod"; ObjDIConnection.ExecuteNonQuery(Query); // 3. set IsMrd to false in data table Query = "UPDATE " + ObjDIConnection.DIDataSetDefault() + "Data" + " SET IsMRD=0, MultipleSource=0"; ObjDIConnection.ExecuteNonQuery(Query); // 4. update IsMrd in DataTable Query = "UPDATE " + DTData + " SET " + DTData + ".IsMRD=1, " + DTData + ".MultipleSource=1 FROM temp1 INNER JOIN " + DTData + " ON temp1.IUSNId = " + DTData + ".IUSNId AND temp1.Timeperiod_nid = " + DTData + ".TimePeriod_NId AND temp1.Area_NId = " + DTData + ".Area_NId"; ObjDIConnection.ExecuteNonQuery(Query); //4. drop table ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName); }
// this method is used for creating cms database if database not exist /// <summary> /// Create CMS database for adaptation If Database Not exise /// </summary> /// <param name="CMSDatabaseName">Name of the created Cms Database</param> /// <returns>True if database created or already exist</returns> public static bool CheckNCreateCMSDatabase(out string CMSDatabaseName) { bool RetVal = false; DIConnection ObjDIConnection = null; FileInfo ScriptFile; string DbScripts = string.Empty; string LngDbScripts = string.Empty; string DBConn = string.Empty; string[] DBConnArr; string DbNId = string.Empty; string Password = string.Empty; string DatabaseName = string.Empty; // Name of Adaptation Database CMSDatabaseName = string.Empty; // Name of CMS Database string DatabasePath = string.Empty; try { // Get Database NID of Default Database DbNId = Global.GetDefaultDbNId(); // If Database NID is null or empty break further execution of code if (string.IsNullOrEmpty(DbNId)) { RetVal = false; return RetVal; } //Get connection details of database DBConnArr = Global.GetDbNConnectionDetails(DbNId, string.Empty); // If Database returned database details are less than 6 break further execution of code if (DBConnArr.Length < 6) { RetVal = false; return RetVal; } //Get Decrypted password Password = DBConnArr[6].ToString(); //Get Adaptation Database name DatabaseName = DBConnArr[4].ToString(); //Name of CMS Database CMSDatabaseName = DatabaseName + "_CMS"; // Create Database connection ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(DBConnArr[2])), DBConnArr[3].ToString(), string.Empty, DatabaseName, DBConnArr[5].ToString(), Password); //Create and Execute database creation script ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Global.CmsDbCreationFile)); // If Script File not exist in the folder break further execution of code if (!ScriptFile.Exists) { RetVal = false; return RetVal; } // get path of database file from web config file DatabasePath = Global.CmsDataBasePath;// ConfigurationManager.AppSettings[Constants.WebConfigKey.CmsDataBasePath].ToString(); // This Script file contains sql syntax to creates database, if database is not already existing in database // Replace DBName with new database name DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("DBName", CMSDatabaseName).Replace("GO", ""); DbScripts = DbScripts.Replace("CmsDataBasePath", DatabasePath); // Execute script file to check and create database schema ObjDIConnection.ExecuteNonQuery(DbScripts); // Close file object ScriptFile.OpenText().Close(); //Create and Execute database tables creation script ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Global.CmsTableDefinition)); // If Script File not exist in the folder break further execution of code if (!ScriptFile.Exists) { RetVal = false; return RetVal; } // This Script file contains sql syntax to creates datatables, if datatables are not already existing in database DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("DBName", CMSDatabaseName);//.Replace("GO", "\nGO"); if (DbScripts.Contains("--GO")) { List<string> ListQuery = new List<string>(); ListQuery.AddRange(Global.SplitString(DbScripts, "--GO")); foreach (string StrQuery in ListQuery) { // Execute script file to create database Tables ObjDIConnection.ExecuteNonQuery(StrQuery); } } else { ObjDIConnection.ExecuteNonQuery(DbScripts); } // Close file object ScriptFile.OpenText().Close(); // Set Retval to true RetVal = true; // Dispose connection object ObjDIConnection.Dispose(); } catch (Exception ex) { RetVal = false; Global.CreateExceptionString(ex, null); } finally { // dispose connection object ObjDIConnection = null; ScriptFile = null; } return RetVal; }
///// <summary> ///// Get all available language code from database ///// </summary> ///// <param name="diConnection"></param> ///// <returns></returns> //private string[] GetAllAvailableLanguageCode(DIConnection diConnection) //{ // string[] RetVal; // DataTable DTAvailableLanguages = null; // List<string> AvlLanguages = new List<string>(); // try // { // DTAvailableLanguages = diConnection.DILanguages(diConnection.DIDataSetDefault()); // foreach (DataRow Row in DTAvailableLanguages.Rows) // { // AvlLanguages.Add(Row["Language_Code"].ToString()); // } // } // catch (Exception) // { // } // RetVal = AvlLanguages.ToArray(); // return RetVal; //} /// <summary> /// Run dbscripts in database from script files /// </summary> /// <param name="ObjDIConnection"></param> /// <returns></returns> private Boolean RunDBScripts(DIConnection ObjDIConnection, string DBNid, string DbDefaultLanguage) { Boolean RetVal = false; FileInfo ScriptFile; string DbScripts = string.Empty; string LngFile = string.Empty; string LngCode = string.Empty; string LngDbScripts = string.Empty; int DefaultDBNid = 0; DataTable DatabaseLanguages = null; try { //Create and Execute table definition scripts ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.TableDefinitionsFile])); DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", ""); ObjDIConnection.ExecuteNonQuery(DbScripts); ScriptFile.OpenText().Close(); //Create and Execute db language scrpts ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DbScriptsCreationFile])); DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", ""); if (DBNid.Trim() != "") { DefaultDBNid = Convert.ToInt32(DBNid); } else { DefaultDBNid = Convert.ToInt32(Global.GetDefaultDbNId()); } DatabaseLanguages = Global.GetAllDBLangaugeCodesByDbNid(DefaultDBNid, ObjDIConnection); foreach (DataRow langRow in DatabaseLanguages.Rows) { LngDbScripts = DbScripts; LngDbScripts = LngDbScripts.Replace("_XX", "_" + langRow["Language_Code"]); foreach (string LngDbScriptBlock in Global.SplitString(LngDbScripts, "--SPSEPARATOR--")) { try { ObjDIConnection.ExecuteNonQuery(LngDbScriptBlock); } catch (Exception ex) { Global.CreateExceptionString(ex, null); } } } ScriptFile.OpenText().Close(); ////Create and Execute db language scrpts //DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", ""); //foreach (string lang in getAllDbLangCodes(Convert.ToInt32(Global.GetDefaultDbNId()))) //{ // LngDbScripts = DbScripts; // LngDbScripts = LngDbScripts.Replace("_XX", "_" + lang); // foreach (string LngDbScriptBlock in Global.SplitString(LngDbScripts, "--SPSEPARATOR--")) // { // try // { // ObjDIConnection.ExecuteNonQuery(LngDbScriptBlock); // } // catch (Exception ex) // { // Global.CreateExceptionString(ex, null); // } // } //} //ScriptFile.OpenText().Close(); //Create and Execute table indexs scripts ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DbTableIndexes])); DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", ""); ObjDIConnection.ExecuteNonQuery(DbScripts); ScriptFile.OpenText().Close(); RetVal = true; // Execute method to synchronize language files from ut_language this.SynchronizeLanguage(ObjDIConnection, DefaultDBNid, DbDefaultLanguage); this.ReverseSynchronizeLanguage(ObjDIConnection, DefaultDBNid); } catch (Exception ex) { Global.CreateExceptionString(ex, null); } finally { } 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> /// Store mapping information /// </summary> /// <param name="mappingInfo">string containing unmatched list & mapping ids</param> /// <param name="type">which type of list(aid or aname)</param> public string storeMappingDetails(string mappingInfo, string type) { DIConnection dIConnection = null; try { string delemeter1 = "{||}"; string delemeter2 = "{}"; string[] mappingAreaInfo = mappingInfo.Split(new string[] { delemeter1 }, StringSplitOptions.None); string mapping_nid = string.Empty; string areaName = string.Empty; DataTable dtKeyword = null; string query = string.Empty; string UserDefinedKey = string.Empty; string MappingKey = string.Empty; dIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"), string.Empty, string.Empty); bool usingMapServer = false;//Convert.ToBoolean(isMapServer.Trim()); if (Session["IsMapServer"] != null) { usingMapServer = Convert.ToBoolean(Session["IsMapServer"]); } foreach (string mappingArea in mappingAreaInfo) { if (mappingArea.IndexOf(delemeter2) > -1) { string[] areaData = mappingArea.Split(new string[] { delemeter2 }, StringSplitOptions.None); UserDefinedKey = MappingKey = ""; if (areaData != null) { UserDefinedKey = areaData[0].Trim(); MappingKey = areaData[1].Trim(); } if (UserDefinedKey.IndexOf("'") > -1) UserDefinedKey = UserDefinedKey.Replace("'", "''"); query = "select * from mappinginformation where user_key = '" + UserDefinedKey + "' and mapping_key='" + MappingKey + "' and type ='" + type + "' and mapserver_used ='" + usingMapServer.ToString() + "'"; dtKeyword = dIConnection.ExecuteDataTable(query); if (dtKeyword.Rows.Count == 0) // If record doesn't exist into table { if (!string.IsNullOrEmpty(MappingKey)) { query = "insert into mappinginformation(user_key,mapping_key,type,item_count,mapserver_used) values('" + UserDefinedKey + "','" + MappingKey + "','" + type + "',0,'" + usingMapServer.ToString() + "')"; dIConnection.ExecuteNonQuery(query); } } query = "select * from mappinginformation where user_key = '" + UserDefinedKey + "' and mapping_key='" + MappingKey + "' and type ='" + type + "'"; dtKeyword = dIConnection.ExecuteDataTable(query); foreach (DataRow dr in dtKeyword.Rows) { if (!string.IsNullOrEmpty(dr["mapping_key"].ToString())) { int count = Int32.Parse(dr["item_count"].ToString()); count++; query = "update mappinginformation set item_count=" + count + " where user_key = '" + UserDefinedKey + "' and mapping_key='" + MappingKey + "' and type ='" + type + "' and mapserver_used ='" + usingMapServer.ToString() + "'"; dIConnection.ExecuteNonQuery(query); } } } } dIConnection.Dispose(); } catch (Exception ex) { dIConnection.Dispose(); Global.CreateExceptionString(ex, null); } return string.Empty; }
public static int DeleteTableRecords(DIConnection dbConnection, string tableName) { int RetVal = -1; string StrQry = string.Empty; try { StrQry = "Delete * from " + tableName; RetVal = dbConnection.ExecuteNonQuery(StrQry); } catch (Exception) { } return RetVal; }
private int GetClassNId(DIConnection DBConnection, DIQueries DBQueries, out bool bNewClass, String classGUID, Boolean SectorGlobal, int iParentNId, String className) { int RetVal = 0; string sQry = string.Empty; Boolean NewClass = false; try { //-- Getting Query from DAL sQry = DBQueries.Calculates.GetICNIdByGId("'" + classGUID + "'"); //-- Executing Query RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (RetVal == 0) { NewClass = true; // -- Getting insert Query sQry = DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(DBQueries.DataPrefix, DBQueries.LanguageCode, className.Replace("'", "''"), classGUID, SectorGlobal, iParentNId, "", ICType.Sector); //-- Using DAL for query execution and getting identity value DBConnection.ExecuteNonQuery(sQry); RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } } catch (Exception ex) { RetVal = 0; } bNewClass = NewClass; return RetVal; }
public static void GetSplittedList(DIConnection dbConnection, string strDelimitedText, string delimiter, bool isNumericValues) { //-- FN_GET_SPLITTED_LIST string[] TxtArr; string StrQry = string.Empty; try { if (!string.IsNullOrEmpty(strDelimitedText)) { CreateSplittedListTable(dbConnection, isNumericValues); //-- Split delimited text into array TxtArr = DICommon.SplitString(strDelimitedText, delimiter); //-- Insert each text into table foreach (string Txt in TxtArr) { if (!string.IsNullOrEmpty(Txt)) { StrQry = "Insert into " + QDSConstants.QDSTables.SplittedList.TableName; if (isNumericValues) { StrQry += " values (" + Txt + ")"; } else { StrQry += " values ('" + Txt + "')"; } dbConnection.ExecuteNonQuery(StrQry); } } } } catch (Exception) { throw; } }
/// <summary> /// Add langPrefNid (INT) table to the Artefacts table in database.mdb /// </summary> /// <returns>bool</returns> public static bool BaselineAccessDbSchema() { DIConnection diConnection; bool retVal = false; bool IsColumnExists = false; string query = string.Empty; using (diConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, @"stock\Database.mdb"), string.Empty, string.Empty)) { try { // Check if field exists to avoid exception try { query = @" ALTER TABLE Artefacts ADD COLUMN LangPrefNid INT"; diConnection.ExecuteNonQuery(query); } catch { IsColumnExists = true; } //if (IsColumnExists == false) //{ // query = @" ALTER TABLE Artefacts ADD COLUMN LangPrefNid INT"; // diConnection.ExecuteNonQuery(query); //} retVal = true; } catch (Exception ex) //catch specific exception by refering to the DIConnectino source. { Global.CreateExceptionString(ex, null); throw ex; } } return retVal; }
/// <summary> /// Create cache result of blocks /// </summary> /// <param name="dbConnection"></param> /// <param name="languageCode"></param> private void CreateCacheResultsForBlock(DIConnection dbConnection, string languageCode, DITables tableNames) { StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; int ProgressCount = 29; try { #region "-- Block --" // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Area block - get area where block exists SBQry.Remove(0, SBQry.Length); SBQry.Append("SELECT " + Area.AreaNId + ", " + Area.AreaBlock); SBQry.Append(" FROM " + tableNames.Area ); SBQry.Append(" WHERE " + Area.AreaBlock + " is not null and " + Area.AreaBlock + "<>''"); StrQry = SBQry.ToString(); DataTable DtAreaBlocks = dbConnection.ExecuteDataTable(StrQry); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Create BlockAreaResults table CacheUtility.CreateBlockAreaResultsTable(dbConnection, languageCode); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); if (DtAreaBlocks.Rows.Count > 0) { foreach (DataRow Row in DtAreaBlocks.Rows) { string AreaNId = Convert.ToString(Row[Area.AreaNId]); string AreaBlock = Convert.ToString(Row[Area.AreaBlock]); //-- Area block - insert record into block area results SBQry.Remove(0, SBQry.Length); SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.BlockAreaResults.TableName); SBQry.Append(" ( " + QDSConstants.QDSTables.BlockAreaResults.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.UnitNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorName + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.Unit + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.Area + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DefaultSG + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.MRDTP + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.MRD + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.SGCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.TPCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DVCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNIds + ", "); SBQry.Append(QDSConstants.QDSTables.BlockAreaResults.Columns.SGNIds + " , " + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceNIds + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.TPNIds + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DVNIds + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DVSeries + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.Dimensions + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IUSNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaParentNId + " )"); SBQry.Append(" SELECT D." + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ","); SBQry.Append(" D." + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + ","); SBQry.Append(" D." + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + AreaNId); SBQry.Append(" FROM " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " AS D"); SBQry.Append(" WHERE d." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + " in (" + AreaBlock + ")"); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); } } // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Area block - Insert into di search results table SBQry.Remove(0, SBQry.Length); SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode); SBQry.Append(" ( " + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + ", "); SBQry.Append(QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + " , " + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + ", " + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsBlockAreaRecord + " )"); SBQry.Append(" SELECT D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SearchLanguage + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.UnitNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IsAreaNumeric + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorName + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.Unit + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.Area + ","); SBQry.Append(" D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DefaultSG + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.MRDTP + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.MRD + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SGCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.TPCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DVCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SGNIds + ","); SBQry.Append(" D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.TPNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DVNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DVSeries + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.Dimensions + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.BlockAreaParentNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IUSNId + ", d." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaParentNId + ", -1"); SBQry.Append(" FROM " + QDSConstants.QDSTables.BlockAreaResults.TableName + " AS D"); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); #endregion } catch (Exception ex) { throw ex; } }
/// <summary> /// Dictionary containing old IconNId as key and new IconNId as value. /// This information shall be utilised to update metadata xml being inserted / updated /// </summary> /// <param name="NidInSourceDB"></param> /// <param name="NidInTargetDB"></param> /// <param name="elementType"></param> /// <param name="sourceQurey"></param> /// <param name="SourceDBConnection"></param> /// <param name="targetQurey"></param> /// <param name="TargetDBConnection"></param> /// <returns> /// </returns> /// <remarks></remarks> public static Dictionary<string, string> ImportElement(int NidInSourceDB, int NidInTargetDB, IconElementType elementType, DIQueries sourceQurey, DIConnection sourceDBConnection, DIQueries targetQurey, DIConnection targetDBConnection) { Dictionary<string, string> RetVal = new Dictionary<string, string>(); string ElementValue = DIIcons.Elements[elementType]; string SqlQuery = string.Empty; DataTable IconsDatatable; string OldIconNId = string.Empty; string NewIconNId = string.Empty; try { if ((sourceDBConnection != null)) { if (DIIcons.IsIconsTblExists(sourceQurey.TablesName.Icons, sourceDBConnection)) { //-- In Target Database: delete records from UT_Icon table if Icon is already associated with given Element Type SqlQuery=DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.DeleteIcon(targetQurey.DataPrefix,ElementValue, NidInTargetDB.ToString()); targetDBConnection.ExecuteNonQuery(SqlQuery); //-- In Source Database: check Icon is associated with the given Element type in UT_ICon table SqlQuery = sourceQurey.Icon.GetIcon(NidInSourceDB.ToString(), ElementValue); IconsDatatable = sourceDBConnection.ExecuteDataTable(SqlQuery); //-- If associated, then copy it it from Source database into target database foreach(DataRow Row in IconsDatatable.Rows) { //-- Insert Icon and get new IconNId NewIconNId = DIIcons.InsertIcon(targetDBConnection, targetQurey, (byte[])(Row["Element_Icon"]), Row["Icon_Type"].ToString(), Convert.ToInt32(Row["Icon_Dim_W"]), Convert.ToInt32(Row["Icon_Dim_H"]), ElementValue,Convert.ToString( NidInTargetDB)).ToString(); //-- Add Item to Dictionary with New IconNId as Key and Old IconNId as Value RetVal.Add(IMG_PREFIX + Row["Icon_NId"].ToString() + ".", IMG_PREFIX + NewIconNId + "."); } } } } catch (Exception ex) { throw new ApplicationException(ex.Message); } 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> /// Create cache result for DISearchResults /// </summary> /// <param name="dbConnection"></param> /// <param name="languageCode"></param> private void CreateCacheResultsForDISearchResults(DIConnection dbConnection, string languageCode, DITables tableNames) { StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; int ProgressCount = 24; try { #region "-- Create cache table (DI_Search_Results) --" //-- Create DI_Search_Result table for using in cache generation CacheUtility.CreateDISearchResultsTable(dbConnection, languageCode); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Delete all record CacheUtility.DeleteTableRecords(dbConnection, QDSConstants.QDSTables.TempMRDRecords.TableName); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Get MRD records and create tempMRDRecords table SBQry.Remove(0, SBQry.Length); SBQry.Append("Insert Into " + QDSConstants.QDSTables.TempMRDRecords.TableName); SBQry.Append(" (" + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount + " )"); SBQry.Append(" SELECT " + Data.IUSNId + ", D." + Data.IndicatorNId + ", D." + Data.UnitNId + ", D." + Data.SubgroupValNId + ", " + Data.AreaNId + ", MAX(D." + QDSConstants.QDSTables.Data.Columns.TimePeriod + ") AS " + QDSConstants.QDSTables.Data.Columns.TimePeriod + ","); SBQry.Append(" Count(*) AS " + QDSConstants.QDSTables.Data.Columns.DVCount); SBQry.Append(" FROM " + tableNames.Data + " AS D"); SBQry.Append(" WHERE D."+ QDSConstants.QDSTables.Data.Columns.ISDefaultSG +" = -1"); SBQry.Append(" GROUP BY D."+ Data.IUSNId +", D."+ Data.AreaNId +", D."+ Data.IndicatorNId +", D."+ Data.UnitNId +", D." + Data.SubgroupValNId); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Update DataValue into TempMRDRecords table using data table SBQry.Remove(0, SBQry.Length); SBQry.Append("UPDATE "+ tableNames.Data +" AS d1 INNER JOIN " + QDSConstants.QDSTables.TempMRDRecords.TableName + " AS t1"); SBQry.Append(" ON (d1." + QDSConstants.QDSTables.Data.Columns.TimePeriod + "=t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + ")"); SBQry.Append(" AND (d1." + Data.IUSNId + "=t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + ") AND (D1." + Data.AreaNId + " = t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ")"); SBQry.Append(" SET t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + " = d1." + Data.DataValue); SBQry.Append(" WHERE d1.isdefaultSG = -1"); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- Insert records into DISearchResults table SBQry.Remove(0, SBQry.Length); SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode); SBQry.Append(" (" + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ")"); SBQry.Append(" SELECT '" + languageCode + "', D." + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ", -1 AS Expr1, D." + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + ", t." + Timeperiods.TimePeriodNId + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + ","); SBQry.Append(" d." + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + " & '[@@@@]' & SG."+ SubgroupVals.SubgroupVal + " AS Expr2, I."+ Indicator.IndicatorName +", U."+ Unit.UnitName +", A."+ Area.AreaName +","); SBQry.Append(" A.area_parent_nid, D." + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount); SBQry.Append(" FROM " + tableNames.TimePeriod + " AS t"); SBQry.Append(" INNER JOIN (((" + tableNames.Unit + " AS U INNER JOIN (" + tableNames.Area + " AS A"); SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.TempMRDRecords.TableName + " AS D ON A."+ Area.AreaNId +" = D." + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ")"); SBQry.Append(" ON U."+ Unit.UnitNId +" = D." + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + ") INNER JOIN " + tableNames.SubgroupVals + " AS SG ON D." + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + " = SG." + SubgroupVals.SubgroupValNId + ")"); SBQry.Append(" INNER JOIN " + tableNames.Indicator + " AS I ON D." + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + " = I."+ Indicator.IndicatorNId +")"); SBQry.Append(" ON t."+ Timeperiods.TimePeriod +" = D." + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); #endregion } catch (Exception ex) { throw ex; } }
/// <summary> /// Get SectorNId /// </summary> /// <param name="DBConnection"></param> /// <param name="DBQueries"></param> /// <param name="bNewSector"></param> /// <returns></returns> private int GetSectorNId(DIConnection DBConnection, DIQueries DBQueries, out bool bNewSector, out Boolean bSectorGlobal) { int RetVal = 0; string sQry = string.Empty; Boolean SectorGlobal = false; Boolean NewSector = false; try { sQry = DBQueries.Calculates.GetICNIdByGId("'" + this.DESheetInformation.SectorGUID + "'"); //-- Executing Query RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry)); if (RetVal == 0) { NewSector = true; if (string.IsNullOrEmpty(this.DESheetInformation.SectorGlobal)) { } else { SectorGlobal = Convert.ToBoolean(this.DESheetInformation.SectorGlobal); } sQry = DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(DBQueries.DataPrefix, DBQueries.LanguageCode, this.DESheetInformation.SectorName.Replace("'", "''"), this.DESheetInformation.IndicatorGUID, SectorGlobal, -1, "", ICType.Sector); //-- Using DAL for query execution and getting identity value DBConnection.ExecuteNonQuery(sQry); RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } } catch (Exception ex) { RetVal = 0; } bSectorGlobal = SectorGlobal; bNewSector = NewSector; return RetVal; }
/// <summary> /// Create cache results for levels /// </summary> /// <param name="dbConnection"></param> /// <param name="languageCode"></param> private void CreateCacheResultsForLevel(DIConnection dbConnection, string languageCode, DITables tableNames) { StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; int AreaLevel = 4; int ProgressCount = 33; try { #region "-- Quick Search (level) --" // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- 1 Add TempColumn column into Area table StrQry = "Alter table " + tableNames.Area + " add column " + QDSConstants.QDSTables.Area.Columns.TempColumn + " varchar"; dbConnection.ExecuteNonQuery(StrQry); //-- 2 Get area levels StrQry = "Select count(*) from " + tableNames.AreaLevel; AreaLevel = (int)dbConnection.ExecuteScalarSqlQuery(StrQry); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); if (AreaLevel < 4) { AreaLevel = 4; } //-- 3 Update query and execute it for (int i = 2; i <= AreaLevel; i++) { StrQry = GetUpdateAreaTempColumnQry(i, languageCode, tableNames); dbConnection.ExecuteNonQuery(StrQry); } // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- 4. Update into table DI_Search_Results of AreaNIds column with TempComumn column from ut_Area_xx table SBQry.Remove(0, SBQry.Length); SBQry.Append("Update " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " as R"); SBQry.Append(" Inner Join "+ tableNames.Area + " as A"); SBQry.Append(" On R." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + " = A." + Area.AreaNId); SBQry.Append(" And R."+ QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + " = A." + Area.AreaParentNId); SBQry.Append(" Set R." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + " = A.TempColumn"); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); // increment progress bar value this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false); //-- 5 Drop TempColumn column from Area table StrQry = "Alter table " + tableNames.Area + " Drop Column " + QDSConstants.QDSTables.Area.Columns.TempColumn; dbConnection.ExecuteNonQuery(StrQry); #endregion } catch (Exception ex) { throw ex; } }
private void DeleteTable(string tableName, DIConnection dbConnection) { try { dbConnection.ExecuteNonQuery(" Drop Table " + tableName); } catch { } }
/// <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; } }