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:
- Geo Chart
- Column Chart
- Bar Chart
- Line Chart
- Pie Chart
- 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:
- Getting Started
- Perform Database Operation
- Creating Library
- 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”,
Figure 1: Start Page of VS 2013
Step 2: Select Web tab from the left pane and select the “ASP.NET Web Application”,
Figure 2: Creating Web Application in VS 2013
Step 3: Select “MVC project template” from the ASP.Net Wizard to create MVC app,
Figure 3: MVC Project Template
This will create your MVC Application and after some changes in default layout page run the application.
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,
- CREATE DATABASE ChartSample
-
- USE [ChartSample]
- GO
-
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- SET ANSI_PADDING ON
- GO
- CREATE TABLE [dbo].[CS_Player](
- [PlayerId] [int] IDENTITY(1,1) NOT NULL,
- [PlayerName] [varchar](50) NULL,
- CONSTRAINT [PK_CS_Player] PRIMARY KEY CLUSTERED
- (
- [PlayerId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
-
- GO
- SET ANSI_PADDING OFF
- GO
- /****** Object: Table [dbo].[CS_PlayerRecord] Script Date: 11/19/2015 4:00:01 PM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[CS_PlayerRecord](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [PlayerId] [int] NULL,
- [Year] [int] NULL,
- [TotalRun] [int] NULL,
- [TotalWickets] [int] NULL,
- [ODIMatches] [int] NULL,
- [TestMatches] [int] NULL,
- CONSTRAINT [PK_CS_PlayerRecord] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
-
- GO
- ALTER TABLE [dbo].[CS_PlayerRecord] WITH CHECK ADD CONSTRAINT [FK_CS_PlayerRecord_CS_Player] FOREIGN KEY([PlayerId])
- REFERENCES [dbo].[CS_Player] ([PlayerId])
- GO
- ALTER TABLE [dbo].[CS_PlayerRecord] CHECK CONSTRAINT [FK_CS_PlayerRecord_CS_Player]
- GO
Step 2: Insert records in the tables.
Step 3: Now we will create procedures with the following code,
- USE [ChartSample]
- GO
- /****** Object: StoredProcedure [dbo].[SC_GetPlayers] Script Date: 11/19/2015 4:21:29 PM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROC [dbo].[SC_GetPlayers]
- AS
- BEGIN
- SELECT *
- FROM CS_Player
- END
-
- USE [ChartSample]
- GO
- /****** Object: StoredProcedure [dbo].[SC_GetPlayerRecordsBtPlayerId] Script Date: 11/19/2015 4:21:26 PM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROC [dbo].[SC_GetPlayerRecordsBtPlayerId] @PlayerId INT
- AS
- BEGIN
- SELECT PlayerId ,
- Year ,
- TotalRun ,
- TotalWickets ,
- ODIMatches ,
- TestMatches
- FROM CS_PlayerRecord
- WHERE PlayerId = @PlayerId
- 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,
Figure 5: Adding New Project
Step 2: Select the “
Class Library” and specify the name as “
Chart Library”,
Figure 6: Creating Class Library
Step 3: Now add a class in the library project,
Figure 7: Adding New Class
Step 4: Replace the class code with the following code,
- namespace ChartLibrary
- {
- public class Players
- {#
- region Properties
-
-
-
- public int PlayerId
- {
- get;
- set;
- }
-
-
-
- public string PlayerName
- {
- get;
- set;
- }
-
-
-
- public List < Players > PlayerList
- {
- get;
- set;
- }
-
-
-
- public List < PlayerRecord > PlayerRecordList
- {
- get;
- set;
- }#
- endregion
- }
- public class PlayerRecord
- {#
- region Properties
-
-
-
- public int PlayerId
- {
- get;
- set;
- }
-
-
-
- public int Year
- {
- get;
- set;
- }
-
-
-
- public int TotalRun
- {
- get;
- set;
- }
-
-
-
- public int TotalWickets
- {
- get;
- set;
- }
-
-
-
- public int ODIMatches
- {
- get;
- set;
- }
-
-
-
- public int TestMatches
- {
- get;
- set;
- }#
- endregion
- }
- }
Step 5: Right click on References in the class library project and click on “
Manage NuGet Packages”,
Figure 8: Adding NuGet Package
Step 6: Search for Enterprise Library and install it in the library project,
Figure 9: Adding Enterprise Library Package
Step 7: Add another class named “
PlayerDAL” and replace the code with the following code,
- using Microsoft.Practices.EnterpriseLibrary.Data;
- using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
- using System;
- using System.Collections.Generic;
- using System.Configuration;
- using System.Data;
- using System.Data.Common;
- using System.Linq;
- using System.Reflection;
- namespace ChartLibrary
- {
- public class PlayerDAL
- {#
- region Variable
-
-
-
- Database objDB;
-
-
-
- string ConnectionString;#
- endregion# region Database Method
- public List < T > ConvertTo < T > (DataTable datatable) where T: new()
- {
- List < T > Temp = new List < T > ();
- try
- {
- List < string > columnsNames = new List < string > ();
- foreach(DataColumn DataColumn in datatable.Columns)
- columnsNames.Add(DataColumn.ColumnName);
- Temp = datatable.AsEnumerable().ToList().ConvertAll < T > (row => getObject < T > (row, columnsNames));
- return Temp;
- }
- catch
- {
- return Temp;
- }
- }
- public T getObject < T > (DataRow row, List < string > columnsName) where T: new()
- {
- T obj = new T();
- try
- {
- string columnname = "";
- string value = "";
- PropertyInfo[] Properties;
- Properties = typeof (T).GetProperties();
- foreach(PropertyInfo objProperty in Properties)
- {
- columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower());
- if (!string.IsNullOrEmpty(columnname))
- {
- value = row[columnname].ToString();
- if (!string.IsNullOrEmpty(value))
- {
- if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null)
- {
- value = row[columnname].ToString().Replace("$", "").Replace(",", "");
- objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null);
- }
- else
- {
- value = row[columnname].ToString();
- objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);
- }
- }
- }
- }
- return obj;
- }
- catch (Exception ex)
- {
- return obj;
- }
- }#
- endregion# region Constructor
-
-
-
- public PlayerDAL()
- {
- ConnectionString = ConfigurationManager.ConnectionStrings["PlayerConnectionString"].ToString();
- }#
- endregion# region Player Details
-
-
-
-
- public List < Players > GetPlayerDetails()
- {
- List < Players > objPlayers = null;
- objDB = new SqlDatabase(ConnectionString);
- using(DbCommand objcmd = objDB.GetStoredProcCommand("SC_GetPlayers"))
- {
- try
- {
- using(DataTable dataTable = objDB.ExecuteDataSet(objcmd).Tables[0])
- {
- objPlayers = ConvertTo < Players > (dataTable);
- }
- }
- catch (Exception ex)
- {
- throw ex;
- return null;
- }
- }
- return objPlayers;
- }
-
-
-
-
-
- public List < PlayerRecord > GetPlayerRecordByPlayerId(Int16 ? playerId)
- {
- List < PlayerRecord > objPlayerRecords = null;
- objDB = new SqlDatabase(ConnectionString);
- using(DbCommand objcmd = objDB.GetStoredProcCommand("SC_GetPlayerRecordsBtPlayerId"))
- {
- objDB.AddInParameter(objcmd, "@PlayerId", DbType.Int16, playerId);
- try
- {
- using(DataTable dataTable = objDB.ExecuteDataSet(objcmd).Tables[0])
- {
- objPlayerRecords = ConvertTo < PlayerRecord > (dataTable);
- }
- }
- catch (Exception ex)
- {
- throw ex;
- return null;
- }
- }
- return objPlayerRecords;
- }#
- endregion
- }
- }
Step 8: Add the following connection string in the Web.Config file in the MVC Application,
- <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 DataIn 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”,
Figure 10: Adding Reference
Step 2: Add the library reference in the MVC application,
Figure 11: Adding Library Reference
Step 3: Now right click on the Controllers folder and click on “
Add Controller”,
Figure 12: Adding Controller in MVC App
Step 3: Select MVC 5 Empty Controller from the wizard,
Figure 13: Adding MVC 5 Empty Controller
And specify the
Controller name, Figure 14: Specifying Controller Name
Step 4: Add the following code in the controller class,
- public class PlayerController: Controller
- {
-
- public ActionResult Index()
- {
- return View();
- }
- public ActionResult PlayerChart()
- {
- Players objPlayers = new Players();
- PlayerDAL objPlayerDAL = new PlayerDAL();
- try
- {
- objPlayers.PlayerList = objPlayerDAL.GetPlayerDetails();
- return View("~/Views/Player/PlayerChart.cshtml", objPlayers);
- }
- catch (Exception ex)
- {
- throw;
- }
- }
- public JsonResult PlayerDashboardList(Int16 ? playerId)
- {
- Players objPlayers = new Players();
- PlayerDAL objPlayerDAL = new PlayerDAL();
- if (object.Equals(playerId, null))
- {
- playerId = 1;
- }
- try
- {
- var response = objPlayerDAL.GetPlayerRecordByPlayerId(playerId);
- if (!object.Equals(response, null))
- {
- objPlayers.PlayerRecordList = response.ToList();
- }
- }
- catch (Exception ex)
- {
- throw;
- }
- return Json(objPlayers.PlayerRecordList, JsonRequestBehavior.AllowGet);
- }
- }
Step 5: Right click on the Views/Player folder and click on the Add -> View and specify the view name as “
Player Chart”,
Figure 15: Adding View
Step 6: Add the following code in the view page,
- @model ChartLibrary.Players
- @
- {
- ViewBag.Title = "PlayerChart";
- }
< script src = "~/Scripts/jquery-1.10.2.js" > < /script>
< script type = "text/javascript" src = "https://www.google.com/jsapi" > < /script> - < style > label
- {
- font - size: 18 px;
- font - weight: lighter;
- }
- select
- {
- width: 250 px;height: 40 px;padding: 0 14 px;font - size: 16 px;
- }
< /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> - @Html.DropDownListFor(m => m.PlayerName, new SelectList(Model.PlayerList, "PlayerId", "PlayerName"), new
- {
- @onchange = "drawChart()", @id = "playerNameList"
- })
< /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", - {
- packages: ["corechart"]
- });
- google.setOnLoadCallback(drawChart);
-
- function drawChart()
- {
- var playerId = $('#playerNameList :selected').val();
- $.ajax(
- {
- url: '@Url.Action("PlayerDashboardList","Player")',
- dataType: "json",
- data:
- {
- playerId: playerId
- },
- type: "GET",
- error: function (xhr, status, error)
- {
- var err = eval("(" + xhr.responseText + ")");
- toastr.error(err.message);
- },
- beforeSend: function ()
- {
- $("#divLoading").show();
- },
- success: function (data)
- {
- PlayerDashboardChart(data);
- return false;
- },
- error: function (xhr, status, error)
- {
- var err = eval("(" + xhr.responseText + ")");
- toastr.error(err.message);
- },
- complete: function ()
- {
- $("#divLoading").hide();
- }
- });
- return false;
- }
-
- function PlayerDashboardChart(data)
- {
- $("#Player_Chart").show();
- var dataArray = [
- ['Years', 'Total Runs', 'Total Wickets', 'ODI Matches', 'Test Matches']
- ];
- $.each(data, function (i, item)
- {
- dataArray.push([item.Year, item.TotalRun, item.TotalWickets, item.ODIMatches, item.TestMatches]);
- });
- var data = google.visualization.arrayToDataTable(dataArray);
- var options = {
- pointSize: 5,
- legend:
- {
- position: 'top',
- textStyle:
- {
- color: '#f5f5f5'
- }
- },
- colors: ['#34A853', 'ff6600', '#FBBC05'],
- backgroundColor: '#454545',
- hAxis:
- {
- title: 'Years',
- titleTextStyle:
- {
- italic: false,
- color: '#00BBF1',
- fontSize: '20'
- },
- textStyle:
- {
- color: '#f5f5f5'
- }
- },
- vAxis:
- {
- baselineColor: '#f5f5f5',
- title: 'Statistics',
- titleTextStyle:
- {
- color: '#00BBF1',
- italic: false,
- fontSize: '20'
- },
- textStyle:
- {
- color: '#f5f5f5'
- },
- viewWindow:
- {
- min: 0,
- format: 'long'
- }
- },
- };
- var chart = new google.visualization.LineChart(document.getElementById('Player_Chart'));
- chart.draw(data, options);
- return false;
- }; < /script>
Step 7: Edit the Views/Shared/_Layout page from the following code,
- <!DOCTYPE html>
- <html>
-
- <head>
- <meta charset="utf-8" />
- <meta name="viewport" content="width=device-width, initial-scale=1.0">
- <title>@ViewBag.Title - MVC Chart App</title> @Styles.Render("~/Content/css") @Scripts.Render("~/bundles/modernizr") </head>
-
- <body>
- <div class="navbar navbar-inverse navbar-fixed-top">
- <div class="container">
- <div class="navbar-header">
- <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>
- <div class="navbar-collapse collapse">
- <ul class="nav navbar-nav">
- <li>@Html.ActionLink("Home", "Index", "Home")</li>
- <li>@Html.ActionLink("About", "About", "Home")</li>
- <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
- <li>@Html.ActionLink("Players", "PlayerChart", "Player")</li>
- </ul> @Html.Partial("_LoginPartial") </div>
- </div>
- </div>
- <div class="container body-content"> @RenderBody()
- <hr />
- <footer>
- <p>© @DateTime.Now.Year - MVC Chart Sample</p>
- </footer>
- </div> @Scripts.Render("~/bundles/jquery") @Scripts.Render("~/bundles/bootstrap") @RenderSection("scripts", required: false) </body>
-
- </html>
Step 8: Now run the application and click on the Players list from the start 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:
Figure 17: Notations in Chart Page
That’s it. Thanks!
SummarySo 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.