Generate Serial Number based on Database value in Windows Application using C# And VB.Net

AnkitPal
 
on Jan 29, 2019 04:44 AM
Sample_436470.zip
4319 Views

Dear Programmer i have a code for thi query but this query is generate serial number uptp 10 after this, it is repeat last number 10 for each time, anyone can modify this code.

private string Branch_SerialNumber(string connection, string query)
{
    string NewId = string.Empty;
    using (SqlConnection cnn = new SqlConnection(connection))
    {
        cnn.Open();
        SqlCommand cmd = new SqlCommand(query, cnn);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string i = dr[0].ToString();
            if (string.IsNullOrEmpty(i))
            {
                NewId = "1";
            }
            else
            {
                i = i.Substring(0);
                int j = Convert.ToInt32(i);
                j = j + 1;
                NewId = j.ToString();
            }
        }
        cnn.Close();
    }
    return string.Concat(NewId);
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 30, 2019 05:50 AM
on Jan 31, 2019 12:26 AM

Hi AnkitPal,

Check the below sample code.

SQL

CREATE TABLE GenerateUniqueId
(
    ID INT IDENTITY,
    UniqueId INT NOT NULL
)

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

string connStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
private void Branch_SerialNumber(object sender, EventArgs e)
{
    string previousIdQuery = "SELECT MAX(UniqueId) UniqueId FROM GenerateUniqueId";
    string newId = GenerateNewId(connStr, previousIdQuery);
    lblId.Text = newId;
    string insertQuery = "INSERT INTO GenerateUniqueId VALUES(" + newId + ")";
    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(insertQuery, con);
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

private string GenerateNewId(string connection, string query)
{
    string newId = string.Empty;
    using (SqlConnection con = new SqlConnection(connection))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string i = dr[0].ToString();
            if (string.IsNullOrEmpty(i))
            {
                newId = "1";
            }
            else
            {
                int j = Convert.ToInt32(i);
                j = j + 1;
                newId = j.ToString();
            }
        }
        con.Close();
    }

    return string.Concat(newId);
}

VB.Net

Private connStr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Private Sub Branch_SerialNumber(ByVal sender As Object, ByVal e As EventArgs)
    Dim previousIdQuery As String = "SELECT MAX(UniqueId) UniqueId FROM GenerateUniqueId"
    Dim newId As String = GenerateNewId(connStr, previousIdQuery)
    lblId.Text = newId
    Dim insertQuery As String = "INSERT INTO GenerateUniqueId VALUES(" & newId & ")"
    Using con As SqlConnection = New SqlConnection(connStr)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(insertQuery, con)
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Sub
Private Function GenerateNewId(ByVal connection As String, ByVal query As String) As String
    Dim newId As String = String.Empty
    Using con As SqlConnection = New SqlConnection(connection)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(query, con)
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim i As String = dr(0).ToString()
            If String.IsNullOrEmpty(i) Then
                newId = "1"
            Else
                Dim j As Integer = Convert.ToInt32(i)
                j = j + 1
                newId = j.ToString()
            End If
        End While
        con.Close()
    End Using
    Return String.Concat(newId)
End Function

Screenshot