动态文档 ID，无需维护计数器！

• Download source files - 18 Kb 

引言

我相信我们中任何处理过商业业务应用程序项目（库存控制、财务会计等）的人，都一定遇到过需要为任何相关模块生成“自动文档 ID”的挑战。典型的例子是，自动生成“销售发票 ID”，其模式为 {yy-nnnnn}，其中 yy 是年份的后两位数字，nnnnn 是递增的计数器，从 00001 开始。

解决此问题的最典型方法是，在表格或某些配置文件中维护一个“计数器”，然后在每次创建新记录时更新它。在许多情况下，这可能是一种可行的方法……然后当你面临更大的挑战，如何在不必维护计数器的情况下做同样的事情时，问题就来了！

我假设本文的读者熟悉 T-SQL 和 ADO.NET C# 环境。

挑战

上周我与一位朋友聊天，他问我是否能帮他解决类似的问题……是的，你猜对了，问题正是本文标题中提到的。他正在寻找一种生成文档 ID 的方法，而无需维护计数器的麻烦。他的问题把我带回了 7 年前的记忆，当时我的一个客户在我为他们做的某个会计系统中提出了类似的功能。

事情是这样开始的：我的客户开辟了一个新的业务流，他们想为这个新部门生成发票，但他们面临的麻烦是，即使在财政年度结束后，他们也需要继续生成发票，这意味着他们仍然可以生成上一财政年度的发票！

像这样：

2006 年 - 最新发票 ID

06-01230

现在，如果他们想生成属于去年的发票，那么在不干扰当前编号系统的情况下，必须找出去年连续的编号并从那里开始。

2005 年 - 最新发票 ID

05-21233

这就是他们想要在不维护任何计数器的情况下实现的目标，但是，他们同意提供他们想要的发票是哪一年的信息，2005 年（过去）还是 2006 年（当前）。

解决方案

我与朋友讨论的解决方案帮助了他，就像它在过去帮助我一样，我想为什么不与社区其他人分享这个讨论呢……也许这会帮助有需要的人，或者只是另一个有趣的阅读，而且我也为自己找了一个借口来写我的第一篇文章！;)

我将解决方案分为两部分，第一部分是存储过程，第二部分是一个简单的 Windows Forms C# 应用程序来演示该技术。

存储过程

我正在使用 SQL Server 2000 的“Northwind”数据库。请运行以下脚本创建一个名为“Invoice”的虚拟表，我们将使用它来存储我们动态创建的文档 ID。

if exists (select * from dbo.sysobjects 
          where id = object_id(N'[dbo].[Invoice]') 
          and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice]
GO
CREATE TABLE [dbo].[Invoice] (
            [Invoice_id] [varchar] (10) 
                              COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [Customer_name] [varchar] (50) 
                              COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [Invoice_amount] [money] NOT NULL 
) ON [PRIMARY]
GO

以下是存储过程代码，它将从用户界面获取输入并生成新的 ID，并将其存储在“Invoice”表中。

CREATE Procedure insert_invoice_record

/*
**  Inserts new record in invoice table with dynamically crated id.
**
**  INPUT - PARAMETERS: Year_flag, Customer_Name, Invoice_Amount
**  OUTPUT - PARAMETERS: Newly created Invoice ID 
**  MODIFICATION HISTORY:
**  NAME                DATE             MODIFICATION
**  Asif Sayed   27th March, 2006      Original Version
*/
 @customer_name varchar(50),
 @invoice_amount money,
 @year_flag char(1),  
 @new_invoice_id varchar(10) OUTPUT
