Plotting data from a database |
In this section, we will show you how to use FusionCharts XT and ASP.NET(C#) to plot charts from data contained in a database. Here, we will do the following:
We have 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 XT 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 two tables:
For demonstration, we have 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 XT - 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 have 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:
When you now run the code, you will get an output as under: |
Converting the example to use Data URL method |
Let's now convert this example to use Data URL method. As previously explained, in Data URL mode, you need two pages:
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 XT - Data URL 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 Data URL method. In our other examples, we have used Data String method //where the XML is generated in the same page as chart. Here, the XML data //for the chart will be generated in PieData.aspx. //To illustrate how to pass additional data as querystring to dataURL, //we have added an animate property, which will be passed to PieData.aspx. //PieData.aspx will handle this animate property and then generate the //XML accordingly. //For the sake of ease, we have 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 have 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 are:
|
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 have 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:
When you view this page, you will 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 the 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 will look for this: |
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MSCharts.aspx.cs" Inherits="DBExample_MSCharts" %>" <HTML> <HEAD> <TITLE>FusionCharts XT - 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("<chart caption='Factory Output report' subCaption='By Quantity' showBorder='1' formatNumberScale='0' rotatelabels='1' showvalues='0'>"); xmlData.AppendFormat("<categories>"); 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("<category label='{0}'/>", oRs.ReadData["dd"].ToString()); } oRs.ReadData.Close(); xmlData.AppendFormat("</categories>"); string factoryquery2 = "select * from factory_master"; DbConn oRs1 = new DbConn(factoryquery2); while (oRs1.ReadData.Read()) { xmlData.AppendFormat("<dataset seriesName='{0}'>", 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("<set value='{0}'/>", oRs2.ReadData[0].ToString()); } oRs2.ReadData.Close(); xmlData.AppendFormat("</dataset>"); } oRs1.ReadData.Close(); xmlData.AppendFormat("</chart>"); 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:
The chart will look as shown in the image below:
|