Pie chart and SQL query

Question

User1309273214 posted

Hi,

I am using the following SQL query to get a score (grade) from a training record. It brings up 2 numbers and I would like to show the numbers as a pie in a chart control, rather than just the numbers.

But I cannot get it to work with the chart control, all I get is a filled circle ? I know that the query works fine but apparently not in combination a chart ?

SelectCommand="SELECT CAST( (((SELECT count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <3)*1.0/ (SELECT count(*) FROM opc WHERE o24 IS NOT NULL)) *100) AS decimal(10,2)) AS SmallerPercent,

CAST( (( ( (SELECT count(*) FROM opc WHERE o24 IS NOT NULL)- (SELECT count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <3)) *1.0 /  (SELECT count(*) FROM opc WHERE o24 IS NOT NULL) ) *100) AS decimal(10,2)) LargerPercent">

Best regards

Answer 1

User-707554951 posted

Hi ricas, 

From your description, I suggest you could  follow the steps below:

1. Create a procedure in your database using following:

create procedure selectPercent

AS

begin

--create a temporaryTable

declare @temporaryTable table (ColumnName decimal, ColumnValue decimal)

insert into @temporaryTable

--replace the follwing one sentence with your query statement

    select 35 as C1, 68 as C2

select C1='SmallerPercent', C2=(select top 1 ColumnName from @temporaryTable)

    union all

select C1='LargerPercent',C2=(select top 1 ColumnValue from @temporaryTable)

end

The output screenshot as below:

2. Call procedure in your sqldatasource control.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString='<%$ ConnectionStrings:ConnectionString %>' 
       SelectCommand="selectPercent" SelectCommandType="StoredProcedure"></asp:SqlDataSource>

3. Binding data to chart control.

<asp:Chart ID="Chart1" runat="server" DataSourceID="SqlDataSource1" >
            <Series>
                <asp:Series Name="Series1" XValueMember="C1" YValueMembers="C2"
                     IsValueShownAsLabel="true" ChartType="Pie" Label="#VALY\n#VALX">                               
                </asp:Series>
            </Series>       
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1"></asp:ChartArea>
            </ChartAreas>         
       </asp:Chart>

The output screenshot as below:

 

Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.

Best regards,

Cathy

Answer 2

User1309273214 posted

Hi Cathy,

Thanks for your effort ! 

I have som problems creating a procedure on the SQL database server, is there any possibility to use the code on the code behind page, rather than a database procedure ?

Brgds  

Answer 3

User-707554951 posted

Hi ricas,

Are you want to know how to use store procedure on the code behind, if you are, I suggest you could refer to the following code:

using (SqlConnection con = new SqlConnection(connenctionstring))
                {
                    using (SqlCommand myCMD = new SqlCommand("sp_Test", con))
                    {
                        myCMD.CommandType = CommandType.StoredProcedure;
                        con.Open();
                        myCMD.ExecuteNonQuery();
                        con.Close();
                    }
                }

Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.

Best regards
Cathy

 

Answer 4

User1309273214 posted

Hi Cathy,

Thanks ! My problem is that due to various reasons I would like to avoid a stored procedure, but to implement all the code on the actual aspx page and the coherent code behind page (c#) You know how to do this ?

Best regards   

Answer 5

User-707554951 posted

Hi ricas

From your description, if you want avoid using stored procedure, I suggest you could query data form database in codebehind by using sqlclient and use the data to fill datatable. Then, do some change for the table by using c#. Finally, binding data for your chart.

The following code is a sample, you could refer to it.

<asp:Chart ID="Chart1" runat="server">
            <Series>
                <asp:Series Name="Series1" XValueMember="C1" YValueMembers="C2"
                     IsValueShownAsLabel="true" ChartType="Pie" Label="#VALY\n#VALX">                               
                </asp:Series>
            </Series>       
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1"></asp:ChartArea>
            </ChartAreas>         
        </asp:Chart>

CodeBehind:

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

protected void Page_Load(object sender, EventArgs e)
        {
            bindchart();

        }
        private void bindchart()
        {
            string strcon = ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(strcon))
            {
                //replace the following with your query statement
                String query = "your query statement ";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {                      
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            DataTable newdt = GenerateTransposedTable(dt);
                         Chart1.DataSource = newdt;
                         Chart1.Series[0].Points.DataBindXY("column0", "column1");
                        }
                    }
                }
            }
        }

        private DataTable GenerateTransposedTable(DataTable inputTable)
        {
            DataTable outputTable = new DataTable();

           
            for (int i = 0; i < inputTable.Columns.Count; i++)
            {
                outputTable.Columns.Add("column" + i);
           }

               
            for (int rCount = 0; rCount < inputTable.Columns.Count; rCount++)
            {
                DataRow newRow = outputTable.NewRow();
               
                newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
                for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
                {
                    string colValue = inputTable.Rows[cCount][rCount].ToString();
                    newRow[cCount + 1] = colValue;
                }
                outputTable.Rows.Add(newRow);
            }
            return outputTable;
        }

Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.

Best regards
Cathy