Google provides the Google API for applying chart representation of data. The Google Chart tool is very dominant and smooth to use and it is free.

Google Chart API provides many types of chart in which we can represent the data in the application. Some of them are given below:

  1. Geo Chart
  2. Column Chart
  3. Bar Chart
  4. Line Chart
  5. Pie Chart
  6. Area Chart

So, let’s create an ASP.NET MVC application and fetch the data from the database and in the application we will use the Google Chart API to represent the data in the Google Chart. We will proceed with the following sections:

  1. Getting Started
  2. Perform Database Operation
  3. Creating Library
  4. Represent Data

Getting Started

In this section we will create MVC application in the Visual Studio 2013 with the following steps:

Step 1: Open Visual Studio 2013 and click on “New Project”,

Start Page of VS 2013
Figure 1: Start Page of VS 2013

Step 2: Select Web tab from the left pane and select the “ASP.NET Web Application”,

Creating Web Application in VS 2013
Figure 2: Creating Web Application in VS 2013

Step 3: Select “MVC project template” from the ASP.Net Wizard to create MVC app,

MVC Project Template
Figure 3: MVC Project Template

This will create your MVC Application and after some changes in default layout page run the application.

Mvc App Start Page
Figure 4: Mvc App Start Page

Perform Database Operation

In this section we will perform database operation in which we will get the data from the database. So, let’s start with the following procedure:

