/// <summary> /// Add PublishedFileName(Text) table to the Artefacts table in database.mdb /// </summary> /// <returns>bool</returns> public static bool ExistenceofColumnAccessDbSchema() { 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 { //// MOD LOG //// Issue Id:BUG 7 Issue Date: 07-May-2014 //// Issue Fixed by:[email protected] //// Description: Exception capturing was used to figure out database colmun existance. Fixed to check for column exist before running the query to create column PublishedFileName. query = @" SELECT Top 1 * FROM Artefacts"; DataTable table = diConnection.ExecuteDataTable(query); if (table.Columns.IndexOf("PublishedFileName") < 0) { query = @" ALTER TABLE Artefacts ADD COLUMN PublishedFileName Text(225)"; var result = diConnection.ExecuteScalarSqlQuery(query); } IsColumnExists = true; } catch { IsColumnExists = true; } retVal = true; } catch (Exception ex) //catch specific exception by refering to the DIConnectino source. { Global.CreateExceptionString(ex, null); throw ex; } } 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; }
/// <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> /// Get path for selected area starting from root area /// </summary> /// <param name="ElementNid">ID of selected Data</param> /// <param name="DI_QueryBase">This contains Connection Details</param> /// <returns>String containg the path of area node starting from root node</returns> public static string GetAreaChain(int AreaNId, DIConnection DIConnection, DIQueries DIQueries) { string RetVal = string.Empty; string sSql = string.Empty; int AreaLevel = 1; // Get Current Area Level sSql = DIQueries.Area.GetArea(FilterFieldType.NId, AreaNId.ToString()); DataTable dtArea = DIConnection.ExecuteDataTable(sSql); if (dtArea != null & dtArea.Rows.Count > 0) { AreaLevel = (int)dtArea.Rows[0][Area.AreaLevel]; } //Get concatinated Area names sSql = DIQueries.Area.GetAreaChain(AreaNId, AreaLevel, DIConnection.ConnectionStringParameters.ServerType); RetVal = DIConnection.ExecuteScalarSqlQuery(sSql).ToString(); 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> /// Get the area level name /// </summary> /// <param name="dbConnection"></param> /// <param name="tableNames"></param> /// <param name="AreaNId"></param> /// <returns></returns> public static string GetAreaLevelName(DIConnection dbConnection, DITables tableNames, string AreaNId) { string RetVal = string.Empty; StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; try { SBQry.Append("SELECT L." + Area_Level.AreaLevelName); SBQry.Append(" FROM "+ QDSConstants.QDSTables.NewParentTable.TableName +" AS P "); SBQry.Append(" INNER JOIN ("+ tableNames.AreaLevel +" AS L INNER JOIN "+ tableNames.Area +" AS A ON L."+ Area_Level.AreaLevel +" = A."+Area.AreaLevel + "+1) "); SBQry.Append(" ON P." + QDSConstants.QDSTables.NewParentTable.Columns.AreaNId + " = A." + Area.AreaNId); SBQry.Append(" where cstr(A." + Area.AreaNId + ") = " + AreaNId); StrQry = SBQry.ToString(); RetVal = Convert.ToString(dbConnection.ExecuteScalarSqlQuery(StrQry)); } 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; }
private bool IsNotesTblExists(ref DIConnection oDT_DB, ref DIQueries oDA_Queries) { string sTblName = ""; bool bReturn = true; string sTablePrefix = ""; //-- check tables exists in source database try { sTablePrefix = oDT_DB.ExecuteScalarSqlQuery(this.NotesQuery.Import_Prefix()).ToString() + "_"; //-- Get all Languages DataView oDV = oDT_DB.ExecuteDataTable(this.NotesQuery.GetDefaultLanguages( false, sTablePrefix)).DefaultView; if (oDV.Count > 0) { //-- Check availability of table sTblName = sTablePrefix + "Notes_" + oDV[0]["Language_Code"].ToString(); try { if (oDT_DB.ExecuteScalarSqlQuery("SELECT count(*) FROM " + sTblName + " WHERE 1=1") == null) { bReturn = false; goto endoffunc_; } } catch (Exception ) { bReturn = false; goto endoffunc_; } bReturn = true; } } catch (Exception ) { bReturn = false; } endoffunc_: return bReturn; }
/// <summary> /// Returns the metadata Description of specified database along with IUS, Area, TimePeriod & Data Count. /// </summary> public static string GetDBMetaData(DIConnection dbConnection, DIQueries dbQueries) { string RetVal = string.Empty; string MaxAreaLevel = string.Empty; try { DataTable DTMeta = dbConnection.ExecuteDataTable(dbQueries.DBMetadata.GetDBMetadata()); //-- Get Maximum Area Level in Area Tables String AreaLevel = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetMaxValue(dbQueries.TablesName.Area, Area.AreaLevel, string.Empty)).ToString(); if (AreaLevel.Length > 0) { MaxAreaLevel = System.Int32.Parse(AreaLevel).ToString(); } if (DTMeta.Rows.Count > 0) { RetVal = DTMeta.Rows[0][DBMetaData.Description].ToString(); // IUS count if (DTMeta.Rows[0][DBMetaData.IUSCount].ToString() != null) { RetVal += "\n\n" + DILanguage.GetLanguageString("IUS") + " " + string.Format("{0:##,###}", DTMeta.Rows[0][DBMetaData.IUSCount]); } // Indicator RetVal += " " + DILanguage.GetLanguageString("INDICATOR") + " " + DTMeta.Rows[0][DBMetaData.IndicatorCount].ToString(); // Area if (DTMeta.Rows[0][DBMetaData.AreaCount].ToString() != null) { RetVal += " " + DILanguage.GetLanguageString("AREA") + " " + string.Format("{0:##,###}", DTMeta.Rows[0][DBMetaData.AreaCount]); } //-- AreaLevel if (MaxAreaLevel.Length > 0) { RetVal += " " + DILanguage.GetLanguageString("AREA_LEVEL") + " " + MaxAreaLevel; } // Time RetVal += " " + DILanguage.GetLanguageString("TIMEPERIOD") + " " + DTMeta.Rows[0][DBMetaData.TimeperiodCount].ToString(); // Source RetVal += " " + DILanguage.GetLanguageString("SOURCE") + " " + DTMeta.Rows[0][DBMetaData.SourceCount].ToString(); // Data count if (DTMeta.Rows[0][DBMetaData.DataCount].ToString() != null) { RetVal += " " + DILanguage.GetLanguageString("DATA") + " " + string.Format("{0:##,###}", DTMeta.Rows[0][DBMetaData.DataCount]); } //-- Get Last updated date from database table. (Column is "Pub_date") if (DTMeta.Rows[0][DBMetaData.PublisherDate] != null) { if (string.IsNullOrEmpty(DTMeta.Rows[0][DBMetaData.PublisherDate].ToString()) == false) { RetVal += Environment.NewLine + DILanguage.GetLanguageString("LAST_UPDATED") + " " + ((DateTime)(DTMeta.Rows[0][DBMetaData.PublisherDate])).ToShortDateString(); } } } else { // Get area count string AreaCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.Area, string.Empty)).ToString(); //// Get IUS count String IUSCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.IndicatorUnitSubgroup, string.Empty)).ToString(); if (IUSCount.Length > 0) { RetVal += DILanguage.GetLanguageString("IUS") + " " + string.Format("{0:##,###}", System.Int32.Parse(IUSCount)); } // Get Indicator count string IndicatorCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.Indicator, string.Empty)).ToString(); RetVal += " " + DILanguage.GetLanguageString("INDICATOR") + " " + IndicatorCount; // Area Count if (AreaCount.Length > 0) { RetVal += " " + DILanguage.GetLanguageString("AREA") + " " + string.Format("{0:##,###}", System.Int32.Parse(AreaCount)); } //-- AreaLevel if (MaxAreaLevel.Length > 0) { RetVal += " " + DILanguage.GetLanguageString("AREA_LEVEL") + " " + MaxAreaLevel; } // Get timeperiod count String TimeperiodCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.TimePeriod, string.Empty)).ToString(); RetVal += " " + DILanguage.GetLanguageString("TIMEPERIOD") + " " + TimeperiodCount; //// Get source count String SourceCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.IndicatorClassifications, IndicatorClassifications.ICType + " =" + DIQueries.ICTypeText[ICType.Source])).ToString(); RetVal += " " + DILanguage.GetLanguageString("SOURCE") + " " + SourceCount; //// Get data count String DataCount = dbConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(dbQueries.TablesName.Data, string.Empty)).ToString(); if (DataCount.Length > 0) { RetVal += " " + DILanguage.GetLanguageString("DATA") + " " + string.Format("{0:##,###}", System.Int32.Parse(DataCount)); } } RetVal = RetVal.Trim(); } catch { } return RetVal; }
private void CreateNotesTableForAllLngs(string sSrcDB, string sTRGTblPrefix, string sSrcDB_Prefix, ref DIConnection sourceDIConnection, ref DIQueries sourceDbQueries) { DataView dvLngs = null; string sLngCode = ""; try { //-- create common Notes table this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes_Data(sTRGTblPrefix)); this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes_profile(sTRGTblPrefix)); //-- Get all Languages from Imp_To.mdb(target Database) dvLngs = this._TargetDBConnection.ExecuteDataTable(this.NotesQuery.GetDefaultLanguages(false, sTRGTblPrefix)).DefaultView; foreach (DataRowView drvLng in dvLngs) { //-- check language exists in source database sLngCode = drvLng[Language.LanguageCode].ToString(); this.CreateSrcConnection(ref sourceDIConnection, sSrcDB); if ((int)sourceDIConnection.ExecuteScalarSqlQuery(this.NotesQuery.check_language_exists(sLngCode, "", false, sTRGTblPrefix)) > 0) { sLngCode = "_" + sLngCode; this.DisposeSrcConnection(ref sourceDIConnection); //-- create LngBsd Notes link tables this.CreateNotesLinkTables(sSrcDB, sSrcDB_Prefix, sLngCode); //-- Create LngBsd Notes tables : UT_Notes_en, UT_Notes_Classification_en this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes(sTRGTblPrefix, sLngCode)); this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Create_Notes_Classification(sTRGTblPrefix, sLngCode)); //-- Delete unmatched records from UT_Notes_en table this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_DeleteUnMatchedFrm_Notes(sTRGTblPrefix, sLngCode)); //-- drop link tables DropLngBasedNotesLinkTbl(); } } //-- drop _notes table this._TargetDBConnection.ExecuteNonQuery("Drop table _Notes"); } catch (Exception ) { } finally { if ((dvLngs != null)) dvLngs.Dispose(); dvLngs = null; } }
private void ImportNotes(string sourceDBFileNameWPath, ref DIConnection sourceDBconnection, ref DIQueries sourceDBQueries) { string TargetDBPrefix = ""; string sSrcDB_Prefix = ""; string SourceDBPrefix = sSrcDB_Prefix; DataView DVLngs = null; DataView DvSrc; string LanguageCode = ""; string TableName = ""; bool IsMainTableCreated = false; string sqlString = ""; bool IsNotesTblExistsInTarget = false; bool IsUpdatedNotesData = false; try { //sourceDBQueries.TablePrefix = sourceDBconnection.ExecuteScalarSqlQuery(this.NotesQuery.Import_Prefix()).ToString(); TargetDBPrefix = this._TargetDBConnection.DIDataSetDefault(); sSrcDB_Prefix = TargetDBPrefix; SourceDBPrefix = sSrcDB_Prefix; //-- Get all Languages from Imp_To.mdb(target Database) DVLngs = this._TargetDBConnection.ExecuteDataTable(this.NotesQuery.GetDefaultLanguages(false, TargetDBPrefix)).DefaultView; //-- check Notes table exists in target database or not IsNotesTblExistsInTarget = this.IsNotesDataTblExists(TargetDBPrefix, ref this._TargetDBConnection); if (IsNotesTblExistsInTarget) { //-- update Data_Nid in UT_Notes_Data table sqlString = this.NotesQuery.IMP_Update_UTNotesData(TargetDBPrefix); this._TargetDBConnection.ExecuteNonQuery(sqlString); } foreach (DataRowView drvLng in DVLngs) { //-- check language exists in source database LanguageCode = drvLng[Language.LanguageCode].ToString(); if ((int)sourceDBconnection.ExecuteScalarSqlQuery(this.NotesQuery.check_language_exists(LanguageCode, "", false, SourceDBPrefix)) > 0) { try { //-- Step 1. create Temp tables for Notes_Data & Notes_profile : //-------------------------------------------------------------------- if (IsMainTableCreated == false) { sourceDBconnection.Dispose(); //-- Notes_Data this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.CreateTempNoteDataTable(sourceDBFileNameWPath, sSrcDB_Prefix + "Notes_Data", "lnk_Notes_Data")); //-- Notes_profile this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.CreateTempNotesProfileTable(sourceDBFileNameWPath, sSrcDB_Prefix + "Notes_profile", "lnk_Notes_profile")); //-- create Temp_Notes_Profile table this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_CreateTemp_Notes_Profile()); IsMainTableCreated = true; sourceDBconnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, sourceDBFileNameWPath, string.Empty, Constants.DBPassword); } //-- Step 2. create _Notes table : ( Select *, 0 as _mapped into [_Notes] FROM lnk_Notes_data) try { this._TargetDBConnection.ExecuteNonQuery("Drop table _Notes"); } catch(Exception ) { } sqlString = this.NotesQuery.IMP_Create_NotesTbl(); this._TargetDBConnection.ExecuteNonQuery(sqlString); //-- Step 3. .UPDATE (_Notes AS N INNER JOIN _Data ON N.Data_NId = [_Data].Data_NId) //-- INNER JOIN UT_Data ON ([_Data].Source_NId = UT_Data.Source_NId) AND //-- ([_Data].FootNote_NId = UT_Data.FootNote_NId) AND ([_Data].Area_NId = UT_Data.Area_NId) AND //-- ([_Data].TimePeriod_NId = UT_Data.TimePeriod_NId) AND ([_Data].IUSNId = UT_Data.IUSNId) //-- SET N.Data_Nid=UT_Data.Data_Nid, Mapped=1 ; sqlString = this.NotesQuery.IMP_Update_Notes(TargetDBPrefix); this._TargetDBConnection.ExecuteNonQuery(sqlString); //-- Step 4. Delete * from _Notes where Mapped <>1 sqlString = this.NotesQuery.IMP_DeleteUnMatchedNotes(); this._TargetDBConnection.ExecuteNonQuery(sqlString); if (IsNotesTblExistsInTarget == false) { //-- Step 5. if Notes_Data doesn't exists in Target database then create all notes tables this.CreateNotesTableForAllLngs(sourceDBFileNameWPath, TargetDBPrefix, sSrcDB_Prefix, ref sourceDBconnection, ref sourceDBQueries); break; // TODO: might not be correct. Was : Exit For } else { //temp //this.DisposeSrcConnection(ref sourceDBconnection); LanguageCode = "_" + LanguageCode; //-- create lngBsd link tables: lnk_Notes, lnk_Notes_Classification CreateNotesLinkTables(sourceDBFileNameWPath, sSrcDB_Prefix, LanguageCode); //-- create lngBsd temp tables :Temp_Notes, Temp_Notes_Classification CreateTempNotesTables(); //-- set mapped to 0 in _Notes table where notes are already exists in UT_Notes_Data table sqlString = this.NotesQuery.IMP_Update_AlreadyExistsNotes(TargetDBPrefix, LanguageCode); this._TargetDBConnection.ExecuteNonQuery(sqlString); //-- Step 4. Delete * from _Notes where Mapped <>1 sqlString = this.NotesQuery.IMP_DeleteUnMatchedNotes(); this._TargetDBConnection.ExecuteNonQuery(sqlString); //-- delete unmatched records frm Temp_Notes table (which are not in _Notes) this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_DeleteUnMatchedFrm_Temp_Notes()); //-- bug fixed on 16Jan,2007: allow to insert duplicate values //-- check Notes with Profile_Email and Classification_Name already exists in target database //-- and update temp_notes table ////this._TargetDBConnection.ExecuteNonQuery(this._TargetDBQueries.IMP_MatchNotes(sTRGTblPrefix, sLngCode)) //-- if exists then overwrite, otherwise create new record //-- update UT_notes_en N set N.Notes= T. where mapped =1 in temp_notes t this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdatedMatchedNotes(TargetDBPrefix, LanguageCode)); //-- delete records from Temp_Notes table where mapped =1 (means already exists in target database) this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_DeleteMatchedFrmTempNotes()); //-- update Notes_Data and Notes_Profile table //-- Bulk Insert : UT_Notes_Profile_en this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertNotesProfile(TargetDBPrefix)); //-- Bulk Insert : UT_Notes_Classification_en this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertNotesClassification(TargetDBPrefix, LanguageCode)); //-- insert New_Classification_Nid in Temp_Notes_Classification and Temp_Notes this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewNId_NotesClassification(TargetDBPrefix, LanguageCode)); this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewClassificationNid_TempNotes()); //-- insert New_Profile_Nid in Temp_Notes_Profile and Temp_Notes this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewNId_NotesProfile(TargetDBPrefix)); this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_UpdateNewProfileNid_TempNotes()); //-- Bulk Insert : UT_Notes_en this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertInNotes(TargetDBPrefix, LanguageCode)); //-- insert new notes_nid in temp_notes table this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_Update_NewNotesNid(TargetDBPrefix, LanguageCode, "")); if (IsUpdatedNotesData == false) { //-- bug fixed on 5July,2007: Changed method name in DAQuery (IMP_InsertInNotesDataForDA) //-- insert new records in UT_Notes_Data table this._TargetDBConnection.ExecuteNonQuery(this.NotesQuery.IMP_InsertInNotesDataForDA(TargetDBPrefix)); IsUpdatedNotesData = true; } //-- delete lnk_Notes, lnk_Notes_Classification, Temp_Notes, Temp_Notes_Classification, Temp_Notes_Profile DropTbls(); } } catch (Exception ex) { //MessageBoxControl.ShowMsg(ex); } } } } catch (Exception ex) { } finally { if ((DVLngs != null)) DVLngs.Dispose(); DVLngs = null; if (sourceDBconnection != null) { this.DisposeSrcConnection(ref sourceDBconnection); } } //-- drop common link table try { this._TargetDBConnection.ExecuteNonQuery("Drop table lnk_Notes_Data"); this._TargetDBConnection.ExecuteNonQuery("Drop table lnk_Notes_profile"); this._TargetDBConnection.ExecuteNonQuery("Drop table Temp_Notes_profile"); this._TargetDBConnection.ExecuteNonQuery("Drop table _Notes"); } catch (Exception ) { } }
/// <summary> /// Returns true if ICON table exists otherwise false. /// </summary> /// <param name="iconTableName"></param> /// <param name="dbConnection"></param> /// <returns></returns> public static bool IsIconsTblExists(string iconTableName, DIConnection dbConnection) { bool RetVal = false; string sqlString = "Select count(*) from " + iconTableName + " where 1=2"; try { if ((dbConnection.ExecuteScalarSqlQuery(sqlString) != null)) { RetVal = true; } } catch (Exception ex) { RetVal = false; } return RetVal; }
/// <summary> /// Inserts Icon into ICON table /// </summary> /// <param name="dbConnection"></param> /// <param name="dbQueries"></param> /// <param name="buffer"></param> /// <param name="iconType"></param> /// <param name="width"></param> /// <param name="height"></param> /// <param name="elementType"></param> /// <param name="elementNid"></param> /// <returns></returns> public static int InsertIcon(DIConnection dbConnection, DIQueries dbQueries, byte[] buffer, string iconType, int width, int height, string elementType, string elementNid) { int RetVal = 0; //System.Data.OleDb.OleDbCommand cmd; //OleDbParameter prmPic = new OleDbParameter(); DbCommand Command = dbConnection.GetCurrentDBProvider().CreateCommand(); DbParameter Parameter; string SqlQuery = string.Empty; try { SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Icon.Insert.InsertIcon(dbQueries.DataPrefix, iconType, width, height, elementType, elementNid); //-- Change for Online Database SqlQuery = SqlQuery.Replace("?", "@Element_Icon"); //SqlQuery = dbQueries.FetchAndUpdateIcons(1, clsIcons.TableName, iconType, width, height, elementType, elementNid); // -- Get New NID generated //cmd = new OleDbCommand(SqlQuery, (OleDbConnection)dbConnection.GetConnection); //cmd.CommandType = CommandType.Text; Command.Connection = dbConnection.GetConnection(); Command.CommandText = SqlQuery; Command.CommandType = CommandType.Text; Parameter = dbConnection.GetCurrentDBProvider().CreateParameter(); { //prmPic.ParameterName = "@Element_Icon"; ////the name used in the query for the parameter //prmPic.OleDbType = OleDbType.Binary; ////set the database type //prmPic.Value = buffer; ////assign the contents of the buffer to the value of the parameter Parameter.ParameterName = "@Element_Icon"; //the name used in the query for the parameter Parameter.DbType= DbType.Binary; //set the database type Parameter.Value = buffer; //assign the contents of the buffer to the value of the parameter } //cmd.Parameters.Add(prmPic); Command.Parameters.Add(Parameter); //-- add the parameter to the command //cmd.ExecuteNonQuery(); Command.ExecuteNonQuery(); //-- this saves the image to the database RetVal =Convert.ToInt32( dbConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY")); } catch (Exception ex) { throw new ApplicationException(ex.Message); } finally { if ((Command != null)) { Command.Dispose(); } } return RetVal; }
/// <summary> /// /// </summary> /// <param name="dbConnection"></param> /// <param name="languageCode"></param> /// <param name="searchIndicators"></param> /// <param name="isSearchForQS"></param> /// <returns></returns> public static DataTable GetSearchChildResults(DIConnection dbConnection, string languageCode, string searchIndicators, bool isSearchForQS) { DataTable RetVal = null; StringBuilder SBQry = new StringBuilder(); string StrQry = string.Empty; string SearchLanguage = languageCode; int MaxAreaLength = -1; int MaxDVSeriesLength = -1; try { SBQry.Remove(0, SBQry.Length); SBQry.Append("Insert into " + QDSConstants.QDSTables.ChildTable.TableName); SBQry.Append(" (" + QDSConstants.QDSTables.ChildTable.Columns.NId + ", " + QDSConstants.QDSTables.ChildTable.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.ChildTable.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.UnitNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.ChildTable.Columns.IndicatorName + ", " + QDSConstants.QDSTables.ChildTable.Columns.Unit + ", " + QDSConstants.QDSTables.ChildTable.Columns.Area + ", " + QDSConstants.QDSTables.ChildTable.Columns.DefaultSG + ", " + QDSConstants.QDSTables.ChildTable.Columns.MRDTP + ", " + QDSConstants.QDSTables.ChildTable.Columns.MRD + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.SGCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.SourceCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.TPCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.DVCount + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.SGNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.SourceNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.TPNIds + ", " + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + ", " + QDSConstants.QDSTables.ChildTable.Columns.Dimensions + ", " + QDSConstants.QDSTables.ChildTable.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.IUSNId + ", " + QDSConstants.QDSTables.ChildTable.Columns.AreaParentNId + ")"); SBQry.Append(" SELECT DISTINCT " + QDSConstants.QDSTables.DISearchResult.Columns.NId + ", '" + SearchLanguage + "' As " + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", C." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", C." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + " As [Indicator], " + 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 + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + ", " + 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); SBQry.Append(" FROM (" + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " C"); if (isSearchForQS) { SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SearchAreas.TableName + " A ON C." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + " = cstr(A.AreaNId) ) "); } else { SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SearchAreas.TableName + " A ON C." + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + " = A.AreaNId ) "); } SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SearchIndicators.TableName + " I ON C." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + " = I.IndicatorNId"); SBQry.Append(" WHERE '" + SearchLanguage + "' = C.SearchLanguage "); SBQry.Append(" ORDER BY C." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", C." + QDSConstants.QDSTables.DISearchResult.Columns.Area + " ASC"); StrQry = SBQry.ToString(); dbConnection.ExecuteNonQuery(StrQry); //-- Get max area length StrQry = "SELECT Max(Len(" + QDSConstants.QDSTables.ChildTable.Columns.Area + ")) FROM " + QDSConstants.QDSTables.ChildTable.TableName; Int32.TryParse(Convert.ToString(dbConnection.ExecuteScalarSqlQuery(StrQry)),out MaxAreaLength); //-- Get max data value length StrQry = "SELECT Max(Len(" + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + ")) FROM " + QDSConstants.QDSTables.ChildTable.TableName; Int32.TryParse(Convert.ToString(dbConnection.ExecuteScalarSqlQuery(StrQry)), out MaxDVSeriesLength); //-- Get data with padded length SBQry.Remove(0, SBQry.Length); SBQry.Append("SELECT Distinct *, "); SBQry.Append(QDSConstants.QDSTables.ChildTable.Columns.Area + " & Space(" + MaxAreaLength + " - Len(" + QDSConstants.QDSTables.ChildTable.Columns.Area + ")) as PaddedArea,"); SBQry.Append(" Space(" + MaxDVSeriesLength + " - Len(" + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + ")) & " + QDSConstants.QDSTables.ChildTable.Columns.DVSeries + " as PaddedDVSeries"); SBQry.Append(" FROM " + QDSConstants.QDSTables.ChildTable.TableName); StrQry = SBQry.ToString(); RetVal = dbConnection.ExecuteDataTable(StrQry); } catch (Exception) { throw; } return RetVal; }
private Int32 GetPresNIdIfExistInDB(DIConnection dBConnection, string Pres_FileName, int GalleryFolderNid, string pres_Type) { string sSql = String.Empty; Int32 RetVal = 0; try { sSql = GetPresNIdQuery(Pres_FileName, GalleryFolderNid, pres_Type); RetVal = Convert.ToInt32(dBConnection.ExecuteScalarSqlQuery(sSql)); } catch (Exception ex) { RetVal = 0; } 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 string GetVersionDetails(string databaseFileName) { string RetVal = string.Empty; DIConnection TempDBConnection = null; try { if (File.Exists(databaseFileName)) { TempDBConnection = new DIConnection(DevInfo.Lib.DI_LibDAL.Connection.DIServerType.MsAccess, string.Empty, string.Empty, databaseFileName, string.Empty, string.Empty); RetVal = "<span style=\"color:gray;font-size:10px;\"> (v." + Convert.ToString(TempDBConnection.ExecuteScalarSqlQuery(DIQueries.GetDatabaseVersion())) + ") </span>"; } } catch (Exception) { } finally { if (TempDBConnection != null) { TempDBConnection.Dispose(); } } return RetVal; }
private string GetVersionDetails(DIConnection dbConnection) { string RetVal = string.Empty; try { if (dbConnection != null) { RetVal = Convert.ToString(dbConnection.ExecuteScalarSqlQuery(DIQueries.GetDatabaseVersion())); } } catch (Exception) { } return RetVal; }