[转]将数据库表存入内存中，随时调用

注意，支持使用Sql语句和只使用表把，把数据库中的表加载到内存。注意，这里的内存是不会释放的。所以此组件是支持在asp.net服务器上使用的。从内存中取出来的表，是由你自定的。数据放置入内存之中,以HashTable对象为存贮对象。提取出来的数据为DataTable对象。也可只得到DataTable表中的一个值，如果你想要DataSet，也可以相互转换。详细使用请使看注解。

    /// <summary>
    /// 对基础数据进行处理。使用.net3.5框架
    /// 作者：若非轻寒 Email:ooofcu@hotmail.com
    /// 原理：把数据库中的表加入到内存对象HashTable中,需要
    /// 调用时,从HashTable对象中取出表或取出部份内容。
    /// </summary>
    public class DataDisposal
    {
        /*      //-----------------范          例-------------------
                /// 作者：若非轻寒 Email:ooofcu@hotmail.com
                DataDisposal g_DataDisposal= new DataDisposal("链接字符串");
                //加载业务基础数据
                g_DataDisposal.AddData("JJ_SFLBB");  
                g_DataDisposal.AddData("JJ_SFWCYYB");
                g_DataDisposal.AddData("JJ_DWXXB");
                //判断指定名称的内称表是否存在
                if(g_DataDisposal.Contains("TableName"))
                    return true;
                //得到业务基础数据,取得整个表
                DataTable dt = g_DataDisposal.GetData("JJ_DWXXB");

                //操作得到数据,在指定的表中按字段取得一个值
                string str = g_DataDisposal.GetTableValue("ID", "0", "DWMC", "JJ_DWXXB");

                //返回一行数据,在指定的表中按主键取得一行值
                DataRow dr = g_DataDisposal.GetDataRow("ID", "00", "JJ_DWXXB");

                 //还有一些其它方法，请自已看
        */

        //private ZIT.I_ZIT_Public g_DBOperation;     //数据访问组件

        private unsafe Hashtable hashTable = new Hashtable(); //用于存贮内存表集合
        private String sConnString = "";

        /// <summary>
        /// 内存数据表集合
        /// </summary>
        public Hashtable HashTableObj
        {
            get { return hashTable; }
            set { hashTable = value; }
        }
        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="sConnString">数据库链接字符串</param>
        public DataDisposal(string sConnString)
        {
            this.sConnString = sConnString;
            GC.SuppressFinalize(hashTable); //请求系统不要调用此对象的终结器(重要,保证此对象不被垃圾回收)
        }

        /// <summary>
        /// 判断本对象中指定的内存表是否存在
        /// </summary>
        /// <param name="sTableName">表名</param>
        public bool Contains(string sTableName)
        {
            return hashTable.ContainsKey(sTableName);
        }

        /// <summary>
        /// 得到本对象中内存表的数量
        /// </summary>
        /// <returns></returns>
        public int Count()
        {
            return hashTable.Count;
        }

        /// <summary>
        /// 向内存表中加载数据表,加载此表所有数据到内存。参数：数据库表名
        /// </summary>
        /// <param name="sTableName">数据表名</param>
        public void AddData(string sTableName)
        {
            try
            {
                if (hashTable.Contains(sTableName))
                {
                    //MessageBox.Show("已加载此表！程序中止！");
                    return;
                }
                string sSql = "select * from " + sTableName;
                DataTable dtTemp = GetDataTable(sSql);
                dtTemp.TableName = sTableName;
                hashTable.Add(sTableName, dtTemp);
                dtTemp = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 按SQL语句向内存表中加载数据表,需要自定一个内存表表名。参数：SQL查询语句,自定义内存表表名
        /// </summary>
        /// <param name="sSql">SQL语句</param>
        /// <param name="sTableName">自定一个内存表名，调用时就用这个表名调用</param>
        public void AddDataSql(string sSql, string sTableName)
        {
            try
            {
                DataTable dtTemp = GetDataTable(sSql);
                dtTemp.TableName = sTableName;
                hashTable.Add(sTableName, dtTemp);
                dtTemp = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 从内存表集合中取出一个指定表名的内存表
        /// </summary>
        /// <param name="sTableName">内存表表名</param>
        /// <returns></returns>
        public DataTable GetData(string sTableName)
        {
            try
            {
                if (hashTable.Contains(sTableName))
                    return (DataTable)hashTable[sTableName];
                else
                {
                    //MessageBox.Show("指定内存表不存在！");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return null;
        }

        /// <summary>
        /// 按条件返回一行数据
        /// select * from sTableName where sID=sValue
        /// </summary>
        /// <param name="sTableName">内存表名与数据库中的表名一样</param>
        /// <param name="sID">表字段名</param>
        /// <param name="sValue">表字段值</param>
        /// <returns>一行数据</returns>
        public DataRow GetDataRow(string sTableName, string sID, string sValue)
        {
            try
            {
                //操作得到数据
                DataTable dtTemp = GetData(sTableName);
                LinqList<DataRow> rowsCollection = new LinqList<DataRow>(dtTemp.Rows);
                //隐式类型
                IEnumerable<DataRow> selectedRows = from r in rowsCollection
                                                    where r[sID].ToString() == sValue
                                                    select r;
                dtTemp = null;
                foreach (DataRow dr in selectedRows)
                {
                    //操作
                    return dr;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return null;
        }

        /// <summary>
        /// 按条件返回一行数据
        /// select * from sTableName where sID1=sValue1 and sID2=sValue2
        /// </summary>
        /// <param name="sTableName">内存表名与数据库中的表名一样</param>
        /// <param name="sID1">表字段名1</param>
        /// <param name="sValue1">表字段值1</param>
        /// <param name="sID2">表字段名2</param>
        /// <param name="sValue2">表字段值2</param>
        /// <returns>一行数据</returns>
        public DataRow GetDataRow(string sTableName, string sID1, string sValue1, string sID2, string sValue2)
        {
            try
            {
                //操作得到数据
                DataTable dtTemp = GetData(sTableName);
                LinqList<DataRow> rowsCollection = new LinqList<DataRow>(dtTemp.Rows);
                IEnumerable<DataRow> selectedRows = from r in rowsCollection
                                                    where r[sID1].ToString() == sValue1 && r[sID2].ToString() == sValue2
                                                    select r; //隐式类型
                dtTemp = null;
                foreach (DataRow dr in selectedRows)
                {
                    //操作
                    return dr;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return null;
        }

        /// <summary>
        /// 按条件查询内存表中的数据
        /// select retValue from sTableName where sID=sValue
        /// </summary>
        /// <param name="retValue">要返回的表字段名 </param>
        /// <param name="sTableName">内存表名与数据库中的表名一样</param>
        /// <param name="sID">表字段名</param>
        /// <param name="sValue">表字段值</param>
        /// <returns>返回值为参数retValue所定义的字段，出现异常则提示错误，并返回""</returns>
        public string GetTableValue(string retValue,string sTableName,string sID, string sValue)
        {
            try
            {
                //操作得到数据
                DataTable dtTemp = GetData(sTableName);
                LinqList<DataRow> rowsCollection = new LinqList<DataRow>(dtTemp.Rows);
                IEnumerable<DataRow> selectedRows = from r in rowsCollection
                                                    where r[sID].ToString() == sValue
                                                    select r; //隐式类型
                dtTemp = null;
                foreach (DataRow dr in selectedRows)
                {
                    //操作
                    return dr[retValue].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return "";
        }

        /// <summary>
        /// 按条件查询内存表中的数据
        /// select retValue from sTableName where sID1=sValue1 and sID2=sValue2
        /// </summary>       
        /// <param name="retValue">要返回的表字段名</param>
        /// <param name="sTableName">内存表名与数据库中的表名一样</param>
        /// <param name="sID1">表字段名1</param>
        /// <param name="sValue1">表字段值1</param>
        /// <param name="sID2">表字段名2</param>
        /// <param name="sValue2">表字段值2</param>
        /// <returns>返回值为参数retValue所定义的字段，出现异常则提示错误，并返回""</returns>
        public string GetTableValue(string retValue, string sTableName,string sID1, string sValue1, string sID2, string sValue2)
        {
            try
            {
                //操作得到数据
                DataTable dtTemp = GetData(sTableName);
                LinqList<DataRow> rowsCollection = new LinqList<DataRow>(dtTemp.Rows);
                IEnumerable<DataRow> selectedRows = from r in rowsCollection
                                                    where r[sID1].ToString() == sValue1 && r[sID2].ToString() == sValue2
                                                    select r; //隐式类型
                dtTemp = null;
                foreach (DataRow dr in selectedRows)
                {
                    //操作
                    return dr[retValue].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return "";
        }

        /// <summary>
        /// 删除内存中的特定表
        /// </summary>
        /// <param name="sTableName"></param>
        public void RemoveTable(string sTableName)
        {
            if (hashTable.Contains(sTableName))
                hashTable.Remove(sTableName);
        }

        /// <summary>
        /// 删除内存中的所有表
        /// </summary>
        public void RemoveAllTable()
        {
            hashTable = null;
        }

        /// <summary>
        /// 将DataTable转化为DataSet
        /// </summary>
        /// <param name="dt">将要转化的DataTable</param>
        /// <returns>新的DataSet</returns>
        public DataSet DataTableToDataSet(DataTable dt)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            return ds;
        }

        /// <summary>
        /// 内部方法，确定此模块得到数据库存唯一方法，
        /// 处理数据访问组件访问数据库得到数据
        /// </summary>
        /// <param name="sSql"></param>
        /// <returns></returns>
        private DataTable GetDataTable(string sSql)
        {
            return Query(sSql).Tables[0];     
        }

        /// <summary>
        /// 内部方法，执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        private DataSet Query(string SQLString)
        {
            using (OleDbConnection connection = new OleDbConnection(sConnString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.OleDb.OleDbException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
    }

    public class LinqList<T> : IEnumerable<T>, IEnumerable
    {
        IEnumerable items;
        public LinqList(IEnumerable items)
        {
            this.items = items;
        }
        #region
        IEnumerable<DataRow> Members;
        IEnumerator<T> IEnumerable<T>.GetEnumerator()
        {
            foreach (T item in items)
                yield return item;
        }
        IEnumerator IEnumerable.GetEnumerator()
        {
            IEnumerable<T> ie = this;
            return ie.GetEnumerator();
        }
        #endregion
    }