Categories: Tutorials

FusionCharts Drill-Downs with EF & LINQ in Visual Studio

All charts (except for the zoom-line chart) in FusionCharts Suite XT can be configured to be rendered as drill-down charts. Drill-down charts can be implemented using several features, for example, the data plots can be linked to other charts, to simple URLs that will be open in the same page or in a different page, or to JavaScript functions defined in the same page. Click here to know more about the drill-down charts supported by FusionCharts. FusionCharts also lets you create charts by fetching data from the database. This tutorial tells you about how you can create the FusionCharts drill-down charts by fetching data from a database using Entity Framework and LINQ in Visual Studio. For this tutorial, the drill-down charts will be implemented as linked charts. Entity Framework(EF), the open source Object Relational Mapping (ORM) for ADO.NET, virtually eliminates the necessity to write data access codes by including support for LINQ for issuing queries. LINQ (Language Integrated Query) offers native data querying capabilities in the .NET Framework based languages.

Table of Contents

Requirements

For rendering charts in VB.NET, we need to make sure that we have the following components downloaded on our local machine:

Visual Studio IDE and .NET Framework [Download Link]
FusionCharts ASP.NET Wrapper [Download Link]

Creating the FusionCharts Drill-down Charts from a Database Using Entity Framework and LINQ

We will now see how you can create drill-down charts by sourcing data from a database using Entity Framework and LINQ. This process is bifurcated into three tasks:

Creating a new project
Creating a database and including it in the App_Data ASP.NET folder
Creating the .edmx file using the Entity Data Model

Creating a New Project

The step by step process for creating a new project is given below. Step 1 Open Visual Studio and click File → New → Project, as shown in the image below.

The New Project dialog box opens. Step 2 In the New Project dialog box, select Installed Templates → Visual C# → Web → ASP.Net Web Application (.NET Framework), as shown in the image below.

Step 3 In the Name field (in the New Project dialog box), type Demo Drilldown using EF and LINQ as the project name. A project structure, as shown in the image below, is created.

Creating a Database and Including it in the App_Data ASP.NET Folder

Step 1 Create a database, Product, with the quarterwiseProductData and Series_wise_Product tables. Use the table structure shown in the image below.

Step 2 Add the App_Data ASP.NET folder within the Demo Drilldown using EF and LINQ project as shown in the image below.

Step 3 Save the Product database within the App_Data folder.

Creating the .edmx File Using the Entity Data Model

A .edmx file is an XML file defining a storage model, a conceptual model, and the mapping between the two models. It also contains the information needed by the ADO.NET Entity Data Model Designer (Entity Designer) for rendering a model graphically. The steps below outline the process for creating a .edmx file based on the Product database created above. Step 1 Right click the project name, Demo Drilldown using EF and LINQ. Step 2 Click Add → New Item. The Add New Item dialog box opens. Step 3 In the Add New Item dialog box, click Installed → Visual C# → Data → ADO.NET Entity Data Model, as shown in the image below.

The Entity Data Model Wizard dialog box opens. Step 4 In the Entity Data Model Wizard, from the What should the model contain box, select EF Designer From Database and click Next.

Step 5 From the Which data connection should your application use to connect to the database drop-down, select Product.mdf and click Next.

Step 6 From the Which Version of Entity Framework do you want to use radio buttons, select Entity Framework 6.X and click Next.

Step 7 From the Which database objects do you want to include in your model checkbox, select both the tables and click Finish.

A visualization of the Entity class thus created is shown below.

Step 8 Add two C# code files within the project. Name them ProductController.cs and SeriesLinkController.cs. Step 9 Add the code given below to the ProductController.cs file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


