作主键生成要考虑多线程,分布式,缓冲,数据库移植等等.我目前的设计是在数据库中专门建立一张表,该表就是记录系统中分配给其他表的主键最大值.


DbTableEnum 是我对系统中所有的表作了一个枚举

public class KeyGenerator
    {
        private static Hashtable keyGens = new Hashtable();
        //缓冲的大小
        private static int _PoolSize = 20;
        private KeyInfo _KeyInfo;

        private KeyGenerator(){ }

        private KeyGenerator(DbTableEnum tblEnum)
        {
            _KeyInfo = new KeyInfo(_PoolSize, tblEnum.ToString());
        }

        ///

        /// 得到一个主键生成器
        ///

        /// 表的类型
        ///
        public static KeyGenerator GetInstance(DbTableEnum tblEnum)
        {
            //确保线程的安全
            lock(typeof(KeyGenerator))
            {
                string key = tblEnum.ToString();
                KeyGenerator keyGen;
                //先从缓存的表对象中取
                if (keyGens.ContainsKey(key))
                {
                    keyGen = (KeyGenerator)keyGens[key];
                }
                else
                {
                    keyGen = new KeyGenerator(tblEnum);
                    keyGens.Add(key, keyGen);
                }
                return keyGen;
            }
        }
        ///

        /// 得到一个可用的主键
        ///

        ///
        public int GetNextKey()
        {
            lock (this)
            {
                return _KeyInfo.GetNextKey();
            }
        }
    }

    class KeyInfo
    {
        private int _KeyMax;
        private int _KeyMin;
        private int _KeyNext;
        private int _PoolSize;
        private string _TblName;

        public KeyInfo(int poolSize, string tblName)
        {
            this._PoolSize = poolSize;
            this._TblName = tblName;
            retrieveFromDB();
        }
        ///

        /// 当前缓冲的主键最大值
        ///

        public int KeyMax
        {
            get
            {
                return _KeyMax;
            }
        }
        ///

        /// 当前缓冲的主键最小值
        ///

        public int KeyMin
        {
            get
            {
                return _KeyMin;
            }
        }
        ///

        /// 取得一个可用的主键,如果缓冲中有,那么就使用缓冲的,否则就从数据库重新取得一批新的主键
        ///

        ///
        public int GetNextKey()
        {
            //如果缓冲的主键使用完了
            if (_KeyNext > _KeyMax)
            {
                retrieveFromDB();
            }
            return _KeyNext++;
        }
        ///

        /// 从数据库取得一批新的主键
        ///

        private void retrieveFromDB()
        {
            string updateKey = "Update DefPrimaryKey set TblKey = TblKey +"
                                + _PoolSize + " where TblName ='"
                                + _TblName + "'";
            string getKey = "Select TblKey from DefPrimaryKey where TblName='" + _TblName + "'";

        //数据库访问,可以使用你自己的DA框架    
        DBOperator dbo = DBOperatorFactory.GetDBOperator(AppSetting.ConnectString, AppSetting.DataBaseType);
            if (dbo == null)
                throw new Exception("Nari Database connection exception");
            try
            {
                dbo.exeNoQrySqlOfPara(updateKey);
                dbo.exeSqlForDataReader(getKey, System.Data.CommandBehavior.SingleRow);
                if (dbo.DReader.Read())
                {
                    if (!dbo.DReader.IsDBNull(0))
                    {
                        _KeyMax = Convert.ToInt32(dbo.DReader.GetValue(0));
                        _KeyMin = _KeyMax - _PoolSize + 1;
                        _KeyNext = _KeyMin;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbo.Close();
            }
        }
    }
测试:
          TblServeyUnit su = new TblServeyUnit();
           su.oId = KeyGenerator.GetInstance(DbTableEnum.ServeyUnit).GetNextKey();
           su.projId = 1;
           su.Attribute = 2;
            su.SurveyUnitX="";
            su.Description = "徐骏的测试";
           
            EntitySqlCmdBuilder builder = new EntitySqlCmdBuilder();
            DBSqlCmdAndParam sql = builder.GetInsertCmd(su, DbTableEnum.ServeyUnit);
            DBOperator dbo = DBOperatorFactory.GetDBOperator(AppSetting.ConnectString,   AppSetting.DataBaseType);
            if (dbo == null)
                return false;
             dbo.exeNoQrySqlOfPara(sql);
            return true;

该设计能充分考虑系统的分布式扩展,并且和数据库无关,是可以数据库移植的,同时利用缓冲,减少了数据库的查询.应该是目前比较完美的自定义主键策略

本文作者:佚名 来源:本站原创
CIO之家 www.ciozj.com 微信公众号:imciow