1實(shí)現(xiàn)步驟以及說明
1.根據(jù)參數(shù)獲取當(dāng)前setNoIndex表里現(xiàn)在的No的index值，如果包含關(guān)鍵字當(dāng)前對(duì)應(yīng)數(shù)據(jù)，則現(xiàn)在SetIndexNoLeft 表中找到有無未使用并未占用的那條數(shù)據(jù)（被占用的數(shù)據(jù)IsTaken=1，生成后使用當(dāng)前時(shí)間與updated時(shí)間進(jìn)行比對(duì)，然后時(shí)間超過30分鐘后會(huì)把狀態(tài)變更為 IsTaken =0 ），如果有就返回，如果沒有就創(chuàng)建一條新的SetIndexNoLeft記錄并返回。
2.如果當(dāng)前SetNoIndex下面沒有數(shù)據(jù)，則新建一條數(shù)據(jù)，并且在SetIndexNoLeft 創(chuàng)建一條新數(shù)據(jù)創(chuàng)建時(shí) IsTaken就是1,被占用。
3.在主要對(duì)象需要插入時(shí)，插入成功后數(shù)據(jù)進(jìn)行刪除當(dāng)前 這一條Id的SetIndexNoLeft數(shù)據(jù)。
2數(shù)據(jù)庫表：
表1：年月日數(shù)據(jù)生成Index數(shù)據(jù)表
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月累計(jì)，1日累計(jì)，2年累計(jì)，3，總共累計(jì)' , @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'當(dāng)前排序' , @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'當(dāng)前標(biāo)記（如果是日累計(jì)就是20230802，如果是月累計(jì)就是 202308 ，如果是年累計(jì)就是 2023，如果是 所有累計(jì)就是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'關(guā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'創(chuàng)建時(shí)間' , @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'更新時(shí)間' , @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'編號(hào)自動(dòng)生成器' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetNoIndex'
GO
表2： 生成的臨時(shí)編號(hào)表
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'編號(hào)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'剩下的編號(hào)' , @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'處理時(shí)間' , @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'編號(hào)拾取的明細(xì)端' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SetIndexNoLeft'
GO
3.需要的Model對(duì)象創(chuàng)建
public class SetIndexNoLeft
{
/// <summary>
///
/// </summary>
[Description("")]
public int Id { get; set; }
/// <summary>
/// 編號(hào)IndexId
/// </summary>
[Description("編號(hào)IndexId")]
public int NoIndexId { get; set; }
/// <summary>
/// 剩下的編號(hào)
/// </summary>
[Description("剩下的編號(hào)")]
public string NoLeft { get; set; }
/// <summary>
/// 是否被占用
/// </summary>
[Description("是否被占用")]
public bool IsTaken { get; set; }
/// <summary>
/// 處理時(shí)間
/// </summary>
[Description("處理時(shí)間")]
public DateTime Updated { get; set; }
/// <summary>
/// 設(shè)置好的No全部
/// </summary>
public string NoOut { get; set; }
}
參考的枚舉實(shí)例（你可能用不到）：
/// <summary>
/// 編號(hào)類型
/// </summary>
public enum ENoTypes
{
報(bào)告編號(hào),客戶編號(hào),用戶編號(hào),樣本編號(hào),店鋪編號(hào),樣本類型編號(hào),項(xiàng)目編號(hào)
}
4 DAL數(shù)據(jù)庫操作類。
namespace DAL
{
/// <summary>
/// 編號(hào)自動(dòng)生成器操作類
/// </summary>
public class DALSetNoIndex
{
/// <summary>
///獲取當(dāng)前 0月累計(jì)，1日累計(jì)，2年累計(jì)，3，總共累計(jì)
/// </summary>
/// <param name="SetNoType"> 0月累計(jì)，1日累計(jì)，2年累計(jì)，3，總共累計(jì)</param>
/// <param name="SetKeyword">當(dāng)前關(guān)鍵字（需要的表單名子）</param>
/// <param name="OutIndexLength">index編號(hào)長(zhǎng)度 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);---當(dāng)前日期全部數(shù)據(jù)（20230108）
set @SetNoType = {SetNoType};--設(shè)定類型
set @OutStrLength ={OutIndexLength};--序號(hào)保留多少位
set @SetKeyword = '{SetKeyword}';---當(dāng)前關(guān)鍵字
Update SetIndexNoLeft set IsTaken = 0 where datediff( minute , Updated , getdate() )>60 and IsTaken =1 ;---先更新本表中所有已過期的數(shù)據(jù) 30分鐘期限
if (@SetNoType = 0)--0月累計(jì)
begin
set @SetThisSigle = SUBSTRING(@ThisDate,1,6);
end
else if(@SetNoType =1 )--1日累計(jì)
begin
set @SetThisSigle = @ThisDate;
end
else if(@SetNoType =2)--2年累計(jì)
begin
set @SetThisSigle = SUBSTRING(@ThisDate,1,4);
end
else --3總共累計(jì)
begin
set @SetThisSigle='';
end
declare @SetIndex int ,@OPIndexId int,@OPIndexNoLeftId int,@HebingOutNo nvarchar(20) ; ----@OPIndexId 為獲取到當(dāng)前Id ，@OPIndexNoLeftId 是獲取當(dāng)前數(shù)據(jù)條,@HebingOutNo 是當(dāng)前要導(dǎo)出的數(shù)據(jù)
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 --獲取對(duì)應(yīng)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; ----返回當(dāng)前剩下那條數(shù)據(jù)的Id
update SetIndexNoLeft set IsTaken = 1 ,Updated = getdate() where Id = @OPIndexNoLeftId; --修改當(dāng)前狀態(tài)
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 ;--在提取結(jié)果
end
end
else
begin
set @SetIndex = 1;
insert into SetNoIndex ([NoType],[ThisIndex],[ThisSigle],[KeyWord]) values (@SetNoType,@SetIndex,@SetThisSigle,@SetKeyword);-- 插入數(shù)據(jù)
set @OPIndexId = @@IDENTITY;--- 獲取當(dāng)前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 )--表內(nèi)無數(shù)據(jù)
begin
insert into SetIndexNoLeft ([NoIndexId] ,[NoLeft],[IsTaken],Updated ) values (@OPIndexId,@HebingOutNo,'true',getdate());
set @OPIndexNoLeftId= @@IDENTITY;--- 獲取當(dāng)前Id
end
select top 1 * from SetIndexNoLeft where Id = @OPIndexNoLeftId; -----返回當(dāng)前數(shù)據(jù)
";
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 引用方式：
其中你需要的那個(gè)數(shù)據(jù)就是 返回對(duì)象 SetIndexNoLeft 屬性的 NoOut文章來源：http://www.zghlxwxcb.cn/news/detail-654248.html
/// <summary>
/// 獲取當(dāng)前編號(hào)
/// </summary>
/// <param name="ThisType">當(dāng)前類型</param>
/// <param name="DPNO">店鋪No</param>
/// <param name="PhoneNo">電話號(hào)碼后四位</param>
/// <returns></returns>
public SetIndexNoLeft GetNoByType(ENoTypes ThisType, string DPNO = "",string PhoneNo="0000")
{
SetIndexNoLeft GetFromDB = null;//數(shù)據(jù)庫獲取的數(shù)據(jù)
switch (ThisType)
{
case ENoTypes.客戶編號(hào):
GetFromDB = GetIndexNo($"客戶編號(hào){DPNO}", 2, 3);
GetFromDB.NoOut = $"YYU{DPNO}KH{GetFromDB.NoLeft}{PhoneNo}"; //YYU+001（店鋪編號(hào)）+KH+2023（年）+999（流水號(hào)）+0000（電話號(hào)碼后4位）
break;
case ENoTypes.店鋪編號(hào):
GetFromDB = GetIndexNo("店鋪編號(hào)", 3, 3);
GetFromDB.NoOut = GetFromDB.NoLeft; //001（店鋪編號(hào)）
break;
case ENoTypes.報(bào)告編號(hào):
GetFromDB = GetIndexNo($"報(bào)告編號(hào){DPNO}", 1, 2);
GetFromDB.NoOut = $"YYU{DPNO}{GetFromDB.NoLeft}"; //YYU+001（店鋪編號(hào)）+2023（年）+08（月）+02（日）+99（流水號(hào)）
break;
case ENoTypes.樣本編號(hào):
GetFromDB = GetIndexNo($"樣本編號(hào){DPNO}", 1, 2);
GetFromDB.NoOut = $"YYU{DPNO}YB{GetFromDB.NoLeft}"; //YYU+001（店鋪編號(hào)）+YB+2023（年）+08（月）+02（日）+99（2位流水號(hào)）
break;
case ENoTypes.樣本類型編號(hào):
GetFromDB = GetIndexNo("樣本類型編號(hào)", 2, 3);
GetFromDB.NoOut = $"YYUYBLX{GetFromDB.NoLeft}"; //YYU+001（店鋪編號(hào)）+YB+2023（年）+08（月）+02（日）+99（2位流水號(hào)）
break;
case ENoTypes.項(xiàng)目編號(hào):
GetFromDB = GetIndexNo("項(xiàng)目編號(hào)", 3, 3);
GetFromDB.NoOut = $"YYUXM{GetFromDB.NoLeft}"; //
break;
default://用戶編號(hào)
GetFromDB = GetIndexNo("用戶編號(hào)", 3, 5);
GetFromDB.NoOut = $"YYU{GetFromDB.NoLeft}"; //YYU+99999（流水號(hào)）
break;
}
return GetFromDB;
}
}
在程序里的應(yīng)用：文章來源地址http://www.zghlxwxcb.cn/news/detail-654248.html
SetIndexNoLeft ThisNoLeft = null;//設(shè)置全局
ThisNoLeft = new DALSetNoIndex().GetNoByType(ENoTypes.客戶編號(hào),thisMendian.No, PhoneNo);//設(shè)定指定的編號(hào)
No.Text = ThisNoLeft.NoOut;
if (ThisNoLeft!=null )//插入數(shù)據(jù)完成后進(jìn)行數(shù)據(jù)刪除
{
new DAL.DALSetNoIndex().Delete(ThisNoLeft.Id);
}
到了這里，關(guān)于關(guān)于計(jì)數(shù)以及Index返回訂單號(hào)升級(jí)版002（控制字符長(zhǎng)度，控制年月標(biāo)記，拾取未使用編號(hào)）--使用兩個(gè)表來滿足操作的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
