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
GOSET QUOTED_IDENTIFIER ON
GOCREATE 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]GOALTER TABLE [dbo].[SetNoIndex] ADD CONSTRAINT [DF_SetNoIndex_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GOALTER TABLE [dbo].[SetNoIndex] ADD CONSTRAINT [DF_SetNoIndex_Updated] DEFAULT (getdate()) FOR [Updated]
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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
GOSET QUOTED_IDENTIFIER ON
GOCREATE 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]GOALTER TABLE [dbo].[SetIndexNoLeft] ADD CONSTRAINT [DF_SetIndexNoLeft_Updated] DEFAULT (getdate()) FOR [Updated]
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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月累計
beginset @SetThisSigle = SUBSTRING(@ThisDate,1,6);
end
else if(@SetNoType =1 )--1日累計
beginset @SetThisSigle = @ThisDate;
end
else if(@SetNoType =2)--2年累計
begin set @SetThisSigle = SUBSTRING(@ThisDate,1,4);
end
else --3總共累計
beginset @SetThisSigle='';
end declare @SetIndex int ,@OPIndexId int,@OPIndexNoLeftId int,@HebingOutNo nvarchar(20) ; ----@OPIndexId 為獲取到當前Id ,@OPIndexNoLeftId 是獲取當前數據條,@HebingOutNo 是當前要導出的數據set @OPIndexNoLeftId = 0;---初始化輸出表Idif 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; ----返回當前剩下那條數據的Idupdate 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 elsebeginset @SetIndex = 1;insert into SetNoIndex ([NoType],[ThisIndex],[ThisSigle],[KeyWord]) values (@SetNoType,@SetIndex,@SetThisSigle,@SetKeyword);-- 插入數據set @OPIndexId = @@IDENTITY;--- 獲取當前Idend --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
/// <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; }
}
在程序里的應用:
SetIndexNoLeft ThisNoLeft = null;//設置全局ThisNoLeft = new DALSetNoIndex().GetNoByType(ENoTypes.客戶編號,thisMendian.No, PhoneNo);//設定指定的編號No.Text = ThisNoLeft.NoOut;if (ThisNoLeft!=null )//插入數據完成后進行數據刪除{new DAL.DALSetNoIndex().Delete(ThisNoLeft.Id);}