SQL處理數據併發，解決ID自增

1 創建MaxIdProcess表，由於存儲ID的最大值

CREATE TABLE [dbo].[MaxIdProcess](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,    --自增ID
    [TableNM] [nvarchar](200) NOT NULL,    --存儲表明
    [Prefix] [nvarchar](50) NULL,    --ID首碼
    [Radix] [char](2) NULL,    --
    [MaxId] [nvarchar](50) NULL,    --存儲最大ID
    [CreateDatetime] [datetime] NULL,    --創建時間
    [LastModifyDatetime] [datetime] NULL,
    [LastModifyBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_MaxIdProcess] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2、創建存儲過程 Pro_GetTableNextMaxIdByTableName 獲取最大ID

 1 CREATE procedure [dbo].[Pro_GetTableNextMaxIdByTableName]  
 2 @TableName char(50),  --table名稱
 3 @Prefix char(3),  --ID首碼
 4 @NextId char(16) out    --ID輸出
 5 AS
 6 
 7 begin 
 8     --if not exists (select * from MaxIdProcess where TableName=@TableName)
 9     --begin
10     --    insert into MaxIdProcess values(@TableName,null)
11     --end
12     --    update MaxIdProcess 
13     --        set @NextId= isnull(MaxId, @Prefix + '0000000000001'),
14     --                MaxId = dbo.Fun_GetMaxId(MaxId,@Prefix)
15     --    where TableName=@TableName
16 
17 
18     --檢查系統表中是否存在該表，如果不存在則調用Pro_GetRandomStr存儲過程獲取一個隨機ID
19    IF EXISTS (SELECT object_id FROM sys.objects(nolock) WHERE type='U' AND name=@TableName)
20    BEGIN
21         EXEC Pro_GetRandomStr @NextId output
22         RETURN 
23    END 
24     
25     --檢查MaxIdProcess表中是否存有需要獲取ID的表名，如果不存在則插入數據
26    if not exists (select * from MaxIdProcess where TableNM=rtrim(@TableName))
27     begin
28         insert into MaxIdProcess values(@TableName,@Prefix,'10',0,getdate(),getdate(),'Auto')
29     end
30     
31         declare @temp bigint
32         update MaxIdProcess
33         set @temp=cast(rtrim(MaxId) as bigint),MaxId=MaxId+1
34         where rtrim(TableNM)=rtrim(@TableName)
35         set @NextId=@Prefix+right(cast(1000000000000000+@temp as nvarchar(16)),13)
36 end

3、創建執行存儲過程，如插入新增用戶

CREATE PROCEDURE [dbo].[Pro_User_Insert]
     @UserId CHAR(16) OUT ,
     @UserNM NVARCHAR(50) ,
     @Description NVARCHAR(255) 
AS

BEGIN TRY
    BEGIN
        DECLARE @Name NVARCHAR(50);
        SELECT  @UserId = UsersTb.UserId ,
                @Name = UsersTb.Description
        FROM    dbo.UsersTb
        WHERE   UsersTb.UserNM = @UserNM;
        IF NOT ISNULL(@UserId, '') = ''
        BEGIN
          SELECT  @UserNM + @Name + '已經存在';
          --SELECT  '用戶已經存在';
          RETURN;
        END;
        DECLARE @MaxId CHAR(16);  
        EXEC dbo.GetTableNextMaxIdByTableName 'User', 'Usr', @MaxId OUT;    
        SET @UserId = @MaxId;
        
        INSERT  INTO UsersTb
        (
         [UserId] ,
         [UserNM] ,
         [Description] 
        )
        VALUES
        (
            @UserId ,
            @UserNM,
            @Description
        );
        SELECT  '執行成功';
    END
 END TRY
  BEGIN CATCH
    SELECT  ERROR_MESSAGE(); 
  END CATCH

4、執行新增用戶存儲過程

DECLARE @UserId int;
EXEC Pro_User_Insert @UserId output,'zhangsan','張三' ;

5、完成

關於 Pro_GetRandomStr 存儲過程

CREATE Procedure [dbo].[Pro_GetRandomStr]
 @RandomStr varchar(16) output
 as
 BEGIN
    set nocount on
    
    declare @s varchar(61)   
    declare @r varchar(16)   
    declare @pos int
    declare @len int
    set @s = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ012345678'   
    set @len = len(@s);
    set @r = ''   
    while len(@r) < 16   
    begin   
        set @pos = cast(rand()*61 as int);
        --while @pos > @len or @pos <1
        --begin
        --    if(@pos < 1)
        --     set @pos = cast(rand()*61 as int);
        --    else
        --     set @pos = cast(@pos /2 as int);
        --end
        set @r = @r + substring(@s, @pos, 1)
        --select @r
    end
    set @RandomStr =  upper(@r)
END

 c# 實現存儲過程

string strcon = "Data Source=(local)\\SQLEXPRESS; Initial Catalog=TestDatabase; ";
            strcon += "Integrated Security=True;";
            using (SqlConnection con = new SqlConnection(strcon))
            {
                con.Open();
                SqlParameter[] parameters = 
                {
                    new SqlParameter("@UserId", SqlDbType.Char,16),
                    new SqlParameter("@UserNM", SqlDbType.NVarChar),
                    new SqlParameter("@Description", SqlDbType.NVarChar),
                };

                //parameters[0].Value = null;
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = "zhangsan";
                parameters[2].Value = "張三";

                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Pro_User_Insert";
                cmd.Connection = con;
                cmd.Parameters.AddRange(parameters);
                object obj = cmd.ExecuteScalar();
                //string msg = cmd.ExecuteScalar().ToString();
                con.Close();
                
            }