创建项目统一的连续增长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中最新的记录