.NET-SqlSever代理(自动流水号)

r囧r小猫 2023-01-08 07:28 123阅读 0赞

### .NET.自动流水号了解一下 ###

*  0. 目录
 *  1. .NET那些事儿
 *   *  1.1 配置步骤
     *   *  1.1.1 创建序列
         *  1.1.2 创建数据库作业
     *  1.2 内置代码调用

# 0. 目录 #

# 1. .NET那些事儿 #

## 1.1 配置步骤 ##

### 1.1.1 创建序列 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70]

Use Training
    GO
    CREATE SEQUENCE [dbo].[PurcharseOrderNumberSeq] 
     AS [bigint]
     START WITH 1
     INCREMENT BY 1
     MINVALUE 1
     MAXVALUE 9999
     CACHE 
    GO

### 1.1.2 创建数据库作业 ###

1）维护作业名称  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 1]  
新建作业，名称：重置SQL序列  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 2]  
2）维护“步骤”  
切换到“步骤”选项卡，新建步骤  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 3]  
a）步骤名称：重置SQL序列为1，  
b）命令：

Use Training
    GO
    ALTER SEQUENCE dbo.PurcharseOrderNumberSeq RESTART WITH 1;

c）点击“确定”按钮  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 4]  
3）维护“计划”  
切换到“计划”选项卡，新建计划  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 5]  
a）重置序列为1  
b）执行频率：每天  
c）每天：凌晨执行  
d）点击“确定”按钮  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 6]

## 1.2 内置代码调用 ##

private string GetAccountAutoNumber(string prefix, string seqName)
            {
                DateTime currentDate = DateTime.Now;
                string formno = prefix + currentDate.Year.ToString() + currentDate.Month.ToString().PadLeft(2, '0') + currentDate.Day.ToString().PadLeft(2, '0');
    
                #region 查询最大的SQL序列
                string sql = $@"SELECT NEXT VALUE FOR {seqName}";
                DataTable dt = _dbHelper.ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0)
                    throw new InvalidPluginExecutionException("未获取到SQL序列：AccountNumberSeq，请联系系统管理员");
                #endregion
    
                formno += dt.Rows[0][0].ToString().PadLeft(4, '0');
                return formno;
            }

**参数讲解:**

*  `参数prefix:`**前缀名**
 *  `参数seqName:`**自定义序列名** \-> 例如:前文PurcharseOrderNumberSeq

--------------------

**返回值讲解**

*  `参数formno:`**流水号** \-> 例如P202101190001

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70]: /images/20221119/cfd8efd1eeb5471c90f210c64fb7a85d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 1]: /images/20221119/da13cb3cf42a4c42b7611e072a2d7d3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 2]: /images/20221119/90bc1ba9029a437a82023c589b0a3dca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 3]: /images/20221119/e35a69244b624566a7fd57e1066b75b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 4]: /images/20221119/defb4fae108748499f81a09babe65834.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210119143629388.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2021011914373295.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ3MzIyOA==,size_16,color_FFFFFF,t_70