FusionCharts Free Documentation

Using FusionCharts with VB.NET (ASP.NET) > Plotting data from a database

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

dataXML method first.
Thereafter, we'll convert this chart to use dataURL method.

For the sake of ease, we'll use an Access Database. The database is present in Download Package > Code > VBNET > DB folder. You can, however, use any database with FusionCharts including MS SQL, Oracle, MySQL 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 > VBNET > DBExample folder. The Access database is present in Download Package > Code > VBNET > DB.

Before we code the ASP.NET pages to retrieve data, let's quickly have a look at the database structure.

The database contains just 2 tables:

Factory_Master: To store the name and id of each factory
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 page that will interact with the database, fetch data and then render a chart.

The ASP.NET page for dataXML method example is named as BasicDBExample.aspx (in DBExample folder). It contains the following code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="BasicDBExample.aspx.vb" Inherits="DBExample_BasicDBExample" %>

<HTML>
  <HEAD>
    <TITLE> FusionCharts Free - Database Example </TITLE>
    <%
    'You need to include the following JS file, if you intend to embed the chart using JavaScript.
    %>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>

In the above code, we have included FusionCharts.js file to render chart through javascript.We are also adding an ASP control literal which acts as the container for the charts. The CreateCharts() function does the generation, and is the code behind the file, BasicDBExample.vb. Let's take a look at the code behind file:

Imports DataConnection
Imports InfoSoftGlobal
Partial Class DBExample_BasicDBExample
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      ' Generate chart in Literal Control
      FCLiteral.Text = CreateChart()

   End Sub

   Public Function CreateChart() As String
      '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.

      'Database Objects - Initialization
      Dim oRs As DbConn, strQuery As String
      'strXML will be used to store the entire XML document generated
      Dim strXML As String

'Generate the graph element
strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'>"

' SQL Query
strQuery = "select a.FactoryId,a.FactoryName, sum(b.Quantity) as TotOutput from factory_master a,factory_output b where a.FactoryId=b.FactoryId group by a.FactoryId,a.FactoryName"

      ' Open Data Reader
      oRs = New DbConn(strQuery)
      'Iterate through each factory
      While oRs.ReadData.Read()

      'Generate <set name='..' value='..' />
      strXML = strXML & "<set name='" & oRs.ReadData("FactoryName").ToString() & "' value='" & oRs.ReadData("TotOutput").ToString() & "' />"

   End While

      ' Close Data Reader
      oRs.ReadData.Close()
      'Finally, close <graph> element
      strXML = strXML & "</graph>"

'Create the chart - Pie 3D Chart with data from strXML
Return FusionCharts.RenderChart("../FusionCharts/FCF_Pie3D.swf", "", strXML, "FactorySum", "650", "450", False, False)

End Function

End Class

The following actions are taking place in this code:

We first include DataConnection and InfoSoftGlobal namespace. DataConnection namespace is contained within DataConn VB.NET class inside App_Code folder of Download pack. It contains the connection parameters to connect to Access database. And InfoSoftGlobal namespace is from FusionCharts.dll file in bin directory.
We declare variables strQuery to store SQL query that fetches data from database and strXML to store XML data.
Thereafter, we generate the XML data document by iterating through the recordset and store it in strXML variable.
Finally, we render the chart using RenderChart() method and pass strXML as dataXML. RenderChart()is called form the Page_Load event listener.

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

Let's now convert this example to use dataURL method. As previously explained, in dataURL mode, 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 name this page as Default.aspx.
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 > CNET > DB_dataURL folder.

Default.aspx contains the following code to render the chart:

<%@ Page Language="VB" %>
<%@ Import Namespace="InfoSoftGlobal" %>

<script runat="server">

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
      '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.

      '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
      Dim strDataURL as string
      'the ASP.NET script in piedata.aspx interacts with the database,
      'converts the data into proper XML form and finally
      'relays XML data document to the chart
      strDataURL = "PieData.aspx"

      'Create the chart - Pie 3D Chart with dataURL as strDataURL
       FCLiteral.Text = FusionCharts.RenderChart("../FusionCharts/FCF_Pie3D.swf", strDataURL, "", "FactorySum", "650", "450", False, False)
   End Sub

</script>

