今天抽了点时间，写了一个通用的生成编号的程序！
我的生成规则为年月日+两位编号，即：yyyyMMdd+两位编号，譬如：2018101001 / 2018101002 / 2018101003
首先，一个项目中有很多表需要生成编号，譬如：产品编号，评估编号，学生学号等
下面看看我的思路，如下：
首先创建一张表，如下：
create table CM_CodeNo
(
Id int identity(1,1) primary key not null,
CodeCate varchar(50) not null unique,--现场考察 非设计类履约评估等
VersionNum TimeStamp not null,
CodeYear int,
CodeMonth int,
CodeDay int,
CodeValue varchar(20),
CodeCount int,
AddTime datetime default(getdate()),
)
简单解读下这张表的构造：
CodeCate：编号分类，譬如：产品编号、评估编号等
VersionNum：乐观锁版本号，用于处理并发，有兴趣的可参考鄙人的博客：https://www.cnblogs.com/chenwolong/p/BF.html#4094056
CodeYear：年
CodeMonth：月
CodeDay：日
CodeCount：代表今天生产了几个编号
AddTime：生产时间
CodeValue：无效字段，没用到...
表的解读就这么多，现在来看一组存储过程：
ALTER proc [dbo].[LockCodeNoProc] --
(
@CodeCate nvarchar(50),
@IsSuccess bit=0 output
)
as
declare @currentYear as int
declare @currentMonth as int
declare @currentDay as int
declare @count as int
declare @CodeCount as int
declare @flag as TimeStamp
declare @rowcount As int set @currentYear=DateName(year,GetDate())
set @currentMonth=DateName(month,GetDate())
set @currentDay=DateName(day,GetDate())
begin tran
select @count=count(1) from CM_Code where CodeYear=@currentYear and CodeMonth = @currentMonth and CodeDay=@currentDay and CodeCate=@CodeCate
if @count=0
begin
insert into CM_Code(CodeCate,CodeYear,CodeMonth,CodeDay,CodeValue,CodeCount,AddTime)
values(@CodeCate,@currentYear,@currentMonth,@currentDay,'',1,GETDATE())
select CodeCount from CM_Code where CodeYear=@currentYear and CodeMonth = @currentMonth and CodeDay=@currentDay and CodeCate=@CodeCate
end
else
begin
select @count=CodeCount,@flag=VersionNum from CM_Code where CodeYear=@currentYear and CodeMonth = @currentMonth and CodeDay=@currentDay and CodeCate=@CodeCate
--waitfor delay '00:00:10' --可用于模拟并发
update CM_Code set CodeCount=@count+1 where VersionNum=@flag and CodeYear=@currentYear and CodeMonth = @currentMonth and CodeDay=@currentDay and CodeCate=@CodeCate
set @rowcount=@@ROWCOUNT
select CodeCount from CM_Code where CodeYear=@currentYear and CodeMonth = @currentMonth and CodeDay=@currentDay and CodeCate=@CodeCate if @rowcount>0
begin
set @IsSuccess=1
select @IsSuccess
end
else
begin
set @IsSuccess=0
select @IsSuccess
end
end
commit tran
调用这个存储过程，会返回当前分类今天生产的编号数量，
时间有限，不作过多解读，大家自行理解，
下面是C#代码：
public class GetCode
{
public static string GetCodeNo(Enum CodeCate)
{
PortalCodeModel M = new PortalCodeModel();
M = U_GetCode.GetCodeNo(CodeCate);
//--模拟发生了并发，需要等待，重复请求，最大请求次数为10次
int retry = ;
while (!M.IsSuccess && retry > )
{
retry--;
GetCodeNo(CodeCate);
}
//
string code = GetCodeString(M.Result);
return code; } public static string GetCodeString(int CodeCount)
{
string initStr = DateTime.Now.ToString("yyyyMMdd");
int initLen = initStr.Length;
int Len = CodeCount.ToString().Length;
int MaxLen = ;
string ling = string.Empty;
int TotalLen = initLen + Len;
if (TotalLen <= MaxLen)
{
switch (Len)
{
case :ling = initStr + "" + CodeCount.ToString();break;
default: ling = initStr + CodeCount.ToString(); break;
}
}
return ling;
}
}
DAL层如下：
public static PortalCodeModel GetCodeNo(Enum CodeCate)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
PortalCodeModel M = new PortalCodeModel();
DataAccessParameterCollection parameters = new DataAccessParameterCollection();
parameters.AddWithValue("@CodeCate", CodeCate.ToString());
parameters.AddWithValue("@IsSuccess", ParameterDirection.ReturnValue); DataSet ds = broker.FillCommandDataSet("LockCodeNoProc", parameters);
//
if (ds != null && ds.Tables.Count > )
{
var Result = Convert.ToInt32(ds.Tables[].Rows[]["CodeCount"]);
var Bol = (ds.Tables[].Rows[][]).ToString();
if (Bol == "True")
{
M.Result = Result;
M.IsSuccess = true;
}
else
{
M.Result = Result;
M.IsSuccess = false;
}
}
return M;
}
}
枚举层如下：
/// <summary>
/// 各个枚举的‘值’使用表名即可
/// </summary>
public enum CodeCateEnum
{
[Description("现场考察")]
Inspection,
[Description("非设计类过程评估")]
EvluationPros,
[Description("非设计类履约评估")]
EvluationPlan,
}
OK ,就这么多，太忙，就不做过多的演示了！
public class PortalCodeModel
{
public bool IsSuccess { get; set; }
public int Result { get; set; }
}
说明下：当返回 IsSuccess 为 False 时，及说明发生了并发，应执行重复请求。这里采用的方式是：重复请求十次，直至成功为止！
C# 代码执行的时候，当第一次执行存储过程，会异常，请自行处理！
谢谢！