Introducing Google Chart In ASP.NET MVC 5

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”,


                                                      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
        /// <summary>
        /// get and set the PlayerId
        /// </summary>
        public int PlayerId
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the PlayerName
        /// </summary>
        public string PlayerName
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the PlayerList
        /// </summary>
        public List < Players > PlayerList
            {
                get;
                set;
            }
            /// <summary>
            /// get and set the PlayerRecordList
            /// </summary>
        public List < PlayerRecord > PlayerRecordList
        {
            get;
            set;
        }#
        endregion
    }
    public class PlayerRecord
    {#
        region Properties
        /// <summary>
        /// get and set the PlayerId
        /// </summary>
        public int PlayerId
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the Year
        /// </summary>
        public int Year
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the TotalRun
        /// </summary>
        public int TotalRun
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the TotalWickets
        /// </summary>
        public int TotalWickets
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the ODIMatches
        /// </summary>
        public int ODIMatches
        {
            get;
            set;
        }
        /// <summary>
        /// get and set the TestMatches
        /// </summary>
        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
        /// <summary>
        /// Specify the Database variable
        /// </summary>
        Database objDB;
        /// <summary>
        /// Specify the static variable
        /// </summary>
        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
        /// <summary>
        /// This constructor is used to get the connectionstring from the config file
        /// </summary>
        public PlayerDAL()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["PlayerConnectionString"].ToString();
        }#
        endregion# region Player Details
        /// <summary>
        /// This method is used to get all players
        /// </summary>
        /// <returns></returns>
        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;
            }
            /// <summary>
            /// This method is used to get player records on the basis of player id
            /// </summary>
            /// <param name="playerId"></param>
            /// <returns></returns>
        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 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”,


                        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
{
    // GET: Player
    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;
}
//This function is used to bind the user data to chart
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!

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.