How to generate a pie chart from 2 string values in asp.net c#

Question

I have 2 columns ID and Severity, both of datatype string in my database table with the Severity column having High, Medium and Low values. I added a Chart control and specified its data using a sql data source but I'm unable to get the output as Y coordinate values should be of integer type. I need to generate a chart like below with every severity level having a percentage value:

Code:

    private void GetChartData()
        {
            string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT ID, Severity FROM AMD", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                con.Close();
            }

            Chart1.Series[0].XValueMember = "Severity";
            Chart1.Series[0].YValueMembers = "ID";
            Chart1.Series[0].ChartArea = "ChartArea1";   
            Chart1.DataSource = dt;
            Chart1.DataBind();


            int high = 0, med = 0, low = 0;
            string[] x = new string[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                x[i] = dt.Rows[i][0].ToString();
                //y[i] = dt.Rows[i][1].ToString();
                if (dt.Rows[i][1].ToString().ToLower().Contains("high"))
                {
                    high++;
                }

                else if (dt.Rows[i][1].ToString().ToLower().Contains("medium"))
                {
                    med++;
                }

                else if (dt.Rows[i][1].ToString().ToLower().Contains("low"))
                {
                    low++;
                }


            Chart1.Series[1].Points.DataBindXY(x, high);
    }
}

How do I go about achieving this? Please guide... Thanks in advance...

Answer 1

Paste this in design page.

<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() {

        var options = {

            title: 'CHART',

            width: 400,

            height: 400,

            bar: { groupWidth: "95%" },

            legend: { position: "none" },

            isStacked: true

        };



        $.ajax({

            type: "POST",

            url: "Chart.aspx/GetChartData",     //Chart.aspx is the page name.

            data: "{}",

            contentType: "application/json; charset=utf-8",

            dataType: "json",

            success: function (r) {

                debugger;

                var data = google.visualization.arrayToDataTable(r.d);

                //var chart = new google.visualization.BarChart($("#chart")[0]);   //***BarChart***

                var chart = new google.visualization.PieChart($("#chart")[0]); //***PieChart***

                chart.draw(data, options);

            },

            failure: function (r) {

                alert(r.d);

            },

            error: function (r) {

                alert(r.d);

            }

        });

    }

</script>



<div id="chart" style="width: 500px; height: 500px;"> //this is the div which shows the chart.

</div>

Paste this in your c# page.

using System.Web.Services;

[WebMethod]

public static List<object> GetChartData()

{

    string query = "select Share,Value  from Table";

    string constr = ConfigurationManager.ConnectionStrings["dbcn"].ConnectionString;

    List<object> chartData = new List<object>();

    chartData.Add(new object[]

{

    "Share", "Value"

});

    using (SqlConnection con = new SqlConnection(constr))

    {

        using (SqlCommand cmd = new SqlCommand(query))

        {

            cmd.CommandType = CommandType.Text;

            cmd.Connection = con;

            con.Open();

            using (SqlDataReader sdr = cmd.ExecuteReader())

            {

                while (sdr.Read())

                {

                    chartData.Add(new object[]

                {

                    sdr["Share"], sdr["Value"]

                });

                }

            }

            con.Close();

            return chartData;

        }

    }

}

Answer 2

Without being able to try any of this, I would guess:

//SqlCommand cmd = new SqlCommand("SELECT ID, Severity FROM AMD", con);
  SqlCommand cmd = new SqlCommand("SELECT Severity, Count(*) AS ItemCount FROM AMD GROUP BY Severity", con);

and then something like

Chart1.Series[0].XValueMember = "Severity";
Chart1.Series[0].YValueMembers = "ItemCount";