Working with FusionCharts using ASP.NET
page 5 of 6
by Anand Narayanaswamy
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 46541/ 98

Creating Charts from a Database

In order to build charts from a database, you need to create a database and add data to it. FusionCharts supports typical database formats such as Microsoft Access and SQL Server 2008; however, for the purpose of this article, I have created an MS Access 2010 database. You can’t call data directly from the database fields. Instead, you need to convert the database fields into XML. The steps given below examine the whole procedure in detail:

Step – 1: Creation of Database

1.   Open Microsoft Access 2010 and double click Blank Database template

2.   Right click Table1 under Tables section, select Design View and enter the fields and its data type. You have to provide a name for the table if prompted.

3.   Save the design view and enter few data to the fields.

4.   Place the database inside App_Data folder. You can create this folder if it doesn’t exist.

Step – 2: Establishing Database Connection

In order to connect ODBC connectivity, you need to create a new file named DbConn.cs and add the following line of code. You should place the file inside App_Code folder.

using System;
using System.Data;
using System.Data.Odbc;
using System.Web;
using System.Configuration;
namespace DataConnection
{
    public class DbConn
      {   
        public OdbcConnection connection;
        public OdbcDataReader ReadData;
        public OdbcCommand aCommand;
        public DbConn(string strQuery)
        {
            string connectionName = "MSAccessConnection";
            string ConnectionString = 
ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            try
            {
                connection = new OdbcConnection();
                connection.ConnectionString = ConnectionString;
                connection.Open();
                GetReader(strQuery);
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write(e.Message.ToString());
            }
        }
        public void GetReader(string strQuery)
        {
            aCommand = new OdbcCommand(strQuery, connection);
            ReadData = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
      }
}

In the above code, we have created an object of OdbcConnection, OdbcDataReader and OdbcCommand classes. You can also see that a function named DbConn which accepts the SQL Query argument has been created and the appropriate code has been added to read and execute the query.

If you are using SQL Server 2008 database, then modify the line of the above code as follows:

string connectionName = "SQLServerConnection";

Step – 3: Adding Libraries

FusionCharts.dll is the core assembly file which you need to include in your bin folder.

Step – 4: Placing Charts

FusionCharts ships with numerous charts in shockwave flash format. You need to include the folder named "FusionCharts" located inside the product package file into your working directory. This folder also contains a FusionCharts.js file which you need to call from your ASP.NET application.

Step – 5: Creating configuration file

Web.Config is one of the most important files in every ASP.NET application. It will have relevant parameters for database connection and other information.

Add the following code by creating a new web.config file. If you create the file from Visual Studio 2010, you only need to provide the code within <connectionStrings></connectionStrings> tag.

<?xml version="1.0"?>
<configuration>
      <appSettings/>
      <connectionStrings>
<add name="MSAccessConnection" providerName="System.Data.Odbc" 
connectionString="Driver={Microsoft Access Driver (*.mdb, 
*.accdb)};Dbq=|DataDirectory|\ipl.accdb"/> 
  </connectionStrings>
      <system.web>      
            <compilation debug="true">
            </compilation>
            <authentication mode="Windows"/>
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
      </system.web>
</configuration>

You need to add following code if you are using the SQL Server 2008 database:

<add name="SQLServerConnection" providerName="System.Data.Odbc" 
connectionString="Driver={SQL 
Server};;uid=USERNAME;pwd=PASSWORD;server=HOST;database=DATABASE_NAME"/>

Step – 6: Creating an ASP.NET Application

We have so far completed the initial steps to plot a simple chart. The final process is to create the relevant ASP.NET application. For this purpose, you need to create a Default.aspx file and add the following code under the <Head> section

<script type="text/javascript" src="FusionCharts/FusionCharts.js"></script>

We also need to add a literal and button control. The chart will be drawn upon clicking the Button control.

In the code behind file, we have to create an instance of the StringBuilder class, pass the SQL query as a parameter to the DbConn function which we created in Step 1. You then have to create an XML file with the help of AppendFormat() method and plot the chart using RenderChart() method. The required snippet of the code is given below:

StringBuilder xmlData = new StringBuilder();
string query = "";
query = "select Team, Points from IPL_POINTS";
DbConn db = new DbConn(query);
xmlData.AppendFormat("<chart>");
while (db.ReadData.Read())
 {
   //Generate <set name='..' value='..' />           
   xmlData.AppendFormat("<set label='{0}' value='{1}' />", 
db.ReadData["Team"].ToString(), db.ReadData["Points"].ToString());
 } 
    xmlData.AppendFormat("</chart>");
    Literal1.Text = FusionCharts.RenderChart("FusionCharts/Column2D.swf", "", 
xmlData.ToString(), "chart1", "500", "400", false, true);

You should also make sure to import the following name spaces

using DataConnection;
using System.Text;
using InfoSoftGlobal;

The chart will appear as shown below after an initial animation which you will observe during the execution of the application.

Figure 4

In real world scenario, you can also use SQL Server 2008 database by following the steps mentioned above except the change in the connection string.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 6:45:43 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search