Post
Ask Question

Bind Stack Column Chart in ASP.NET Using jQuery And Ajax

I am using Highchart plugin to generate stack column chart. Here, I will use the same concept to create a web method in a web service and calling those methods in jQuery Ajax.

Step 1: Create a table as in the following:

  1. CREATE TABLE tblRevenue(  
  2.   
  3.    Id int primary key IDENTITY(1,1) NOT NULL,  
  4.   
  5.    year varchar](4) NULL,  
  6.   
  7.    quarter [varchar](4) NULL,  
  8.   
  9.    amount bigint NULL  
  10.   
  11. )  

But in real time scenario you might get data from one or more tables using joins.

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

I have attached script in sample download files, you can use that script to execute into the database. 

Step 2: Create a stored procedure.

  1. CREATE PROCEDURE Pr_getrevenue  
  2.   
  3. AS  
  4.   
  5. BEGIN  
  6.   
  7.    SELECT * FROM  
  8.   
  9.    (SELECT year,quarter,Sum(amount)amount  
  10.   
  11.    FROM tblrevenue  
  12.   
  13.    GROUP BY year,quarter) AS s  
  14.   
  15.    PIVOT ( Sum(amount)  
  16.   
  17.    FOR [quarter] IN ([Q1],[Q2],[Q3],[Q4])  
  18.   
  19.    )AS pv  
  20.   
  21. END  

Step 3: 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. [WebService(Namespace = "http://tempuri.org/")]  
  9. [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]  
  10. // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.   
  11. [System.Web.Script.Services.ScriptService]  
  12. public class ChartWebService: System.Web.Services.WebService   
  13. {  
  14.   
  15.     public class RevenueEntity   
  16.     {  
  17.         public string year   
  18.         {  
  19.             get;  
  20.             set;  
  21.         }  
  22.         public int quarter1   
  23.         {  
  24.             get;  
  25.             set;  
  26.         }  
  27.         public int quarter2   
  28.         {  
  29.             get;  
  30.             set;  
  31.         }  
  32.         public int quarter3   
  33.         {  
  34.             get;  
  35.             set;  
  36.         }  
  37.         public int quarter4   
  38.         {  
  39.             get;  
  40.             set;  
  41.         }  
  42.     }  
  43.   
  44.     [WebMethod]  
  45.     public List < RevenueEntity > GetRevenueDetails()   
  46.     {  
  47.         List < RevenueEntity > revenues = new List < RevenueEntity > ();  
  48.         using(SqlConnection con = new SqlConnection("Data Source=.;Trusted_Connection=true;DataBase=test"))   
  49.         {  
  50.             using(SqlCommand cmd = new SqlCommand())   
  51.             {  
  52.                 cmd.Connection = con;  
  53.                 cmd.CommandType = CommandType.StoredProcedure;  
  54.                 cmd.CommandText = "Pr_getrevenue";  
  55.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  56.                 DataSet ds = new DataSet();  
  57.                 da.Fill(ds, "Revenue");  
  58.                 if (ds != null)   
  59.                 {  
  60.                     if (ds.Tables.Count > 0)   
  61.                     {  
  62.                         if (ds.Tables["Revenue"].Rows.Count > 0)   
  63.                         {  
  64.                             foreach(DataRow dr in ds.Tables["Revenue"].Rows)   
  65.                             {  
  66.                                 revenues.Add(new RevenueEntity   
  67.                                 {  
  68.                                     year = dr["year"].ToString(), quarter1 = Convert.ToInt32(dr["Q1"]),  
  69.                                         quarter2 = Convert.ToInt32(dr["Q3"]), quarter3 = Convert.ToInt32(dr["Q3"]), quarter4 = Convert.ToInt32(dr["Q4"])  
  70.                                 });  
  71.                             }  
  72.                         }  
  73.                     }  
  74.                 }  
  75.             }  
  76.         }  
  77.         return revenues;  
  78.     }  
  79. }  
Don't forget to enable the following attributes in web service.

[System.Web.Script.Services.ScriptService]

Step 4: Add jQuery references as in the following:

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

