1实现步骤以及说明
1.根据参数获取当前setNoIndex表里现在的No的index值,如果包含关键字当前对应数据,则现在SetIndexNoLeft 表中找到有无未使用并未占用的那条数据(被占用的数据IsTaken=1,生成后使用当前时间与updated时间进行比对,然后时间超过30分钟后会把状态变更为 IsTaken =0 ),如果有就返回,如果没有就创建一条新的SetIndexNoLeft记录并返回。
2.如果当前SetNoIndex下面没有数据,则新建一条数据,并且在SetIndexNoLeft 创建一条新数据创建时 IsTaken就是1,被占用。
3.在主要对象需要插入时,插入成功后数据进行删除当前 这一条Id的SetIndexNoLeft数据。
2数据库表:
表1:年月日数据生成Index数据表
USE [YingyuYubingBaogao2023]
GO
/****** Object: Table [dbo].[SetNoIndex] Script Date: 2023/8/17 17:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SetNoIndex](
[Id] [int] IDENTITY(1,1) NOT NULL,
[NoType] [int] NULL,
[ThisIndex] [int] NULL,
[ThisSigle] [nvarchar](30) NULL,
[KeyWord] [nvarchar](30) NULL,
[CreateDate] [datetime] NULL,
[Updated] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SetNoIndex] ADD CONSTRAINT [DF_SetNoIndex_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[SetNoIndex] ADD CONSTRAINT [DF_SetNoIndex_Updated] DEFAULT (getdate()) FOR [Updated]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0月累计,1日累计,2年累计,3,总共累计' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex', @level2type=N'COLUMN',@level2name=N'NoType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前排序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex', @level2type=N'COLUMN',@level2name=N'ThisIndex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前标记(如果是日累计就是20230802,如果是月累计就是 202308 ,如果是年累计就是 2023,如果是 所有累计就是0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex', @level2type=N'COLUMN',@level2name=N'ThisSigle'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关键字' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex', @level2type=N'COLUMN',@level2name=N'KeyWord'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex', @level2type=N'COLUMN',@level2name=N'Updated'
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'编号自动生成器' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex'
GO
表2: 生成的临时编号表
USE [YingyuYubingBaogao2023]
GO
/****** Object: Table [dbo].[SetIndexNoLeft] Script Date: 2023/8/17 17:55:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SetIndexNoLeft](
[Id] [int] IDENTITY(1,1) NOT NULL,
[NoIndexId] [int] NULL,
[NoLeft] [nvarchar](40) NULL,
[IsTaken] [bit] NULL,
[Updated] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SetIndexNoLeft] ADD CONSTRAINT [DF_SetIndexNoLeft_Updated] DEFAULT (getdate()) FOR [Updated]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号IndexId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetIndexNoLeft', @level2type=N'COLUMN',@level2name=N'NoIndexId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'剩下的编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetIndexNoLeft', @level2type=N'COLUMN',@level2name=N'NoLeft'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否被占用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetIndexNoLeft', @level2type=N'COLUMN',@level2name=N'IsTaken'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'处理时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetIndexNoLeft', @level2type=N'COLUMN',@level2name=N'Updated'
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'编号拾取的明细端' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetIndexNoLeft'
GO
3.需要的Model对象创建
public class SetIndexNoLeft
{
/// <summary>
///
/// </summary>
[Description("")]
public int Id { get; set; }
/// <summary>
/// 编号IndexId
/// </summary>
[Description("编号IndexId")]
public int NoIndexId { get; set; }
/// <summary>
/// 剩下的编号
/// </summary>
[Description("剩下的编号")]
public string NoLeft { get; set; }
/// <summary>
/// 是否被占用
/// </summary>
[Description("是否被占用")]
public bool IsTaken { get; set; }
/// <summary>
/// 处理时间
/// </summary>
[Description("处理时间")]
public DateTime Updated { get; set; }
/// <summary>
/// 设置好的No全部
/// </summary>
public string NoOut { get; set; }
}
参考的枚举实例(你可能用不到):
/// <summary>
/// 编号类型
/// </summary>
public enum ENoTypes
{
报告编号,客户编号,用户编号,样本编号,店铺编号,样本类型编号,项目编号
}
4 DAL数据库操作类。
namespace DAL
{
/// <summary>
/// 编号自动生成器操作类
/// </summary>
public class DALSetNoIndex
{
/// <summary>
///获取当前 0月累计,1日累计,2年累计,3,总共累计
/// </summary>
/// <param name="SetNoType"> 0月累计,1日累计,2年累计,3,总共累计</param>
/// <param name="SetKeyword">当前关键字(需要的表单名子)</param>
/// <param name="OutIndexLength">index编号长度 0001 就是4 </param>
/// <returns></returns>
public SetIndexNoLeft GetIndexNo(string SetKeyword, int SetNoType = 0, int OutIndexLength = 5)
{
string sql = $@"declare @SetNoType int ,@SetThisSigle nvarchar(10), @SetKeyword nvarchar(10),@ThisDate nvarchar(20),@OutStr nvarchar(20),@OutStrLength int;
set @ThisDate =CONVERT(nvarchar, getdate(), 112);---当前日期全部数据(20230108)
set @SetNoType = {SetNoType};--设定类型
set @OutStrLength ={OutIndexLength};--序号保留多少位
set @SetKeyword = '{SetKeyword}';---当前关键字
Update SetIndexNoLeft set IsTaken = 0 where datediff( minute , Updated , getdate() )>60 and IsTaken =1 ;---先更新本表中所有已过期的数据 30分钟期限
if (@SetNoType = 0)--0月累计
begin
set @SetThisSigle = SUBSTRING(@ThisDate,1,6);
end
else if(@SetNoType =1 )--1日累计
begin
set @SetThisSigle = @ThisDate;
end
else if(@SetNoType =2)--2年累计
begin
set @SetThisSigle = SUBSTRING(@ThisDate,1,4);
end
else --3总共累计
begin
set @SetThisSigle='';
end
declare @SetIndex int ,@OPIndexId int,@OPIndexNoLeftId int,@HebingOutNo nvarchar(20) ; ----@OPIndexId 为获取到当前Id ,@OPIndexNoLeftId 是获取当前数据条,@HebingOutNo 是当前要导出的数据
set @OPIndexNoLeftId = 0;---初始化输出表Id
if exists(select * from SetNoIndex where KeyWord = @SetKeyword and ThisSigle = @SetThisSigle )
begin -- 如果存在就更新
select @OPIndexId = Id from SetNoIndex where KeyWord = @SetKeyword and ThisSigle = @SetThisSigle --获取对应Id
--select @OPIndexId as OPIndexId ;
if exists(select * from SetIndexNoLeft where [NoIndexId] = @OPIndexId and IsTaken =0)
begin
----select 'aaaaaa';
select top 1 @OPIndexNoLeftId = Id from SetIndexNoLeft where [NoIndexId] = @OPIndexId and IsTaken = 0 order by NoLeft; ----返回当前剩下那条数据的Id
update SetIndexNoLeft set IsTaken = 1 ,Updated = getdate() where Id = @OPIndexNoLeftId; --修改当前状态
end
else
begin
update SetNoIndex set ThisIndex = ThisIndex+1 ,Updated =getdate() where KeyWord = @SetKeyword and ThisSigle = @SetThisSigle;--先更新
select @SetIndex= ThisIndex from SetNoIndex where Id =@OPIndexId ; -- KeyWord = @SetKeyword and ThisSigle = @SetThisSigle ;--在提取结果
end
end
else
begin
set @SetIndex = 1;
insert into SetNoIndex ([NoType],[ThisIndex],[ThisSigle],[KeyWord]) values (@SetNoType,@SetIndex,@SetThisSigle,@SetKeyword);-- 插入数据
set @OPIndexId = @@IDENTITY;--- 获取当前Id
end
--select DATALENGTH( @ThisDate);
--select @ThisDate;
--select @SetThisSigle;
---select @OPIndexNoLeftId ;
select @HebingOutNo = @SetThisSigle + right('00000000000'+convert(varchar, @SetIndex),@OutStrLength) from SetNoIndex where Id = @OPIndexId ;
if( @OPIndexNoLeftId = 0 )--表内无数据
begin
insert into SetIndexNoLeft ([NoIndexId] ,[NoLeft],[IsTaken],Updated ) values (@OPIndexId,@HebingOutNo,'true',getdate());
set @OPIndexNoLeftId= @@IDENTITY;--- 获取当前Id
end
select top 1 * from SetIndexNoLeft where Id = @OPIndexNoLeftId; -----返回当前数据
";
return DBUtility.DapperDbHelper.Query<SetIndexNoLeft>(sql).FirstOrDefault();
}
/// <summary>
/// 删除占用
/// </summary>
/// <param name="ID">ID</param>
/// <returns></returns>
public ResultMsg Delete(int ID)
{
ResultMsg msg = new ResultMsg();
try
{
string sql = "DELETE [SetIndexNoLeft] WHERE[ID] = @ID";
msg.ReturnInt = DapperDbHelper.Execute(sql, new { ID = ID });
msg.Success = true;
return msg;
}
catch (Exception ex)
{
msg.Success = false;
msg.ErrMsg = ex.Message;
}
return msg;
}
/// <summary>
/// 释放占用
/// </summary>
/// <param name="ID">ID</param>
/// <returns></returns>
public ResultMsg CleanTaken(int ID)
{
ResultMsg msg = new ResultMsg();
try
{
string sql = "Update [SetIndexNoLeft] set IsTaken =0 WHERE[ID] = @ID";
msg.ReturnInt = DapperDbHelper.Execute(sql, new { ID = ID });
msg.Success = true;
return msg;
}
catch (Exception ex)
{
msg.Success = false;
msg.ErrMsg = ex.Message;
}
return msg;
}
4 引用方式:
其中你需要的那个数据就是 返回对象 SetIndexNoLeft 属性的 NoOut文章来源:https://www.toymoban.com/news/detail-654248.html
/// <summary>
/// 获取当前编号
/// </summary>
/// <param name="ThisType">当前类型</param>
/// <param name="DPNO">店铺No</param>
/// <param name="PhoneNo">电话号码后四位</param>
/// <returns></returns>
public SetIndexNoLeft GetNoByType(ENoTypes ThisType, string DPNO = "",string PhoneNo="0000")
{
SetIndexNoLeft GetFromDB = null;//数据库获取的数据
switch (ThisType)
{
case ENoTypes.客户编号:
GetFromDB = GetIndexNo($"客户编号{DPNO}", 2, 3);
GetFromDB.NoOut = $"YYU{DPNO}KH{GetFromDB.NoLeft}{PhoneNo}"; //YYU+001(店铺编号)+KH+2023(年)+999(流水号)+0000(电话号码后4位)
break;
case ENoTypes.店铺编号:
GetFromDB = GetIndexNo("店铺编号", 3, 3);
GetFromDB.NoOut = GetFromDB.NoLeft; //001(店铺编号)
break;
case ENoTypes.报告编号:
GetFromDB = GetIndexNo($"报告编号{DPNO}", 1, 2);
GetFromDB.NoOut = $"YYU{DPNO}{GetFromDB.NoLeft}"; //YYU+001(店铺编号)+2023(年)+08(月)+02(日)+99(流水号)
break;
case ENoTypes.样本编号:
GetFromDB = GetIndexNo($"样本编号{DPNO}", 1, 2);
GetFromDB.NoOut = $"YYU{DPNO}YB{GetFromDB.NoLeft}"; //YYU+001(店铺编号)+YB+2023(年)+08(月)+02(日)+99(2位流水号)
break;
case ENoTypes.样本类型编号:
GetFromDB = GetIndexNo("样本类型编号", 2, 3);
GetFromDB.NoOut = $"YYUYBLX{GetFromDB.NoLeft}"; //YYU+001(店铺编号)+YB+2023(年)+08(月)+02(日)+99(2位流水号)
break;
case ENoTypes.项目编号:
GetFromDB = GetIndexNo("项目编号", 3, 3);
GetFromDB.NoOut = $"YYUXM{GetFromDB.NoLeft}"; //
break;
default://用户编号
GetFromDB = GetIndexNo("用户编号", 3, 5);
GetFromDB.NoOut = $"YYU{GetFromDB.NoLeft}"; //YYU+99999(流水号)
break;
}
return GetFromDB;
}
}
在程序里的应用:文章来源地址https://www.toymoban.com/news/detail-654248.html
SetIndexNoLeft ThisNoLeft = null;//设置全局
ThisNoLeft = new DALSetNoIndex().GetNoByType(ENoTypes.客户编号,thisMendian.No, PhoneNo);//设定指定的编号
No.Text = ThisNoLeft.NoOut;
if (ThisNoLeft!=null )//插入数据完成后进行数据删除
{
new DAL.DALSetNoIndex().Delete(ThisNoLeft.Id);
}
到了这里,关于关于计数以及Index返回订单号升级版002(控制字符长度,控制年月标记,拾取未使用编号)--使用两个表来满足操作的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!