Dynamic Document ID’s without maintaining Counters!

• <A href="DynamicIDs/DynamicIds.zip">Download source files - 18 Kb</A> 

Introduction

I am sure if any of us who have been dealt with a commercial business application project (Inventory Control, Financial Accounting etc.) must have been challenged with a mechanism to produce “Automatic Document ID” for any of the modules involved. The typical example would be to have “Sales Invoice IDs” to be generated automatically with pattern {yy-nnnnn}, were yy->last two digits of year and nnnnn->00001 incrementing counter.

The most typical solution to this issue would be to have a “counter” maintained in a Table or some Configuration file then update it every time a new record has been created. For many situations this could be a way to go… then comes the time when you are further challenged with how about doing the same thing without having the headache of maintaining the counter!

I assume the reader of this article is comfortable with understanding of T-SQL and ADO.NET C# environment.

Challenge

I was having a conversation with a friend last week and he asked me if I can help him with something like this… yes you got it right the issue was exactly as mentioned in the title of this article. The guy was looking to generate document id without the trouble of maintaining the counter. His query took me back to 7 years down the memory lane when one of my clients asked me similar feature in one of the Accounting system I did for them.

It all started like this: my client started a new business stream and they wanted to generate Invoices for this new department, however the trouble they face was to keep generating the Invoices, even when the Financial year is closed, that means they can still generate the Invoice of last fiscal year!

Some thing like this:

For year 2006 - Latest Invoice ID:

06-01230

Now, if they want to generate Invoice belong to last year then without disturbing the current numbers system must find out last year continuation number and start from there:

For year 2005 - Latest Invoice ID:

05-21233

This is all they wanted to achieve it without maintaining any counter, however, they did agree to supply information regarding which year Invoice they want 2005 (past) or 2006 (current).

Solution

The solution I discussed with my friend has helped him as it did helped me in past, I thought why not share this discussion with rest of the community… may be this would help someone in need or just another interesting read, and also I got myself an excuse to write my very first ever article!;)

I am dividing the solution in two parts, first part will be the Stored Procedure and second part would be a simple windows forms C# application to demonstrate the technique.

Stored Procedure:

I am using the “Northwind” database from Sql Server 2000. Please run the following script to create a dummy table called “Invoice”, which we will use it to store our dynamically crated document ids.

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

Following is the stored procedure code which will take input from user interface and generate new id and store it inside “Invoice” table.

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

Following code can be used to test the stored procedure using 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

How it works!

If you look at the stored procedure code carefully, it does following to get to new invoice code:

1. Identify if Invoice belong to past (05) or current year (06)
2. Looks for Max number available after “yy-” pattern.
3. If MAX returns NULL that means it is first entry, hence counter becomes “00001” else it takes the Max number and fills leading “0s” based on counter (in this case 5) length minus length of Max number.

So this way every time a entry is made to Invoice table it will get the most recent counter based on last entered row, that means even if someone physically delete a earlier row from table, it will not affect and will always give most recent invoice id with latest counter.

How about more patterns?

Sure! Following pattern talks about a different approach… to get to “mmyy-nnnnn”, following changes are required:

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

So, as you can see possibilities are endless, we can change the logic as per the requirement and we don’t have to worry about taking care of the counter anymore for any of the desired logic of getting a new id!

The other uncalled for usage could be, by just looking at the last Invoice number one can see that “these many Invoices generated for that particular month”.

Let's see through the eyes of ADO.NET

Let’s see it through the eyes of ADO.NET:

Please download the attached code and open it using Visual Studio 2003, make sure you change connection string properly and run the code to see the result for your self. This windows form app was quickly put together to demonstrate the usage of stored proc, it is in no way a quality work for example to check the numeric value in Invoice Amount textbox, so I assume you will be a “good user” to enter numeric values in that data entry controlJ

Code behind save button looks like this:

//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();
    }
}

How good is this solution?<o:p>

Well, as it goes with any solution which we arrive at, it has its own share of pros and cons.  The only potential issue which I can see is a very rare chance of getting the same id generated for more then one user, in other words, Concurrency!  I did some testing on this by generating ids 1/10 of every second with five concurrent processes, but was not able to generate a single instance of duplicate.  I would encourage any of you if you get a chance do give this code a shot to see if you can produce the duplicate scenario.  The remedy in case duplicate happens, if you notice in code final stage the stored procedure do check for it and ask the user to try saving the record again.  One can also put an additional check there to see if duplicate happened the without bothering the user try one more attempt at getting the id and use it to save in table.  Lastly, the proposed solution is for a typical business application which will be running on desktop client server environment, I am not even remotely considering that this code will be used to generate ids in an environment where a million plus hits are happening every other second.

About... blah blah

This is my first attempt to post an Article; hope I did justice to it. I have always taken it on chin when it comes to constructive criticism. So, if you feel you like to pass on any comment, please do so I would love to hear it.

If you like to praise my work then don’t be shy, I like praising too;)

Disclaimer: Just like to say this… don’t held me liable if you use what we discussed here and it results in LOSS of any form or shape, and if you got PROFIT then lest share it;) who doesn’t like profit anyway….:) just kidding.