How to Create Google Charts in ASP.Net With JSON

Introduction

This article explains how to work with Google Charts and also how Google Charts are better compared to Microsoft RDLC charts and Crystal Report Charts.

In most websites are static in the HTML and we cannot use our RDLC charts and Crystal Report Charts. Google Charts is useful if you have some static data to display. For that just create that data in JSON format and provide charts to display it.

• It works fine.
• Its free to use from Google.
• Google Charts are very interactive compared to RDLC and Crystal Reports.
• Easy to customize and design.

You can find a number of articles on this topic but they are not well explained with procedures. Here is a good example, read it once you will be a master.

Clean, Simple, Easy.

For more details visit: Google Charts.

If you want to develop RDLC charts then here is a useful article.

How to Create RDLC Charts and Complete Series of All Charts

This article provides the following 3 examples (my favourite ones):

1. Columns charts
2. Pie Charts
3. Bar Charts

Columns charts



Pie Charts



Bar Charts



Images are used from: Using Google Charts.

Requirements for Developing Google Charts in ASP.NET

ASP.NET, SQL Server and a reference for the Google jsapi.

Step 1: SQL

In this I will show which table I am using and which type of query I am binding to the Google Charts.

Here I have used the following 2 tables:

1. PaymentDetails
2. PlanMaster
   

In the PaymentDetails table I am storing all the payment related data with PlanID from the PlanMasterTable.

In the PlanMaster table I am storing all Plannames.

Here I am providing a Table Snapshot view. I will provide all the tables script and data for testing.



Here is the query that will get the records and we will bind them to the charts.

It's a normal query I have used. In a real scenario it would have many more tables.



I have created a Stored Procedure for displaying the data.



Hey we have now completed the SQL Server part. Now we are moving to C# and ASP.NET.

Step 2: Creating the ASP.NET Web Application

Create a new ASP.NET Web Application named WorkingwithGooglecharts and Select .Net Framework 4.0 when adding the project.

After adding you will get a blank solution with default stuff provided by the ASP.NET Web Application.

First I will show you how to create Column charts.

Add a new page to the project with the name Columncharts.aspx.



Step 3: Pass Data to Google Charts

After adding the page, the first step is to pass data to Google Charts.

Google Charts requires data in JSON format. For passing JSON data I am creating WebMethod.

Before WebMethod I have created a class with the name ChartDetails.

It will have the following 2 Properties:

1. PlanName
2. PaymentAmount

After adding the class I will now create a WebMethod of type ChartDetails.

Because I will pass a List of ChartDetails to JSON.



Then inside WebMethod I have created a SQL Connection for getting data.



After setting the Connection String, I have written a SQL command of type Stored Producre to get data from SQL Tables into a DataTable.

SqlCommand cmd = new SqlCommand("Usp_Getdata", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);

After filling in data for the DataTable now pass that data into a list of type ChartDetails.

List<ChartDetails> dataList = new List<ChartDetails>();
foreach (DataRow dtrow in dt.Rows)
{
      ChartDetails details = new ChartDetails();
      details.PlanName = dtrow[0].ToString();
      details.PaymentAmount = Convert.ToInt32(dtrow[1]);
      dataList.Add(details);
}

After all here is the complete method to be created.

[WebMethod]
public static List<ChartDetails> GetChartData()
{

      using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["GCConnectionString"].ToString()))
      {
            SqlCommand cmd = new SqlCommand("Usp_Getdata", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);

            List<ChartDetails> dataList = new List<ChartDetails>();

            foreach (DataRow dtrow in dt.Rows)
            {
                  ChartDetails details = new ChartDetails();
                  details.PlanName = dtrow[0].ToString();
                  details.PaymentAmount = Convert.ToInt32(dtrow[1]);

                  dataList.Add(details);
            }
            return dataList;
      }
}

Now we have completed the passing of the data. Now let's move to the design part.

Step 4: Google Charts Design

This is the design part of Google Charts.

