自增長欄位值的連續遞增實現

一．背景在上一篇《資料庫操作類SqlHelper》博文的最後，提到了一個實踐運用中遇到的問題，就是資料庫表中的自增長欄位的賦值不受人為控制。比如資料庫有一個tb_Department表，DeptNO欄位為自增長主鍵。現在插入一行數據啊！DeptNO欄位怎麼就是22了呢，不應該是從4開始嗎？原 ...

一．背景

在上一篇《資料庫操作類SqlHelper》博文的最後，提到了一個實踐運用中遇到的問題，就是資料庫表中的自增長欄位的賦值不受人為控制。比如資料庫有一個tb_Department表，DeptNO欄位為自增長主鍵。

現在插入一行數據

啊！DeptNO欄位怎麼就是22了呢，不應該是從4開始嗎？

原因：這個表之前進行過很多插入操作，資料庫針對自增長欄位的每次插入都會自動+1，後來刪除了一部分行數據，然後重新插入的時候，資料庫不會依據表中缺失的欄位值進行賦值，而是在原先的基礎上繼續+1賦值。

結果：在新插入的“哈哈系”數據行之前，其實資料庫已經向表裡插入過21次了，只是DeptNO欄位值大於3的行數據被刪除了，現在要新插入行數據的話，就會在21的基礎上+1，也就是第二個表中出現的22了。

期望：

在插入新數據的時候，針對自增長欄位可以人為控制；
實際運用中，其實用戶並不知道數據表中自增長欄位缺失的是哪些值，程式需要自動提供缺失或者預設值。

二．設計

1.在插入新數據的時候，針對自增長欄位可以人為控制

資料庫中針對自增長欄位在插入時，不可以指定顯式值的。

insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')

這樣插入數據會報錯的，提示你“當Identity_Insert設置為off時，不能為表’tb_Department’中的標識列插入顯式值”。很明顯，第一個期望的解決方案就是將Identity_Insert設置on，然後執行顯式值插入，最後關閉標識列插入開關。

set identity_insert tb_Department on
insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')
set identity_insert tb_Department off

執行看看能不能插入，哇哦，成功了，棒棒噠。

2.實際運用中，用戶並不知道數據表中自增長欄位未使用有哪些值，程式需要自動提供缺失或者預設值

自增長欄位的值分為缺失值和預設值（這個術語是我自己定的，為了方便描述）

缺失值：比如數據表中自增長欄位的值為（1，2，3，5），則缺失值為4。要想讓程式自動檢索到缺失值，需要對數據表進行全面掃描，逐行判斷自增長欄位的值是否連續遞增，只要檢索到不連續的值就將對應序列的值返回，並顯示在窗體上，無需用戶自己輸入。

預設值：比如數據表中自增長欄位的值為（1，2，3，4），則預設值為5。假設原先有10行數據，然後將大於4的行刪除後，自增長欄位本身還是連續遞增的，只需要找到預設值，返回給用戶。

利用SQL腳本創建存儲過程實現：（註意：該實現返回當前自增長欄位中第一個缺失值/預設值，只適用於每次插入一行數據的情況）

--創建一個存儲過程用於自動提取自增長欄位的第一個缺失值和預設值
create procedure NumOfDeptNOForInsert
@temp int output  --定義一個輸出參數，用於返回缺失值/預設值
as
declare @Count int   --定義一個當前表中的行數
select @Count=COUNT(1) from tb_Department   --給變數@Count賦值
declare @I int, @IsOK bit = 0,@num int = 1  --定義一個用於迴圈的@I變數，一個用於判斷是缺失值還是預設值的變數@IsOK，一個記錄預設值的變數@num
set @I = 1;   --變數@I賦值為1
while(@I <= @Count)   --開始迴圈掃描行數據
begin
    select @temp=DeptNO from tb_Department where DeptNO=@I   --檢索DeptNO值=@I值的行數據
    if(@temp != @I)  --判斷@I值與DeptNO值是否比對不成功
    begin
        set @temp = @I  --將@I值賦值給@temp
        set @IsOK = 0   --標記為缺失值
        break    --退出迴圈     
    end
    else    --判斷@I值與DeptNO值是否比對成功
    begin
        set @I = @I +1  --@I+1
        set @num = @I   --將@I賦值給@num
        set @IsOK = 1   --標記為預設值
    end 
end   

