In the second part of FusionCharts XT with ASP.NET series, learn how to use SQL Server data to create JavaScript charts. We will use the .NET library bundled with FusionCharts XT to create charts in ASP.NET (C#). In the first article of this series, we saw how this .NET library reduces developer effort and generates the HTML and JavaScript required to produce interactive charts in the browser.

FusionCharts XT with ASP.NET

What we are going to visualize

We will use the same SQL Server database that powers our showcase Management Dashboard. To keep things simple, we will plot the Total Sales Revenue of each Year.

.NET library bundled with FusionCharts XT

FusionCharts XT is essentially a JavaScript library. In order to render a chart, certain amount of HTML and JavaScript is required, along with the chart data in XML or JSON. In order to automate this, we provide a .NET library with FusionCharts XT, which generates the HTML and JavaScript required. You can find this library in FusionCharts XT Download Package > Code > CS > Bin > FusionCharts.dll. By referencing this library, rendering charts is just one line of code! Let us see how..

What we will need

Preparing the project

We will be using Visual Studio 2010 for this series. Visual Studio 2008 or Visual Web Developer will suit fine too. You can re-use the project we created in the previous article, or create a new one:
  • Create a blank ASP.NET (C#) web site and save it as FusionChartsXT_with_ASPNET.
  • Right-click the project’s name in Solution Explorer > Add New Item > Web Form. Keep the name as Using_MS_SQL_Server.aspx.
  • Copy the Charts folder from the FusionCharts Download Package and paste it in the Solution Explorer.
  • Include the FusionCharts XT’s JavaScript file in Using_MS_SQL_Server.aspx:[cceWl lang=’html’]
  • Right-click the project’s name in Solution Explorer > Add ASP.NET Folder > App_Data.
  • Right-click App_Data > Add Existing Item > Browse to the folder containing Sample SQL Server Database.
  • Right-click the project’s name in Solution Explorer > Add Reference > Browse. Now browse to FusionCharts XT Download Package > Code > CS > Bin > FusionCharts.dll.
  • In Using_MS_SQL_Server.aspx.cs, include the namespace of this library by writing
    using InfoSoftGlobal;
The above steps are necessary whenever you want to use FusionCharts XT in a .NET project. Before we begin coding, let us have a look at the database. It has 7 tables: But we will work with just 2 of them for this demo – FC_Orders and FC_OrderDetails:

Creating charts using Data String method

As explained in the previous post, you can supply data to FusionCharts using the Data String method or the Data URL method. First, let us try the Data String method. In this method, the XML or JSON data is embedded within the web page, along with the chart’s HTML and JavaScript code. This method doesn’t require a static data file or a virtual data provider. Moreover, once the chart has finished loading, the data is present locally within the page. In Using_MS_SQL_Server.aspx, create a Literal tag with a unique ID. In the code-behind page, write the following code. In-line comments will help you understand what we’re doing:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;
using InfoSoftGlobal;

public partial class Using_MS_SQL_Server : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Construct the connection string to interface with the SQL Server Database
        string connStr = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersFusion ChartsDocumentsVisual Studio 2010WebSitesFusionChartsXT_with_ASPNETApp_DataFusionChartsDB.mdf;Integrated Security=True;User Instance=True";

        // Initialize the string which would contain the chart data in XML format
        StringBuilder xmlStr = new StringBuilder();

        // Provide the relevant customization attributes to the chart
        xmlStr.Append("");

        // Create a SQLConnection object 
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            // Establish the connection with the database
            conn.Open();

            // Construct and execute SQL query which would return the total amount of sales for each year
            SqlCommand query = new SqlCommand("SELECT SUM(FC_OrderDetails.UnitPrice * FC_OrderDetails.Quantity) AS AMOUNT, YEAR(FC_Orders.OrderDate) AS yr FROM FC_Orders INNER JOIN FC_OrderDetails ON FC_OrderDetails.OrderID = FC_Orders.OrderID GROUP BY YEAR(FC_Orders.OrderDate) ORDER BY YEAR(FC_Orders.OrderDate)", conn);

            // Begin iterating through the result set
            SqlDataReader rst = query.ExecuteReader();

            while (rst.Read())
            {
                // Construct the chart data in XML format
                xmlStr.AppendFormat("", rst["yr"].ToString(), rst["AMOUNT"].ToString());
            }

            // End the XML string
            xmlStr.Append("");

            // Close the result set Reader object and the Connection object
            rst.Close();
            conn.Close();

            // Call the RenderChart method, pass the correct parameters, and write the return value to the Literal tag
            chart_from_db.Text = FusionCharts.RenderChart(
                "FusionChartsXT/Column2D.swf", // Path to chart's SWF
                "",         // Leave blank when using Data String method
                xmlStr.ToString(),  // xmlStr contains the chart data
                "annual_revenue",   // Unique chart ID
                "640", "340",       // Width & Height of chart
                false,              // Disable Debug Mode
                true);              // Register with JavaScript object
        }
    }
}
Save and run this project. This is the chart that you should see in your browser:

