.NET สร้าง Auto ID Number ใช้สำหรับ Order ID หรือ Member ID รหัสลำดับต่างๆ โดยมีอักษร (VB.Net, C#)

CREATE TABLE [dbo].[GenerateNumber](
	[Sequence] [int] NOT NULL,
 CONSTRAINT [PK_GenerateNumber] PRIMARY KEY CLUSTERED 
(
	[Sequence] ASC
)) ON [PRIMARY]

GO

INSERT INTO GenerateNumber (Sequence) VALUES ('1');

        protected void Page_Load(object sender, EventArgs e)
        {
            this.lblSeqNo.Text = GenerateNumber();
        }

        protected static string GenerateNumber()
        {
            var strSeqNo = string.Empty; 
            var strPrefix = "TC"; // Prefix : TC-
            var intLength = 5; // Length : TC-XXXXX

            var intSequence = 0;
            
            var objConn = new SqlConnection();
            var objCmd = new SqlCommand();
            var strSQL = string.Empty;

            var strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;database=mydatabase; " +
            " Max Pool Size=400;Connect Timeout=600;";
            objConn.ConnectionString = strConnString;
            objConn.Open();

            //*** Get current sequence
            strSQL = "SELECT Sequence FROM GenerateNumber";
            objCmd = new SqlCommand(strSQL, objConn);
            SqlDataReader dr = objCmd.ExecuteReader();
            if(dr.HasRows)
            {
                dr.Read();
                intSequence = Convert.ToInt32(dr["Sequence"]);
            }
            dr.Close();

            //*** Update new sequence
            strSQL = "UPDATE GenerateNumber SET  Sequence = Sequence + 1 ";
            objCmd = new SqlCommand(strSQL, objConn);
            objCmd.ExecuteNonQuery();

            //*** Display sequence
            strSeqNo = string.Format("{0}-{1}", strPrefix, intSequence.ToString().PadLeft(intLength,'0'));

            objConn.Close();
            objConn = null;

            return strSeqNo;
        }

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.lblSeqNo.Text = GenerateNumber()
    End Sub

    Protected Shared Function GenerateNumber() As String
        Dim strSeqNo = String.Empty
        Dim strPrefix = "TC" ' Prefix : TC-
        Dim intLength = 5 ' Length : TC-XXXXX
        Dim intSequence = 0

        Dim objConn = New SqlConnection()
        Dim objCmd = New SqlCommand()
        Dim strSQL = String.Empty

        Dim strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;database=mydatabase; " &
            " Max Pool Size=400;Connect Timeout=600;"
        objConn.ConnectionString = strConnString
        objConn.Open()

        '*** Get current sequence
        strSQL = "SELECT Sequence FROM GenerateNumber"
        objCmd = New SqlCommand(strSQL, objConn)
        Dim dr As SqlDataReader = objCmd.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            intSequence = Convert.ToInt32(dr("Sequence"))
        End If
        dr.Close()

        '*** Update new sequence
        strSQL = "UPDATE GenerateNumber SET  Sequence = Sequence + 1 "
        objCmd = New SqlCommand(strSQL, objConn)
        objCmd.ExecuteNonQuery()

        '*** Return sequence
        strSeqNo = String.Format("{0}-{1}", strPrefix, intSequence.ToString().PadLeft(intLength, "0"))

        objConn.Close()
        objConn = Nothing

        Return strSeqNo
    End Function

CREATE TABLE [dbo].[GenerateNumber](
	[Year] [int] NOT NULL,
	[Month] [int] NOT NULL,
	[Sequence] [int] NOT NULL,
 CONSTRAINT [PK_GenerateNumber] PRIMARY KEY CLUSTERED 
(
	[Year] ASC,
	[Month] ASC,
	[Sequence] ASC
)) ON [PRIMARY]

        protected void Page_Load(object sender, EventArgs e)
        {
            this.lblSeqNo.Text = GenerateNumber();
        }

        protected static string GenerateNumber()
        {
            var strSeqNo = string.Empty;
            var strPrefix = "TC"; // Prefix : TC-
            var intLength = 5; // Length : TC-XXXXX

            var intYear = DateTime.Now.Year;
            var intMonth = DateTime.Now.Month;
            var intSequence = 0;

            var objConn = new SqlConnection();
            var objCmd = new SqlCommand();
            var strSQL = string.Empty;

            var strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;database=mydatabase; " +
            " Max Pool Size=400;Connect Timeout=600;";
            objConn.ConnectionString = strConnString;
            objConn.Open();

            //*** Get current sequence
            strSQL = "SELECT Sequence FROM GenerateNumber WHERE Year = " + intYear + " AND Month = " + intMonth + " ";
            objCmd = new SqlCommand(strSQL, objConn);
            SqlDataReader dr = objCmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                intSequence = Convert.ToInt32(dr["Sequence"]);
            }
            dr.Close();

            //*** Insert new month (when new month)
            if (intSequence == 0)
            {
                intSequence = 1;
                strSQL = "INSERT INTO GenerateNumber (Year, Month, Sequence) VALUES (" + intYear + "," + intMonth + "," + intSequence + ");  ";
                objCmd = new SqlCommand(strSQL, objConn);
                objCmd.ExecuteNonQuery();
            }
            
            //*** Update new sequence
            strSQL = "UPDATE GenerateNumber SET  Sequence = Sequence + 1 WHERE Year = " + intYear + " AND Month = " + intMonth + "  ";
            objCmd = new SqlCommand(strSQL, objConn);
            objCmd.ExecuteNonQuery();

            //*** Display sequence
            strSeqNo = string.Format("{0}-{1}-{2}-{3}", strPrefix, intYear, 
                intMonth.ToString().PadLeft(2,'0'), intSequence.ToString().PadLeft(intLength, '0'));

            objConn.Close();
            objConn = null;

            return strSeqNo;
        }

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.lblSeqNo.Text = GenerateNumber()
    End Sub

    Protected Shared Function GenerateNumber() As String
        Dim strSeqNo = String.Empty
        Dim strPrefix = "TC" ' Prefix : TC-
        Dim intLength = 5 ' Length : TC-XXXXX
        Dim intYear = DateTime.Now.Year
        Dim intMonth = DateTime.Now.Month
        Dim intSequence = 0

        Dim objConn = New SqlConnection()
        Dim objCmd = New SqlCommand()
        Dim strSQL = String.Empty

        Dim strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;database=mydatabase; " &
        " Max Pool Size=400;Connect Timeout=600;"
        objConn.ConnectionString = strConnString
        objConn.Open()

        '*** Get current sequence
        strSQL = "SELECT Sequence FROM GenerateNumber WHERE Year = " & intYear & " AND Month = " & intMonth & " "
        objCmd = New SqlCommand(strSQL, objConn)
        Dim dr As SqlDataReader = objCmd.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            intSequence = Convert.ToInt32(dr("Sequence"))
        End If
        dr.Close()

        '*** Insert new month (when new month)
        If intSequence = 0 Then
            intSequence = 1
            strSQL = "INSERT INTO GenerateNumber (Year, Month, Sequence) VALUES (" & intYear & "," & intMonth & "," & intSequence & ");  "
            objCmd = New SqlCommand(strSQL, objConn)
            objCmd.ExecuteNonQuery()
        End If

        '*** Update new sequence
        strSQL = "UPDATE GenerateNumber SET  Sequence = Sequence + 1 WHERE Year = " & intYear & " AND Month = " & intMonth & "  "
        objCmd = New SqlCommand(strSQL, objConn)
        objCmd.ExecuteNonQuery()

        '*** Display sequence
        strSeqNo = String.Format("{0}-{1}-{2}-{3}", strPrefix, intYear,
                                 intMonth.ToString().PadLeft(2, "0"), intSequence.ToString().PadLeft(intLength, "0"))

        objConn.Close()
        objConn = Nothing

        Return strSeqNo
    End Function