In this article I will explain how to populate ASP.Net DataList control by binding DataSet client side using jQuery AJAX.
Database and Connection string
For this sample to work you will need to download the Microsoft Northwind database using the following link
Below is the connection string from the Web.Config file
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLExpress;
database=Northwind;Integrated Security=true"/>
</connectionStrings>
DataList Markup
Below is the ASP.Net DataList markup where I have bound the columns from the Customer table.
<asp:DataList ID="dlCustomers" runat="server" RepeatLayout="Table" RepeatColumns="3"
CellPadding="2" CellSpacing="2">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" border="1" style="width: 200px; height: 100px;
border: dashed 2px #04AFEF; background-color: #B0E2F5">
<tr>
<td>
<b><u><span class="name">
<%# Eval("ContactName") %></span></u></b>
</td>
</tr>
<tr>
<td>
<b>City: </b><span class="city"><%# Eval("City") %></span><br />
<b>Postal Code: </b><span class="postal"><%# Eval("PostalCode") %></span><br />
<b>Country: </b><span class="country"><%# Eval("Country")%></span><br />
<b>Phone: </b><span class="phone"><%# Eval("Phone")%></span><br />
<b>Fax: </b><span class="fax"><%# Eval("Fax")%></span><br />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Collections.Generic
Binding the Dummy DataListItem
You will need to bind dummy DataListItem to the ASP.Net DataList so that we can populate it using jQuery AJAX. When binding the dummy DataListItem I have taken into account the RepeatColumns property so that client side too our DataList will have same number of horizontal items.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindDummyItem();
}
}
private void BindDummyItem()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("CustomerID");
dummy.Columns.Add("ContactName");
dummy.Columns.Add("Country");
dummy.Columns.Add("City");
dummy.Columns.Add("PostalCode");
dummy.Columns.Add("Phone");
dummy.Columns.Add("Fax");
int count = dlCustomers.RepeatColumns == 0 ? 1 : dlCustomers.RepeatColumns;
for (int i = 0; i < count; i++)
{
dummy.Rows.Add();
}
dlCustomers.DataSource = dummy;
dlCustomers.DataBind();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindDummyItem()
End If
End Sub
Private Sub BindDummyItem()
Dim dummy As New DataTable()
dummy.Columns.Add("CustomerID")
dummy.Columns.Add("ContactName")
dummy.Columns.Add("Country")
dummy.Columns.Add("City")
dummy.Columns.Add("PostalCode")
dummy.Columns.Add("Phone")
dummy.Columns.Add("Fax")
Dim count As Integer = If(dlCustomers.RepeatColumns = 0, 1, dlCustomers.RepeatColumns)
For i As Integer = 0 To count - 1
dummy.Rows.Add()
Next
dlCustomers.DataSource = dummy
dlCustomers.DataBind()
End Sub
WebMethod to handle jQuery AJAX
Now we will create a Web method that will actually handle the AJAX requests from the jQuery AJAX handler and return the records fetched from the Customers table of the Northwind database as a string of XML.
C#
[WebMethod]
public static string GetCustomers()
{
string query = "SELECT top 10 * FROM Customers";
SqlCommand cmd = new SqlCommand(query);
return GetData(cmd).GetXml();
}
private static DataSet GetData(SqlCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return ds;
}
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetCustomers() As String
Dim query As String = "SELECT top 10 * FROM Customers"
Dim cmd As New SqlCommand(query)
Return GetData(cmd).GetXml()
End Function
Private Shared Function GetData(cmd As SqlCommand) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(strConnString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As New DataSet()
sda.Fill(ds)
Return ds
End Using
End Using
End Using
End Function
Above you can see I am executing a simple select query on the Customers table of the Northwind database using the GetData function DataSet object. This dataset object is later on converted to XML using the GetXml() method in the web method.
Client side functionality
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$("[id*=dlCustomers]").hide();
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
});
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Table");
var repeatColumns = parseInt("<%=dlCustomers.RepeatColumns == 0 ? 1 : dlCustomers.RepeatColumns %>");
var rowCount = Math.ceil(customers.length / repeatColumns);
var i = 0;
while (i < repeatColumns * rowCount) {
var row = $("[id*=dlCustomers] tr").eq(0).clone(true);
for (var j = 0; j < repeatColumns; j++) {
var customer = $(customers[i]);
if (customer.length == 0) {
$("table:last", row).remove();
} else {
$(".name", row).eq(j).html(customer.find("ContactName").text());
$(".city", row).eq(j).html(customer.find("City").text());
$(".postal", row).eq(j).html(customer.find("PostalCode").text());
$(".country", row).eq(j).html(customer.find("Country").text());
$(".phone", row).eq(j).html(customer.find("Phone").text());
$(".fax", row).eq(j).html(customer.find("Fax").text());
}
i++;
}
$("[id*=dlCustomers]").append(row);
}
$("[id*=dlCustomers] tr").eq(0).remove();
$("[id*=dlCustomers]").show();
}
</script>
In the above code snippet a jQuery AJAX call is made to the Web method GetCustomers in the document ready event of the page. The web method then returns an XML string which is parsed using the jQuery parseXML method. Once the XML document is created rows are created and appended to the ASP.Net DataList control.
Screenshot
Demo
Downloads
