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(
  3. Id int primary key IDENTITY(1,1) NOT NULL,
  5. year varchar](4) NULL,
  7. quarter [varchar](4) NULL,
  9. amount bigint NULL
  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
  3. AS
  5. BEGIN
  7. SELECT * FROM
  9. (SELECT year,quarter,Sum(amount)amount
  11. FROM tblrevenue
  13. GROUP BY year,quarter) AS s
  15. PIVOT ( Sum(amount)
  17. FOR [quarter] IN ([Q1],[Q2],[Q3],[Q4])
  19. )AS pv
  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. {
  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. }
  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>
  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() {
  4. $.ajax({
  6. type: "POST",
  8. contentType: "application/json; charset=utf-8",
  10. url: "Services/ChartWebService.asmx/GetRevenueDetails",
  12. data: "{}",
  14. dataType: "json",
  16. success: function(Result) {
  19. Result = Result.d;
  22. var series = [];
  24. var categories = [];
  25. var quarter1 = [];
  27. var quarter2 = [];
  29. var quarter3 = [];
  31. var quarter4 = [];
  34. for (var i in Result) {
  36. categories.push(Result[i].year);
  38. quarter1.push(Result[i].quarter1);
  40. quarter2.push(Result[i].quarter2);
  42. quarter3.push(Result[i].quarter3);
  44. quarter4.push(Result[i].quarter4);
  46. }
  48. series.push({
  50. name: 'Quarter 1',
  52. data: quarter1
  54. },
  56. {
  58. name: 'Quarter 2',
  60. data: quarter2
  62. },
  64. {
  66. name: 'Quarter 3',
  68. data: quarter3
  70. },
  72. {
  74. name: 'Quarter 4',
  76. data: quarter4
  78. }
  80. );
  82. BindChart(categories, series);
  84. },
  86. error: function(xhr) {
  88. alert('Request Status: ' + xhr.status + ' Status Text: ' + xhr.statusText + ' ' + xhr.responseText);
  90. }
  92. });
  94. });
  96. function BindChart(categories, series) {
  98. $('#container').highcharts({
  100. chart: {
  102. type: 'column'
  104. },
  106. title: {
  108. text: 'Stack Column Chart Demo'
  110. },
  112. xAxis: {
  114. categories: categories,
  116. labels: {
  118. style: {
  120. color: 'black',
  122. fontWeight: 'bold',
  124. fontSize: '14px'
  126. },
  128. }
  130. },
  132. legend: {
  134. itemStyle: {
  136. fontSize: '15px',
  138. font: '15pt Trebuchet MS, Verdana, sans-serif',
  140. color: '#0000FF'
  142. }
  144. },
  146. yAxis: {
  148. min: 0,
  150. title: {
  152. text: 'Amount in (Rs.)'
  154. },
  156. labels: {
  158. style: {
  160. color: 'black',
  162. fontWeight: 'bold',
  164. fontSize: '12px'
  166. }
  168. },
  170. stackLabels: {
  172. enabled: true,
  174. style: {
  176. fontWeight: 'bold',
  178. fontSize: '15px',
  180. color: 'black'
  182. }
  184. }
  186. },
  188. tooltip: {
  190. formatter: function() {
  192. return '<b>' + this.x + '</b><br/>' +
  194. this.series.name + ': ' + this.y + '<br/>' +
  196. 'Total: ' + this.point.stackTotal;
  198. }
  200. },
  202. plotOptions: {
  204. column: {
  206. stacking: 'normal',
  208. dataLabels: {
  210. enabled: true,
  212. color: (Highcharts.theme && Highcharts.theme.dataLabelsColor) || 'white',
  214. style: {
  216. textShadow: '0 0 3px black'
  218. }
  220. }
  222. }
  224. },
  226. series: series
  228. });
  230. }
  231. </script>

Step 6: UI Design as in the following.

  1. <div id="container">
  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.