Generating Custom Auto Numbers and Using it in Application

Raj.Trivedi
Posted by in C# category on for Beginner level | Points: 250 | Views : 16113 red flag

Hello Team,

In this article we will see how we can generate custom Auto Numbers and use it in Application

Introduction


In real word development we come across a Situation where we need to generate Invoice Numbers,Customer Id or just custom auto numbers in real world application development.

In this article we will see how we can programmatically generate Custom Auto Numbers in SQL Server and use it in Front end i.e. in Application.




Objective

  1. Creating a Stored Procedure that will generate Custom Auto Number.
  2. Implement the Custom Auto Number in front end.



Using the code


Let us begin with creating a Simple Windows Application in Visual Studio 2010.
Drag and Drop 2 Text-boxes and One Button to save the record.

The UI will be something as below screen.



  1. First we will create a Table Named CustomerInfo
  2. Then we will create 2 Stored Procedure 1 named AutoGenerateCustomerID and InsertCustomer
  3. The above stored Procedure will be used for Insert and Generating Customer ID.

Explanation of AutoGenerateCustomerID Stored Procedure

In the stored Proc we have declared 3 internal Variables named Rowcount,TempNumber,CustomerID.

  1. RowCount will check whether there is any record in the table.If not then the Customer ID Variable will be set to 1000.
  2. If there is record in the table it will check the maximum CustomerID in the table and store it in the TempNumber.
  3. Once the Max Customer ID is stored in the TempNumber variable we will be add 1 to the max Number and store it in the Customer ID.
  4. Finally we will select the Customer ID for implementing it in Front end


Explanation of InsertCustomer Stored Procedure

  1. In this Stored Procedure we have 2 Parameters in the store Procedure that is CustomerID and CustName that are required to execute the Procedure.
  2. Once both the parameters are passed the insert Query will fire and save the Record.


Note :- Both The Stored Procedure are Pasted in the code Section.
// Table and Stored Procedure
Table :- CustomerInfo
CREATE TABLE [dbo].[CustomerInfo](
	[CustomerID] [int] Not NULL,
	[CustomerName] [varchar](50) NULL
)																																				 --Stored Procedure for Generating CustomerID								
create proc [dbo].[AutoGenerateCustomerID]
as
begin
declare @RowCount int;
declare @CustomerID int;
declare @TempNewNumber int;
set @RowCount = (Select COUNT(CustomerID) from CustomerInfo )
if(@RowCount =0)
begin
set @CustomerID = 1000
end
else
begin
set @TempNewNumber =(select MAX(CustomerID) from CustomerInfo) 
set @CustomerID = @TempNewNumber + 1
end
select @CustomerID 'CustomerID'
end


-- Stored Procedure for Inserting CustomerInfo
create Proc InsertCustomer
(
@CustomerID int,
@CustomerName varchar(50)
)
as
begin
insert into CustomerInfo(CustomerID,CustomerName)
values
(@CustomerID,@CustomerName)
end
//Code Behind
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;


namespace Generating_Auto_Number
{
    public partial class Form1 : Form
    {
        string errdesc = "0";
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
                con.Open();
                SqlCommand cmd = new SqlCommand("InsertCustomer", con);  //Calling The Stored Procedure of Inserting Customer
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.Parameters.AddWithValue("@CustomerID",SqlDbType.Int).Value = xtxtCustomerID.Text.Trim();
                cmd.Parameters.AddWithValue("@CustomerName", SqlDbType.VarChar).Value = xtxtName.Text.Trim();
                int result = cmd.ExecuteNonQuery();
                if (result == 1)
                {
                    MessageBox.Show("Customer Saved");
                    GeneratingCustomerID();
                }

            }
            catch (Exception ex)
            {
                errdesc = ex.Message;
            }
        }



        // Function for Calling the Stored Procedure "AutoGenerateCustomerID" and Passing the Column Value to CustomerID Text box and clearing the Name Textbox.
        private void GeneratingCustomerID()
        {
            try
            {
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
                con.Open();
                SqlCommand cmd = new SqlCommand("AutoGenerateCustomerID", con); // Calling The Stored Procedure of AutoGenerateCustomerID
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    
                    xtxtCustomerID.Text = ds.Tables[0].Rows[0]["CustomerID"].ToString();
                    xtxtName.Text = "";
                }

            }
            catch(Exception ex)
            {
                errdesc = ex.Message;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // Calling the Function on Form Load so that CustomerID is autogenerated on Form Load.
            GeneratingCustomerID();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            // Calling the Function on Reset Button so that CustomerID is autogenerated on Form Load.
            GeneratingCustomerID();
        }
    }
}

Output Screens





If we see the Customer ID is 1001 for the Customer named "Kalpesh".Now once we click OK the Customer ID will automatically changed to 1002 see the Next Screen



As we see now the Customer ID is 1002.

As we go on saving the customer the Customer will auto Increment.

Note :- If you click the reset Button the AutoNumber will stay the same because you are not adding the Customer in the Database.So it will not increment on Reset Button.


Hope this will help a Lot... 

Page copy protected against web site content infringement by Copyscape

About the Author

Raj.Trivedi
Full Name: Raj Trivedi
Member Level:
Member Status: Member,MVP
Member Since: 6/16/2012 2:04:41 AM
Country: India
Regard's Raj.Trivedi "Sharing is Caring" Please mark as answer if your Query is resolved
http://www.dotnetfunda.com/profile/raj.trivedi.aspx
Raj Trivedi i.e. me started my career as Support Professional and then moved on the Software development eventually reached at these skills Software Development | Enthusiastic Blogger | Content Writer | Technical Writer | Problem Solver | Lecturer on Technology Subjects | Runnerup Award Winner on www.dotnetfunda.com and firm believer in Sharing as a way of Caring Yet this much achieved its still a long way to go and there is biggest dream lying to be one of the best entrepreneurs of India in Technology Department. The Dream has just started and i hope it follows. Highlights are mentioned in details in my profile at http://in.linkedin.com/pub/raj-trivedi/30/61/b30/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)