Display percentage in Doughnut chart from multiple Tables in ASP.Net using C# and VB.Net

RichardS...

SOLVED CLOSED ACTIVE ANSWERED
User: RichardSa
Posted: on Oct 09, 2021 01:00 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 7217

I have been working hard to learn how to create doughnut chart in asp web application, I had to use a different method to create doughnut chart. However I am trying to learn if it is possible to display COUNT data from multiple tables in the chart?

I have 4 tables (Table1, Table2, Table3 and Table4) that display different data. I want to count the records that are there in each Table and display in the chart.

I need to count the total number of records inside Table1; I also count the records inside Table2, Table3 and Table4 “based on user”. Then I can display the count in doughnut chart in percentage (%)

For example:

If I count the records in Table1, and its 40; then when Table is counted it is 187, Table3 has 80 and Table4 has 300. Then I display the numbers for each table in the chart.

Below is the Code I used.

N.B. - ONLY A SINGLE TABLE IS USED FOR THIS CHART. BUT I WANT TO ACHIEVE IT WITH MULTIPLE TABLES. THERE IS ALSO STORED PROCEDURE IN THE BELOW CODE. BUT I DON’T WANT TO USE STORED PROCEDURE FOR THIS PARTICULAR TASK.

Please I need great help on this; I will greatly appreciate your help. Thank you

HTML

<asp:Chart ID="Chart1" runat="server" BackGradientStyle="LeftRight"
    BorderlineWidth="0" Palette="None" PaletteCustomColors="steelblue">
    <Titles>
        <asp:Title ShadowOffset="10" Name="Items" />
    </Titles>
    <Legends>
    </Legends>
    <Series>
        <asp:Series Name="Default" />
    </Series>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1" BorderWidth="0" />
    </ChartAreas>
</asp:Chart>

protected void Page_Load(object sender, EventArgs e)
{
        Bindchart();
}
 
private void Bindchart()
{
    SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
    con.Open();
    cmd = new SqlCommand("GetSaleData", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
 
    DataTable ChartData = ds.Tables[0];
 
    //storing total rows count to loop on each Record 
    string[] XPointMember = new string[ChartData.Rows.Count];
    int[] YPointMember = new int[ChartData.Rows.Count];
 
    for (int count = 0; count < ChartData.Rows.Count; count++)
    {
        //storing Values for X axis 
        XPointMember[count] = ChartData.Rows[count]["Items"].ToString();
        //storing values for Y Axis 
        YPointMember[count] = Convert.ToInt32(ChartData.Rows[count]["Figures"]);
 
    }
    //binding chart control 
    Chart1.Series[0].Points.DataBindXY(XPointMember, YPointMember);
 
    //Setting width of line 
    Chart1.Series[0].BorderWidth = 10;
    //setting Chart type  
    Chart1.Series[0].ChartType = SeriesChartType.Doughnut;
 
 
    foreach (Series charts in Chart1.Series)
    {
        foreach (DataPoint point in charts.Points)
        {
            switch (point.AxisLabel)
            {
                case "Q1": point.Color = Color.Maroon; break;
                case "Q2": point.Color = Color.Indigo; break;
                case "Q3": point.Color = Color.Red; break;
                case "Q4": point.Color = Color.Green; break;
            }
            point.Label = string.Format("{0:0} - {1}", point.YValues[0], point.AxisLabel);
 
        }
    }
    con.Close();
}

Download FREE API for Word, Excel and PDF in ASP.Net

arjunv

0 3

Reply
ANSWER
User: arjunv
Replied: on Oct 09, 2021 05:24 AM
Report

Hi RichardSa,

Kindly refer below code.

For binding chart from database refer below article.

HTML

<asp:Chart ID="Chart1" runat="server" Height="300px" Width="400px">
    <Titles>
        <asp:Title ShadowOffset="3" Name="Items" />
    </Titles>
    <Legends>
        <asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default" LegendStyle="Row" />
    </Legends>
    <Series>
        <asp:Series Name="Default" ChartType="Doughnut" />
    </Series>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1" BorderWidth="0" IsSameFontSizeForAllAxes="true" />
    </ChartAreas>
</asp:Chart>

Namespace

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.DataVisualization.Charting

Code

protected void Page_Load(object sender, EventArgs e)
{
    string query = "SELECT 'Table 1' AS 'Table',COUNT(*) AS 'Total' FROM Table1 WHERE Admin = 'Richard'";
    query += " UNION ALL";
    query += " SELECT 'Table 2',COUNT(*) FROM Table2 WHERE Admin = 'Richard'";
    query += " UNION ALL";
    query += " SELECT 'Table 3',COUNT(*) FROM Table3 WHERE Admin = 'Richard'";
    query += " UNION ALL";
    query += " SELECT 'Table 4',COUNT(*) FROM Table4 WHERE Admin = 'Richard'";
    DataTable dt = GetData(query);

    string[] x = new string[dt.Rows.Count];
    int[] y = new int[dt.Rows.Count];
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        x[i] = dt.Rows[i][0].ToString();
        y[i] = Convert.ToInt32(dt.Rows[i][1]);
    }
    Chart1.Series[0].Points.DataBindXY(x, y);
    Chart1.Series[0].ChartType = SeriesChartType.Doughnut;
    Chart1.Series[0].Label = "#VAL (#PERCENT)";
    Chart1.Series[0].LegendText = "#AXISLABEL";
    Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;
    Chart1.Legends[0].Enabled = true;
}

private static DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    using (SqlCommand cmd = new SqlCommand(query))
    {
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);

                return dt;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim query As String = "SELECT 'Table 1' AS 'Table',COUNT(*) AS 'Total' FROM Table1 WHERE Admin = 'Richard'"
    query += " UNION ALL"
    query += " SELECT 'Table 2',COUNT(*) FROM Table2 WHERE Admin = 'Richard'"
    query += " UNION ALL"
    query += " SELECT 'Table 3',COUNT(*) FROM Table3 WHERE Admin = 'Richard'"
    query += " UNION ALL"
    query += " SELECT 'Table 4',COUNT(*) FROM Table4 WHERE Admin = 'Richard'"
    Dim dt As DataTable = GetData(query)
    Dim x As String() = New String(dt.Rows.Count - 1) {}
    Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}

    For i As Integer = 0 To dt.Rows.Count - 1
        x(i) = dt.Rows(i)(0).ToString()
        y(i) = Convert.ToInt32(dt.Rows(i)(1))
    Next

    Chart1.Series(0).Points.DataBindXY(x, y)
    Chart1.Series(0).ChartType = SeriesChartType.Doughnut
    Chart1.Series(0).Label = "#VAL (#PERCENT)"
    Chart1.Series(0).LegendText = "#AXISLABEL"
    Chart1.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
    Chart1.Legends(0).Enabled = True
End Sub

Private Shared Function GetData(ByVal query As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
    Using cmd As SqlCommand = New SqlCommand(query)
        Using con As SqlConnection = New SqlConnection(constr)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                sda.SelectCommand = cmd
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Display percentage in Doughnut chart from multiple Tables in ASP.Net using C# and VB.Net

Create ASP.Net Chart Control from Database using C# and VB.Net Example

Company

Explore

Follow us

Contact Us