在大型的项目中,比如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中最新的记录