Wednesday, November 6, 2013

Asp.net - Google Pie Chart in ASP.net

In this article i will show you how to bind dynamic data in Google pie chart in ASP.net.

Step 1
Download Northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Note : For SQL Server 2012 User,Download Northwind database from the following link
http://businessimpactinc.com/install-northwind-database/

Step 2
Attach Northwind database into MS-SQL server.

Note: For SQL Server 2012 User follow the instruction from above given website(Second Link) to attach database in MS-SQL Server 2012.

Step 3
Create a ASP.net empty web site and give the solution name as SolGooglePieChart.

Step 4
Install a Latest Jquery from Nuget Package Manager.Right click on Website and select Manage Nuget Packages,it is look like this



Click on Image for better view.

Step 5
Add App_Code folder in the solution and add a New Folder inside the App_Code folder and give folder name as ORD,it is look like this



Click on Image for better view.

Step 6
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this



Click on Image for better view.

Step 7
Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this



Click on Image for better view.



Click on Image for better view.

Visual studio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 8
Create a Categories and Product object that will use LINQ to SQL to map to this table.go to the Server Explorer,select Northwind database,go to the Tables and select Employees and Product table,it is look like this



Click on Image for better view.

Drag and drop tables from Server explorer onto the design surface of the O/R Designer,it is look like this



Click on Image for better view

Step 9
Create a Categories Static Class for calculate count of per category of Product from Tables using Linq or Lambda Expression,it is look like this
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for Categories
/// </summary>
public static class Categories
{
 
 #region Method

    /// <summary>
    /// Get Categories wise Product Count Data
    /// </summary>
    /// <returns>List</returns>
 public static IList GetProductCountData()
 {
  try
  {
   ORD.NorthwindDCDataContext DC = new ORD.NorthwindDCDataContext();

            // Using Linq
            //return (from CQ in DC.Categories
            //        join PQ in DC.Products
            //        on CQ.CategoryID equals PQ.CategoryID
            //        into CP
            //        orderby CQ.CategoryName ascending
            //        select new 
            //        { 
            //            CategoryName = CQ.CategoryName,
            //            ProductCount = CP.Count()
            //        }).ToList();

            // Using Lambda Expression

            return DC.Categories.GroupJoin(DC.Products,
                        CQ => CQ.CategoryID, PQ => PQ.CategoryID,
                        (CQ, CP) => new
                        {
                            CategoryName = CQ.CategoryName,
                            ProductCount = CP.Count()
                        }).OrderBy(LE => LE.CategoryName).ToList();
            
  }
  catch (Exception ex)
  {
   throw new Exception(ex.Message); 
  }
 }

 #endregion
}

Step 10
Now Add Defualt.aspx page in the solution and Create a Web Method in Default.aspx.cs for call Web Method from JSON function.it is look like this
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }

    #region Methods

    [WebMethod]
    public static IList BindGooglePieChart()
    {
        return Categories.GetProductCountData();
    }

    #endregion
}

Generally we will create static web methods in webservice and we will use those methods to call it from JQuery instead of that directly we can create static methods with [WebMethod] attribute in our code behind file and use those methods from JQuery.

Server side code part done,now let see Client side code.

Step 11
Define a Div Tag where the chart will drawn,it is look like this
<body>
    <form id="form1" runat="server">
        <div id="GooglePieChart" style="width: 800px; height: 500px;margin: 0px auto"></div>
    </form>
</body>

Step 12
Add JQuery file Reference inside the head tag of the page,it is look like this
<script language="javascript" type="text/javascript" src="Scripts/jquery-2.0.3.min.js"></script>

Step 13
Add Google JSAPI API inside the head tag of the page,it is look like this 
<script language="javascript" type="text/javascript" src="https://www.google.com/jsapi"></script>

Step 14
 Load the Google Visualization and Chart libraries,it is look like this
<script language="javascript" type="text/javascript">
        google.load('visualization', '1', { packages: ['corechart'] });
</script>

Step 15
Call a Page Web Method in JQuery for fetching data from the server,it is look like this
<script language="javascript" type="text/javascript">

        $(document).ready(function () {

          
            $.ajax({
                type: 'POST',
                dataType: 'json',
                contentType: 'application/json',
                url: 'Default.aspx/BindGooglePieChart',
                data: '{}',
                success:
                    function (response) {

                        var data = new google.visualization.DataTable();
                        data.addColumn('string', 'CategoryName');
                        data.addColumn('number', 'ProductCount');

                        var Values = response.d;

                        for (var i = 0; i < Values.length; i++) {
                            data.addRow([Values[i].CategoryName, Values[i].ProductCount]);
                        }

                      
                        new google.visualization.PieChart($("#GooglePieChart")[0]).
                            draw(data, { title: "Categories wise Product Count" });

                    }
            });
        });

    </script>

Run the Project.

Full Code of ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Google Pie Chart</title>
    <script language="javascript" type="text/javascript" src="Scripts/jquery-2.0.3.min.js"></script>
    
    <script language="javascript" type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script language="javascript" type="text/javascript">
        google.load('visualization', '1', { packages: ['corechart'] });
    </script>

    <script language="javascript" type="text/javascript">

        $(document).ready(function () {

          
            $.ajax({
                type: 'POST',
                dataType: 'json',
                contentType: 'application/json',
                url: 'Default.aspx/BindGooglePieChart',
                data: '{}',
                success:
                    function (response) {

                        var data = new google.visualization.DataTable();
                        data.addColumn('string', 'CategoryName');
                        data.addColumn('number', 'ProductCount');

                        var Values = response.d;

                        for (var i = 0; i < Values.length; i++) {
                            data.addRow([Values[i].CategoryName, Values[i].ProductCount]);
                        }

                      
                        new google.visualization.PieChart($("#GooglePieChart")[0]).
                            draw(data, { title: "Categories wise Product Count" });

                    }
            });
        });

    </script>

</head>
<body>
    <form id="form1" runat="server">
        <div id="GooglePieChart" style="width: 800px; height: 500px;margin: 0px auto"></div>
    </form>
</body>
</html>

Output

Click on Image for better view

Download
Download Source Code

No comments:

Post a Comment