<HTML>
  <HEAD>
    <TITLE>
      FusionCharts Free - dataURL and Database Example
    </TITLE>
    <%
      'You need to include the following JS file, if you intend to embed the chart using JavaScript.
    %>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>

  <BODY>

<asp:Literal ID="FCLiteral" runat="server"></asp:Literal>

</BODY>
</HTML>

In the above code, we:s

Include InfoSoftGlobal namespace and FusionCharts.js JavaScript class.
Create the dataURL string and store it in strDataURL variable.
Finally, we render the chart using RenderChart() method and set dataURL as strDataURL.

PieData.aspx.vb contains the following code to output XML Data:

Imports DataConnection
Partial Class DB_dataURL_PieData
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal obj As Object, ByVal e As EventArgs) Handles Me.Load
'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.

      'Database Objects - Initialization
      Dim oRs As DbConn, strQuery As String
      'strXML will be used to store the entire XML document generated
      Dim strXML As String

' SQL Query
strQuery = "select a.FactoryId,a.FactoryName, sum(b.Quantity) as TotOutput from factory_master a,factory_output b where a.FactoryId=b.FactoryId group by a.FactoryId,a.FactoryName"

' Open Data Reader
oRs = New DbConn(strQuery)

      'Iterate through each factory
      While oRs.ReadData.Read()

         'Generate <set name='..' value='..'/>
         strXML = strXML & "<set name='" & oRs.ReadData("FactoryName").ToString() & "' value='"       & oRs.ReadData("TotOutput").ToString() & "' />"

      End While
      ' Close Data Reader
      oRs.ReadData.Close()
      'Finally, close <graph> element
      strXML = strXML & "</graph>"

'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(strXML)
   End Sub
End Class

In the above page:

Include DataConnection Namespace to fetch data from database.
The result is stored it in strXML variable.
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.

We have used DataConnection Namespace in the above code and in all subsequent Database examples. Using this class we establish connection to the MS Access database with ADO.NET component. Let's go through the lines of code inside this class:

Imports Microsoft.VisualBasic
Imports System.Data.Odbc
Imports System.Data
Imports System.Web
Imports System.Configuration

Namespace DataConnection

   ''' <summary>
   ''' DataBase Connection Class.
   ''' </summary>
   Public Class DbConn
      Public connection As OdbcConnection
      Public ReadData As OdbcDataReader
      Public aCommand As OdbcCommand

      ''' <summary>
      ''' Data Connection and get Data Reader
      ''' </summary>
      ''' <param name="strQuery">SQL Query</param>
      Public Sub New(ByVal strQuery As String)
         Dim ConnectionString As String, connectionName As String

' MS Access DataBase Connection - Defined in Web.Config
connectionName = "MSAccessConnection"

'' SQL Server DataBase Connection - Defined in Web.Config
'' connectionName = "SQLServerConnection";

         ' Creating Connection string using web.config connection string
         ConnectionString = ConfigurationManager.ConnectionStrings(connectionName).ConnectionString
         Try

' Creating OdbcConnection Oject
connection = New OdbcConnection()

' Setting Conection String
connection.ConnectionString = ConnectionString

' Open Connection
connection.Open()

' get reader
GetReader(strQuery)

            Catch ex As Exception
            HttpContext.Current.Response.Write(ex.Message)
         End Try

      End Sub

      ''' <summary>
      ''' Create an instance dataReader
      ''' </summary>
      ''' <param name="strQuery">SQL Query</param>
      ''' <remarks>Return type object of OdbcDataReader</remarks>
      Public Sub GetReader(ByVal strQuery As String)

' Create a Command object
aCommand = New OdbcCommand(strQuery, connection)

' Create data reader object using strQuery string
ReadData = aCommand.ExecuteReader(CommandBehavior.CloseConnection)

End Sub
End Class
End Namespace

What it does:

Set up Connection as per the connection string defined in web.config file.

<connectionStrings>
<add name="MSAccessConnection" providerName="System.Data.Odbc" connectionString="Driver={Microsoft Access Driver (*.mdb)};Dbq=|DataDirectory|\FactoryDB.mdb"/>
</connectionStrings>

To change your connection to any other database server, you only need to setup web.config file.
The code to connect to SQL Server Database is also given in comment form. To connect to SQL Server you have to activate the corresponding code.
It accepts SQL Query, executes it and returns the result as ASP.NET DataReader object -ReadData.