Bind Drilldown Highchart in ASP.NET Using jQuery and Ajax

I am using the same concept to create a web method in a web service and calling those methods in jQuery.

Step 1

Create a table as in the following:

  1. CREATE TABLE tblRevenue (  
  2.    Id int Primary Key IDENTITY(1,1) NOT NULL,  
  3.    amount bigint NULL,  
  4.    quarter varchar(4) NULL,  
  5.    year varchar(4) NULL,
  6. )  

After completion of table design, enter some of the test data into table to work for our sample



We will now create a web method in the web service and use that method to call it from jQuery.

Step 2

Create an ASP.NET Web Service. Add an .asmx page to the current solution and modify the code as in the following example:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Services;  
  6. using System.Data;//  
  7. using System.Data.SqlClient;//  
  8. namespace DrillDownHighchart.Services  
  9. {  
  10.     /// <summary>  
  11.     /// Summary description for WebServiceChart  
  12.     /// </summary>  
  13.     [WebService(Namespace = "http://tempuri.org/")]  
  14.     [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]  
  15.     [System.ComponentModel.ToolboxItem(false)]  
  16.     // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.   
  17.     [System.Web.Script.Services.ScriptService]  
  18.     public class WebServiceChart : System.Web.Services.WebService  
  19.     {  
  20.         public class RevenueEntity  
  21.         {  
  22.             public string year { getset; }  
  23.             public int amount { getset; }  
  24.             public Boolean drilldown { getset; }  
  25.         }  
  26.         [WebMethod]  
  27.         public List<RevenueEntity> GetRevenueByYear()  
  28.         {  
  29.             List<RevenueEntity> YearRevenues = new List<RevenueEntity>();  
  30.             DataSet ds = new DataSet();  
  31.             using (SqlConnection con = new SqlConnection("Data Source=.;Trusted_Connection=true;DataBase=test"))  
  32.             {  
  33.                 using (SqlCommand cmd = new SqlCommand())  
  34.                 {  
  35.                     cmd.CommandText = "select year,SUM(amount)amount from tblRevenue group by year";  
  36.                     cmd.Connection = con;  
  37.                     using (SqlDataAdapter da = new SqlDataAdapter(cmd))  
  38.                     {  
  39.                         da.Fill(ds, "dsRevenue");  
  40.                     }  
  41.                 }  
  42.             }  
  43.             if (ds != null)  
  44.             {  
  45.                 if (ds.Tables.Count > 0)  
  46.                 {  
  47.                     if (ds.Tables["dsRevenue"].Rows.Count > 0)  
  48.                     {  
  49.                         foreach (DataRow dr in ds.Tables["dsRevenue"].Rows)  
  50.                         {  
  51.                             YearRevenues.Add(new RevenueEntity  
  52.                             {  
  53.                                 year = dr["year"].ToString(),  
  54.                                 amount = Convert.ToInt32(dr["amount"]),  
  55.                                 drilldown = true  
  56.                             });  
  57.                         }  
  58.                     }  
  59.                 }  
  60.             }  
  61.             return YearRevenues;  
  62.         }  
  63.         [WebMethod]  
  64.         public List<RevenueEntity> GetRevenueByQuarter(string year)  
  65.         {  
  66.             List<RevenueEntity> QuarterRevenues = new List<RevenueEntity>();  
  67.             DataSet ds = new DataSet();  
  68.             using (SqlConnection con = new SqlConnection("Data Source=.;Trusted_Connection=true;DataBase=test"))  
  69.             {  
  70.                 using (SqlCommand cmd = new SqlCommand())  
  71.                 {  
  72.                     cmd.CommandText = "select quarter,SUM(amount)amount from tblRevenue where year='" + year + "' group by quarter";  
  73.                     cmd.Connection = con;  
  74.                     using (SqlDataAdapter da = new SqlDataAdapter(cmd))  
  75.                     {  
  76.                         da.Fill(ds, "dsQuarter");  
  77.                     }  
  78.                 }  
  79.             }  
  80.             if (ds != null)  
  81.             {  
  82.                 if (ds.Tables.Count > 0)  
  83.                 {  
  84.                     if (ds.Tables["dsQuarter"].Rows.Count > 0)  
  85.                     {  
  86.                         foreach (DataRow dr in ds.Tables["dsQuarter"].Rows)  
  87.                         {  
  88.                             QuarterRevenues.Add(new RevenueEntity  
  89.                             {  
  90.                                 year = dr["quarter"].ToString(),  
  91.                                 amount = Convert.ToInt32(dr["amount"])  
  92.   
  93.                             });  
  94.                         }  
  95.                     }  
  96.                 }  
  97.             }  
  98.             return QuarterRevenues;  
  99.         }  
  100.     }  
  101.   
  102. }  

