using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using FusionCharts.Charts;

public partial class Pages_DataBaseExample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        string query = "select Region, SUM([Total Sales]) as [Total Sales] from Sales_Record group by Region";
        // establish DB connection and fetch chart data
        GetChartData(ref dt, query);
        // from DB data create chart compatible json
        string chartJsonData = ProcessChartData(dt);
        //Create chart instance
        // charttype, chartID, width, height, data format, data

        Chart chart = new Chart("column2d", "first_chart", "900", "550", "json", chartJsonData);
        Literal1.Text = chart.Render();
    }
    private void GetChartData(ref DataTable dt, string query)
    {

        string connetionString = null;
        string serverName = "POUSHALI-PC\\SHAREPOINT";
        string databaseName = "DrillDownDB";
        //clear previous data from data table
        dt.Clear();
        // we are connectiong by windows authentication so, Trusted_Connection = True;
        connetionString = "Data Source=" + serverName + ";Initial Catalog=" + databaseName + ";Trusted_Connection=True;";

        using (SqlConnection con = new SqlConnection(connetionString))
        {
            con.Open();
            using (SqlCommand command = new SqlCommand(query, con))
            using (SqlDataAdapter da = new SqlDataAdapter(command))
            {
                // fill data table
                da.Fill(dt);

            }

        }
    }
    private string ProcessChartData(DataTable dt)
    {
        StringBuilder jsonData = new StringBuilder();
        StringBuilder data = new StringBuilder();
        // store chart config name-config value pair

        Dictionary<string, string> chartConfig = new Dictionary<string, string>();

        chartConfig.Add("caption", "Total Sales by Region"); 
        chartConfig.Add("xAxisName", "Region");
        chartConfig.Add("yAxisName", "Total Sales");
        chartConfig.Add("numberSuffix", "k");
        chartConfig.Add("theme", "fusion");

        // json data to use as chart data source
        jsonData.Append("{'chart':{");
        foreach (var config in chartConfig)
        {
            jsonData.AppendFormat("'{0}':'{1}',", config.Key, config.Value);
        }
        jsonData.Replace(",", "},", jsonData.Length - 1, 1);
        data.Append("'data':[");

        //iterate through data table to build data object
        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataRow row in dt.Rows)
            {
                data.AppendFormat("{{'label':'{0}','value':'{1}'}},", row[0].ToString(), row[1].ToString());
            }
        }
        data.Replace(",", "]", data.Length - 1, 1);

        jsonData.Append(data.ToString());
        jsonData.Append("}");
        return jsonData.ToString();
    }

}