Using the ASP.NET AJAX Accordion to Dynamically Display Content From the Database
As given in the ASP.NET AJAX documentation “The Accordion is a web control that allows you to provide multiple panes and display them one at a time. It is like having several CollapsiblePanels where only one can be expanded at a time.”
In this article, we will see how to dynamically pull data from the database and display it using an ASP.NET AJAX Accordion control. We will be using the data from the Employees table of the Northwind database. We have specifically chosen this table so in order to demonstrate how to display images dynamically in an Accordion control. You can adopt the approach shown in this article to display Authors information for your website or to display Employee Info.
Note: I am using Visual Studio 2008 and thereby utilizing the ASP.NET AJAX plumbing that comes along with it.
Let us get started with a Step-by-Step approach to do so. Viewers, who have prior experience in configuring the SqlDataSource, can jump directly to Step 5:
Step 1: Open VS 2008. Click File > New > Website. Choose ASP.NET Website from the list of installed template, choose target platform as .NET Framework 3.5, choose the desired language and enter the location where you would like to store the website on your FileSystem. I have created a folder called VS2008 Projects, so the location over here is C:\VS2008 Projects\ DynamicAccordion. After typing the location, click OK.
Step 2: Open Default.aspx. Switch to the Design mode of Default.aspx. Open the toolbox (Ctrl+Alt+X) > Data Tab > Drag and drop a SqlDataSource control on to the form. Click on the smart tag or right click SqlDataSource > Show Smart Tag > ‘Configure Data Source’ wizard. Click on ‘New Connection’ to open the ‘Add Connection’. Type your ‘Server Name’ and ‘Select a database Name’ to connect to. Over here, I have used (local) as the ‘ServerName’ and the database I am connecting to, is Northwind. Click on ‘Test Connection’ to make sure that there are no errors while connecting to the server. Click Ok.
Step 3: In the ‘Configure Data Source’, click ‘Next’. An option will be displayed to save the connection string to the configuration file. Select the checkbox ‘Yes, save this connection as:’, type a name for the connectionstring ‘NorthwindConnectionString’ and click Next.
Step 4: In the ‘Configure Select Statement’ > select ‘Specify Columns from Tables or Views’ radiobutton > Select ‘Orders’ table in the Name and choose EmployeeID, FirstName, LastName and Notes. Click Next > ‘Test Query’ to preview data > click Finish. The wizard adds a SqlDataSource control to the page as shown below.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [FirstName], [LastName], [Notes] FROM [Employees]" >
</asp:SqlDataSource>
If you check your web.config, the connection string is added as shown below:
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Step 5: Now add a <ScriptManager> control to the page and then an <Accordion> control from the toolbox (Accordion from the ASP.NET AJAX toolkit). Set the data source property of the Accordion to the 'SqlDataSource1'. After setting a few more properties like the HeaderCssClass, the markup will look similar to the following:
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [FirstName], [LastName], [Notes] FROM [Employees]" >
</asp:SqlDataSource>
<h2>About US</h2><br />
<cc1:Accordion ID="Accordion1" runat="server"
SelectedIndex="0"
HeaderCssClass="header"
HeaderSelectedCssClass="headerSelected"
ContentCssClass="contentAcc"
AutoSize="None"
FadeTransitions="true"
TransitionDuration="100"
SuppressHeaderPostbacks = "true"
DataSourceID="SqlDataSource1"
>
<HeaderTemplate>
</HeaderTemplate>
<ContentTemplate>
</ContentTemplate>
</cc1:Accordion>
</div>
</form>
</body>
The css used for HeaderCssClass, HeaderSelectedCssClass and ContentCssClass in the Accordion, will look similar to the following:
<head runat="server">
<title>Dynamically Populating Accordion</title>
<style type="text/css">
body
{
font: normal 11px auto "Trebuchet MS", Verdana;
background-color: #ffffff;
color: #4f6b72;
}
.header
{
color: #4f6b72;
background: #D5EDEF;
margin-bottom:10px;
}
.headerSelected
{
background: #F5FAFA;
color: #797268;
}
.contentAcc
{
background: #fff;
color: #4f6b72;
}
</style>
</head>
Step 6: So far so good! We will now display the records from the Employees table in the Accordion control. The <HeaderTemplate> will contain the Employee Name and the <ContentTemplate> will contain the Employee information like the notes and picture of the employee. Use the following mark up:
<cc1:Accordion ID="Accordion1" runat="server"
SelectedIndex="0"
HeaderCssClass="header"
HeaderSelectedCssClass="headerSelected"
ContentCssClass="contentAcc"
AutoSize="None"
FadeTransitions="true"
TransitionDuration="250"
SuppressHeaderPostbacks = "true"
DataSourceID="SqlDataSource1"
>
<HeaderTemplate>
<b>
<a href="">
<%#DataBinder.Eval(Container.DataItem, "FirstName")%>
<%#DataBinder.Eval(Container.DataItem, "LastName")%>
</a>
</b>
</HeaderTemplate>
<ContentTemplate>
<div style="float:left; margin:8px;">
<asp:Image ID="Image1" runat="server"
ImageUrl='<%# "DisplayImage.ashx?id=" + Eval("EmployeeID") %>' />
</div>
<div style="margin-top:8px;">
<%#DataBinder.Eval(Container.DataItem, "Notes")%>
</div>
</ContentTemplate>
</cc1:Accordion>
If you observe, we have used the DataBinder.Eval() to retrieve the FirstName and LastName in a hyperlink, which is to be displayed in the <HeaderTemplate>. When the user clicks on this link, the ContentTemplate appears which contains details about the Employee.
Note: Observe that we are using an ImageHandler to pull images and are dynamically passing the EmployeeID to the handler. I prefer using handlers as they provide me with a more efficient and flexible means of accessing server-side resources.
Step 7: In order to display the image in the Accordion control, we will create an Http handler. To do so, right click project > Add New Item > Generic Handler > DisplayImage.ashx. Add the code shown below to the handler.
C#
<%@ WebHandler Language="C#" Class="DisplayImage" %>
using System;
using System.Configuration;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;
public class DisplayImage : IHttpHandler
{
byte[] empPic = null;
long seq = 0;
public void ProcessRequest(HttpContext context)
{
Int32 empno;
if (context.Request.QueryString["id"] != null)
empno = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");
context.Response.OutputStream.Write(ShowEmpImage(empno), 78, Convert.ToInt32(seq) - 78);
}
public byte[] ShowEmpImage(int empno)
{
string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(conn);
string sql = "SELECT photo FROM Employees WHERE EmployeeID = @ID";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", empno);
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
seq = dr.GetBytes(0, 0, null, 0, int.MaxValue) - 1;
empPic = new byte[seq + 1];
dr.GetBytes(0, 0, empPic, 0, Convert.ToInt32(seq));
connection.Close();
}
return empPic;
}
public bool IsReusable
{
get
{
return false;
}
}
}
VB.NET
<%@ WebHandler Language="vb" Class="DisplayImage" %>
Imports System
Imports System.Configuration
Imports System.Web
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class DisplayImage
Implements IHttpHandler
Private empPic() As Byte = Nothing
Private seq As Long = 0
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim empno As Int32
If context.Request.QueryString("id") IsNot Nothing Then
empno = Convert.ToInt32(context.Request.QueryString("id"))
Else
Throw New ArgumentException("No parameter specified")
End If
context.Response.OutputStream.Write(ShowEmpImage(empno), 78, Convert.ToInt32(seq) - 78)
End Sub
Public Function ShowEmpImage(ByVal empno As Integer) As Byte()
Dim conn As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
Dim connection As New SqlConnection(conn)
Dim sql As String = "SELECT photo FROM Employees WHERE EmployeeID = @ID"
Dim cmd As New SqlCommand(sql, connection)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@ID", empno)
connection.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
seq = dr.GetBytes(0, 0, Nothing, 0, Integer.MaxValue) - 1
empPic = New Byte(seq){}
dr.GetBytes(0, 0, empPic, 0, Convert.ToInt32(seq))
connection.Close()
End If
Return empPic
End Function
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
In the code shown above, we are using the Request.QueryString[“id”] to retrieve the EmployeeID(emp_id) from the handler url. The ID is then passed to the ‘ShowEmpImage()’ method where the image is fetched from the database using SqlDataReader and returned in a byte[] object. Using the OutputStream.Write(), we write the sequence of bytes to the current stream and you get to see your image in the Accordion control.
Note: If you have been reading my articles in the past, you must have observed that I have changed a few bits of the ImageHandler. I did so after reading Peter Vogel’s article about ImageHandler over here. I felt his approach was more scalable and easy to use.
We are all set now to run the application. If you have followed the steps correctly, on running the application, you will see a screen similar to the one shown below:
The Accordion is a handy control and can be used in a variety of scenarios like displaying the Employee Information or designing an ‘About Us’ page for your website. Write back and let me know how you plan to use this approach. The entire source code in C# and VB.NET can be downloaded over here. I hope this article was useful and I thank you for viewing it.
Was this article worth reading? Share it with fellow developers too. Thanks!
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and a new one recently at
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigious Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.
Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook