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?
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:
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.
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.
How about something like this?
public enum SequenceIds
{
ELG0001 = 1,
ELG0002
}
public void Something()
{
var x = SequenceIds.ELG0002;
}
