How To Implement Google Combo Chart Dynamically Using Entity Framework In ASP.NET

Introduction

In this article, I will demonstrate how to implement a Google combo chart dynamically using entity framework in ASP.NET. I will use jQuery Ajax to retrieve data from database and display in Google Combo Chart. The Chart will display respective data on its point line with animation. 

Step 1

Open SQL server 2014 and create a database table to insert data and retrieve data.

  1. CREATE TABLE [dbo].[CompanyHiringReport](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Year] [intNULL,  
  4.     [Account] [intNULL,  
  5.     [HR] [intNULL,  
  6.     [IT] [intNULL,  
  7.     [Sales] [intNULL,  
  8.     [Marketing] [intNULL,  
  9.  CONSTRAINT [PK_CompanyHiringReport] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [ID] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  13. ON [PRIMARY]  
  14.   
  15. GO  
ASP.NET

Step 2

Open Visual Studio 2015 click on New Project and create an empty web application project.

Screenshot for creating new project 1

ASP.NET

After clicking on New Project one window will appear; select Web from left panel, choose ASP.NET Web Application and give a meaningful name of your project then click on OK as shown in the below screenshot.

Screenshot for creating new project 2

ASP.NET

After clicking on OK one more window will appear, choose empty, check the Web Forms checkbox, and click on OK, as shown in the below screenshot.

Screenshot for creating new project 3

ASP.NET

Step 3

Click on Tools, select NuGet Package Manager, then choose Manage NuGet Packages for Solution and click on it.

Screenshot for NuGet Package

ASP.NET

After that, a window will appear, choose browse type as bootstrap, and install the package in the project as shown.

ASP.NET

Similarly, type JQuery and install the latest version of JQuery package in the project from NuGet then close NuGet Solution.

ASP.NET

Keep the useful file in Content and scripts folder as shown below.

ASP.NET

Step 4

Add entity framework, right click on Models folder, select Add, then select New Item then click on it.

Screenshot for adding entity framework 1

ASP.NET

After clicking on the New item you will get a window, from there select Data from the left panel and choose ADO.NET Entity Data Model, name it DBModels (this name is not mandatory you can give any name) then click on Add.

Screenshot for adding entity framework 2

ASP.NET

After you click on Add, a window wizard will open, choose EF Designer from the database and click Next.

Screenshot for adding entity framework 3

ASP.NET

After click on Next a window will  appear. Choose New Connection

Screenshot for adding entity framework 4

ASP.NET

Another window will appear asking you to add your server name; if it is local then enter a dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 5

ASP.NET

The connection will get added. If you wish to save connect as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding entity framework 6

ASP.NET

After clicking on NEXT another window will appear to choose database table name as shown in below screenshot then click on Finish. Entity framework will be added and respective class gets generated under Models folder.

Screenshot for adding entity framework 7

ASP.NET

Screenshot for adding entity framework 8

ASP.NET

Following class will be added

  1. namespace GoogleComboChart_Demo.Models  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public partial class CompanyHiringReport  
  7.     {  
  8.         public int ID { get; set; }  
  9.         public Nullable<int> Year { get; set; }  
  10.         public Nullable<int> Account { get; set; }  
  11.         public Nullable<int> HR { get; set; }  
  12.         public Nullable<int> IT { get; set; }  
  13.         public Nullable<int> Sales { get; set; }  
  14.         public Nullable<int> Marketing { get; set; }  
  15.     }  
  16. }  

Step 5

Right click on project, select Add, choose Web Form and click on it. As shown in the below image.

ASP.NET

After clicking on the web form one window will appear, give it a name and click on OK

ASP.NET

Step 6

Right click on scripts folder select Add choose JavaScript File then click on it.

ASP.NET

After click on JavaScript File window will appear,  give it the name ComboChart then click on OK.

ASP.NET

Add the following jQuery and JavaScript code to retrieve data from database and display in chart.

  1. var chartData; // global variable for hold chart data  
  2. google.load("visualization""1", { packages: ["corechart"] });  
  3. // Here We will fill chartData  
  4. $(document).ready(function () {  
  5.     $.ajax({  
  6.         url: "CobmoChart.aspx/GetChartData",  
  7.         data: "",  
  8.         dataType: "json",  
  9.         type: "POST",  
  10.         contentType: "application/json; chartset=utf-8",  
  11.         success: function (data) {  
  12.             chartData = data.d;  
  13.         },  
  14.         error: function () {  
  15.             alert("Error loading data! Please try again.");  
  16.         }  
  17.     }).done(function () {  
  18.         // after complete loading data  
  19.         google.setOnLoadCallback(drawChart);  
  20.         drawChart();  
  21.     });  
  22. });  
  23. function drawChart() {  
  24.     var data = google.visualization.arrayToDataTable(chartData);  
  25.     var options = {  
  26.         title: "Company Performance Product Category Wise",  
  27.         pointSize: 5,  
  28.         seriesType: "bars",  
  29.         series: { 3: { type: "line" } }  
  30.     };  
  31.     var comboChart = new google.visualization.ComboChart(document.getElementById('ComboChart_div'));  
  32.     comboChart.draw(data, options);  
  33. }  

Step 7

Add following scripts and styles in head section of web form.

  1. <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">  
  2. <script type="text/javascript" src="https://www.google.com/jsapi"></script>  
  3. <link href="Content/bootstrap.min.css" rel="stylesheet" />  
  4. <script src="scripts/jquery-3.3.1.min.js"></script>  
  5.  <script src="scripts/bootstrap.min.js"></script>  
  6. <script src="scripts/ComboChart.js"></script>  

Step 8

Design web form using textbox control, button control and gridview control and bootstrap 4 class to look good.

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.         <div class="container py-4">  
  4.             <h5 class="text-center text-uppercase">How to Implement Google Combo Chart Dynamically Using Entity Framework in Asp.Net</h5>  
  5.             <div class="card">  
  6.                 <div class="card-header bg-primary">  
  7.                     <h6 class=" text-upparcase text-white">Company Hiring Report</h6>  
  8.                 </div>  
  9.                 <div class="card-body">  
  10.                     <button style="margin-bottom: 10px;" class="btn btn-primary rounded-0" type="button" data-target="#CompantReport" data-toggle="modal"><i class="fa fa-plus-circle"></i>Add New Report</button>  
  11.                     <div class="modal fade" id="CompantReport">  
  12.                         <div class="modal-dialog modal-lg">  
  13.                             <div class="modal-content">  
  14.                                 <div class="modal-header">  
  15.                                     <h4 class="modal-title">Company Hiring Report</h4>  
  16.                                     <button type="button" class="close" data-dismiss="modal">×</button>  
  17.                                 </div>  
  18.                                 <div class="modal-body">  
  19.                                     <div class="row">  
  20.                                         <div class="col-md-4">  
  21.                                             <div class="form-group">  
  22.                                                 <label>Month of hiring:</label>  
  23.                                                 <asp:TextBox ID="txtYear" CssClass="form-control" runat="server"></asp:TextBox>  
  24.                                                 <asp:RequiredFieldValidator ID="rfvYear" ControlToValidate="txtYear" CssClass="text-danger" runat="server" ErrorMessage="Please enter year"></asp:RequiredFieldValidator>  
  25.                                             </div>  
  26.                                         </div>  
  27.                                         <div class="col-md-4">  
  28.                                             <div class="form-group">  
  29.                                                 <label>Account Department:</label>  
  30.                                                 <asp:TextBox ID="txtAccountDepartment" CssClass="form-control" runat="server"></asp:TextBox>  
  31.                                                 <asp:RequiredFieldValidator ID="rfvAccount" ControlToValidate="txtYear" CssClass="text-danger" runat="server" ErrorMessage="Please enter number employee hired"></asp:RequiredFieldValidator>  
  32.                                             </div>  
  33.                                         </div>  
  34.                                         <div class="col-md-4">  
  35.                                             <div class="form-group">  
  36.                                                 <label>HR Department:</label>  
  37.                                                 <asp:TextBox ID="txtHRDepartment" CssClass="form-control" runat="server"></asp:TextBox>  
  38.                                                 <asp:RequiredFieldValidator ID="rfvHR" ControlToValidate="txtHRDepartment" CssClass="text-danger" runat="server" ErrorMessage="Please enter number employee hired"></asp:RequiredFieldValidator>  
  39.                                             </div>  
  40.                                         </div>  
  41.                                     </div>  
  42.                                     <div class="row">  
  43.                                         <div class="col-md-4">  
  44.                                             <div class="form-group">  
  45.                                                 <label>IT Department:</</label>  
  46.                                                 <asp:TextBox ID="txtITDepartment" CssClass="form-control" runat="server"></asp:TextBox>  
  47.                                                 <asp:RequiredFieldValidator ID="rfvIT" ControlToValidate="txtITDepartment" CssClass="text-danger" runat="server" ErrorMessage="Please enter number employee hired"></asp:RequiredFieldValidator>  
  48.                                             </div>  
  49.                                         </div>  
  50.                                         <div class="col-md-4">  
  51.                                             <div class="form-group">  
  52.                                                 <label>Sales Department:</label>  
  53.                                                 <asp:TextBox ID="txtSalesDepartment" CssClass="form-control" runat="server"></asp:TextBox>  
  54.                                                 <asp:RequiredFieldValidator ID="rfvSales" ControlToValidate="txtSalesDepartment" CssClass="text-danger" runat="server" ErrorMessage="Please enter number employee hired"></asp:RequiredFieldValidator>  
  55.                                             </div>  
  56.                                         </div>  
  57.                                         <div class="col-md-4">  
  58.                                             <div class="form-group">  
  59.                                                 <label>Marketing Department:</label>  
  60.                                                 <asp:TextBox ID="txtMarketingDepartment" CssClass="form-control" runat="server"></asp:TextBox>  
  61.                                                 <asp:RequiredFieldValidator ID="rfvMarketing" ControlToValidate="txtMarketingDepartment" CssClass="text-danger" runat="server" ErrorMessage="Please enter number employee hired"></asp:RequiredFieldValidator>  
  62.                                             </div>  
  63.                                         </div>  
  64.                                     </div>  
  65.                                 </div>  
  66.                                 <div class="modal-footer">  
  67.                                     <asp:Button ID="btnSubmit" CssClass="btn btn-primary rounded-0" runat="server" Text="Submit" OnClick="btnSubmit_Click" />  
  68.                                     <button type="button" class="btn btn-danger rounded-0" data-dismiss="modal">Close</button>  
  69.                                 </div>  
  70.                             </div>  
  71.                         </div>  
  72.                     </div>  
  73.                     <asp:GridView ID="CompanyHiringReportGridView" AutoGenerateColumns="false" runat="server" CssClass="table table-bordered table-striped">  
  74.                         <Columns>  
  75.                             <asp:BoundField HeaderText="ID" DataField="ID" />  
  76.                             <asp:BoundField HeaderText="Year" DataField="Year" />  
  77.                             <asp:BoundField HeaderText="Account Department" DataField="Account" />  
  78.                             <asp:BoundField HeaderText="HR Department" DataField="HR" />  
  79.                             <asp:BoundField HeaderText="IT Department" DataField="IT" />  
  80.                             <asp:BoundField HeaderText="Sales Department" DataField="Sales" />  
  81.                             <asp:BoundField HeaderText="Marketing Department" DataField="Marketing" />  
  82.                         </Columns>  
  83.                     </asp:GridView>  
  84.                 </div>  
  85.             </div>  
  86.             <div id="ComboChart_div" style="width: 100%; height: 400px">  
  87.             </div>  
  88.         </div>  
  89.     </form>  
  90. </body>  

Step 9

Double click on submit button and write the following C# code.

Add the following namespace.

  1. using GoogleComboChart_Demo.Models;  
  2. using System.Web.Script.Services;  
  3. using System.Web.Services;  

Complete C# code of web form

  1. using GoogleComboChart_Demo.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Web.Script.Services;  
  6. using System.Web.Services;  
  7. using System.Web.UI.WebControls;  
  8.   
  9. namespace GoogleComboChart_Demo  
  10. {  
  11.     public partial class CobmoChart : System.Web.UI.Page  
  12.     {  
  13.         protected void Page_Load(object sender, EventArgs e)  
  14.         {  
  15.             if (!IsPostBack)  
  16.             {  
  17.                 BindGridView();  
  18.                 ClearTextBox();  
  19.             }  
  20.         }  
  21.         private void ClearTextBox()  
  22.         {  
  23.             txtYear.Text = string.Empty;  
  24.             txtAccountDepartment.Text = string.Empty;  
  25.             txtHRDepartment.Text = string.Empty;  
  26.             txtITDepartment.Text = string.Empty;  
  27.             txtSalesDepartment.Text = string.Empty;  
  28.             txtMarketingDepartment.Text = string.Empty;  
  29.         }  
  30.   
  31.         private void BindGridView()  
  32.         {  
  33.             using (DBModel db = new DBModel())  
  34.             {  
  35.                 CompanyHiringReportGridView.DataSource = (from r in db.CompanyHiringReports select r).ToList();  
  36.                 CompanyHiringReportGridView.DataBind();  
  37.             }  
  38.         }  
  39.         [WebMethod]  
  40.         [ScriptMethod(ResponseFormat = ResponseFormat.Json)]  
  41.         public static object[] GetChartData()  
  42.         {  
  43.             List<CompanyHiringReport> data = new List<CompanyHiringReport>();  
  44.             using (DBModel db = new DBModel())  
  45.             {  
  46.                 data = db.CompanyHiringReports.ToList();  
  47.             }  
  48.             var chartData = new object[data.Count + 1];  
  49.             chartData[0] = new object[]{  
  50.                 "Year",  
  51.                 "Accounts",  
  52.                 "HR",  
  53.                 "IT",  
  54.                 "Sales",  
  55.                 "Marketing"  
  56.             };  
  57.             int j = 0;  
  58.             foreach (var i in data)  
  59.             {  
  60.                 j++;  
  61.                 chartData[j] = new object[] { i.Year.ToString(), i.Account, i.HR, i.IT, i.Sales, i.Marketing };  
  62.             }  
  63.             return chartData;  
  64.         }  
  65.   
  66.         protected void btnSubmit_Click(object sender, EventArgs e)  
  67.         {  
  68.             using (DBModel db = new DBModel())  
  69.             {  
  70.                 CompanyHiringReport companyYearly = new CompanyHiringReport();  
  71.                 companyYearly.Year = Convert.ToInt32(txtYear.Text);  
  72.                 companyYearly.Account = Convert.ToInt32(txtAccountDepartment.Text);  
  73.                 companyYearly.HR = Convert.ToInt32(txtHRDepartment.Text);  
  74.                 companyYearly.IT = Convert.ToInt32(txtITDepartment.Text);  
  75.                 companyYearly.Sales = Convert.ToInt32(txtSalesDepartment.Text);  
  76.                 companyYearly.Marketing = Convert.ToInt32(txtMarketingDepartment.Text);  
  77.   
  78.                 db.CompanyHiringReports.Add(companyYearly);  
  79.                 db.SaveChanges();  
  80.                 ClearTextBox();  
  81.             }  
  82.             this.BindGridView();  
  83.         }  
  84.     }  
  85. }  

Step 10

Right click on web config file add the following code to avoid validation related error.

  1. <appSettings>  
  2.   <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />  
  3. </appSettings>  

Step 11

Run project ctrl+F5

Screenshot 1

ASP.NET

Screenshot 2

ASP.NET

Conclusion

In this article. I have explained how to implement Google combo chart dynamically using entity framework in asp.net. We can add yearly report department name and number of the employee hired by year by clicking on Add New Report button. 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now