Step 1: Create database and database tables from the following query,

  1. CREATE DATABASE ChartSample
  3. USE [ChartSample]
  4. GO
  6. SET ANSI_NULLS ON
  7. GO
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10. SET ANSI_PADDING ON
  11. GO
  12. CREATE TABLE [dbo].[CS_Player](
  13. [PlayerId] [int] IDENTITY(1,1) NOT NULL,
  14. [PlayerName] [varchar](50) NULL,
  15. CONSTRAINT [PK_CS_Player] PRIMARY KEY CLUSTERED
  16. (
  17. [PlayerId] ASC
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  21. GO
  22. SET ANSI_PADDING OFF
  23. GO
  24. /****** Object: Table [dbo].[CS_PlayerRecord] Script Date: 11/19/2015 4:00:01 PM ******/
  25. SET ANSI_NULLS ON
  26. GO
  27. SET QUOTED_IDENTIFIER ON
  28. GO
  29. CREATE TABLE [dbo].[CS_PlayerRecord](
  30. [ID] [int] IDENTITY(1,1) NOT NULL,
  31. [PlayerId] [int] NULL,
  32. [Year] [int] NULL,
  33. [TotalRun] [int] NULL,
  34. [TotalWickets] [int] NULL,
  35. [ODIMatches] [int] NULL,
  36. [TestMatches] [int] NULL,
  37. CONSTRAINT [PK_CS_PlayerRecord] PRIMARY KEY CLUSTERED
  38. (
  39. [ID] ASC
  40. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  41. ) ON [PRIMARY]
  43. GO
  44. ALTER TABLE [dbo].[CS_PlayerRecord] WITH CHECK ADD CONSTRAINT [FK_CS_PlayerRecord_CS_Player] FOREIGN KEY([PlayerId])
  45. REFERENCES [dbo].[CS_Player] ([PlayerId])
  46. GO
  47. ALTER TABLE [dbo].[CS_PlayerRecord] CHECK CONSTRAINT [FK_CS_PlayerRecord_CS_Player]
  48. GO
Step 2: Insert records in the tables.

Step 3: Now we will create procedures with the following code,
  1. USE [ChartSample]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[SC_GetPlayers] Script Date: 11/19/2015 4:21:29 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROC [dbo].[SC_GetPlayers]
  9. AS
  10. BEGIN
  11. SELECT *
  12. FROM CS_Player
  13. END
  15. USE [ChartSample]
  16. GO
  17. /****** Object: StoredProcedure [dbo].[SC_GetPlayerRecordsBtPlayerId] Script Date: 11/19/2015 4:21:26 PM ******/
  18. SET ANSI_NULLS ON
  19. GO
  20. SET QUOTED_IDENTIFIER ON
  21. GO
  22. ALTER PROC [dbo].[SC_GetPlayerRecordsBtPlayerId] @PlayerId INT
  23. AS
  24. BEGIN
  25. SELECT PlayerId ,
  26. Year ,
  27. TotalRun ,
  28. TotalWickets ,
  29. ODIMatches ,
  30. TestMatches
  31. FROM CS_PlayerRecord
  32. WHERE PlayerId = @PlayerId
  33. END
Creating Library

In this section we will add class library project in the same solution and by using the reference we will get data from the database. So, let’s start with the following steps:

Step 1: Right click on the solution and add a new project,

Adding New Project
Figure 5: Adding New Project

Step 2: Select the “Class Library” and specify the name as “Chart Library”,

Creating Class Library
Figure 6: Creating Class Library

Step 3: Now add a class in the library project,

Adding New Class
Figure 7: Adding New Class

Step 4: Replace the class code with the following code,
  1. namespace ChartLibrary
  2. {
  3. public class Players
  4. {#
  5. region Properties
  6. /// <summary>
  7. /// get and set the PlayerId
  8. /// </summary>
  9. public int PlayerId
  10. {
  11. get;
  12. set;
  13. }
  14. /// <summary>
  15. /// get and set the PlayerName
  16. /// </summary>
  17. public string PlayerName
  18. {
  19. get;
  20. set;
  21. }
  22. /// <summary>
  23. /// get and set the PlayerList
  24. /// </summary>
  25. public List < Players > PlayerList
  26. {
  27. get;
  28. set;
  29. }
  30. /// <summary>
  31. /// get and set the PlayerRecordList
  32. /// </summary>
  33. public List < PlayerRecord > PlayerRecordList
  34. {
  35. get;
  36. set;
  37. }#
  38. endregion
  39. }
  40. public class PlayerRecord
  41. {#
  42. region Properties
  43. /// <summary>
  44. /// get and set the PlayerId
  45. /// </summary>
  46. public int PlayerId
  47. {
  48. get;
  49. set;
  50. }
  51. /// <summary>
  52. /// get and set the Year
  53. /// </summary>
  54. public int Year
  55. {
  56. get;
  57. set;
  58. }
  59. /// <summary>
  60. /// get and set the TotalRun
  61. /// </summary>
  62. public int TotalRun
  63. {
  64. get;
  65. set;
  66. }
  67. /// <summary>
  68. /// get and set the TotalWickets
  69. /// </summary>
  70. public int TotalWickets
  71. {
  72. get;
  73. set;
  74. }
  75. /// <summary>
  76. /// get and set the ODIMatches
  77. /// </summary>
  78. public int ODIMatches
  79. {
  80. get;
  81. set;
  82. }
  83. /// <summary>
  84. /// get and set the TestMatches
  85. /// </summary>
  86. public int TestMatches
  87. {
  88. get;
  89. set;
  90. }#
  91. endregion
  92. }
  93. }
Step 5: Right click on References in the class library project and click on “Manage NuGet Packages”,

Adding NuGet Package
Figure 8: Adding NuGet Package

Step 6: Search for Enterprise Library and install it in the library project,

Adding Enterprise Library Package
Figure 9: Adding Enterprise Library Package

Step 7: Add another class named “PlayerDAL” and replace the code with the following code,

  1. using Microsoft.Practices.EnterpriseLibrary.Data;
  2. using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Configuration;
  6. using System.Data;
  7. using System.Data.Common;
  8. using System.Linq;
  9. using System.Reflection;
  10. namespace ChartLibrary
  11. {
  12. public class PlayerDAL
  13. {#
  14. region Variable
  15. /// <summary>
  16. /// Specify the Database variable
  17. /// </summary>
  18. Database objDB;
  19. /// <summary>
  20. /// Specify the static variable
  21. /// </summary>
  22. string ConnectionString;#
  23. endregion# region Database Method
  24. public List < T > ConvertTo < T > (DataTable datatable) where T: new()
  25. {
  26. List < T > Temp = new List < T > ();
  27. try
  28. {
  29. List < string > columnsNames = new List < string > ();
  30. foreach(DataColumn DataColumn in datatable.Columns)
  31. columnsNames.Add(DataColumn.ColumnName);
  32. Temp = datatable.AsEnumerable().ToList().ConvertAll < T > (row => getObject < T > (row, columnsNames));
  33. return Temp;
  34. }
  35. catch
  36. {
  37. return Temp;
  38. }
  39. }
  40. public T getObject < T > (DataRow row, List < string > columnsName) where T: new()
  41. {
  42. T obj = new T();
  43. try
  44. {
  45. string columnname = "";
  46. string value = "";
  47. PropertyInfo[] Properties;
  48. Properties = typeof (T).GetProperties();
  49. foreach(PropertyInfo objProperty in Properties)
  50. {
  51. columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower());
  52. if (!string.IsNullOrEmpty(columnname))
  53. {
  54. value = row[columnname].ToString();
  55. if (!string.IsNullOrEmpty(value))
  56. {
  57. if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null)
  58. {
  59. value = row[columnname].ToString().Replace("$", "").Replace(",", "");
  60. objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null);
  61. }
  62. else
  63. {
  64. value = row[columnname].ToString();
  65. objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);
  66. }
  67. }
  68. }
  69. }
  70. return obj;
  71. }
  72. catch (Exception ex)
  73. {
  74. return obj;
  75. }
  76. }#
  77. endregion# region Constructor
  78. /// <summary>
  79. /// This constructor is used to get the connectionstring from the config file
  80. /// </summary>
  81. public PlayerDAL()
  82. {
  83. ConnectionString = ConfigurationManager.ConnectionStrings["PlayerConnectionString"].ToString();
  84. }#
  85. endregion# region Player Details
  86. /// <summary>
  87. /// This method is used to get all players
  88. /// </summary>
  89. /// <returns></returns>
  90. public List < Players > GetPlayerDetails()
  91. {
  92. List < Players > objPlayers = null;
  93. objDB = new SqlDatabase(ConnectionString);
  94. using(DbCommand objcmd = objDB.GetStoredProcCommand("SC_GetPlayers"))
  95. {
  96. try
  97. {
  98. using(DataTable dataTable = objDB.ExecuteDataSet(objcmd).Tables[0])
  99. {
  100. objPlayers = ConvertTo < Players > (dataTable);
  101. }
  102. }
  103. catch (Exception ex)
  104. {
  105. throw ex;
  106. return null;
  107. }
  108. }
  109. return objPlayers;
  110. }
  111. /// <summary>
  112. /// This method is used to get player records on the basis of player id
  113. /// </summary>
  114. /// <param name="playerId"></param>
  115. /// <returns></returns>
  116. public List < PlayerRecord > GetPlayerRecordByPlayerId(Int16 ? playerId)
  117. {
  118. List < PlayerRecord > objPlayerRecords = null;
  119. objDB = new SqlDatabase(ConnectionString);
  120. using(DbCommand objcmd = objDB.GetStoredProcCommand("SC_GetPlayerRecordsBtPlayerId"))
  121. {
  122. objDB.AddInParameter(objcmd, "@PlayerId", DbType.Int16, playerId);
  123. try
  124. {
  125. using(DataTable dataTable = objDB.ExecuteDataSet(objcmd).Tables[0])
  126. {
  127. objPlayerRecords = ConvertTo < PlayerRecord > (dataTable);
  128. }
  129. }
  130. catch (Exception ex)
  131. {
  132. throw ex;
  133. return null;
  134. }
  135. }
  136. return objPlayerRecords;
  137. }#
  138. endregion
  139. }
  140. }
