ASP.Net: Insert (Save) unique auto generated number in database using C# and VB.Net

Hi makumbi,

Please refer below sample.

HTML

<asp:TextBox ID="txtNumber" runat="server">
</asp:TextBox>
<asp:Button ID="btnGenerate" runat="server" Text="Generate" OnClick="Generate" />

SQL

Create Table [UniqueNumber]
(
    [ID] INT IDENTITY(1,1),
    [Number] VARCHAR(20)
)

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        while (true)
        {
            Int64 number = this.GenerateNumber();
            if (GetID(number) == 0)
            {
                txtNumber.Text = number.ToString();
                break;
            }
        }
    }
}

protected void Generate(object sender, EventArgs e)
{
    string name = txtNumber.Text;
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO [UniqueNumber] VALUES (@Numbers)", con))
        {
            cmd.Parameters.AddWithValue("@Numbers", name);
            ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Value Generated and Inserted successfully.')", true);
            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    Response.Redirect(Request.Url.AbsoluteUri);
}

private int GetID(Int64 number)
{
    int id = 0;
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [UniqueNumber] WHERE Number = @Id", con))
        {
            cmd.Parameters.AddWithValue("@Id", number);
            con.Open();
            id = Convert.ToInt16(cmd.ExecuteScalar());
            con.Close();
        }
    }

    return id;
}

protected Int64 GenerateNumber()
{
    string numbers = "1234567890";
    int length = 10;
    string number = string.Empty;
    for (int i = 0; i < length; i++)
    {
        string num = string.Empty;
        do
        {
            int index = new Random().Next(0, numbers.Length);
            num = numbers.ToCharArray()[index].ToString();
        } while (number.IndexOf(num) != -1);
        number += num;
    }

    return Convert.ToInt64(number);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        While True
            Dim number As Int64 = Me.GenerateNumber()
            If GetID(number) = 0 Then
                txtNumber.Text = number.ToString()
                Exit While
            End If
        End While
    End If
End Sub

Protected Sub Generate(ByVal sender As Object, ByVal e As EventArgs)
    Dim name As String = txtNumber.Text
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO [UniqueNumber] VALUES (@Numbers)", con)
            cmd.Parameters.AddWithValue("@Numbers", name)
            ClientScript.RegisterStartupScript(Me.[GetType](), "alert", "alert('Value Generated and Inserted successfully.')", True)
            con.Open()
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub

Private Function GetID(ByVal number As Int64) As Integer
    Dim id As Integer = 0
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM [UniqueNumber] WHERE Number = @Id", con)
            cmd.Parameters.AddWithValue("@Id", number)
            con.Open()
            id = Convert.ToInt16(cmd.ExecuteScalar())
            con.Close()
        End Using
    End Using
    Return id
End Function

Protected Function GenerateNumber() As Int64
    Dim numbers As String = "1234567890"
    Dim length As Integer = 10
    Dim number As String = String.Empty
    For i As Integer = 0 To length - 1
        Dim num As String = String.Empty
        Do
            Dim index As Integer = New Random().[Next](0, numbers.Length)
            num = numbers.ToCharArray()(index).ToString()
        Loop While number.IndexOf(num) <> -1
        number += num
    Next
    Return Convert.ToInt64(number)
End Function

Screenshot

Output

Database