C# DIConnection.ExecuteScalarSqlQuery方法代码示例

    /// <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));

//.........这里部分代码省略.........

        /// <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();
//.........这里部分代码省略.........

        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
//.........这里部分代码省略.........

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