namespace FCDemoWithSqlServerUsing_AspWrapper
{
    //controller class for quaring fetched data
    public class ProductController
    {
        //funcion for getting all months
        public List getMonth()
        {
            using (ProductEntities data = new ProductEntities())
            {

                var month = from product in data.quarterwiseProductDatas
                            select product.Month;
                return month.ToList();
            }

        }
        //for all quarters
        public List getQuarter()
        {
            using (ProductEntities data = new ProductEntities())
            {
                var quarter = from product in data.quarterwiseProductDatas
                              select product.quarter;
                return quarter.ToList();
            }

        }
        //Fetching all values
        public List getValues()
        {
            using (ProductEntities data = new ProductEntities())
            {
                var values = from product in data.quarterwiseProductDatas
                             select product.Value;
                return values.ToList();
            }
        }
        //Fetching series
        public List getSeries()
        {
            using (ProductEntities data = new ProductEntities())
            {
                var series = from product in data.quarterwiseProductDatas
                             select product.series;
                return series.ToList();
            }
        }
        //Fetching Monthwise values
        public List getMonthwiseValue(string series, string quarter)
        {
            using (ProductEntities data = new ProductEntities())
            {


                var monthValue = from product in data.quarterwiseProductDatas
                                 where product.quarter == quarter & product.series == series
                                 select product.Value;





                return monthValue.ToList();
            }
        }
        //Fetching quarter wise values
        public List quarterWiseMonth(string quarter)
        {

            using (ProductEntities data = new ProductEntities())
            {
                var monthVal = from product in data.quarterwiseProductDatas
                               where product.quarter == quarter
                               orderby product.Month descending
                               select product.Month;
                return monthVal.ToList();
            }

        }
    }
}

Step 10 Add the code given below to the SeriesLinkController.cs file. Along with the code added in the ProductController.cs file, this code will add custom controllers where LINQ has been implemented for querying the data fetched from the database using Entity Framework.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


namespace FCDemoWithSqlServerUsing_AspWrapper
{
    public class Series
    {
        public int value;
        public string link;
        public Series(int value,string link)
        {
            this.value = value;
            this.link = link;
        }
    }
    //controller for handling all series information
    public class SeriesLinkContoller
    {
        //fetching all series
        public List getSeries()
        {
            using (ProductEntities data = new ProductEntities())
            {

                var series = from product in data.Series_wise_Product
                            select product.seriesName;
                return series.ToList();
            }
        }
        //fetching series wise data
        public List getSeriesWiseData(string series)
        {
            List valueLink = new List();
            using (ProductEntities data = new ProductEntities())
            {
                var seriesLink = from product in data.Series_wise_Product
                                 where product.seriesName == series
                                 select new { linkdata = product.link, valuedata = product.SeriesValue };

                foreach(var obj in seriesLink)
                {
                    valueLink.Add(new Series(obj.valuedata, obj.linkdata));
                }
            }
            return valueLink;
        }
        //Fetching series values
        public List getSeriesValue()
        {
            List values = new List();
            using (ProductEntities data = new ProductEntities())
            {

                var seriesValue = from product in data.Series_wise_Product
                                  select product.SeriesValue;
               foreach(var val in seriesValue)
                {
                    values.Add(val);
                }
            }
            return values;
        }
        public List getLink()
        {
            using (ProductEntities data = new ProductEntities())
            {

              var  linkValue = from product in data.Series_wise_Product
                            select product.link;
                return linkValue.ToList();

            }

            }
    }
}

Step 11 Create a new folder within the project hierarchy and name it scripts. Paste all FusionCharts JavaScript files in the scripts folder.

Step 12 Create a new web form and name it Default.aspx. In the Default.aspx file, paste the code given below.

<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <script src="scripts/fusioncharts.js"></script>
    <script src="scripts/fusioncharts.js"></script>
    <script src="scripts/fusioncharts.theme.fint.js"></script>
    <title>Multi series column 2d drilldown demo</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Literal ID="l1" runat="server">
        Chart will load here
    </asp:Literal>
    </div>
    </form>
</body>
</html>
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using FusionCharts.Charts;
using System.Web.Script.Serialization;