Step 8: Add the following connection string in the Web.Config file in the MVC Application,
  1. <add name="PlayerConnectionString" connectionString="Data Source=Your Server Name; Initial Catalog=ChartSample; User Id=uid; Password=Your password" providerName="System.Data.SqlClient" />
Step 9: Build the solution

Represent Data

In this section we will get the data from the library reference and represent data in the newly added view in the chart form. So, let’s start with the following steps:

Step 1: Right click on the reference in the MVC Application project and click on “Add Reference”,

Adding Reference
Figure 10: Adding Reference

Step 2: Add the library reference in the MVC application,

Adding Library Reference
Figure 11: Adding Library Reference

Step 3: Now right click on the Controllers folder and click on “Add Controller”,

Adding Controller in MVC App
Figure 12: Adding Controller in MVC App

Step 3: Select MVC 5 Empty Controller from the wizard,

Adding MVC5 Empty Controller
Figure 13: Adding MVC 5 Empty Controller

And specify the Controller name,

Specifying Controller Name
Figure 14: Specifying Controller Name

Step 4: Add the following code in the controller class,
  1. public class PlayerController: Controller
  2. {
  3. // GET: Player
  4. public ActionResult Index()
  5. {
  6. return View();
  7. }
  8. public ActionResult PlayerChart()
  9. {
  10. Players objPlayers = new Players();
  11. PlayerDAL objPlayerDAL = new PlayerDAL();
  12. try
  13. {
  14. objPlayers.PlayerList = objPlayerDAL.GetPlayerDetails();
  15. return View("~/Views/Player/PlayerChart.cshtml", objPlayers);
  16. }
  17. catch (Exception ex)
  18. {
  19. throw;
  20. }
  21. }
  22. public JsonResult PlayerDashboardList(Int16 ? playerId)
  23. {
  24. Players objPlayers = new Players();
  25. PlayerDAL objPlayerDAL = new PlayerDAL();
  26. if (object.Equals(playerId, null))
  27. {
  28. playerId = 1;
  29. }
  30. try
  31. {
  32. var response = objPlayerDAL.GetPlayerRecordByPlayerId(playerId);
  33. if (!object.Equals(response, null))
  34. {
  35. objPlayers.PlayerRecordList = response.ToList();
  36. }
  37. }
  38. catch (Exception ex)
  39. {
  40. throw;
  41. }
  42. return Json(objPlayers.PlayerRecordList, JsonRequestBehavior.AllowGet);
  43. }
  44. }