AS
 SET NOCOUNT ON
 SET DATEFORMAT dmy
 
  DECLARE @err_code integer
 DECLARE @found_error integer
 DECLARE @err_msg  varchar(1000)
 DECLARE @tmp_invoice_id nvarchar(10)
 DECLARE @tmp_date nvarchar(8)

 SET @found_error = 0
 SET @err_code = 0

 -- store current year from date on database server
 SET @tmp_date = (SELECT RIGHT(CAST(year(getdate()) AS nvarchar(4)),2))
    
 -- check for year flag (P-Past, C-Current) to be used
 IF (@year_flag) = 'P'
 BEGIN
   -- if year has zero in front minus 1 year from next digit
   IF (LEFT(@tmp_date, 1)) = '0'  
      SET @tmp_date = '0' + CONVERT(NVARCHAR(2), 
                         CONVERT(INTEGER, LEFT(@tmp_date,2)) - 1) + '-'
   ELSE
      SET @tmp_date=CONVERT(NVARCHAR(2),CONVERT(INTEGER, @tmp_date) - 1) + '-'
   END
 ELSE
     SET @tmp_date = @tmp_date + '-'
 

 -- find max of invoice ids counter from current table to be used to crate 
 -- new id
 SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 4, 5) + 1)
                        FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))
 

 -- if this is first invoice record then start counter with ....1 else 
 -- whatever the most recent counter
 IF @tmp_invoice_id IS NULL
    SET @tmp_invoice_id = '00001'
 ELSE
    SET @tmp_invoice_id = replicate('0',5-LEN(@tmp_invoice_id)) + 
        @tmp_invoice_id
 
 -- store new invoice id to output param
 SET @new_invoice_id = @tmp_date+@tmp_invoice_id
 

 -- check if any other user has already utilized the newly acquired 
 -- invoice id
 IF EXISTS (SELECT invoice_id
  FROM Invoice
  WHERE UPPER(invoice_id) = UPPER(@new_invoice_id))
 BEGIN
    SET @err_msg = '* Invoice ID: ' + @new_invoice_id + 
       ' already exists!, please try saving again!' + CHAR(13)
    SET @found_error = 1
 END
 

  -- if error found skip insert
 IF (@found_error = 1)
  GOTO Exception
 

 -- Insert the record in invoice table with new id
 INSERT INTO Invoice (invoice_id, customer_name, invoice_amount)
        VALUES (@new_invoice_id, @customer_name, @invoice_amount)
 

 -- make a final check to see if any other error happend during process
 SET @err_code = @@ERROR
 IF (@err_code <> 0)
 BEGIN
   SET @err_msg = 'Error ' + CONVERT(VARCHAR(20), @err_code)  
       + ' occurred while Generating Invoice Record'
   GOTO exception
 END
 
 RETURN 0
 

 exception:
    RaisError ('Creating Invoice: %s',  16, 1, @err_msg)
    RETURN -1

GO

以下代码可用于使用 SQL Enterprise Manager 测试存储过程。

DECLARE @new_invoice_id varchar(10)

-- following will create invoice for past year
EXEC insert_invoice_record 'test customer', 12.22, 'P',@new_invoice_id OUTPUT
PRINT @new_invoice_id

-- following will create invoice for current year
EXEC insert_invoice_record 'test customer', 12.22, 'C',@new_invoice_id OUTPUT
PRINT @new_invoice_id

它是如何工作的！

如果你仔细查看存储过程代码，它会执行以下操作来获取新的发票代码：

1. 确定发票属于过去年份（05）还是当前年份（06）。
2. 查找“yy-”模式后可用的最大数字。
3. 如果 MAX 返回 NULL，则表示这是第一个条目，因此计数器变为“00001”；否则，它将获取最大数字并根据计数器（本例中为 5）长度减去最大数字长度来填充前导“0”。

这样，每次向 Invoice 表中添加条目时，它都会根据最后输入的行获取最新的计数器，这意味着即使有人从表中物理删除了较早的行，它也不会受到影响，并且将始终提供带有最新计数器的最新发票 ID。

更多模式呢？

当然！以下模式讲述了另一种方法……要获得“mmyy-nnnnn”，需要进行以下更改：

SET @tmp_date = (SELECT (CASE WHEN LEN(MONTH(getdate())) = 1 
                         THEN '0' + CAST(MONTH(getdate()) AS nvarchar(2))
                         ELSE CAST(MONTH(getdate()) AS nvarchar(2)) END) 
                     + RIGHT(CAST(year(getdate()) AS nvarchar(4)),2)) + '-'

SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 6, 5) + 1)
                       FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))

IF @tmp_invoice_id IS NULL
    SET @tmp_invoice_id = '00001'
ELSE
BEGIN
    IF @tmp_invoice_id = '100000' 
    BEGIN
        SET @err_msg = 'Only 99999 unique Invoice can be generated for a ' +
                       'given Month!'
        GOTO exception
    END
    ELSE
        SET @tmp_invoice_id = replicate('0',5-LEN(@tmp_invoice_id)) + 
                                                             @tmp_invoice_id
    END
END

所以，如你所见，可能性是无限的，我们可以根据需求更改逻辑，我们不再需要担心为任何获取新 ID 的所需逻辑维护计数器！

另一个不经意的用途可能是，只需查看最后一张发票号，就可以知道“该特定月份生成了多少张发票”。

