Using the ASP.NET AJAX Accordion to Dynamically Display Content From the Database

Posted by: Suprotim Agarwal , on 10/11/2008, in Category ASP.NET AJAX
Views: 107719
Abstract: 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.
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:
 Accordion
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!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
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



Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by rajesh on Monday, November 10, 2008 10:51 AM
Good article, this is very help full and keep posting.....
Comment posted by Joel on Friday, December 12, 2008 11:09 AM
When using the accordion as a databound control I ran into a significant obstacle.  Here is the url of my forum post describing the problem:  http://forums.asp.net/t/1356317.aspx

There is a bug at codeplex with some fixed code for the accordion:  http://www.codeplex.com/AjaxControlToolkit/WorkItem/View.aspx?WorkItemId=11055

Anyone interested in reading more about the "Shifting ID" problem can read about it here: http://weblogs.asp.net/infinitiesloop/archive/2006/10/16/TRULY-Understanding-Dynamic-Controls-_2800_Part-4_2900_.aspx
Comment posted by Suprotim Agarwal on Friday, December 12, 2008 12:37 PM
Joel: Thanks for sharing this info with us!
Comment posted by Satish on Monday, February 2, 2009 4:55 AM
How to keep open the multiple panes Accordion control?
Comment posted by Mike on Wednesday, February 25, 2009 3:23 PM
How can you show everything under one group such as a region?
I want to use this and show all stores under each each region such as

Northeast
   -- see all stores in the NE region
Southeast
   -- see all stores in the SE region

and so on,
Currently its showing like

Northeast
-- store 1
Northeast
-- store 2
Northeast
-- store 3

and so on. How can I group the region and only show it once?
Comment posted by Suprotim Agarwal on Thursday, February 26, 2009 6:48 AM
Mike: I blogged about it sometime back over here:
http://www.sqlservercurry.com/2008/09/select-top-n-rows-per-groupcategory.html

Hope this is what you are looking out for.
Comment posted by Mike on Thursday, February 26, 2009 9:30 AM
re: Suprotim Agarwal, thanks but I'm calling an existing Oracle proc that is returning me the data, so is there a way to do this on the front end so I'm not waiting on the Oracle DB to make the change?
Comment posted by Serafo on Tuesday, September 1, 2009 3:55 PM
Great!. I have a table that references itsel. I am trying doing a recursive function to populate the accordion y subAcordions, but is not working. Any one of you could help me with a example  please!!

Thanks in advance.
Comment posted by retert on Saturday, October 30, 2010 5:01 AM
<br/><html>
Comment posted by aida on Sunday, April 3, 2011 4:00 AM
I am unable to display the image i am using sql server 2008 and vs2008. there are no errors. but at line:
empno = Convert.ToInt32(context.Request.QueryString["id"]); says Input string was not in a correct format.why?
can you please explain this if (context.Request.QueryString["id"] != null)
           empno = Convert.ToInt32(context.Request.QueryString["id"]);  what is id? and also
string sql = "SELECT [C_Image] FROM [Fibretec].[dbo].[Category] WHERE C_ID = @ID";what  @ID stands for here. shouldn't it be C_ID=@C_ID here C_ID stands for  column name. i am confused between these id and ID. i have just started learning. thanks in advance.
Comment posted by aida on Sunday, April 3, 2011 5:56 AM
great work! i have a table in sqlserver 2008 with field name C_Image with datatype image. i am working in vs2008. i am new to .net .  please explain what does id stands for in code:if (context.Request.QueryString["id"] != null)
            empno = Convert.ToInt32(context.Request.QueryString["id"]);.  
Also what does ID stands for in code:string sql = "SELECT photo FROM Employees WHERE EmployeeID = @ID";
        SqlCommand cmd = new SqlCommand(sql, connection);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@ID", empno);
Since i am using field C_ID for the condition  so shouldn't it be "SELECT C_Image FROM Category WHERE C_ID=@C_ID";
Please help me out..... :(
Comment posted by aida on Sunday, April 3, 2011 10:10 AM
<%@ Page Title="" Language="C#"     MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Product.aspx.cs" Inherits="Product" CodeBehind ="~/Product.aspx.cs"  %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>


<asp:Content ID="Content2" ContentPlaceHolderID="MenuContentPlaceHolder" Runat="Server">

    <div >
    
    <ajax:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </ajax:ToolkitScriptManager>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="Data Source=archie-pc;Initial Catalog=Fibretec;Persist Security Info=True;User ID=sa;Password=9871057101;"          
    SelectCommand="SELECT [C_ID]
      ,[Category_Name]
  FROM [Fibretec].[dbo].[Category]" >
    </asp:SqlDataSource>  
   <ajax:Accordion ID="Accordion1" runat="server"
        SelectedIndex="0"
        HeaderCssClass="accordionHeader"
        HeaderSelectedCssClass="accordionHeaderSelected"
        ContentCssClass="accordionContent"
        AutoSize="None"
        FadeTransitions="true"
        TransitionDuration="250"
        SuppressHeaderPostbacks = "true"
        DataSourceID="SqlDataSource1"        
        >
        <HeaderTemplate>
        <b>
        <a href="">
            <%#DataBinder.Eval(Container.DataItem, "C_ID")%>
            <%#DataBinder.Eval(Container.DataItem, "Category_Name")%>
        </a>
        </b>

        </HeaderTemplate>

        <ContentTemplate>
         <div style="float:left; margin:8px;">                    
            <asp:Image ID="Image1" runat="server"
            ImageUrl='<%# "DisplayImage.ashx?C_ID=" + Eval("C_ID") %>' AlternateText ="Image " />
         </div>
        
        </ContentTemplate>
    
    </ajax:Accordion>
   </div>
</asp:Content>

<%@ WebHandler Language="C#" Class="DisplayImage" %>

using System;
using System.Web;
using System.Configuration;
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["C_ID"] != null)
            
           empno = Convert.ToInt32(context.Request.QueryString["C_ID"]);
       else
           throw new ArgumentException("No parameter specified");


        context.Response.ContentType = "image/jpeg";
        
        context.Response.OutputStream.Write(ShowEmpImage(empno), 78, (Convert.ToInt32(seq) - 78));  
    }
    public byte[] ShowEmpImage(int empno)
    {
        string conn = "Data Source=archie-pc;Initial Catalog=Fibretec;Persist Security Info=True;User ID=sa;Password=9871057101;";
        SqlConnection connection = new SqlConnection(conn);
        string sql = "SELECT [C_Image] FROM [Fibretec].[dbo].[Category] WHERE C_ID = @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;
        }
    }

}
Comment posted by f on Thursday, July 25, 2013 1:10 PM
dsda

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

FREE .NET MAGAZINES

Free DNC .NET Magazine

Tags

JQUERY COOKBOOK

jQuery CookBook