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
The 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 the new invoice code:
- Identify if Invoice belong to past (05) or current year (06)
- Looks for Max number available after "yy-" pattern.
- 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
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 control 🙂
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 = @"DataSource=(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?
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 praise 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.
I originally posted this article on codeproject.com, posting here again so that more folks can have a look at it and share their say with me.
