C# DIConnection.Dispose方法代码示例

        /// <summary>
        /// Generate Cache Result tables on language basis
        /// </summary>
        /// <param name="databasePath"></param>
        public void GenerateCacheResults(string databasePath)
        {
            List<string> DbLangCodes = new List<string>();
            DIConnection DbConnection = null;
            string DataFolerName = string.Empty;
            int ProgressCount = 20;

            try
            {
                //-- raise event to display progress form
                this.RaiseDisplayProgressFormEvent();

                // increment progress bar value
                this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);

                DbConnection = new DIConnection(DIServerType.MsAccess, "", "", databasePath, "", "");

                DbLangCodes = DI7OfflineSPHelper.GetAllDbLangCodes(DbConnection);

                // increment progress bar value
                this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);

                //-- Merge Textual_Data_Value and Data_value column into Data_value column
                DIDataValueHelper.MergeTextualandNumericDataValueColumn(databasePath);

                // increment progress bar value
                this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);

                //-- Create new temp table and alter table schemas
                this.CreateNAlterSchemas(DbConnection);

                // increment progress bar value
                this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);

                //-- Generate cache on language basis
                foreach (string LangCode in DbLangCodes)
                {
                    this.CreateCacheResults(DbConnection, LangCode);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (DbConnection != null)
                {
                    DbConnection.Dispose();
                    DbConnection = null;
                }
            }
        }

        /// <summary>
        /// Process Target Areas that matched with Source Areas
        /// </summary>
        public void ProcessMatchedAreas()
        {
            DataTable Table = null;

            AreaBuilder AreaBuilderObj = null;
            AreaInfo AreaInfoObj = null;
            Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>();

            DIConnection SourceDBConnection = null;
            DIQueries SourceDBQueries = null;

            //-- Step 1: Get TempTable with Sorted SourceFileName
            Table = this.DBConnection.ExecuteDataTable(this.TemplateQueries.GetMatchedAreas());

            //-- Step 2:Initialise Indicator Builder with Target DBConnection
            AreaBuilderObj = new AreaBuilder(this.DBConnection, this.DBQueries);

            //-- Step 3: Import Nids for each SourceFile
            foreach (DataRow Row in Table.Copy().Rows)
            {
                try
                {

                    SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]), String.Empty, MergetTemplateConstants.DBPassword);
                    SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);

                    AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaNId]), 1, SourceDBConnection, SourceDBQueries);

                }
                catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
                finally
                {
                    if (SourceDBConnection != null)
                        SourceDBConnection.Dispose();
                    if (SourceDBQueries != null)
                        SourceDBQueries.Dispose();
                }
            }
        }

    //Added to check gallery existence
    public int GalleryExistence(string requestParam)
    {
        int RetVal;
        DataTable dtPresentation;
        DIConnection DIConnection;
        string GetPresentationsQuery;
        string[] Params;
        int UserNId ; ;
        int AdminNId ;
        DIConnection = null;
        try
        {
            Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter);
            UserNId = Convert.ToInt32(Params[0].Trim());
            AdminNId = Convert.ToInt32(this.Get_AdminNId());
            DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"),
                          string.Empty, string.Empty);

            GetPresentationsQuery = "SELECT * FROM Presentations WHERE user_nid = " + UserNId + " OR user_nid = " + AdminNId + "";
            dtPresentation = DIConnection.ExecuteDataTable(GetPresentationsQuery);
            RetVal = dtPresentation.Rows.Count;
            return RetVal;
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);
            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }
    }

    public static string GetSearchedKeywords(string KeywordNIds, List<string> SearchWords, string databaseURL)
    {
        string RetVal;
        string GetKeywordsQuery, KeyWord;
        DataTable DtKeywords;
        DIConnection DIConnection;

        RetVal = string.Empty;
        GetKeywordsQuery = "SELECT * FROM keywords WHERE keyword_nid IN (" + KeywordNIds + ") And keyword_type='UDK'";
        DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseURL,
                       string.Empty, string.Empty);
        try
        {
            DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery);
            if (DtKeywords.Rows.Count > 0)
            {
                foreach (DataRow DrKeyWord in DtKeywords.Rows)
                {
                    KeyWord = DrKeyWord["keyword"].ToString();
                    RetVal += KeyWord + "||";
                }
            }
            else
            {
                GetKeywordsQuery = "SELECT * FROM keywords WHERE keyword_nid IN (" + KeywordNIds + ")";
                DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery);
                if (DtKeywords.Rows.Count > 0)
                {
                    foreach (DataRow DrKeyWord in DtKeywords.Rows)
                    {
                        KeyWord = DrKeyWord["keyword"].ToString();
                        RetVal += KeyWord + "||";
                    }
                }
            }

            if (RetVal.Length > 0)
            {
                RetVal = RetVal.Substring(0, RetVal.Length - 2);
            }
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);

            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }

        return RetVal;
    }

    public static void Create_Constraint_Artefact_For_Version_2_0_SDMLMLFile(string RegistrationId, string DbNId, string UserNId, string AgencyId, string FileURL)
    {
        string InsertQuery, OutputFolder;
        DIConnection DIConnection;
        XmlDocument SimpleDataFileXML;
        XmlNodeList ObjXmlNodeList;
        SDMXObjectModel.Message.StructureType ConstraintStructure;
        SDMXObjectModel.Structure.ContentConstraintType ContentConstraint;
        DataKeySetType DataKeySet;
        DataKeyValueType DataKeyValue;
        int KeyIndex;

        string SimpleDataFileUrl = string.Empty;
        string ConstraintFileName = string.Empty;
        string ConstraintFileLocation = string.Empty;

        InsertQuery = string.Empty;
        OutputFolder = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DbNId + "\\sdmx\\Constraints\\" + UserNId);
        ConstraintFileName = DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Prefix + RegistrationId + ".xml";
        ConstraintFileLocation = OutputFolder + "\\" + ConstraintFileName;
        DIConnection = null;

        try
        {
            DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty);
            SimpleDataFileXML = new XmlDocument();
            if (!String.IsNullOrEmpty(FileURL))
            {
                SimpleDataFileXML.Load(FileURL);
            }
            ObjXmlNodeList = SimpleDataFileXML.GetElementsByTagName("sts:Series");
            ConstraintStructure = new SDMXObjectModel.Message.StructureType();
            ConstraintStructure.Structures = new StructuresType();
            ConstraintStructure.Structures.Constraints = new List<ConstraintType>();

            ContentConstraint = new SDMXObjectModel.Structure.ContentConstraintType();
            ContentConstraint.id = DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Prefix + RegistrationId;
            ContentConstraint.Name.Add(new TextType(null, DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Name + RegistrationId));
            ContentConstraint.agencyID = AgencyId;
            ContentConstraint.version = DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Version;
            ContentConstraint.Description.Add(new TextType(null, DevInfo.Lib.DI_LibSDMX.Constants.Constraint.Description));
            ContentConstraint.Annotations = null;
            ContentConstraint.ReleaseCalendar = null;
            ContentConstraint.ConstraintAttachment = new ContentConstraintAttachmentType();

            if (!String.IsNullOrEmpty(FileURL))
            {
                ContentConstraint.ConstraintAttachment.Items = new object[1];
                ContentConstraint.ConstraintAttachment.Items[0] = FileURL;
                ContentConstraint.ConstraintAttachment.ItemsElementName = new ConstraintAttachmentChoiceType[] { ConstraintAttachmentChoiceType.SimpleDataSource };
            }

            DataKeySet = new DataKeySetType();
            DataKeySet.isIncluded = true;
            ContentConstraint.Items.Add(DataKeySet);
            KeyIndex = 0;
            foreach (XmlNode SeriesNode in ObjXmlNodeList)
            {
                ((DataKeySetType)(ContentConstraint.Items[0])).Key.Add(new DataKeyType());
                foreach (XmlAttribute SeriesAttribute in SeriesNode.Attributes)
                {
                    DataKeyValue = new DataKeyValueType();
                    DataKeyValue.id = SeriesAttribute.Name;
                    DataKeyValue.Items.Add(new SimpleKeyValueType());
                    ((SimpleKeyValueType)(DataKeyValue.Items[0])).Value = SeriesAttribute.Value;
                    ((DataKeySetType)(ContentConstraint.Items[0])).Key[KeyIndex].KeyValue.Add(DataKeyValue);
                }
                KeyIndex = KeyIndex + 1;
            }

            ConstraintStructure.Structures.Constraints.Add(ContentConstraint);

            SDMXObjectModel.Serializer.SerializeToFile(typeof(SDMXObjectModel.Message.StructureType), ConstraintStructure, ConstraintFileLocation);
            InsertQuery = "INSERT INTO Artefacts (DBNId, Id, AgencyId, Version, URN, Type, FileLocation)" +
                                " VALUES(" + DbNId + ",'" + ContentConstraint.id + "','" + ContentConstraint.agencyID + "','" + ContentConstraint.version + "','" + string.Empty + "'," + Convert.ToInt32(ArtefactTypes.Constraint).ToString() + ",'" + ConstraintFileLocation + "');";
            DIConnection.ExecuteDataTable(InsertQuery);
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);

            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }
    }

        /// <summary>
        /// Process Mapped Areas from MappedRows.
        /// </summary>
        public void ProcessMappedAreas()
        {
            DIConnection SrcDBConnection = null;
            DIQueries SrcDBQueries = null;

            AreaBuilder TrgAreaBuilder = null;
            AreaBuilder SourceAreaBuilder = null;
            AreaInfo SrcAreaInfo = null;

            DataTable Table = null;
            string SourceFileWPath = string.Empty;
            int TrgAreaNid = 0;

            if (this.MappedTables.ContainsKey(TemplateMergeControlType.Areas))
            {
                TrgAreaBuilder = new AreaBuilder(this.DBConnection, this.DBQueries);

                foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Areas].MappedTable.MappedTable.Rows)
                {
                    //todo:
                    Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportAreas());

                    TrgAreaNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Area.AreaNId]);

                    if (Table != null && Table.Rows.Count > 0)
                    {
                        try
                        {
                            SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);

                            SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty);
                            SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection);

                            // Get Source Area Info
                            SourceAreaBuilder = new AreaBuilder(SrcDBConnection, SrcDBQueries);
                            SrcAreaInfo= SourceAreaBuilder.GetAreaInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID]));

                            // Import Mapped Area
                            TrgAreaBuilder.ImportAreaFrmMappedArea(SrcAreaInfo, SrcAreaInfo.Nid, TrgAreaNid, SrcDBQueries, SrcDBConnection);
                        }
                        finally
                        {
                            if (SrcDBConnection != null)
                            {
                                SrcDBConnection.Dispose();
                                SrcDBQueries.Dispose();
                            }
                        }
                    }
                }
            }
        }

        private void ProcessMappedSubgroupType()
        {
            DIConnection SrcDBConnection = null;
            DIQueries SrcDBQueries = null;

            DI6SubgroupTypeBuilder TrgSubgroupTypeBilder = null;
            DI6SubgroupTypeBuilder SourceSGTypeBuilder = null;
            DI6SubgroupTypeInfo SrcSubgroupTypeInfo = null;

            DataTable Table = null;

            string SourceFileWPath = string.Empty;
            int TrgSGDNid = 0;

            if (this.MappedTables.ContainsKey(TemplateMergeControlType.SubgroupDimensions))
            {
                TrgSubgroupTypeBilder = new DI6SubgroupTypeBuilder(this.DBConnection, this.DBQueries);

                foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.SubgroupDimensions].MappedTable.MappedTable.Rows)
                {
                    Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportSubgroupDimensions(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + SubgroupTypes.SubgroupTypeNId])));

                    TrgSGDNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + SubgroupTypes.SubgroupTypeNId]);

                    if (Table != null && Table.Rows.Count > 0)
                    {

                        try
                        {
                            SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);

                            SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty);
                            SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection);

                            // get subgroup type info
                            SourceSGTypeBuilder = new DI6SubgroupTypeBuilder(SrcDBConnection, SrcDBQueries);
                            SrcSubgroupTypeInfo = SourceSGTypeBuilder.GetSubgroupTypeInfoByNid(Convert.ToInt32(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID]));

                            // Import Mapped Subgroup Type Values
                            TrgSubgroupTypeBilder.ImportSubgroupTypeFrmMappedSubgroupType(SrcSubgroupTypeInfo, SrcSubgroupTypeInfo.Nid, TrgSGDNid, SrcDBQueries, SrcDBConnection);

                        }
                        finally
                        {
                            if (SrcDBConnection != null)
                            {
                                SrcDBConnection.Dispose();
                            }
                        }

                    }

                }
            }
        }

    public static void Delete_Registration_Artefact(string DbNId, string UserNId, string RegistrationId)
    {
        string Query, FileNameWPath;
        DIConnection DIConnection;

        Query = string.Empty;
        FileNameWPath = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Constants.FolderName.Data + DbNId + "\\sdmx\\Registrations\\" + UserNId + "\\" + RegistrationId + DevInfo.Lib.DI_LibSDMX.Constants.XmlExtension);
        DIConnection = null;

        try
        {
            DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty);
            DIConnection.ExecuteDataTable("DELETE FROM Artefacts WHERE Id = '" + RegistrationId + "' AND DBNId = " + DbNId + " AND Type = " + Convert.ToInt32(DevInfo.Lib.DI_LibSDMX.ArtefactTypes.Registration).ToString() + ";");

            if (File.Exists(FileNameWPath))
            {
                File.Delete(FileNameWPath);
            }
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);

            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }
    }

        /// <summary>
        /// Delete Presentaion from Gallery Database
        /// </summary>
        /// <param name="presentationPath"></param>
        /// <param name="dbPath"></param>
        /// <remarks>To be deleted</remarks> 
        public void DeletePresentationFromGalleryDatabase_old(String presentationPath, string dbPath)
        {
            int PresNIdForDelete = 0;
            string PresFileNameForDelete = string.Empty;
            string PresGalleryPathForDelete = string.Empty;
            string sSql = string.Empty;
            string PDS_PresentationNIdTobeUpdated = string.Empty;

            //Get Presentaion filename and Gallery path
            PresGalleryPathForDelete = Path.GetDirectoryName(presentationPath);
            PresFileNameForDelete = Path.GetFileName(presentationPath);

            if (this.ValidateDB(dbPath))
            {

                // Find Presention in PresMaster and get it's NId
                try
                {
                    sSql = "SELECT " + PresentationMaster.Pres_NId + " FROM " + DBTable.UT_PresMst + " WHERE " +
                    PresentationMaster.Pres_FileName + "= '" + PresFileNameForDelete + "'";

                    // Get  pres nid to be deleted
                    PresNIdForDelete = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(sSql));

                    //If pres nid found then delete this pres records from tables
                    if (PresNIdForDelete != 0)
                    {
                        //DELETE FROM Pres keyword
                        sSql = "DELETE FROM " + DBTable.UT_PresKeyword + " WHERE " + PresentationMaster.Pres_NId + "=" + PresNIdForDelete;
                        this.DBConnection.ExecuteNonQuery(sSql);

                        //DELETE FROM Pres Mst
                        sSql = "DELETE FROM " + DBTable.UT_PresMst + " WHERE " + PresentationMaster.Pres_NId + "=" + PresNIdForDelete;
                        this.DBConnection.ExecuteNonQuery(sSql);

                        //Delete from presearch
                        sSql = "DELETE FROM  UT_PreSearches WHERE  PDS_Presentation_NIds=" + PresNIdForDelete;
                        this.DBConnection.ExecuteNonQuery(sSql);

                        string ConnString = this.DBConnection.GetConnection().ConnectionString;
                        DIServerType TempserverType = DBConnection.ConnectionStringParameters.ServerType;
                        this.DBConnection.Dispose();

                        DIConnection TempDBConn = new DIConnection(ConnString, TempserverType);

                        System.Data.Common.DbDataAdapter Adapter = TempDBConn.CreateDBDataAdapter();
                        System.Data.Common.DbCommand cmd = TempDBConn.GetCurrentDBProvider().CreateCommand();
                        cmd.CommandText = "Select * from " + DBTable.UT_PreSearches;
                        cmd.Connection = TempDBConn.GetConnection();
                        Adapter.SelectCommand = cmd;

                        System.Data.Common.DbCommandBuilder CmdBuilder = TempDBConn.GetCurrentDBProvider().CreateCommandBuilder();
                        CmdBuilder.DataAdapter = Adapter;

                        DataSet TargetFileDataset = new System.Data.DataSet();

                        Adapter.Fill(TargetFileDataset, DBTable.UT_PreSearches);

                        //  Update
                        foreach (DataRow DRow in TargetFileDataset.Tables[0].Rows)
                        {
                            if (DRow[PreSearches.PDS_Presentation_NIds].ToString().Contains(PresNIdForDelete.ToString()))
                            {
                                if (DRow[PreSearches.PDS_Presentation_NIds].ToString().EndsWith(PresNIdForDelete.ToString()))
                                {
                                    DRow[PreSearches.PDS_Presentation_NIds] = DRow[PreSearches.PDS_Presentation_NIds].ToString().Replace(PresNIdForDelete.ToString(), " ").Trim();
                                }
                                else
                                {
                                    DRow[PreSearches.PDS_Presentation_NIds] = DRow[PreSearches.PDS_Presentation_NIds].ToString().Replace(PresNIdForDelete.ToString() + ",", "").Trim();
                                }
                            }
                        }
                        TargetFileDataset.AcceptChanges();

                        //update TempDataTable into target database
                        Adapter.Update(TargetFileDataset, DBTable.UT_PreSearches);
                        System.Threading.Thread.Sleep(1000);
                        TempDBConn.Dispose();
                        this.DBConnection = new DIConnection(ConnString, TempserverType);
                    }
                }
                catch (Exception ex)
                {
                }

            }
        }

    private DataTable GetASResultsTableFromIndexingDatabase(int DBNId, string SearchIndicatorICs, string SearchAreas, string SearchLanguage, bool HandleAsDIUAOrDIUFlag)
    {
        DataTable RetVal;
        string GetASResultsQuery;
        DIConnection DIConnection;
        System.Data.Common.DbParameter DbParam;
        List<System.Data.Common.DbParameter> DbParams;

        RetVal = null;
        GetASResultsQuery = string.Empty;
        DIConnection = null;

        try
        {
            DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"),
                           string.Empty, string.Empty);
            DbParams = new List<System.Data.Common.DbParameter>();

            DbParam = DIConnection.CreateDBParameter();
            DbParam.ParameterName = "AS_DBNId";
            DbParam.DbType = DbType.Int32;
            DbParam.Value = DBNId.ToString();
            DbParams.Add(DbParam);

            DbParam = DIConnection.CreateDBParameter();
            DbParam.ParameterName = "AS_SearchIndicatorICs";
            DbParam.DbType = DbType.String;
            DbParam.Value = SearchIndicatorICs;
            DbParams.Add(DbParam);

            DbParam = DIConnection.CreateDBParameter();
            DbParam.ParameterName = "AS_SearchAreas";
            DbParam.DbType = DbType.String;

            if (HandleAsDIUAOrDIUFlag)
            {
                DbParam.Value = SearchAreas;
            }
            else
            {
                DbParam.Value = string.Empty;
            }

            DbParams.Add(DbParam);

            DbParam = DIConnection.CreateDBParameter();
            DbParam.ParameterName = "AS_SearchLanguage";
            DbParam.DbType = DbType.String;
            DbParam.Value = SearchLanguage;
            DbParams.Add(DbParam);

            GetASResultsQuery = "SELECT * FROM Indexing_Table " +
                                "WHERE AS_DBNId = @AS_DBNId AND AS_SearchIndicatorICs = @AS_SearchIndicatorICs " +
                                "AND AS_SearchAreas = @AS_SearchAreas AND AS_SearchLanguage = @AS_SearchLanguage;";

            RetVal = DIConnection.ExecuteDataTable(GetASResultsQuery, CommandType.Text, DbParams);

            for (int i = 1; i < RetVal.Columns.Count; i++)
            {
                RetVal.Columns[i].ColumnName = RetVal.Columns[i].ColumnName.Substring(3);
            }
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);
            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }

        return RetVal;
    }

    private static DataTable Get_User(int UserNId)
    {
        DataTable RetVal;
        DIConnection DIConnection;
        string Query;

        RetVal = null;
        DIConnection = null;

        try
        {
            DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "stock//Database.mdb"), string.Empty, string.Empty);
            Query = "SELECT * FROM Users WHERE NId = " + UserNId.ToString() + ";";
            RetVal = DIConnection.ExecuteDataTable(Query);
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);

            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }

        return RetVal;
    }

    private DataTable GetGalleryTable(string keyword, string Language, int DBNId, string Type)
    {
        DataTable DTKeyword, RetVal = null;
        DIConnection DIConnection;
        string GetKeywordsQuery, GetPresentationsQuery;
        DataTable DtKeywords;

        DIConnection = null;

        try
        {
            DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"),
                          string.Empty, string.Empty);

            RetVal = new DataTable();
            if (!string.IsNullOrEmpty(keyword))
            {
                GetKeywordsQuery = "SELECT * FROM Keywords WHERE keyword LIKE '%" + keyword.Trim() + "%' AND keyword_type = 'UDK'";
                DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery);
                if (DtKeywords.Rows.Count > 0)
                {
                    foreach (DataRow KeywordRow in DtKeywords.Rows)
                    {
                        GetPresentationsQuery = "SELECT * FROM Presentations WHERE dbnid = '" + DBNId.ToString() + "' AND lng_code = '" + Language + "'";
                        GetPresentationsQuery += " AND keyword_nids LIKE '%," + KeywordRow["keyword_nid"].ToString() + ",%'";
                        if (Type != "A")
                        {
                            GetPresentationsQuery += " AND type = '" + Type + "';";
                        }
                        DTKeyword = DIConnection.ExecuteDataTable(GetPresentationsQuery);
                        RetVal.Merge(DTKeyword);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);
            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
            }
        }

        return RetVal;
    }

    private string NGallery_GetMoreKeywordXML(string KeywordNIds, string LangCode)
    {
        string RetVal;
        string Areas, Indicators, UDKs;
        string GetKeywordsQuery;
        DataTable DtKeywords;
        DIConnection DIConnection;

        RetVal = "<results>";
        Areas = string.Empty;
        Indicators = string.Empty;
        UDKs = string.Empty;
        GetKeywordsQuery = "SELECT * FROM keywords WHERE keyword_nid IN (" + KeywordNIds + ")";
        DIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"),
                       string.Empty, string.Empty);
        try
        {
            DtKeywords = DIConnection.ExecuteDataTable(GetKeywordsQuery);

            foreach (DataRow DrKeyWord in DtKeywords.Rows)
            {
                switch (DrKeyWord["keyword_type"].ToString())
                {
                    case "A":
                        Areas += DrKeyWord["keyword"].ToString() + ", ";
                        break;
                    case "I":
                        Indicators += DrKeyWord["keyword"].ToString() + ", ";
                        break;
                    case "UDK":
                        UDKs += DrKeyWord["keyword"].ToString() + ", ";
                        break;
                    default:
                        break;
                }
            }

            if (Areas.Length > 0)
            {
                Areas = Areas.Substring(0, Areas.Length - 2);
            }

            if (Indicators.Length > 0)
            {
                Indicators = Indicators.Substring(0, Indicators.Length - 2);
            }

            if (UDKs.Length > 0)
            {
                UDKs = UDKs.Substring(0, UDKs.Length - 2);
            }

            XmlDocument LangSpecificWords;
            LangSpecificWords = null;
            string langAreas = "Areas";
            string langIndicators = "Indicators";
            string langUDKs = "Keywords";

            try
            {
                LangSpecificWords = new XmlDocument();
                LangSpecificWords.Load(Server.MapPath(@"~\stock\language\" + LangCode.ToString() + @"\Gallery.xml"));
                foreach (XmlNode Keyword in LangSpecificWords.GetElementsByTagName("lng"))
                {
                    if (Keyword.Attributes["id"].Value.ToUpper() == "LANGMOREAREAS")
                    {
                        langAreas = Keyword.Attributes["val"].Value;
                    }
                    if (Keyword.Attributes["id"].Value.ToUpper() == "LANGMOREINDICATORS")
                    {
                        langIndicators = Keyword.Attributes["val"].Value;
                    }
                    if (Keyword.Attributes["id"].Value.ToUpper() == "LANGMOREUDKS")
                    {
                        langUDKs = Keyword.Attributes["val"].Value;
                    }
                }
            }
            catch (Exception ex)
            {
                Global.CreateExceptionString(ex, null);
            }
            //RetVal += "<ar txt=\"Area\" val=\"" + Areas + "\"/>";
            //RetVal += "<ind txt=\"Indicator\" val=\"" + Indicators + "\"/>";
            //RetVal += "<udk txt=\"Keywords\" val=\"" + UDKs + "\"/>";
            RetVal += "<ar txt=\"" + langAreas + "\" val=\"" + Areas + "\"/>";
            RetVal += "<ind txt=\"" + langIndicators + "\" val=\"" + Indicators + "\"/>";
            RetVal += "<udk txt=\"" + langUDKs + "\" val=\"" + UDKs + "\"/>";
            RetVal += "</results>";
        }
        catch (Exception ex)
        {
            Global.CreateExceptionString(ex, null);
            throw ex;
        }
        finally
        {
            if (DIConnection != null)
            {
                DIConnection.Dispose();
//.........这里部分代码省略.........

        internal void ImportAssistants()
        {
            DIConnection SourceDBConnection = null;
            DIQueries SourceDBQueries = null;
            string DataPrefix = string.Empty;
            string LanguageCode = string.Empty;

            //get all source database name
            foreach (string SourceFileNameWPath in this.SourceDatabaseFileNamesWPath)
            {
                //for each source database, import notes

                try
                {
                    SourceDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess,
                                string.Empty, string.Empty, SourceFileNameWPath, string.Empty, Common.Constants.DBPassword));
                    DataPrefix = SourceDBConnection.DIDataSetDefault();
                    LanguageCode = SourceDBConnection.DILanguageCodeDefault(DataPrefix);
                    SourceDBQueries = new DIQueries(DataPrefix, LanguageCode);

                    this.ImportAssistant(SourceDBConnection, SourceDBQueries);

                }
                catch (Exception)
                {

                }
                finally
                {
                    if (SourceDBConnection != null)
                    {
                        SourceDBConnection.Dispose();
                        this._TargetDBConnection.Dispose();
                    }

                    if (SourceDBQueries != null)
                    {
                        SourceDBQueries = null;
                    }
                }

            }
        }

        private void ImportEBook(ref DIConnection Connection, ref DIQueries queries, string languageCode, DITables sourceTableNames, DITables targetTableNames)
        {
            string SqlString = string.Empty;
            string TablePrefix = this._TargetDBConnection.DIDataSetDefault();

            DataTable SourceTopicTable = null;
            string TargetConnectionString = this._TargetDBConnection.GetConnection().ConnectionString;
            string SourceConnectionString = Connection.GetConnection().ConnectionString;
            string SourceDBName = Connection.ConnectionStringParameters.DbName;
            string TargetDBName = this._TargetDBConnection.ConnectionStringParameters.DbName;
            OleDbCommand InsertCommand;
            OleDbDataAdapter Adapter;
            OleDbCommandBuilder CmdBuilder;
            DataSet EbookDataset;
            DataRow Row;
            try
            {
                this._TargetDBConnection.ExecuteNonQuery(AssistantQueries.DeleteFrmEBook(targetTableNames.AssistanteBook));

                // get record from source database
                SourceTopicTable = Connection.ExecuteDataTable(" Select * from " + sourceTableNames.AssistanteBook);

                if (SourceTopicTable.Rows.Count > 0)
                {
                                       //dispose target and source connection
                    this._TargetDBConnection.Dispose();
                    Connection.Dispose();

                    InsertCommand = new OleDbCommand();
                    InsertCommand.Connection = new OleDbConnection(TargetConnectionString);

                    Adapter = new OleDbDataAdapter("Select * from  " + sourceTableNames.AssistanteBook, TargetConnectionString);

                    CmdBuilder = new OleDbCommandBuilder(Adapter);

                    EbookDataset = new DataSet(sourceTableNames.AssistanteBook);
                    Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    Adapter.Fill(EbookDataset, targetTableNames.AssistanteBook);         //Fill data adapter
                    Row = EbookDataset.Tables[0].NewRow();

                    try
                    {
                        Row[Assistant_eBook.EBook] = SourceTopicTable.Rows[0][Assistant_eBook.EBook]; //ShpBuffer
                        EbookDataset.Tables[0].Rows.Add(Row);
                        Adapter.Update(EbookDataset, targetTableNames.AssistanteBook);              // Save changes to the database
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.ExceptionFacade.ThrowException(ex);
                    }

                    if (CmdBuilder != null)
                    {
                        CmdBuilder.Dispose();
                        CmdBuilder = null;
                    }

                    if (InsertCommand != null)
                    {
                        InsertCommand.Dispose();
                        InsertCommand = null;
                    }
                    if (Adapter != null)
                    {
                        Adapter.Dispose();
                        Adapter = null;
                    }

                    //reconnect the source and target database
                    this._TargetDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess,
                         string.Empty, string.Empty, TargetDBName, string.Empty, Common.Constants.DBPassword));
                    Connection = new DIConnection(SourceConnectionString, DIServerType.MsAccess);
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.ExceptionFacade.ThrowException(ex);
            }
            finally
            {
                if (SourceTopicTable != null)
                {
                    SourceTopicTable.Dispose();
                }
            }
        }