Step 5: Right click on the Views/Player folder and click on the Add -> View and specify the view name as “Player Chart”,

Adding View
Figure 15: Adding View

Step 6: Add the following code in the view page,
  1. @model ChartLibrary.Players
  2. @
  3. {
  4. ViewBag.Title = "PlayerChart";
  5. }
    < script src = "~/Scripts/jquery-1.10.2.js" > < /script>
    < script type = "text/javascript" src = "https://www.google.com/jsapi" > < /script>
  6. < style > label
  7. {
  8. font - size: 18 px;
  9. font - weight: lighter;
  10. }
  11. select
  12. {
  13. width: 250 px;height: 40 px;padding: 0 14 px;font - size: 16 px;
  14. }
    < /style> <
    h2 style = "margin:25px 0; color:#5a5a5a;" > Player Chart < /h2> < div class = "clear" > < /div>
    < div     class = "row" > < div class = "col-md-8" >
    < section id =     "loginForm"     style = "margin-bottom:25px;" >
    < span style = "margin-right:15px; font-size:15px; font-weight:lighter;" > @Html.LabelFor(m => m.PlayerName, "Player Name") < /span>
  15. @Html.DropDownListFor(m => m.PlayerName, new SelectList(Model.PlayerList, "PlayerId", "PlayerName"), new
  16. {
  17. @onchange = "drawChart()", @id = "playerNameList"
  18. })
    < /section>
    < /div>
    < /div>
    < div class = "clear" > < /div>
    < div > < div id =     "Player_Chart"     style = "width: 100%; height: 500px" >
    < /div>
    < /div>
    < div id = "divProcessing" class = "processingButton" style = "display: none; text-align: center" >
    < img src = "~/Images/ajaxloader_small.gif" width = "16" height = "11" / >
    < /div>
    < div id = "divLoading" class = "loadingCampus" >
    < div class = "LoadingImageForActivity" >
    < img width = "31" height = "31" alt = "" src = "~/Images/ajax-loader-round-dashboard.gif" / >
    < /div>
    < /div>
    < script type = "text/javascript" >
    google.load("visualization", "1",
  19. {
  20. packages: ["corechart"]
  21. });
  22. google.setOnLoadCallback(drawChart);
  24. function drawChart()
  25. {
  26. var playerId = $('#playerNameList :selected').val();
  27. $.ajax(
  28. {
  29. url: '@Url.Action("PlayerDashboardList","Player")',
  30. dataType: "json",
  31. data:
  32. {
  33. playerId: playerId
  34. },
  35. type: "GET",
  36. error: function (xhr, status, error)
  37. {
  38. var err = eval("(" + xhr.responseText + ")");
  39. toastr.error(err.message);
  40. },
  41. beforeSend: function ()
  42. {
  43. $("#divLoading").show();
  44. },
  45. success: function (data)
  46. {
  47. PlayerDashboardChart(data);
  48. return false;
  49. },
  50. error: function (xhr, status, error)
  51. {
  52. var err = eval("(" + xhr.responseText + ")");
  53. toastr.error(err.message);
  54. },
  55. complete: function ()
  56. {
  57. $("#divLoading").hide();
  58. }
  59. });
  60. return false;
  61. }
  62. //This function is used to bind the user data to chart
  63. function PlayerDashboardChart(data)
  64. {
  65. $("#Player_Chart").show();
  66. var dataArray = [
  67. ['Years', 'Total Runs', 'Total Wickets', 'ODI Matches', 'Test Matches']
  68. ];
  69. $.each(data, function (i, item)
  70. {
  71. dataArray.push([item.Year, item.TotalRun, item.TotalWickets, item.ODIMatches, item.TestMatches]);
  72. });
  73. var data = google.visualization.arrayToDataTable(dataArray);
  74. var options = {
  75. pointSize: 5,
  76. legend:
  77. {
  78. position: 'top',
  79. textStyle:
  80. {
  81. color: '#f5f5f5'
  82. }
  83. },
  84. colors: ['#34A853', 'ff6600', '#FBBC05'],
  85. backgroundColor: '#454545',
  86. hAxis:
  87. {
  88. title: 'Years',
  89. titleTextStyle:
  90. {
  91. italic: false,
  92. color: '#00BBF1',
  93. fontSize: '20'
  94. },
  95. textStyle:
  96. {
  97. color: '#f5f5f5'
  98. }
  99. },
  100. vAxis:
  101. {
  102. baselineColor: '#f5f5f5',
  103. title: 'Statistics',
  104. titleTextStyle:
  105. {
  106. color: '#00BBF1',
  107. italic: false,
  108. fontSize: '20'
  109. },
  110. textStyle:
  111. {
  112. color: '#f5f5f5'
  113. },
  114. viewWindow:
  115. {
  116. min: 0,
  117. format: 'long'
  118. }
  119. },
  120. };
  121. var chart = new google.visualization.LineChart(document.getElementById('Player_Chart'));
  122. chart.draw(data, options);
  123. return false;
  124. }; < /script>
