Query to generate Serial Number (Row Number) in SQL Server

Hi makumbi,

Use ROW_NUMBER function in the SELECT statement.

Please refer below sample.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<form id="form1" runat="server">
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="Sr No" DataField="SrNo" />
        <asp:BoundField HeaderText="Name" DataField="FirstName" />
        <asp:BoundField HeaderText="Date" DataField="BirthDate" />
        <asp:BoundField HeaderText="City" DataField="City" />
        <asp:BoundField HeaderText="Country" DataField="Country" />
    </Columns>
</asp:GridView>
</form>

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if(!this.IsPostBack)
    {
        this.BindGridView();
    }
}

private void BindGridView()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using(SqlConnection con = new SqlConnection(conString))
    {
        using(SqlCommand cmd = new SqlCommand("SELECT ROW_NUMBER() OVER(ORDER BY BirthDate) SrNo,* FROM Employees", con))
        {
            con.Open();
            gvEmployees.DataSource = cmd.ExecuteReader();
            gvEmployees.DataBind();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGridView()
    End If
End Sub

Private Sub BindGridView()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("SELECT ROW_NUMBER() OVER(ORDER BY BirthDate) SrNo,* FROM Employees", con)
            con.Open()
            gvEmployees.DataSource = cmd.ExecuteReader()
            gvEmployees.DataBind()
            con.Close()
        End Using
    End Using
End Sub

Screenshot