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.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.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.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.
<html xmlns="http://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.
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.