generating sequence number in csharp

Question

I am working on ASP.Net using C# I want to generate a sequence id that should be like this:

ELG0001 , ELG0002, ...

ELG is the PREFIX and 0001 should be in sequence i am using sql server 2005 This ID will be generated and added to my database. How can I do this?

can u help me with coding

Answer 1

Here's a simple ID Generator for SQL Server:

CREATE TABLE IDSeed
(
    ID int IDENTITY(10001,1)
)
GO

CREATE PROCEDURE NewSequenceID
(
    @NewID char(7) out
)
AS
BEGIN
    INSERT INTO IDSeed DEFAULT VALUES

    SELECT @NewID = 'ELG' + RIGHT(CAST(SCOPE_IDENTITY() AS nvarchar(5)), 4)
END
GO

/*
 * Test the NewSequenceID proc
 */
DECLARE @TestID char(7)

EXEC NewSequenceID @TestID out

SELECT @TestID

The IDSeed table will continue to accumulate rows. This is probably not an issue, but if it is a problem you can purge the table with a script on a regular basis. The only part left is to call the procedure from C# code and retrieve the value of the @TestID parameter.

This example is based on your question: the sequence is not expected to exceed 9999 elements. You would have to modify the code to support larger sequence IDs.

Note that a transaction is not necessary in procedure NewSequenceID because the SCOPE_IDENTITY() function only returns values from the current SQL session. If another thread performs a near-simultaneous insert, the value returned from SCOPE_IDENTITY() won't be affected.

Answer 2

You pretty much can not - you need to generate the numbers on and from a persistent data store (like SQL Server) ultimately, to guarantee uniqueness, even in case multiple copies of your ASP.NET appdomain are running (which may happen regularly).

So, database side:

• You need a table for all squences and the last assigned value (Like "ELG" "1" - the number can be stored numeric)
• IN a transaction: read last number, update to next higher one, commit and return number to high code level after formatting.

Answer 3

How about something like this?

public enum SequenceIds
{
    ELG0001 = 1,
    ELG0002
}

public void Something()
{
    var x = SequenceIds.ELG0002;
}

Answer 4

Hi,

We are using mssql 2005 and we were facing the same problem of having a unique number with a prefix or a postfix.

We used a stored procedure and a table to generate a sequence number for us

Following is the code for the stored procedure

CREATE PROCEDURE [get_next_receipt_no]
(
  @pSequenceValue INT OUTPUT
)
AS
BEGIN;
  BEGIN TRAN;
    INSERT INTO seq_receipt_no DEFAULT VALUES;
    SET @pSequenceValue = SCOPE_IDENTITY();
    DELETE FROM seq_receipt_no WHERE SEQ_ID <> @pSequenceValue;
  COMMIT TRAN;
END;

Following is the code for the table

CREATE TABLE [seq_receipt_no](
    [SEQ_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

If the database would have been oracle, you get sequences as objects accessible from SQL queries itself. MSSQL does not have this functionality.

I hope this helps.

Kalpak Luniya

Answer 5

using this code we can do it simply

public string CJ()
    {
        string Id = GenerateId("cust", "cust_id", 6, "ELG", true);
        return Id;
    }
    public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix, bool Padding)
    {
        string Query, con, Id;
        con = "Data Source=CJ\\SQLEXPRESS;Initial Catalog=seq;Persist Security Info=True;User ID=sa;Password=123";
        SqlConnection cn = new SqlConnection(con);
        int preLength,padLength;
        preLength = Convert.ToInt32(Prefix.Length);
        padLength = ColumnLength - preLength;
        if (Padding == true )
        {
             Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(preLength + 1) + "," + padLength + ") AS INTEGER))+1," + padLength + "),' ',0) FROM " + TableName;

        }
        else
        {
            Query = "SELECT '" + Prefix + "' + CAST(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(preLength + 1) + "," + padLength + ") AS INTEGER))+1 AS VARCHAR) FROM " + TableName;
        }
        SqlCommand com = new SqlCommand(Query, cn);
        cn.Open();
        if (com.ExecuteScalar().ToString() == "")
        {
            Id = Prefix;
            if (Padding == true)
            {
                for (int i = 1; i  padLength - 1; i++)
                {
                    Id += "0";
                }
            }
            Id += "1";
        }
        else
        {
            Id = Convert.ToString(com.ExecuteScalar());
        }
        cn.Close();
        return Id;
}

thanxx for the help just add the method CJ() as i have done here

protected void Button1_Click(object sender, EventArgs e)
    {
        string con;
        con = "Data Source=CJ\\SQLEXPRESS;Initial Catalog=seq;Persist Security Info=True;User ID=sa;Password=123";
        using (SqlConnection cn = new SqlConnection(con))
        {
            cn.Open();
            using(SqlTransaction trans = cn.BeginTransaction())
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandText = "INSERT INTO cust([cust_id],[cust_name],[cust_add]) VALUES(@cust_id,@cust_name,@cust_add)";
                cmd.Parameters.Add("@cust_id",CJ());
                cmd.Parameters.Add("@cust_name",TextBox1.Text);
                cmd.Parameters.Add("@cust_add",TextBox2.Text);
                cmd.ExecuteNonQuery();
                trans.COmmit();
            }
            cn.Close();
            Response.Write("alert('DATA SAVED')");
            TextBox1.Text = "";
            TextBox2.Text = "";
        }

   }