Don't forget to enable the attribute as in the following:

[System.Web.Script.Services.ScriptService]

Step 3

Add jQuery references as in the following:

  1. <script src="Script/jquery.min.js" type="text/javascript"></script>  
  2. <script src="Script/highcharts.js" type="text/javascript"></script>  
  3. <script src="Script/drilldown.js" type="text/javascript"></script>  

Step 4

Implement jQuery Ajax as in the following:

  1. <script type="text/javascript">  
  2.   
  3.   $(document).ready(function () {  
  4.       $.ajax({  
  5.           type: "POST",  
  6.           contentType: "application/json; charset=utf-8",  
  7.           url: "Services/WebServiceChart.asmx/GetRevenueByYear",  
  8.           data: "{}",  
  9.           dataType: "json",  
  10.           success: function (Result) {  
  11.   
  12.               Result = Result.d;  
  13.               var data = [];  
  14.   
  15.               for (var i in Result) {  
  16.                   var serie = { name: Result[i].year, y: Result[i].amount, drilldown: Result[i].drilldown };  
  17.                   data.push(serie);  
  18.               }  
  19.   
  20.               BindChart(data);  
  21.           },  
  22.           error: function (Result) {  
  23.               alert("Error");  
  24.           }  
  25.       });  
  26.   });  
  27.   
  28.   function BindChart(seriesArr) {  
  29.       $('#container').highcharts({  
  30.           chart: {  
  31.               type: 'column',  
  32.               backgroundColor: '#CCE6FF',  
  33.               borderColor: '#6495ED',  
  34.               borderWidth: 2,  
  35.               className: 'dark-container',  
  36.               plotBackgroundColor: '#F0FFF0',  
  37.               plotBorderColor: '#6495ED',  
  38.               plotBorderWidth: 1,  
  39.               events: {  
  40.   
  41.                   drilldown: function (e) {  
  42.                       if (!e.seriesOptions) {  
  43.                           var chart = this;  
  44.                           chart.showLoading('Loading Quarter wise Revenue ...');  
  45.                           var dataArr = CallChild(e.point.name);  
  46.                           chart.setTitle({  
  47.                               text: 'Quarter wise Revenue Report'  
  48.                           });  
  49.                           data = {  
  50.                               name: e.point.name,  
  51.                               data: dataArr  
  52.                           }  
  53.                           setTimeout(function () {  
  54.                               chart.hideLoading();  
  55.                               chart.addSeriesAsDrilldown(e.point, data);  
  56.                           }, 1000);  
  57.                       }  
  58.                   }  
  59.               }  
  60.           },  
  61.           title: {  
  62.               text: 'Year wise Revenue Report'  
  63.           },  
  64.           xAxis: {  
  65.               type: 'category'  
  66.           },  
  67.   
  68.   
  69.           plotOptions: {  
  70.               series: {  
  71.                   borderWidth: 0,  
  72.                   dataLabels: {  
  73.                       enabled: true  
  74.                   }  
  75.               }  
  76.           },  
  77.           series: [{  
  78.               name: 'Year',  
  79.               colorByPoint: true,  
  80.               data: seriesArr  
  81.           }],  
  82.   
  83.           drilldown: {  
  84.               series: []  
  85.           }  
  86.       });  
  87.   }  
  88.   
  89.   function CallChild(name) {  
  90.       var Drilldowndata = [];  
  91.       $.ajax({  
  92.           type: "POST",  
  93.           contentType: "application/json; charset=utf-8",  
  94.           url: "Services/WebServiceChart.asmx/GetRevenueByQuarter",  
  95.           data: JSON.stringify({ year: name }),  
  96.           dataType: "json",  
  97.           success: function (Result) {  
  98.               Result = Result.d;  
  99.               for (var i in Result) {  
  100.                   var serie = { name: Result[i].year, y: Result[i].amount };  
  101.                   Drilldowndata.push(serie);  
  102.               }  
  103.           },  
  104.           error: function (Result) {  
  105.               alert("Error");  
  106.           }  
  107.       })  
  108.       return Drilldowndata;  
  109.   }  
  110. t;/script>  

Step 5

Do the UI Design as in the following:

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.     <div id="container">  
  4.     </div>  
  5.     </form>  
  6. </body>  

Step 6

The output in a browser is as in the following:

Output in Browser

After clicking on bar the following chart by quarter will be generated.

quarter wise chart

I hope you like this article and understood how to bind a drilldown highchart in ASP.NET using jQuery Ajax.


Similar Articles