JavaScript charts using SQL Server data with FusionCharts XT – Part 2

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.

Table of Contents

FusionCharts XT with ASP.NET

• Part 1 – Create JavaScript charts in ASP.NET (C#)
• Part 2 – Plot JavaScript charts using SQL Server data
• Part 3 – Create Drill-Down charts in ASP.NET(C#)
• Part 4 – Create LinkedCharts in ASP.NET(C#)

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

• Visual Studio or Visual Web Developer
• SQL Server
• The latest version of FusionCharts XT
• Sample SQL Server Database

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..