Display sqlserver query result in a MS Chart Pie

Marius Vasile

I get data with

string sqlQuery = "SELECT DATEPART(MONTH, tr.DataInitierii) AS Month," +
    "coalesce(SUM(CASE When Dep LIKE '1.1.0%' then 1 end),0) A1," +
    "coalesce(SUM(CASE When Dep LIKE '1.1.3%' then 1 end),0) A2," +
    "coalesce(SUM(CASE When Dep LIKE '1.1.4%' then 1 end),0) A6," +
    "coalesce(SUM(CASE When Dep LIKE '1.1.5%' then 1 end),0) A8," +
    "coalesce(SUM(CASE When Dep LIKE '1.1.7%' then 1 end),0) A19," +
    "coalesce(SUM(CASE When Dep LIKE '1.2%' then 1 end),0) A20," +
    "coalesce(SUM(CASE When Dep LIKE '1.5%' then 1 end),0) A21," +
    "coalesce(SUM(CASE When Dep LIKE '1.6%' then 1 end),0) A22," +
    "coalesce(SUM(CASE When Dep LIKE '1.7%' then 1 end),0) A23 " +
    "FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# " +
    "WHERE DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND tc.Specialitate = @Spec GROUP BY DATEPART(MONTH, tr.DataInitierii)";

I display the data in a table but I want to display in a Pie Chart as well, using A1, A2...A23 values. How can I do that?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty1y ago

Accepted answer

Once check below example.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ChartPage.aspx.cs" Inherits="ChartPage" %>




    Pie Chart Example

Code Behind:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.DataVisualization.Charting;

public partial class ChartPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadDataAndDisplayChart();
        }
    }

    private void LoadDataAndDisplayChart()
    {
        string connectionString = "your connection string";
        string sqlQuery = "your query";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(sqlQuery, conn);
            cmd.Parameters.AddWithValue("@CurrentYear", DateTime.Now.Year);
            cmd.Parameters.AddWithValue("@Spec", "Your Speciality");

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable resultTable = new DataTable();
            adapter.Fill(resultTable);

            if (resultTable.Rows.Count > 0)
            {
                DataRow row = resultTable.Rows[0];

                Series series = new Series
                {
                    Name = "SpecialityData",
                    IsValueShownAsLabel = true,
                    ChartType = SeriesChartType.Pie
                };

                series.Points.AddXY("A1", Convert.ToInt32(row["A1"]));
                series.Points.AddXY("A2", Convert.ToInt32(row["A2"]));
                series.Points.AddXY("A6", Convert.ToInt32(row["A6"]));
                series.Points.AddXY("A8", Convert.ToInt32(row["A8"]));
                series.Points.AddXY("A19", Convert.ToInt32(row["A19"]));
                series.Points.AddXY("A20", Convert.ToInt32(row["A20"]));
                series.Points.AddXY("A21", Convert.ToInt32(row["A21"]));
                series.Points.AddXY("A22", Convert.ToInt32(row["A22"]));
                series.Points.AddXY("A23", Convert.ToInt32(row["A23"]));

                pieChart.Series.Add(series);
            }
        }
    }
}

Tuhin Paul1y ago

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.DataVisualization.Charting;

public partial class ChartPage : System.Web.UI.Page
{
    private readonly string connectionString = "your connection string";
    private readonly string sqlQuery = "your query";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadDataAndDisplayChart();
        }
    }

    private void LoadDataAndDisplayChart()
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@CurrentYear", DateTime.Now.Year);
                    cmd.Parameters.AddWithValue("@Spec", "Your Speciality");

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable resultTable = new DataTable();
                        adapter.Fill(resultTable);

                        if (resultTable.Rows.Count > 0)
                        {
                            DataRow row = resultTable.Rows[0];

                            Series series = new Series
                            {
                                Name = "SpecialityData",
                                IsValueShownAsLabel = true,
                                ChartType = SeriesChartType.Pie
                            };

                            series.Points.AddXY("A1", Convert.ToInt32(row["A1"]));
                            // ...

                            pieChart.Series.Add(series);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            // Handle error
        }
    }
}

Consider using ORM (Object-Relational Mapping) tools like Entity Framework.

Use ASP.NET Chart Controls' built-in features (e.g., data binding).

Optimize chart rendering for better performance.