Site icon FusionBrew – The FusionCharts Blog

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

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

    FusionCharts XT with ASP.NET

    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

    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: 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:
    Exit mobile version