Generating sequence number in C#

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 you help me with coding?

Answer 1

The first answer sometimes won't work if there is concurrency. The other solutions ends with a large table without needing it.

If you're working with SQL Server 2016 or above you can use the SEQUENCE object, it's stright forward and made for this situation. Look at https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16

In previous versions you can use the normal locking mechanism to achieve what you need:

You will need a Table For Sequence(s), you can generate as many rows as sequences you need, I mean ten or a dozen, not hundreds or you'll end up with a bottleneck.

CREATE TABLE SequenceTable
(
   k_val  varchar(5) not null,  --Here you can put the ELG string or whatever
   my_serial int not null,
   CONSTRAINT PK_SequenceTable UNIQUE PRIMARY KEY CLUSTERED
)
GO

--To keep it simple I will asume there is a first row with a key_val
--And a positive value in my_serial
--And I won't handle errors nor exceptions
CREATE PROC GetKey(
  @key_val varchar(5)
)
As
Begin transaction  --To keep locks until the proc ends

   --With the normal isolation level, the UPDATE will put an exclusive 
   --lock on the Row, no one else can read or write until the commit
   --With this UPDATE I'll set the new value
   UPDATE SequenceTable
      SET my_serial = my_serial +1
   WHERE k_val = @key_val

   --Here, as the lock is mine, I can read and return the new generated Value
   SELECT my_serial
   FROM SequenceTable
   WHERE k_val = @key_val

commit transaction  -- The Lock is released
GO

In this way you can use the normal ACID database behavior for you.

Answer 2

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.

Answer 3

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

   }

Answer 4

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 5

How about something like this?

public enum SequenceIds
{
    ELG0001 = 1,
    ELG0002
}

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

Answer 6

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.