Introduction:
In this
article I will explain how to bind Grid view and automatically show row/record
number in ASP.NET(C# & vb.net).
There are
two methods to display serial number or record Number.
1. Get Row
number using SQL query
2. Generate
Row number in Gridview
- First Method:
Using ROW_NUMBER() e.g. in
this article example I used the query "Select ROW_NUMBER()
over (order by EmpId) as IndexNo,* from EmDetails " and in
the source code of gridview adding a Bound field
as
<asp:BoundField HeaderText="IndexNo" DataField="IndexNo" />
- Second Method:
This is
the simplest way. Just add in gridview as a column
<asp:TemplateField HeaderText="IndexNo.">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField> in the gridview
Implementation:
Create A database “Test”, then create a table “EmDetails” as given
in the following image:
Column
Name
|
Data
Type
|
Emp_Id
|
Int(primary key. So set Is
Identity=true)
|
Name
|
varchar(100)
|
Address
|
varchar(100)
|
Salary
|
int
|
Create
connection string to connect your application with Database:
<connectionStrings>
<add name="EmpCon" connectionString="Data Source=localhost;Initial
Catalog=Test;IntegratedSecurity=True"/>
</connectionStrings>
Source Code:
In the design page (.aspx) place a GridView control from the Data
category of the visual studio’s toolbox and set as:
<div>
<fieldset style="width:300px;">
<legend><strong>Add Row number in gridview </strong></legend>
<table
width="100%">
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:GridView ID="grdEmp" runat="server" AutoGenerateColumns="False" Width="100%">
<Columns>
<%--<asp:TemplateField
HeaderText="IndexNo.">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>--%>
<asp:BoundField HeaderText="IndexNo" DataField="IndexNo" />
<asp:BoundField HeaderText="Name"
DataField="Name"
/>
<asp:BoundField HeaderText="Salary"
DataField="Salary"
/>
<asp:BoundField HeaderText="Address"
DataField="Address"
/>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#ffffff" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#7B0606" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#7B0606" ForeColor="White" HorizontalAlign="Center" />
<RowStyle CssClass="rowHover"
BackColor="#F7F6F3"
ForeColor="#333333"></RowStyle>
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
<EmptyDataRowStyle Width
= "550px"
ForeColor="Red"
Font-Bold="true"
HorizontalAlign =
"Center"/>
</asp:GridView>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
</table>
</fieldset>
</div>
C#.Net Code:
In the
code behind file (.aspx.cs) write the code as:
First
include following namespaces:
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
public partial class recordNo : System.Web.UI.Page
{
protected void
Page_Load(object sender, EventArgs e)
{
if
(!Page.IsPostBack)
{
BindEmpGrid();
}
}
private void
BindEmpGrid()
{
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
DataTable
dt = new DataTable();
SqlDataAdapter
adp = new SqlDataAdapter();
SqlCommand
cmd = new SqlCommand();
try
{
cmd = new
SqlCommand("Select
ROW_NUMBER() over (order by EmpId) as IndexNo,* from EmDetails",
con);
// cmd =
new SqlCommand("Select * from EmDetails", con);
adp.SelectCommand = cmd;
adp.Fill(dt);
if
(dt.Rows.Count > 0)
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),
"Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
cmd.Dispose();
dt.Clear();
dt.Dispose();
adp.Dispose();
con.Close();
}
}
//GridView paging
protected void
grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdEmp.PageIndex = e.NewPageIndex;
BindEmpGrid();
}
}
Second Method:
<asp:BoundFieldHeaderText="IndexNo" DataField="IndexNo" /> and uncomment the following template field line
<asp:TemplateField HeaderText="IndexNo.">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
In code behind file comment
or replace the query "Select ROW_NUMBER() over (order by Emp_Id) as IndexNo,* from
Emdetails" with "Select * from Emdetails"
VB.Net Code
write the
following code:
Imports System.Data
Imports
System.Data.SqlClient
Imports
System.Configuration
Partial Public Class recordNo
Inherits System.Web.UI.Page
Protected Sub
Page_Load(ByVal sender As
Object, ByVal e
As EventArgs)
If Not Page.IsPostBack Then
BindEmpGrid()
End If
End Sub
Private Sub
BindEmpGrid()
Dim con
As SqlConnection
= New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
Dim dt As DataTable = New DataTable()
Dim adp As SqlDataAdapter = New
SqlDataAdapter()
Dim cmd
As SqlCommand
= New SqlCommand()
Try
cmd = New
SqlCommand("Select
ROW_NUMBER() over (order by EmpId) as IndexNo,* from EmDetails",
con)
' cmd =
new SqlCommand("Select * from EmDetails", con);
adp.SelectCommand = cmd
adp.Fill(dt)
If
dt.Rows.Count > 0 Then
grdEmp.DataSource = dt
grdEmp.DataBind()
End
If
Catch
ex As Exception
ScriptManager.RegisterStartupScript(this,
Me.GetType(), "Message",
"alert('Error occured : " +
ex.Message.ToString() + "');", True)
Finally
cmd.Dispose()
dt.Clear()
dt.Dispose()
adp.Dispose()
con.Close()
End Try
End Sub
'GridView paging
Protected Sub
grdEmp_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
grdEmp.PageIndex = e.NewPageIndex
BindEmpGrid()
End Sub
End Class
0 comments:
Post a Comment