Generating sequence number in C#

I am working on ASP.Net using C# I want to generate a sequence id th开发者_开发知识库at 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 you help me with coding?

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 = "";
        }

   }

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.

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.

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.

How about something like this?

public enum SequenceIds
{
    ELG0001 = 1,
    ELG0002
}

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