namespace FCDemoWithSqlServerUsing_AspWrapper
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            //Declaringcollections to retain data fetched through controllers
            
            ProductController cont = new ProductController();
            SeriesLinkContoller linkcont = new SeriesLinkContoller();
            List ProductMonth = cont.getMonth();
            List ProductQuarter = cont.getQuarter();
            List ProductValue = cont.getValues();
            List ProductSeries = cont.getSeries();
            List SeriesNames = linkcont.getSeries();
            List LinkValue = linkcont.getLink();
            List SeriesValue = linkcont.getSeriesValue();

            //Building JSON String
            StringBuilder JSON = new StringBuilder();
            //appending all cosmatic properties
            JSON.Append("{" +
                  "'chart': {" +
                  "'caption': 'Comparison of two products on the basis of Quarterly Revenue'," +
                  "'xAxisname': 'Quarter'," +
                  "'yAxisName': 'Revenues (In USD)'," +
                  "'numberPrefix': '$'," +
                  "'plotFillAlpha': '80'," +
                  "'paletteColors': '#0075c2,#1aaf5d'," +
                  "'baseFontColor': '#333333'," +
                  "'baseFont': 'Helvetica Neue,Arial'," +
                  "'captionFontSize': '14'," +
                  "'subcaptionFontSize': '14'," +
                  "'subcaptionFontBold': '0'," +
                  "'showBorder': '0'," +
                  "'bgColor': '#ffffff'," +
                  "'showShadow': '0'," +
                  "'canvasBgColor': '#ffffff'," +
                  "'canvasBorderAlpha': '0'," +
                  "'divlineAlpha': '100'," +
                  "'divlineColor': '#999999'," +
                  "'divlineThickness': '1'," +
                  "'divLineIsDashed': '1'," +
                  "'divLineDashLen': '1'," +
                  "'divLineGapLen': '1'," +
                  "'usePlotGradientColor':'0'," +
                  "'showplotborder': '0'," +
                  "'valueFontColor': '#ffffff'," +
                  "'placeValuesInside': '1'," +
                  "'showHoverEffect': '1'," +
                  "'rotateValues': '1'," +
                  "'showXAxisLine': '1'," +
                  "'xAxisLineThickness': '1'," +
                  "'xAxisLineColor': '#999999'," +
                  "'showAlternateHGridColor': '0'," +
                  "'legendBgAlpha': '0'," +
                  "'legendBorderAlpha': '0'," +
                  "'legendShadow': '0'," +
                  "'legendItemFontSize': '10'," +
                  "'legendItemFontColor': '#666666'" +
                  " },"
                );
            //appenfing into StringBuilder objectiterating through collections
            JSON.Append("'categories': [{" +

                         "'category': [ ");
            foreach (var quar in ProductQuarter.Distinct())
            {
                //for last element escaping comma
                if (quar == ProductQuarter.Distinct().Last())
                {
                    JSON.Append("{ 'label': '" + quar + "' }");
                    break;

                }
                JSON.Append("{ 'label': '" + quar + "' },");

            }
            JSON.Append("]" +
     "}]," +
     "'dataset': [");
            foreach (var prod in SeriesNames.Distinct())
            {
                List seriesWiseValue = linkcont.getSeriesWiseData(prod);
                JSON.Append("{" +
                    "'seriesname':" + "'" + prod + "'," + "'data': [");
                foreach (var linkValue in seriesWiseValue)
                {
                    if (linkValue == seriesWiseValue.Last())
                    {
                        JSON.Append("{" +

              "'value':" + "'" + linkValue.value + "','link':" + "'" + linkValue.link + "'}");
                        break;
                    }

                    JSON.Append("{" +

             "'value':" + "'" + linkValue.value + "','link':" + "'" + linkValue.link + "'},");
                }
                if (prod == SeriesNames.Distinct().Last())
                {
                    JSON.Append("]" +

                   " }");
                    break;
                }
                JSON.Append("]" +

                   " },");

            }
            JSON.Append("],");
            JSON.Append("'linkeddata': [");
            foreach (var quat in ProductQuarter.Distinct())
            {
                if (quat == ProductQuarter.Distinct().Last())
                {
                    JSON.Append("{'id': '" + quat + "'," +

         "'linkedchart': {" +
                    "'chart': {" +
                        "'caption': 'Month wise Revenue for the 1st quarter'," +
                 "'subCaption': 'Harrys SuperMart'," +
                 "'xAxisname': 'Months'," +
                 "'yAxisName': 'Revenues (In USD)'," +
                 "'numberPrefix': '$'," +
                 "'theme': 'fint'" +
            " }," +
            "'categories': [{" +
                  "'category': [" +

                  getQuarterWiseMonth(quat) +
                  "]" +
                  "}],"


            );
                    JSON.Append("'dataset': [");

                    foreach (var seri in SeriesNames.Distinct())
                    {
                        if (seri == SeriesNames.Distinct().Last())
                        {
                            JSON.Append("{" +
                              "'seriesname': '" + seri + "',"
                      + "'data': [" + getquarterseriesWiseData(seri, quat) + "]}"




                              );
                            break;
                        }
                        JSON.Append("{" +
                            "'seriesname': '" + seri + "',"
                    + "'data': [" + getquarterseriesWiseData(seri, quat) + "]},"




                            );


                    }
                    JSON.Append("]}}");
                    break;
                }

                JSON.Append("{'id': '" + quat + "'," +

           "'linkedchart': {" +
                      "'chart': {" +
                          "'caption': 'Month wise Revenue for the 1st quarter'," +
                   "'subCaption': 'Harrys SuperMart'," +
                   "'xAxisname': 'Months'," +
                   "'yAxisName': 'Revenues (In USD)'," +
                   "'numberPrefix': '$'," +
                   "'theme': 'fint'" +
              " }," +
              "'categories': [{" +
                    "'category': [" +

                    getQuarterWiseMonth(quat) +
                    "]" +
                    "}],"


              );
                JSON.Append("'dataset': [");

                foreach (var seri in SeriesNames.Distinct())
                {

                    if (seri == SeriesNames.Distinct().Last())
                    {
                        JSON.Append("{" +
                          "'seriesname': '" + seri + "'"
                  + ",'data': [" + getquarterseriesWiseData(seri, quat) + "]}"

                          );

                        break;

                    }
                    JSON.Append("{" +
                          "'seriesname': '" + seri + "',"
                  + "'data': [" + getquarterseriesWiseData(seri, quat) + "]},"

                          );


                }
                JSON.Append("]}},");
            }

            JSON.Append("]}");
            //replacing all ' into "
            string str = JSON.ToString().Replace('\'', '\"');
            //writing JSON string into a JSON file and if data will be modified it wil be reflected in json file
            System.IO.File.WriteAllText(@Server.MapPath("json.json"), str);
            //calling FC Constructor
            Chart cc = new Chart("mscolumn2d", "mychart", "750", "550", "jsonurl", "json.json");
            //Rendering chart by calling Render()
            l1.Text = cc.Render();

        }

        //function for fetching series wise and quarter wise values
        public StringBuilder getquarterseriesWiseData(string series, string quarter)
        {
            ProductController cont = new ProductController();
            StringBuilder subjson = new StringBuilder();

            foreach (var seriwise in cont.getMonthwiseValue(series, quarter))
            {
                if (seriwise == cont.getMonthwiseValue(series, quarter).Last())
                {
                    subjson.Append("{" +
                    "'value': '" + seriwise + "'}");
                    break;
                }
                subjson.Append("{" +
                    "'value': '" + seriwise + "'},");

            }
            return subjson;

        }
        //For fetching quarter wise data.
        public StringBuilder getQuarterWiseMonth(string quarter)
        {
            ProductController cont = new ProductController();
            StringBuilder subjson = new StringBuilder();
            foreach (var month in cont.quarterWiseMonth(quarter).Distinct())
            {
                if (month == cont.quarterWiseMonth(quarter).Distinct().Last())
                {
                    subjson.Append("{" +
                 "'label': '" + month + "'}");
                    break;
                }
                subjson.Append("{" +
                 "'label': '" + month + "'},");

            }
            return subjson;

        }
    }
}

Step 13 Press Ctrl + F5 to execute Default.aspx. Your output should look as seen in the image below.

In your live chart, click the data plot for a quarter to drill-down to see the monthly revenue for that quarter.

Was There a Problem Rendering the Chart?

In case something went wrong and you are unable to see the chart, check for the following:

The chart ID should be unique for all charts rendered on the same page. Otherwise, it will result in a JavaScript error.
If the chart does not show up at all, check if the fusioncharts.js and FusionCharts wrapper FusionCharts.java was loaded. Also, check if the path to the fusioncharts.js and the FusionCharts.java files is correct, and whether the files exist in that location.

Nikita Jhanglani and Soumya Dutta