Note:This article is featured as the article of the day at Asp.Net Community.
Introduction:-
Introduction:-
In this article you are going to learn how to
use Jquery’s datatable plugin with
gridview using asp.net and sql server as a back end database.
Step 1:-Now create two table in sql server and populate it
with data as shown below:
CountryMaster
StateMaster
Step 2:-Now
create one stored procedure to fetch the records from database.
CREATE PROCEDURE [dbo].[SPState]
AS
BEGIN
select *,(select c.[Country] from [CountryMaster] c where c.[Id] = s.[CountryId]) as [Country] from StateMaster s
END
Step 3:-Now
download Jquery datatable plugin from here
Step 4:-Now
extract that downloaded zip file and go to media folder which contains required
css and .js file to use datatable in your asp.net application.
Step 5:-Now in
order to connect our asp.net application with sql server we need
connectionString.Therefore in your web.config file include connectionString tag
under configuration tag.
<connectionStrings>
<add name="conStr" connectionString="Data Source=localhost;Initial Catalog=YoursDbName;Integrated
Security=True"/>
</connectionStrings>
Step 6:- Now let’s
create default.aspx page
<head runat="server">
<title></title>
<script src="jquery-1.11.1.min.js" type="text/javascript"></script>
<link href="jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script src="jquery.dataTables.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
$(".gvv").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable({
"lengthMenu":[[3,5,10,25,-1],[3,5,10,25,"All"]] //value:item pair
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="gvState" runat="server" AllowPaging="True" CssClass="gvv display" AutoGenerateColumns="False">
<Columns>
<asp:TemplateField HeaderText="SrNo" ItemStyle- HorizontalAlign="Center">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="10%" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Id" Visible="false">
<ItemTemplate>
<asp:Label ID="lblId" runat="server" Text='<%# Eval("Id") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="35%" />
</asp:TemplateField>
<asp:TemplateField HeaderText="State" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="lblState" runat="server" Text='<%# Eval("State") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="35%" />
</asp:TemplateField>
</Columns>
</asp:GridView>
/ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>
Step 7:-
Default.aspx.cs
Include three namespace
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillGrid();
}
}
private void fillGrid()
{
string conn=
ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
SqlConnection con = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand("SPState",con);
cmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
cmd.Dispose();
gvState.DataSource = ds;
gvState.DataBind();
}
Step 8:-View your
default.aspx page in browser and it will
almost look like below:-
Step 9:-Start searching
what are you waiting for.
Happy Coding!