First we need to create a goblal variable of Google Charts.

<script type="text/javascript">
      // Global variable to hold data
      // Load the Visualization API and the piechart package.
      google.load('visualization', '1', { packages: ['corechart'] });
</script>

The next step, after declaring the global varaiable, is to call WebMethod from jQuery to do that.

I have created a function.

$(function ()
{
   $.ajax(
   {
      type: 'POST',
      dataType: 'json',
      contentType: 'application/json',
      url: 'ColumnChart.aspx/GetChartData',
      data: '{}',
      success: function (response)
      {
         drawchart(response.d); // calling method
      },

      error: function ()
      {
         alert("Error loading data! Please try again.");
      }
   });
})

This function contains the main thing URL, "ColumnChart.aspx/GetChartData", that will call WebMethod.

And on success I have called another function Draw chart to which I am passing the response that I am getting from Post.

function drawchart(dataValues)
{
      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      var data = new google.visualization.DataTable();

      data.addColumn('string', 'PlanName');
      data.addColumn('number', 'PaymentAmount');

      for (var i = 0; i < dataValues.length; i++)
      {
            data.addRow([dataValues[i].PlanName, dataValues[i].PaymentAmount] );
      }
      // Instantiate and draw our chart, passing in some options

      var chart = new google.visualization.ColumnChart(document.getElementById('chartdiv'));

      chart.draw(data,
      {
            title: "Show Google Chart in Asp.net",
            position: "top",
            fontsize: "14px",
            chartArea: { width: '50%' },
      });
}

Declaring datatable and adding Columns into it.

var data = new google.visualization.DataTable();

data.addColumn('string', 'PlanName');
data.addColumn('number', 'PaymentAmount');

Then using for loop to pass data from Respone into Datatable.

for (var i = 0; i < dataValues.length; i++)
{
      data.addRow([dataValues[i].PlanName, dataValues[i].PaymentAmount] );
}

Instantiate and draw our chart, passing in some options as in the following:

var chart = new google.visualization.ColumnChart(document.getElementById('chartdiv'));

Here we pass data and other options that we have to customize the look of the charts.

chart.draw(data,
{
      title: "Show Google Chart in Asp.net",
      position: "top",
      fontsize: "14px",
      chartArea: { width: '50%' },
});

Here is the complete function view.

<script type="text/javascript">
        $(function () {
            $.ajax({
                type: 'POST',
                dataType: 'json',
                contentType: 'application/json',
                url: 'ColumnChart.aspx/GetChartData',
                data: '{}',
                success: function (response) {
                    drawchart(response.d); // calling method
                },

                error: function () {
                    alert("Error loading data! Please try again.");
                }
            });
        })

        function drawchart(dataValues) {
            // Callback that creates and populates a data table,
            // instantiates the pie chart, passes in the data and
            // draws it.
            var data = new google.visualization.DataTable();

            data.addColumn('string', 'PlanName');
            data.addColumn('number', 'PaymentAmount');

            for (var i = 0; i < dataValues.length; i++)
            {
                data.addRow([dataValues[i].PlanName, dataValues[i].PaymentAmount] );
            }
            // Instantiate and draw our chart, passing in some options
           var chart =  new google.visualization.ColumnChart(document.getElementById('chartdiv'));

       chart.draw(data,
         {
             title: "Show Google Chart in Asp.net",
             position: "top",
             fontsize: "14px",
             chartArea: { width: '50%' },
         });
        }
    </script>

Step 5: Display the Chart

The last thing is to display the chart. I used a div for this and given an id to it.

<body>
    <form id="form1" runat="server">

     <div id="chartdiv" style="width: 600px; height: 350px;">
     </div>

    </form>
</body>

Step 6: Run

Now just run the application and check it.

Here is the output of a Column Chart.



Here is how the data is displayed on charts.



Here are all the charts I have shown you.



You can download this charts attachment and check it. You will find all the examples and code related to it in details.