Step 5: Implement jQuery Ajax as in the following.

  1. <script type="text/javascript">  
  2.     $(document).ready(function() {  
  3.   
  4.         $.ajax({  
  5.   
  6.             type: "POST",  
  7.   
  8.             contentType: "application/json; charset=utf-8",  
  9.   
  10.             url: "Services/ChartWebService.asmx/GetRevenueDetails",  
  11.   
  12.             data: "{}",  
  13.   
  14.             dataType: "json",  
  15.   
  16.             success: function(Result) {  
  17.   
  18.   
  19.                 Result = Result.d;  
  20.   
  21.   
  22.                 var series = [];  
  23.   
  24.                 var categories = [];  
  25.                 var quarter1 = [];  
  26.   
  27.                 var quarter2 = [];  
  28.   
  29.                 var quarter3 = [];  
  30.   
  31.                 var quarter4 = [];  
  32.   
  33.   
  34.                 for (var i in Result) {  
  35.   
  36.                     categories.push(Result[i].year);  
  37.   
  38.                     quarter1.push(Result[i].quarter1);  
  39.   
  40.                     quarter2.push(Result[i].quarter2);  
  41.   
  42.                     quarter3.push(Result[i].quarter3);  
  43.   
  44.                     quarter4.push(Result[i].quarter4);  
  45.   
  46.                 }  
  47.   
  48.                 series.push({  
  49.   
  50.                         name: 'Quarter 1',  
  51.   
  52.                         data: quarter1  
  53.   
  54.                     },  
  55.   
  56.                     {  
  57.   
  58.                         name: 'Quarter 2',  
  59.   
  60.                         data: quarter2  
  61.   
  62.                     },  
  63.   
  64.                     {  
  65.   
  66.                         name: 'Quarter 3',  
  67.   
  68.                         data: quarter3  
  69.   
  70.                     },  
  71.   
  72.                     {  
  73.   
  74.                         name: 'Quarter 4',  
  75.   
  76.                         data: quarter4  
  77.   
  78.                     }  
  79.   
  80.                 );  
  81.   
  82.                 BindChart(categories, series);  
  83.   
  84.             },  
  85.   
  86.             error: function(xhr) {  
  87.   
  88.                 alert('Request Status: ' + xhr.status + ' Status Text: ' + xhr.statusText + ' ' + xhr.responseText);  
  89.   
  90.             }  
  91.   
  92.         });  
  93.   
  94.     });  
  95.   
  96.     function BindChart(categories, series) {  
  97.   
  98.         $('#container').highcharts({  
  99.   
  100.             chart: {  
  101.   
  102.                 type: 'column'  
  103.   
  104.             },  
  105.   
  106.             title: {  
  107.   
  108.                 text: 'Stack Column Chart Demo'  
  109.   
  110.             },  
  111.   
  112.             xAxis: {  
  113.   
  114.                 categories: categories,  
  115.   
  116.                 labels: {  
  117.   
  118.                     style: {  
  119.   
  120.                         color: 'black',  
  121.   
  122.                         fontWeight: 'bold',  
  123.   
  124.                         fontSize: '14px'  
  125.   
  126.                     },  
  127.   
  128.                 }  
  129.   
  130.             },  
  131.   
  132.             legend: {  
  133.   
  134.                 itemStyle: {  
  135.   
  136.                     fontSize: '15px',  
  137.   
  138.                     font: '15pt Trebuchet MS, Verdana, sans-serif',  
  139.   
  140.                     color: '#0000FF'  
  141.   
  142.                 }  
  143.   
  144.             },  
  145.   
  146.             yAxis: {  
  147.   
  148.                 min: 0,  
  149.   
  150.                 title: {  
  151.   
  152.                     text: 'Amount in (Rs.)'  
  153.   
  154.                 },  
  155.   
  156.                 labels: {  
  157.   
  158.                     style: {  
  159.   
  160.                         color: 'black',  
  161.   
  162.                         fontWeight: 'bold',  
  163.   
  164.                         fontSize: '12px'  
  165.   
  166.                     }  
  167.   
  168.                 },  
  169.   
  170.                 stackLabels: {  
  171.   
  172.                     enabled: true,  
  173.   
  174.                     style: {  
  175.   
  176.                         fontWeight: 'bold',  
  177.   
  178.                         fontSize: '15px',  
  179.   
  180.                         color: 'black'  
  181.   
  182.                     }  
  183.   
  184.                 }  
  185.   
  186.             },  
  187.   
  188.             tooltip: {  
  189.   
  190.                 formatter: function() {  
  191.   
  192.                     return '<b>' + this.x + '</b><br/>' +  
  193.   
  194.                         this.series.name + ': ' + this.y + '<br/>' +  
  195.   
  196.                         'Total: ' + this.point.stackTotal;  
  197.   
  198.                 }  
  199.   
  200.             },  
  201.   
  202.             plotOptions: {  
  203.   
  204.                 column: {  
  205.   
  206.                     stacking: 'normal',  
  207.   
  208.                     dataLabels: {  
  209.   
  210.                         enabled: true,  
  211.   
  212.                         color: (Highcharts.theme && Highcharts.theme.dataLabelsColor) || 'white',  
  213.   
  214.                         style: {  
  215.   
  216.                             textShadow: '0 0 3px black'  
  217.   
  218.                         }  
  219.   
  220.                     }  
  221.   
  222.                 }  
  223.   
  224.             },  
  225.   
  226.             series: series  
  227.   
  228.         });  
  229.   
  230.     }  
  231. </script>  

Step 6: UI Design as in the following.

  1. <div id="container">  
  2.   
  3. </div>  

Step 7: Check output in the browser (see below screenshot of Stack Column Chart).

 
 

I hope you liked this article and understood how to bind a Stack Column Chart in ASP.NET using jQuery Ajax.


Similar Articles