创建项目统一的连续增长ID

在大型的项目中，比如ERP,OA我们需要在整个项目中所有的表都是用统一的规范ID的变化，这就需要创建一个sequence来统一管理。具体做法如下

一.创建统一管理数据表T_Sequence，结构如下：

ID	FullName	数据类型	长度	小数位	标识	主键	允许空	默认值	说明
1	ID	nvarchar	36	0		是	否		主键
2	FullName	nvarchar	50	0			否		名称
3	Degreesion	Int	4	0			否		起始值
4	Sequence	int	4	0			否		当前序列
5	Step	int	4	0			否		步长
6	Description	nvarchar	200	0			是		描述

 

实例数据如下

ID	FullName	Sequence	Step	Degreesion	Description
1000	T_Sequence	1003	1	1000	全局序列
1001	StuInfo	100004	1	100000	学生信息表
1002	ScoreInfo	10003	1	10000	学生分数表
1003	TeacherInfo	10001	1	10000	教师信息表

可以知道StuInfo中第一条数控制该表中的ID的，它的Sequence一直会保持和ID中的最大的值相同。而把数据中所有表都存到T_Sequence表中，起始序列可以自定义，步长也可以自定义，当StuInfo中有4条数据时，就可以看出

二.用linq to sql来实现，如下：

       1.DAL代码如下：

[Table(Name="T_Sequence")]
    public class T_Sequence
    {
        [Column(Name = "ID", DbType = "nvarchar(36)", CanBeNull = false, IsPrimaryKey = true)]
        public string ID { get; set; }
        [Column(Name = "FullName", DbType = "nvarchar(50)", CanBeNull = false)]
        public string FullName { get; set; }
        [Column(Name = "Sequence", DbType = "int", CanBeNull = false)]
        public int Sequence { get; set; }
        [Column(Name = "Degression", DbType = "int", CanBeNull = false)]
        public int Degression { get; set; }
        [Column(Name = "Step", DbType = "int", CanBeNull = false)]
        public int Step { get; set; }
        [Column(Name = "Description", DbType = "nvarchar(200)", CanBeNull = true)]
        public string Description { get; set; }
    }
    [Database(Name = "Temp")]
    public class T_SequenceContext : DataContext
    {
        public Table<T_Sequence> T_Sequences
        {
            get { return this.GetTable<T_Sequence>(); }
        }
 
        public T_SequenceContext()
            :
            base(SQLHelper.GetConnectString("ConnectionString1"))
        {
        }
        public T_SequenceContext(string connection)
            :
            base(connection)
        {
        }
        public T_SequenceContext(IDbConnection connection)
            :
            base(connection)
        {
        }
    }

       2.BLL代码如下：

public class T_SequenceRepository
    {
        T_SequenceContext sequence;
        public T_SequenceRepository()
        {
            if (sequence == null)
            {
                sequence = new T_SequenceContext();
            }
        }
        public string GetSequence(string tableName)
        {
            T_Sequence seq = (from c in sequence.T_Sequences
                    where c.FullName == tableName
                              select c).FirstOrDefault();
            if (seq == null)
            {
                T_Sequence self = (from c in sequence.T_Sequences
                                   where c.FullName == "T_Sequence"
                                   select c).FirstOrDefault();
                seq = new T_Sequence();
                seq.ID = (self.Sequence + self.Step).ToString();
                seq.FullName = tableName;
                seq.Prefix = "";
                seq.Separator = "";
                seq.Step = 1;
                seq.Degression = 0;
                seq.Sequence = seq.Degression + seq.Step;
                seq.Description = "";
                seq.CodePrefix = "";
                sequence.T_Sequences.InsertOnSubmit(seq);
                UpdateSelf(int.Parse(seq.ID));
                sequence.SubmitChanges();
            }
            else
            {
                T_Sequence self = (from c in sequence.T_Sequences
                                   where c.FullName == "T_Sequence"
                                   select c).FirstOrDefault();
                seq.Sequence = seq.Sequence + seq.Step;
                sequence.SubmitChanges();
            }
            return seq.Sequence.ToString();
        }
        //更行T_Sequence的Sequence
        public void UpdateSelf(int newSeq)
        {
            T_Sequence seq = (from c in sequence.T_Sequences
                              where c.FullName == "T_Sequence"
                              select c).FirstOrDefault();
            if (seq != null)
            {
                seq.Sequence = newSeq;
                sequence.SubmitChanges();
            }
        }
    }

   3.UIL代码如下：

   StuInfo stu = new StuInfo();
   T_SequenceRepository seq = new T_SequenceRepository();
   stu.ID = seq.GetSequence("StuInfo");//
  //StuInfo是表名，如果数据表T_Sequence中存在这个表的ID记录就会以步长递增，否则添加，ID记录为起始值。在T_Sequence中每个表都只有一条记录，而sequence字段的值对应StuInfo中最新的记录