Creating charts using Data URL method

As previously explained, in Data URL method, you need two pages:
  • Chart Container Page – The page which embeds the HTML code to render the chart. This page also tells the chart where to load the data from. We’ll use the Using_MS_SQL_Server.aspx page for this.
  • Data Provider Page – This page provides the XML data to the chart. We’ll name this page as DataProvider.aspx.Keep the code in Using_MS_SQL_Server.aspx as it is. Delete all but the first line from DataProvider.aspx. From Using_MS_SQL_Server.aspx.cs, cut the contents of the Page_Load() method, till before the RenderChart() method’s line. Paste it in DataProvider.aspx.cs. This is what you should have in DataProvider.aspx.cs:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Text;
    using InfoSoftGlobal;
    
    public partial class DataProvider : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Construct the connection string to interface with the SQL Server Database
            string connStr = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersFusion ChartsDocumentsVisual Studio 2010WebSitesFusionChartsXT_with_ASPNETApp_DataFusionChartsDB.mdf;Integrated Security=True;User Instance=True";
    
            // Initialize the string which would contain the chart data in XML format
            StringBuilder xmlStr = new StringBuilder();
    
            // Provide the relevant customization attributes to the chart
            xmlStr.Append("");
    
            // Create a SQLConnection object 
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                // Establish the connection with the database
                conn.Open();
    
                // Construct and execute SQL query which would return the total amount of sales for each year
                SqlCommand query = new SqlCommand("SELECT SUM(FC_OrderDetails.UnitPrice * FC_OrderDetails.Quantity) AS AMOUNT, YEAR(FC_Orders.OrderDate) AS yr FROM FC_Orders INNER JOIN FC_OrderDetails ON FC_OrderDetails.OrderID = FC_Orders.OrderID GROUP BY YEAR(FC_Orders.OrderDate) ORDER BY YEAR(FC_Orders.OrderDate)", conn);
    
                // Begin iterating through the result set
                SqlDataReader rst = query.ExecuteReader();
    
                while (rst.Read())
                {
                    // Construct the chart data in XML format
                    xmlStr.AppendFormat("", rst["yr"].ToString(), rst["AMOUNT"].ToString());
                }
    
                // End the XML string
                xmlStr.Append("");
    
                // Close the result set Reader object and the Connection object
                rst.Close();
                conn.Close();
    
                // This page should return only XML content
                Response.ContentType = "text/xml";
                Response.Write(xmlStr.ToString());
            }
        }
    }
    
    This completes the code for DataProvider.aspx. Run this page, and all you will see is the chart data in XML format:
    
    
    
    
    
    Coming back to Using_MS_SQL_Server.aspx.cs, modify the RenderChart() method to use Data URL:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using InfoSoftGlobal;
    
    public partial class Using_MS_SQL_Server : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
            // Call the RenderChart method, pass the correct parameters, and write the return value to the Literal tag
            chart_from_db.Text = FusionCharts.RenderChart(
                "FusionChartsXT/Column2D.swf", // Path to chart's SWF
                Server.UrlEncode("DataProvider.aspx"), // Page which returns chart data
                "",  // String containing the chart data. Leave blank when using Data URL.
                "annual_revenue",   // Unique chart ID
                "640", "340",       // Width & Height of chart
                false,              // Disable Debug Mode
                true);              // Register with JavaScript object
        }
    }
    
    We use the Server.UrlEncode() method to take care of any special characters in the URL / querystring. Save and run this page. You should see the same chart as before: The data for this chart is provided by DataProvider.aspx.cs. You can see this by opening the Network tab in either Firebug or Chrome’s Developer Tools.

    Download Source Project

    Next in the series: Creating Drill-down JavaScript charts with ASP.NET(C#)

    In the third part of this series, we will add drill-down functionality to the above chart, so that we can drill-down into the year 1995 and see monthly revenue for that year. You can read more about drill-down charts in the FusionCharts Documentation. Stay tuned..

Take your data visualization to a whole new level

From column to donut and radar to gantt, FusionCharts provides with over 100+ interactive charts & 2,000+ data-driven maps to make your dashboards and reports more insightful

Explore FusionCharts

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 responses on “JavaScript charts using SQL Server data with FusionCharts XT – Part 2

  1. hi i used above code but it generate an error 

    This page contains the following errors:
    error on line 3 at column 1: Extra content at the end of the document
    Below is a rendering of the page up to the first error.”
     
    so please send me proper solutions for this on my mail id [email protected]
     
    Best Regards
    Mr.Laxman

  2. Hi, when i try to copy the code as it is, i get error XML parsing error. Please fix the code

  3. it works, thanks alot