if(@IsOK =0)   --判斷是缺失值還是預設值，如果是缺失值
begin
    select @temp  --直接返回@temp
end
else    --如果是預設值
begin
    set @temp = @num  --將@num賦值給@temp
    select @temp      --再返回@temp
end

自增長欄位的連續遞增插入的存儲過程設計好後，首先在SQL Server中檢驗一下。

declare @temp int --定義輸出參數
exec dbo.NumOfDeptNOForInsert @temp  --調用儲存過程
print @temp  --列印輸出參數

缺失值的檢驗：

調用存儲過程看看缺失的第一個值是不是5，結果跟預期一樣。

預設值的檢驗：

調用存儲過程，找到的第一個預設值為7，結果跟預期的一樣。

三．實踐

前面的分析設計做好後，當然就是運用於實踐了，主要是編寫獲取自增長欄位的缺失值/預設值的方法：

        /// <summary>
        /// 獲取自增長欄位的第一個缺失值或者預設值
        /// </summary>
        /// <returns>缺失值/預設值</returns>
        private int GetDeptNO()
        {
            string cmdText = @"NumOfDeptNOForInsert";
            SqlParameter[] parameters =
            {
                new SqlParameter("@temp",SqlDbType.Int)
            };
            parameters[0].Direction = ParameterDirection.Output;
            int deptNO = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.StoredProcedure, cmdText,parameters);
            return deptNO;
        }

程式整體的UI設計和編碼在博文《資料庫操作類SqlHelper》中都已經講述，這裡就不在反覆講了。相對於之前來說，需要更改代碼的地方為“增加”按鈕的點擊處理程式和InsertData()方法：

        private void tsbInsert_Click(object sender, EventArgs e)
        {
            cmdType = CmdType.Insert;
            //將gbDept控制項設置可用,textbox控制項設為可用,並將Text屬性清空
            this.gbDept.Enabled = true;
            this.txtDeptName.Enabled = true;
            this.txtDeptName.Text = string.Empty;
            //顯示即將插入的DeptNO值
            this.lbDeptNO.Text = GetDeptNO().ToString();
        }

        /// <summary>
        /// 插入數據
        /// </summary>
        private void InsertData()
        {
            //判斷系部名稱是否為空
            if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim()))
            {
                MessageBox.Show("系部名稱不能為空！");
                return;
            }
            //定義插入數據的SQL腳本，其中set identity_insert tb_Department on/off主要是為了能讓自增長主鍵連續有序地插入
            string cmdText = @"set identity_insert tb_Department on 
                               insert into tb_Department(DeptNO,DeptName) values(@DeptNO,@DeptName)
                               set identity_insert tb_Department off";
            ////定義插入數據的Sql腳本
            //string cmdText = @"insert into tb_Department(DeptName) values(@DeptName)";
            //SQL腳本參數設置
            SqlParameter[] parameters =
            {
                new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text),
                new SqlParameter("@DeptName",(object)this.txtDeptName.Text.Trim())
            };
            //執行插入，並返回受影響的行數
            int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters);
            //判斷是否插入成功，並提示
            if (rows > 0)
            {
                //更新datagridview控制項的數據
                LoadData();
                //顯示即將插入的DeptNO值
                this.lbDeptNO.Text = GetDeptNO().ToString();
                //將系部名稱設為空
                this.txtDeptName.Text = string.Empty;
                MessageBox.Show("插入成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("插入失敗！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            //執行增刪改操作
            switch (cmdType)
            {
                case CmdType.Insert:
                    InsertData();
                    break;
                case CmdType.Delete:
                    DeleteData();
                    break;
                case CmdType.Update:
                    UpdateData();
                    break;
            }
        }

四．結果

UI和編碼完成後，調試程式是很關鍵的，能從調試的過程中重現整個功能的思路，也能找到一些問題所在，修複bug，然後重編碼。廢話不多說看結果吧：

1.缺失值：

假設一開始的tb_Department表如下圖所示，理論上缺失的值為（4，5，7），現在往表裡插入新值，看看結果如何。點擊“增加”按鈕，窗體下方系部編號自動出現第一個缺失值4，系部名稱我們設置為“嘟嘟系”，提交添加成功後，系部編號會自動顯示下一個缺失值5。為了後面的預設值的結果，我們再增加缺失值（5，7）兩行數據，使DeptNO欄位連續遞增。