You are viewing documentation for an older version. For current documentation - click here.
Using FusionCharts with ASP.NET(C#) > Plotting data from a database

In this section, we'll show you how to use FusionCharts and ASP.NET(C#) to plot charts from data contained in a database. We'll create a pie chart to show "Production by Factory" using:

  • dataStr method first.
  • Thereafter, we'll convert this chart to use dataURL method.
  • Next, we will create a multi-series line chart from database

We've used MySQL database here. The database dump is present in Download Package > Code > CS > App_Data folder. You can, however, use any database with FusionCharts including MS SQL, Oracle, Access etc.

Before you go further with this page, we recommend you to please see the previous section "Basic Examples" as we start off from concepts explained in that page.

The code examples contained in this page are present in Download Package > Code > CS > DBExample folder. The MS-SQL database dump is present in Download Package > Code > CS > App_Data.

 
Database Structure
Before we code the ASP.NET(C#) pages to retrieve data, let's quickly have a look at the database structure.

The database contains just 2 tables:

  1. Factory_Master: To store the name and id of each factory
  2. Factory_Output: To store the number of units produced by each factory for a given date.

For demonstration, we've fed some dummy data in the database. Let's now shift our attention to the ASP.NET(C#) page that will interact with the database, fetch data and then render a chart.

 
Building the ASP.NET(C#) Page using Data String Method
The ASP.NET(C#) page for dataStr method example is named as BasicDBExample.aspx (in DBExample folder). It contains the following code: 
<%@ Page Language="C#" AutoEventWireup="false" CodeFile="BasicDBExample.aspx.cs" Inherits="DBExample_BasicDBExample" %>
<HTML>
  <HEAD>
    <TITLE>FusionCharts - Database Example </TITLE>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>
  <body>
    <form id='form1' name='form1' method='post' runat="server">
      <asp:Literal ID="Literal1" runat="server"></asp:Literal> 
    </form>
  </body>
</HTML>


Code Behind page: 

protected void Page_Load(object sender, EventArgs e)
{
  //In this example, we show how to connect FusionCharts to a database.
  //For the sake of ease, we've used an Access database which is present in
  //../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each other. 
  //xmlData will be used to store the entire XML document generated
  StringBuilder xmlData=new StringBuilder();
  //Generate the chart element
  xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>"); 
  //Create recordset to get details for the factories
  string factoryQuery = "select a.FactoryId,a.FactoryName,sum(b.Quantity) as TotQ from     Factory_Master a,Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId,a.FactoryName";
  DbConn oRs = new DbConn(factoryQuery);
  //Iterate through each record
  while (oRs.ReadData.Read())
  {
    //Generate <set label='..' value='..' /> 
    xmlData.AppendFormat("<set label='{0}' value='{1}' />",oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString() );
  }
  oRs.ReadData.Close();
  //Close chart element
  xmlData.Append("</chart>");
  //Create the chart - Pie 3D Chart with data from xmlData
  Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", xmlData.ToString(), "FactorySum", "600", "300", false, true);
}

The following actions are taking place in this code:

  1. We first include FusionCharts.js JavaScript class and FusionCharts.dll , to enable easy embedding of FusionCharts.
  2. We then include DBConn.cs, which contains connection parameters to connect to the database.
  3. Thereafter, we generate the XML data document by iterating through each record and store it in strXML variable.
  4. Finally, we render the chart using RenderChart() method and pass strXML as dataStr.

When you now run the code, you'll get an output as under:

 
Converting the example to use dataURL method

Let's now convert this example to use dataURL method. As previously explained, in dataURL mode, you need two pages:

  1. 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 name this page as Default.aspx.
  2. Data Provider Page - This page provides the XML data to the chart. We'll name this page as PieData.aspx

The pages in this example are contained in Download Package > Code > CS > DB_dataURL folder.

 
Chart Container Page - Default.aspx
Default.aspx contains the following code to render the chart: 
<%@ Page Language="C#" AutoEventWireup="false" CodeFile="Default.aspx.cs" Inherits="DB_dataURL_Default" %>
<HTML>
  <HEAD>
    <TITLE>FusionCharts - dataURL and Database Example </TITLE>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>
  <body>
    <form id='form1' name='form1' method='post' runat="server">
      <asp:Literal ID="Literal1" runat="server"></asp:Literal> 
    </form>
  </body>
</HTML>
Code behind:
protected void Page_Load(object sender, EventArgs e)
{
  //In this example, we show how to connect FusionCharts to a database 
  //using dataURL method. In our other examples, we've used dataXML method
  //where the XML is generated in the same page as chart. Here, the XML data
  //for the chart would be generated in PieData.aspx.

  //To illustrate how to pass additional data as querystring to dataURL, 
  //we've added an animate	property, which will be passed to PieData.aspx. 
  //PieData.aspx would handle this animate property and then generate the 
  //XML accordingly.

  //For the sake of ease, we've used an Access database which is present in
  //../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each other.

  //Variable to contain dataURL
  //Set DataURL with animation property to 1
  //NOTE: It's necessary to encode the dataURL if you've added parameters to it

  String dataURL = Server.UrlEncode("PieData.aspx?animate=1");
  //Create the chart - Pie 3D Chart with dataURL as strDataURL
  Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", dataURL, "", "FactorySum", "600", "300", false, true);

}

In the above code, we're:

  1. Including FusionCharts.js JavaScript class and FusionCharts.dll
  2. Create the dataURL string and store it in strDataURL variable. We append a dummy property - animate to show how to pass parameters to dataURL. After building the dataURL, we encode it using encodeDataURL function defined in FusionCharts.dll.
  3. Finally, we render the chart using RenderChart() method and set dataURL as strDataURL.
Creating the data provider page PieData.aspx
PieData.aspx contains the following code to output XML Data: 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using DataConnection;

public partial class DB_dataURL_PieData : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    //This page generates the XML data for the Pie Chart contained in
    //Default.aspx.     
    //For the sake of ease, we've used an Access database which is present in
    //../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each other. 
    //xmlData will be used to store the entire XML document generated 
    StringBuilder xmlData = new StringBuilder();
    //Default.aspx has passed us a property animate. We request that. 
    string animateChart;
    animateChart = Request["animate"];
    //Set default value of 1 
    if (animateChart != null && animateChart.Length == 0)
    {
      animateChart = "1";
    }
    //Generate the chart element 
    xmlData.AppendFormat("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation='{0}'>", animateChart);
    //create recordset to get details for the factories 
    string query = "select a.FactoryId, a.FactoryName, sum(b.Quantity) as TotQ from .Factory_Master a, Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId, a.FactoryName";
    DbConn oRs = new DbConn(query);
    //Iterate through each factory 
    while (oRs.ReadData.Read())
    {
      //Generate <set label='..' value='..' /> 
      xmlData.AppendFormat("<set label='{0}' value='{1}' />", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString());
    }
    oRs.ReadData.Close();
    //Close chart element 
    xmlData.Append("</chart>");
    //Set Proper output content-type 
    Response.ContentType = "text/xml";
    //Just write out the XML data
    //NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER 
    Response.Write(xmlData.ToString());
  }
}

In the above page:

  1. We first request the animate property which has been passed to it (from dataURL)
  2. We generate the data and store it in strXML variable
  3. Finally, we write this data to output stream without any HTML tags.

When you view this page, you'll get the same output as before.

 
Create multi-series line chart

Let's now see how we can create a multi-series chart from database. This is no different from creating the Pie chart we saw above.

All you need to do is to build proper XML from the database that you query from your database. Multi-series charts have a different data format from the format of an XML for a single-series chart like pie. Let us see how the code would look for this: 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MSCharts.aspx.cs" Inherits="DBExample_MSCharts" %>"
<HTML>
  <HEAD>
    <TITLE>FusionCharts - Database Example </TITLE>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>
  <body>
    <form id='form1' name='form1' method='post' runat="server">
      <asp:Literal ID="Literal1" runat="server"></asp:Literal> 
    </form>
  </body>
</HTML>


Code Behind page: 

public string protected void Page_Load(object sender, EventArgs e)
{
  StringBuilder xmlData = new StringBuilder();
  xmlData.Append("");
  xmlData.AppendFormat("");
  string factoryQuery = "select distinct format(datepro,'dd/mm/yyyy') as dd from factory_output";
  DbConn oRs = new DbConn(factoryQuery);
  while (oRs.ReadData.Read())
  {
    xmlData.AppendFormat("", oRs.ReadData["dd"].ToString());
  }
  oRs.ReadData.Close();
  xmlData.AppendFormat("");
  string factoryquery2 = "select * from factory_master";
  DbConn oRs1 = new DbConn(factoryquery2);
  while (oRs1.ReadData.Read())
  {
    xmlData.AppendFormat("", oRs1.ReadData["factoryname"].ToString());
    string factoryquery3 = "select quantity from factory_output where factoryid=" + oRs1.ReadData["factoryid"].ToString();
    DbConn oRs2 = new DbConn(factoryquery3);
    while (oRs2.ReadData.Read())
    {
      xmlData.AppendFormat("", oRs2.ReadData[0].ToString());
    }
    oRs2.ReadData.Close();
    xmlData.AppendFormat("");
  }
  oRs1.ReadData.Close();
  xmlData.AppendFormat("");
  Literal1.Text = Literal1.Text = FusionCharts.RenderChart("../FusionCharts/MSLine.swf", "", xmlData.ToString(), "myFirst", "600", "300", false, true, false);
}

The following actions are taking place in this code:

  1. We first include FusionCharts.js JavaScript class and FusionCharts.dll
  2. We query database for all distinct dates to add them as categories labels of the chart (categories are stored in xmlData StringBuilder object)
  3. Next, we query database for all factory-wise and dates-wise output data to add them to chart data where data for each factory becomes a dataset (datasets are appended into xmlData StringBuilder object)
  4. Finally, we render the chart using RenderChart() method and pass xmlData as dataStr

The chart would look as shown in the image below: