Post
Ask Question

Auto Increment ID Column Using nvarchar DataType In SQL Server

Introduction

This article is about Auto Increment ID Column using nvarchar datatype in SQL Server.

SQL server provides identity property to get auto-increment record values where the datatype used is “int”. This article explains a way to use nvarchar datatype column to get auto-increment record values.

e.g. S00001, E00001, INV00001. (Here there are prefix like S, E, INV used to denote the record ids.)

This article provides a solution to use alphanumeric record ids incremented automatically.

Solution

  1. Create an Employee table as below,



    Here “ID” column is for storing record id as similar to Serial No of any entry made. “EmployeeNo” column is used to store the alphanumeric auto increment id. “Name” stores the name of the Employee.

  2. Create a Windows Form with a TextBox and a Button control.



    The above figure shows the use of a textbox to enter name in the ”employee” table where the id is auto-incremented.

  3. Code

    1. Import the NameSpace 



    2. Create 2 methods OpenConnection() and CloseConnection() to handle the SQL Server Connection object.
      1. private void OpenConnection(SqlConnection con) {  
      2.     con.ConnectionString = "Data Source=.; Initial Catalog=demodb; Integrated Security=True";  
      3.     if (con.State == ConnectionState.Closed) {  
      4.         con.Open();  
      5.     }  
      6. }  
      7. private void CloseConnection(SqlConnection con) {  
      8.     con.Close();  
      9. }  
    3. On the Insert button click the id is auto-incremented and the relevant data is inserted in the employee table. (id, employeeno and name)
      1. SqlConnection con = new SqlConnection();  
      2. OpenConnection(con);  
      3. string id = AutoIncrementID();  
      4. int idLimit = 7;  
      5. string eno = "E" + ZeroAppend("0000000" + id, idLimit);  
      6. string query = "insert into employee values ('" + id + "', '" + eno + "', '" + txtName.Text + "')";  
      7. SqlCommand cmd = new SqlCommand(query, con);  
      8. int i = cmd.ExecuteNonQuery();  
      9. if (i > 0) {  
      10.     MessageBox.Show("Data Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information);  
      11. }  
      12. CloseConnection(con);  
      Here a method AutoIncrementID() is used to do the task of auto-incrementing based on the last entry inserted in the table.
      1. private string AutoIncrementID() {  
      2.     SqlConnection con = new SqlConnection();  
      3.     OpenConnection(con);  
      4.     SqlCommand cmd = new SqlCommand("SELECT ISNULL(MAX(ID),0) + 1 from employee", con);  
      5.     SqlDataReader dr = cmd.ExecuteReader();  
      6.     string id = null;  
      7.     if (dr.Read()) {  
      8.         id = dr[0].ToString();  
      9.     }  
      10.     CloseConnection(con);  
      11.     return id;  
      12. }  
      13. //The function ZeroAppend is used to append “0” after the prefix to the code.  
      14. public static string ZeroAppend(string data, int idLimit) {  
      15.     return data.Substring(data.Length - idLimit);  
      16. }  
  4. Output,