Step 7: Edit the Views/Shared/_Layout page from the following code,
  1. <!DOCTYPE html>
  2. <html>
  4. <head>
  5. <meta charset="utf-8" />
  6. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  7. <title>@ViewBag.Title - MVC Chart App</title> @Styles.Render("~/Content/css") @Scripts.Render("~/bundles/modernizr") </head>
  9. <body>
  10. <div class="navbar navbar-inverse navbar-fixed-top">
  11. <div class="container">
  12. <div class="navbar-header">
  13. <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse"> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> @Html.ActionLink("Chart Sample", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" }) </div>
  14. <div class="navbar-collapse collapse">
  15. <ul class="nav navbar-nav">
  16. <li>@Html.ActionLink("Home", "Index", "Home")</li>
  17. <li>@Html.ActionLink("About", "About", "Home")</li>
  18. <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
  19. <li>@Html.ActionLink("Players", "PlayerChart", "Player")</li>
  20. </ul> @Html.Partial("_LoginPartial") </div>
  21. </div>
  22. </div>
  23. <div class="container body-content"> @RenderBody()
  24. <hr />
  25. <footer>
  26. <p>© @DateTime.Now.Year - MVC Chart Sample</p>
  27. </footer>
  28. </div> @Scripts.Render("~/bundles/jquery") @Scripts.Render("~/bundles/bootstrap") @RenderSection("scripts", required: false) </body>
  30. </html>
Step 8: Now run the application and click on the Players list from the start page,

Chart Page
Figure 16: Chart Page

Now you can change the player name from the list box and find out the difference. When you hover the point it will show the following statistics:

Notations in Chart Page
Figure 17: Notations in Chart Page

That’s it. Thanks!

Summary

So far this article described the chart representation of the data from the Google Chart API tool in the MVC 5 application. You can change the chart type and view the data into difference representation of chart format. Thanks for reading the article.