How to populate google charts using SQL Server Dat

Question

I wish to replace the hard coded data with SQL Server Database. However, I'm stucked as I am still new to this.. I just tried the Google Chart and its working with hard-coded values, please guide me step-by-step to change the values to data from my database.

If theres any informations you need, please let me know. I'll try to provide them. Thanks for the help in advance guys! ):

Code for my Model:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ChartInMvcApplication.Models
{
    public class ProductModel
    {
        public string YearTitle { get; set; }
        public string SaleTitle { get; set; }
        public string PurchaseTitle { get; set; }
        public Product ProductData { get; set; }
    }
    public class Product
    {
        public string Year { get; set; }
        public string Purchase { get; set; }
        public string Sale { get; set; }
    }
}

Code for my Controller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ChartInMvcApplication.Models;

namespace ChartInMvcApplication.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {
            ProductModel objProductModel = new ProductModel();
            objProductModel.ProductData = new Product();
            objProductModel.ProductData = GetChartData();
            objProductModel.YearTitle = "Year";
            objProductModel.SaleTitle = "Sale";
            objProductModel.PurchaseTitle = "Purchase";
            return View(objProductModel);

        }
        /// <summary>
        /// Code to get the data which we will pass to chart
        /// </summary>
        /// <returns></returns>
        public Product GetChartData()
        {
            Product objproduct = new Product();
            /*Get the data from databse and prepare the chart record data in string form.*/
            objproduct.Year = "2009,2010,2011,2012,2013,2014";
            objproduct.Sale = "2000,1000,3000,1500,2300,500";
            objproduct.Purchase = "2100,1400,2900,2400,2300,1500";
            return objproduct;
        }
    }
}

Code for my View:

 @model ChartInMvcApplication.Models.ProductModel
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);

    function drawChart() {
        // Create and populate the data table.
        var years = [@Model.ProductData.Year];
        var sales = [@Model.ProductData.Sale];
        var Purchase = [@Model.ProductData.Purchase];

        var data = new google.visualization.DataTable();
        data.addColumn('string', '@Model.YearTitle');
        data.addColumn('number', '@Model.SaleTitle');
        data.addColumn('number', '@Model.PurchaseTitle');
        for (i = 0; i < years.length; i++) {
            data.addRow([years[i].toString(), sales[i], Purchase[i]]);
        }
        var options = {
            title: 'Sale and Purchase Compare',
            hAxis: { title: '@Model.YearTitle', titleTextStyle: { color: 'red'} }
        };

        var chart = newgoogle.visualization.ColumnChart(document.getElementById('chartdiv'));
        chart.draw(data, options);
    }
</script>
<div id="chartdiv" style="width: 500px; height: 300px;">
</div>

Answer 1

You have to add data to each product object.

public Product GetChartData()
        {
            Product objproduct = new Product();
            //Get Data for years as a string list.
            List<string> years = Database.GetYears();
            foreach(var year in years) 
              GetChartDataForYear(year, ref objproduct);
            return objproduct; 
        }


        public void GetChartDataForYear(string year, Product objproduct out)
        {  
            if(!string.IsNullorEmpty(objproduct.Year))      
            objproduct.Year += (",");      


            if(!string.IsNullorEmpty(objproduct.Sale))      
            objproduct.Sale += (",");    

            if(!string.IsNullorEmpty(objproduct.Purchase ))      
            objproduct.Purchase += (",");      

            objproduct.Year += year.ToString();
            objproduct.Sale += GetSale(int year);
            objproduct.Purchase += GetPurchase(int year);

        }





public string GetSale(string year)
        {
            // To get from DB.
             return "";
        }




 public string GetPurchase(string year)
    {
       // To get from DB.
       return "";
    }

Answer 2

The best way is to bring the values from the database and bind them into a list, then do a foreach to populate your chart.

In your model create 3 functions like this :

public List<string> getYears()
{
    List<string> years = new  List<string>();
    string connectionString = ConfigurationManager.AppSettings["StringConnection"].ToString();
    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        cn.Open();
        SqlCommand sqlCommand = new SqlCommand("SELECT year FROM tableTest", cn);
        SqlDataReader reader = sqlCommand.ExecuteReader();
        while (reader.Read())
        {
            years.Add(reader["year"].ToString());
        }
        cn.Close();
    }
    return years;
}

public List<string> getSale()
{
    List<string> sales = new List<string>();
    List<string> years = getYears();
    foreach (var year in years)
    {
        string connectionString = ConfigurationManager.AppSettings["StringConnection"].ToString();
        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            cn.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT sale FROM tableTest where year = '" + year + "'", cn);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                sales.Add(reader["sale"].ToString());
            }
            cn.Close();
        }
    }
    return sales;
}

public List<string> getPurchase()
{
    List<string> purchases = new List<string>();
    List<string> years = getYears();
    foreach (var year in years)
    {
        string connectionString = ConfigurationManager.AppSettings["StringConnection"].ToString();
        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            cn.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT purchase FROM tableTest where year = '" + year + "'", cn);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                purchases.Add(reader["purchase"].ToString());
            }
            cn.Close();
        }
    }
    return purchases;
}

Add a function in your Model to change Lists to strings:

public string listToString(List<string> list)
{
    string output = "";
    foreach(var item in list)
    {
        output = (String.IsNullOrEmpty(output)) ? item : output + "," + item;
    }
    return output;
}

In your controller the function will be :

public Product GetChartData()
{
    ProductModel prod = new ProductModel();

    Product objproduct = new Product();
    /*Get the data from databse and prepare the chart record data in string form.*/
    objproduct.Year = prod.listToString(prod.getYears());
    objproduct.Sale = prod.listToString(prod.getSale());
    objproduct.Purchase = prod.listToString(prod.getPurchase());
    return objproduct;
}