I have an Employee table with an employee ID column EmpID (nvarchar(5)).
This is how I insert data into the Employee table
Employee obj = new Employee();
obj.EmpID = ?
DBContext.Employees.InsertOnSubmit(obj);
DBContext.SubmitChanges();
I want to make EmpID's value to
00001
00002
00003
00004
....
....
How can I create such a custom auto-generated sequence likes this ?
I would recommend to let SQL Server handle the auto-incrementing part by using an INT IDENTITY column. Don't do this in your C# code - that's a database task.
For your second column that you want to have in a specific, formatted fashion, use a computed column based on that identity column.
Try something like this:
CREATE TABLE dbo.Employee
(ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
EmpID AS RIGHT('0000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
.... your other columns here.......
)
That way, when you insert rows into the table, you'll get:
ID EmpID
---------------
1 00001
2 00002
........
17 00017
18 00018
......
78 00078
The ID column is your actual primary and clustering key, it's small, fast, never changes - perfect for a clustering key. And your column EmpID is always automatically generated from the ID and shows the value in the formatted fashion as you wanted.
Update : as @ta.speot.is rightfully commented: if you don't need that EmpID value in the database for anything, then the best approach would be to just have the ID INT IDENTITY column in the database, and handle the generating of the EmpID in your C# code in the Employee class:
public class Employee
{
[Key]
public int ID { get; set; }
[NotMapped]
public string EmpID
{
get { return "0000" + ID.ToString(); } // do whatever you need to do here!
}
}
ID can't be represented by VARCHAR(5)?
0000* in your EmpIDID INT IDENTITY(1, 1) (i.e. standard EF Id column) without the computed column and then put [NotMapped] public string EmpID { get { ... } } into Employee.