今天抽了点时间,写了一个通用的生成编号的程序!
我的生成规则为年月日+两位编号,即:yyyyMMdd+两位编号,譬如:2018101001 / 2018101002 / 2018101003
首先,一个项目中有很多表需要生成编号,譬如:产品编号,评估编号,学生学号等
下面看看我的思路,如下:
首先创建一张表,如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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:无效字段,没用到...
表的解读就这么多,现在来看一组存储过程:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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#代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public class GetCode { public static string GetCodeNo(Enum CodeCate) { PortalCodeModel M = new PortalCodeModel(); M = U_GetCode.GetCodeNo(CodeCate); //--模拟发生了并发,需要等待,重复请求,最大请求次数为10次 int retry = 10; while (!M.IsSuccess && retry > 0) { 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 = 10; string ling = string.Empty; int TotalLen = initLen + Len; if (TotalLen <= MaxLen) { switch (Len) { case 1:ling = initStr + "0" + CodeCount.ToString();break; default: ling = initStr + CodeCount.ToString(); break; } } return ling; } }
DAL层如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 > 0) { var Result = Convert.ToInt32(ds.Tables[0].Rows[0]["CodeCount"]); var Bol = (ds.Tables[1].Rows[0][0]).ToString(); if (Bol == "True") { M.Result = Result; M.IsSuccess = true; } else { M.Result = Result; M.IsSuccess = false; } } return M; } }
枚举层如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/// <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# 代码执行的时候,当第一次执行存储过程,会异常,请自行处理!
谢谢!