Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Bind data and display datatable on aspx page using Jquery/JSON in asp.net

Mar 31, 2012
Introduction:

In this article I will explain how to bind data to datatable and display it on our aspx page using JQuery or JSON in asp.net.


Description:
  
In previous article I explained
how to bind data to gridview with JQuery. Now I am using same concept to explain how to bind data to datatable using JQuery/JSON and display it on aspx page in asp.net.
To implement this concept first we need to design table in database give name as UserInformation to save user details in database.
Column Name
Data Type
Allow Nulls
UserId
int(set identity property=true)
No
UserName
varchar(50)
Yes
Location
nvarchar(max)
Yes
After completion table design enter some of user details in database to work for our sample.
Now create new website and write the following code in your aspx page

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Asp.net Bind Data to Datatable using JQuery or JSON</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function(data) {
for (var i = 0; i < data.d.length; i++) {
$("#tbDetails").append("<tr><td>" + data.d[i].UserId + "</td><td>" + data.d[i].UserName + "</td><td>" + data.d[i].Location + "</td></tr>");
}
},
error: function(result) {
alert("Error");
}
});
});
</script>
<style type="text/css">
table,th,td
{
border:1px solid black;
border-collapse:collapse;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table id="tbDetails" cellpadding="0" cellspacing="0">
<thead style="background-color:#DC5807; color:White; font-weight:bold">
<tr style="border:solid 1px #000000">
<td>UserId</td>
<td>UserName</td>
<td>Location</td>
</tr>
</thead>
<tbody>
</tbody>
</table>
</form>
</body>
</html>
If you observe above code in header section I added script file link by using that file we have a chance to interact with JQuery and if you want to know the script function declared in header section check these posts JQuery UI AutoComplete textbox with database and call asp.net pagemethods in JQuery. Here I explained that function clearly.

Now open code behind file and add the following namespaces


using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Collections.Generic;
After that write the following code in code behind

C#.NET Code


protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static UserDetails[] BindDatatable()
{
DataTable dt = new DataTable();
List<UserDetails> details = new List<UserDetails>();

using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=MySampleDB;Integrated Security=true"))
{
using (SqlCommand cmd = new SqlCommand("select TOP 10 UserId,UserName,Location from UserInformation", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dtrow in dt.Rows)
{
UserDetails user = new UserDetails();
user.UserId = dtrow["UserId"].ToString();
user.UserName = dtrow["UserName"].ToString();
user.Location = dtrow["Location"].ToString();
details.Add(user);
}
}
}
return details.ToArray();
}
public class UserDetails
{
public string UserId { get; set; }
public string UserName { get; set; }
public string Location { get; set; }
}
VB.NET Code:


Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Partial Class VBSample
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
End Sub

<WebMethod()> _
Public Shared Function BindDatatable() As UserDetails()
Dim dt As New DataTable()
Dim details As New List(Of UserDetails)()

Using con As New SqlConnection("Data Source=SureshDasari;Initial Catalog=MySampleDB;Integrated Security=true")
Using cmd As New SqlCommand("select TOP 10 UserId,UserName,Location from UserInformation", con)
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
For Each dtrow As DataRow In dt.Rows
Dim user As New UserDetails()
user.UserId = dtrow("UserId").ToString()
user.UserName = dtrow("UserName").ToString()
user.Location = dtrow("Location").ToString()
details.Add(user)
Next
End Using
End Using
Return details.ToArray()
End Function
Public Class UserDetails
Public Property UserId() As String
Get
Return m_UserId
End Get
Set(ByVal value As String)
m_UserId = Value
End Set
End Property
Private m_UserId As String
Public Property UserName() As String
Get
Return m_UserName
End Get
Set(ByVal value As String)
m_UserName = Value
End Set
End Property
Private m_UserName As String
Public Property Location() As String
Get
Return m_Location
End Get
Set(ByVal value As String)
m_Location = Value
End Set
End Property
Private m_Location As String
End Class
End Class
Demo

Download sample code attached

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

27 comments :

Anonymous said...

Very Very Helpfull

Dinesh said...

Good one

Muhammad Farooq said...

Excellent Post...It Solves my Problem around reading more than 300 post on different post.

I want to call this On Blur Event of Text Box
this.TxtAccountCode.Attributes.Add("onblur", "javascript:CallMe('" + TxtAccountCode.ClientID + "', '" + this.TxtAccountDescription.ClientID + "')");

Muhammad Farooq said...

Once Again Zabardast.

S. Rama Shankara Rao said...

Hi ,

This is Shankar,

how to add asp button to this grid(auto filling grid using Jquery) which fires onClick event to process some action on that particular row data.
and code the code behind to get data of that row

please help me.

Happyman said...

very very helpful and nice code

Hanish Goel said...

Thank you... Its really useful

vaibho said...

Hi, I can run it well with aspx page in asp.net but when i try to implement it in mvc then it gives problem. I wrote web method in controller and called it from ajax call. seems blank table. When i debugged and checked i was the list array returning data, but in chrome development tool (F12), it shows blank data in networking tab. Is there any js conflict? please let me know.

Unknown said...

its not running on asp.net 2.0

Abhishek Naniwadekar said...

Its not working. I m getting following error
Failed to load resource: the server responded with a status of 500 (Internal Server Error)
http://localhost:1582/Default.aspx/BindDatatable

Anonymous said...

Excellent, thank you so much for such an "Easy to understand" article

Unknown said...

it works fine...but additionally i should add the edit and delete links to the table...can u pls help me

Unknown said...

undefined error what is it

Anonymous said...

its very much useful thank you..... :)

Jotiram Shendarkar. Kine,Ajara,Kolhapur(Maharastra) said...

nice article

Anonymous said...

thank you. good article.

Unknown said...

1155 rows only reterive the code... how can i get more records 1155

Unknown said...

This code perfecty work for below 1155 rows of records only..... how can i get the record more then 1155...
Guys check it.........

Anonymous said...

You are a god

ajay said...

how edit this records?

Anonymous said...

What if I return data as DataTable(i,e DataTable in return type)? How to retrieve return data in jQuery as DataTable in return type?

hkfgkfgjfg said...

............

Unknown said...
This comment has been removed by the author.
Unknown said...

How to bind paging in Table Using Jquery Json

Anonymous said...

Excellent, very useful and I worked very well

Chetan Sarode said...

Very Helpful

Unknown said...

show error if reocrds more then 200 hunders

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.