让我们从 ADO.NET 的角度来看

让我们从 ADO.NET 的角度来看

请下载附件代码并使用 Visual Studio 2003 打开，确保您正确更改连接字符串并运行代码以查看结果。此 Windows 窗体应用程序是快速拼凑起来以演示存储过程的用法，它绝不是高质量的作品，例如检查“发票金额”文本框中的数值，所以我假设您将是一个“好用户”在此数据输入控件中输入数值J

保存按钮后面的代码如下所示：

//declare connection string
string cnString = @"Data Source=(local);Initial Catalog=northwind;" +
                   "User Id=northwind;Password=northwind";

//use following if you use standard security
//string cnString = @"Data Source=(local);Initial Catalog=northwind;
//                    Integrated Security=SSPI";

//declare connection and command
SqlConnection saveCnn = new SqlConnection(cnString);
SqlCommand saveCmd = new SqlCommand("insert_invoice_record", saveCnn);

try
{
    //open connection
    saveCnn.Open();

    //configure command 
    saveCmd.CommandTimeout = 90;
    saveCmd.CommandType = CommandType.StoredProcedure;

    //create parameters and add it to command object
    //parameter for customer name
    SqlParameter parCustomerName = new SqlParameter("@customer_name", 
         SqlDbType.VarChar, 50);
    parCustomerName.Value = txtCustomerName.Text.Trim();

    //parameter for invoice amount
    SqlParameter parInvoiceAmount = new SqlParameter("@invoice_amount", 
         SqlDbType.Money);
    parInvoiceAmount.Value = Convert.ToDouble(txtInvoiceAmount.Text.Trim());

    //parameter for last year flag
    SqlParameter parYearFlag = new SqlParameter("@year_flag", SqlDbType.Char,
         1);
    if (chkLastYear.Checked == true)
        parYearFlag.Value = "P";
    else
        parYearFlag.Value = "C";

    //parameter to get invoice id as output
    SqlParameter parInvoiceID = new SqlParameter("@new_invoice_id", 
        SqlDbType.VarChar, 10);
    parInvoiceID.Value = "-1";
    parInvoiceID.Direction = ParameterDirection.Output;

    saveCmd.Parameters.Add(parCustomerName);
    saveCmd.Parameters.Add(parInvoiceAmount);
    saveCmd.Parameters.Add(parYearFlag);
    saveCmd.Parameters.Add(parInvoiceID);

    //execute command to create invoice
    saveCmd.ExecuteNonQuery();

    //get new id and display in invoice id textbox
    txtInvoiceID.Text = parInvoiceID.Value.ToString();

    //close the connection
    saveCnn.Close();

    MessageBox.Show("Invoice Record created with ID: " + txtInvoiceID.Text);
}
catch (Exception ex)
{
    //display error message in case something goes wrong
    MessageBox.Show("Following Error found while creating Invoice Record: " +
                    ex.Message);
}
finally
{
    //check to see if conneciton is still open the close it
    if(saveCnn.State == ConnectionState.Open)
    {
        saveCnn.Close();
    }
}

 

这个解决方案有多好？

嗯，任何解决方案都有其优缺点。  我唯一能看到的潜在问题是极小的概率，即多个用户生成相同的 ID，换句话说，并发性！  我对此进行了一些测试，通过五个并发进程每秒生成十分之一的 ID，但未能生成任何重复实例。  我鼓励你们中任何有机会的人尝试运行此代码，看看是否能重现重复场景。  如果发生重复，补救措施是，如果您注意到代码的最后阶段，存储过程会进行检查并要求用户再次尝试保存记录。  也可以在那里添加一个额外的检查，以查看是否发生重复，在不打扰用户的情况下再尝试获取 ID 并将其用于保存到表中。  最后，所提出的解决方案适用于在桌面客户端服务器环境中运行的典型业务应用程序，我根本没有考虑将此代码用于在每秒发生百万次以上点击的环境中生成 ID。

关于...废话

这是我第一次尝试发布文章；希望我没有辜负它。对于建设性批评，我一直虚心接受。所以，如果你有任何评论，请务必提出，我乐意倾听。

如果你喜欢我的作品，那就不要害羞，我也喜欢被赞美；)

免责声明：我只是想说……如果您使用我们在这里讨论的内容并导致任何形式或形状的损失，请不要追究我的责任；如果您获得了利润，那么让我们分享它；) 毕竟谁不喜欢利润呢…… :) 开个玩笑。