using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Telerik.Web.UI;
using System.Data.SqlClient;
using Telerik.Charting;
using System.Drawing;

public partial class Default : System.Web.UI.Page
{
    protected SqlConnection dbCon;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings["PopulationConnectionString"].ConnectionString);
            using (dbCon)
            {
                InitRadChart2(dbCon);
            }
        }

    }

    private void InitRadChart2(SqlConnection dbCon)
    {
    //    RadChart1.Appearance.BarOverlapPercent = 40;
    //    RadChart1.Appearance.BarWidthPercent = 80;
        RadChart1.PlotArea.YAxis.AxisMode = ChartYAxisMode.Extended;
        RadChart1.PlotArea.XAxis.LayoutMode = Telerik.Charting.Styles.ChartAxisLayoutMode.Inside;
        RadChart1.Width = 700;
        RadChart1.Height = 500;
        //RadChart1.PlotArea.Appearance.Dimensions.Margins.Left = Telerik.Charting.Styles.Unit.Percentage(10);
        //RadChart1.PlotArea.Appearance.Dimensions.Margins.Bottom = Telerik.Charting.Styles.Unit.Percentage(40);
        //RadChart1.PlotArea.Appearance.Dimensions.Margins.Right = Telerik.Charting.Styles.Unit.Percentage(2);
        RadChart1.PlotArea.XAxis.Appearance.MajorGridLines.Visible = false;
        RadChart1.PlotArea.YAxis.Appearance.MajorGridLines.Visible = true;

        // Loading all different years and initializing X axis items.
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT Distinct(City) FROM Population ORDER BY City", dbCon);
        DataSet ds = new DataSet();
        adapter.Fill(ds);

        RadChart1.PlotArea.XAxis.Clear();
        RadChart1.PlotArea.XAxis.AutoScale = false;
        foreach (DataRow dbRow in ds.Tables[0].Rows)
        {
            RadChart1.PlotArea.XAxis.AddItem(dbRow["City"].ToString());
        }

        UpdateRadChart2(dbCon);
    }

    private void UpdateRadChart2(SqlConnection dbCon)
    {
        // Remove the previous series.
        RadChart1.Series.Clear();

        // Form sql query to the database.
        string sqlString = "SELECT s.SexID, s.Name, p.City, p.[Value] FROM [Male/Female] AS s INNER JOIN Population as p ON s.SexID = p.SexID ORDER BY s.SexID, City";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlString, dbCon);
        DataSet ds = new DataSet();
        adapter.Fill(ds);

        // Load data.
        int oldid = -1;
        int id;
        ChartSeries currentSeries = null;

        foreach (DataRow dbRow in ds.Tables[0].Rows)
        {
            id = (int)dbRow["SexID"];

            if (id != oldid)
            {
                currentSeries = RadChart1.CreateSeries((string)dbRow["Name"], Color.Empty, Color.Empty, ChartSeriesType.Bar);
                //currentSeries.Appearance.Border.Color = Color.Black;

                currentSeries.Appearance.ShowLabels = false;
                oldid = id;
            }

            if (currentSeries != null)
            {
                currentSeries.AddItem((double)dbRow["Value"]);
            }
        }

        // Set additional properties and settings for the chart.
        RadChart1.ChartTitle.TextBlock.Text = "City Population";
    }
}
