In this article I will explain the following
two things:
1.
Bind gridview with data from sql server table.
2. Create gridview scrollable keeping header fixed with the help of jQuery as shown in image.
2. Create gridview scrollable keeping header fixed with the help of jQuery as shown in image.
First of
all create a DataBase in Sql server and name it e.g.
"Test" and in this database create a table with the following
Columns and Data type as shown below and name this table
"Emp_table".
Column
Name
|
Data
Type
|
Emp_Id_Id
|
Int(Primary Key. So set is
identity=true)
|
Emp_Name
|
varchar(100)
|
Salary
|
varchar(50)
|
Now connect
our asp.net application with Sql Server database
<connectionStrings>
<add name="Empcon" connectionString="Data Source=localhost;Initial
Catalog=test;Integrated Security=True"/>
</connectionStrings>
Below is
the HTML Source of the Default.aspx page.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Scrollable
GridView with Fix Header </title>
<link href="CSS/GridviewScroll.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
<script src="Scripts/gridviewScroll.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
gridviewScroll();
});
function gridviewScroll() {
$('#<%=grdEmp.ClientID%>').gridviewScroll({
width: 400,
height:
150,
barhovercolor: "#5D7B9D",
barcolor: "#5D7B9D"
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset style="width:400px;">
<legend>Scrollable
GridView with Fix Header</legend>
<table style="width:100%;">
<tr>
<td>
<asp:GridView ID="grdEmp" runat="server"
AutoGenerateColumns="False"
AllowPaging="false"
Width="100%"
CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField HeaderText="Employee Name" DataField="Emp_Name" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField HeaderText="Salary"
DataField="Salary"
HeaderStyle-HorizontalAlign="Left" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</td>
</tr>
</table>
</fieldset>
</div>
</form>
</body>
</html>
You have
to download GridviewScroll.css file which is in CSS folder and gridviewScroll.min.js file which is in Scriptsfolder (highlighted
two rows).
Download
it from the following Link:
Download
and use paste these files in your project.
Asp.Net
C# Code
In
code behind file(default.aspx.cs) write the code to bind gridview from sql
server database table as:
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 Fix_Header : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
protected void
Page_Load(object sender, EventArgs e)
{
if
(!Page.IsPostBack)
{
BindGridView();
}
}
private void
BindGridView()
{
DataTable
dt = new DataTable();
SqlCommand
cmd = null;
SqlDataAdapter
adp = null;
try
{
cmd = new
SqlCommand("Select
* from Emp_Table", con);
adp = new
SqlDataAdapter(cmd);
adp.Fill(dt);
if
(dt.Rows.Count > 0)
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
else
{
grdEmp.DataSource = null;
grdEmp.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),
"Message", "alert('Error occured: " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
cmd.Dispose();
adp = null;
dt.Clear();
dt.Dispose();
}
}
}
Asp.Net (VB) Code
In code behind file (default.aspx.vb) write the code to bind
gridview from sql server database table as:
Import these Namspaces:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Empcon").ConnectionString)
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGridView()
End If
End Sub
Private Sub BindGridView()
Dim dt As New DataTable()
Dim cmd As SqlCommand = Nothing
Dim adp As SqlDataAdapter = Nothing
Try
cmd = new
SqlCommand("Select
* from Emp_Table", con)
adp
= new SqlDataAdapter(cmd)
adp.Fill(dt)
If dt.Rows.Count > 0 Then
grdEmp.DataSource
= dt
grdEmp.DataBind()
Else
grdEmp.DataSource
= Nothing
grdEmp.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error
occured: " +
ex.Message.ToString() + "');", True)
Finally
con.Close()
cmd.Dispose()
adp = Nothing
dt.Clear()
dt.Dispose()
End Try
End Sub
0 comments:
Post a Comment