We help IT Professionals succeed at work.

supplying dynamic data to a chart, asp.net c#

4,078 Views
Last Modified: 2014-08-05
I have developed a new chart using c# with some static data. I also have an existing chart using chart control but it is dynamic and its data is supplied via a stored procedure.

Question: How can I use the stored procedure of the existing chart to supply data for the new chart?

FYI, both charts work okay. I am using vs2010 4.0, c#.

Or alternatively, show me how to apply the settings in the new chart to the existing chart created via chart control. Is this possible?

Thank you.

The code for the new chart developed in c# uses static data but needs to be replaced with the stored proc:
       double[] yValues = { 15, 60, 12, 13 };   //for column chart
        double[] yValues2 = { 30, 32, 30, 0 };   //for line
        string[] xValues = { "September", "October", "November", "December" };

        Chart chart = new Chart();

        Series series = new Series("Default");
        series.ChartType = SeriesChartType.Column;
        chart.Series.Add(series);

        Series series2 = new Series("Default2");
        series2.ChartType = SeriesChartType.Line;
        series2.Color = System.Drawing.Color.Red;
        series2.BorderWidth = 5; // this is actually the line width...
        chart.Series.Add(series2);

        ChartArea chartArea = new ChartArea();
        Axis yAxis = new Axis(chartArea, AxisName.Y);
        Axis xAxis = new Axis(chartArea, AxisName.X);

        chart.Series["Default"].Points.DataBindXY(xValues, yValues);
        chart.Series["Default2"].Points.DataBindXY(xValues, yValues2);
        chart.Series["Default"].Color = System.Drawing.Color.Blue; // change default if you want to
        chart.Series["Default"].Points[0].Color = System.Drawing.Color.Red;
        chart.Series["Default"].Points[chart.Series["Default"].Points.Count - 1].Color = System.Drawing.Color.Orange;

        chart.ChartAreas.Add(chartArea);

        chart.Width = new Unit(300, UnitType.Pixel);
        chart.Height = new Unit(200, UnitType.Pixel);
        string filename = "C:\\image\\Chart.png";
        chart.SaveImage(filename, ChartImageFormat.Png);

Open in new window

The existing chart using chart control with stored procedure:
    <asp:Chart ID="Chart1" runat="server" DataSourceID="SqlDataSource1" 
        BackColor="Transparent" BorderlineColor="Black" BorderlineDashStyle="Solid" 
        Palette="Pastel" Width="846px" onload="Chart1_Load">
        <series>
            <asp:Series Name="Series1" XValueMember="ColName" YValueMembers="ColValue" color="lightgreen" >
            </asp:Series>
        </series>
        <chartareas>
            <asp:ChartArea Name="ChartArea1" BackColor="White" BackSecondaryColor="Yellow">
                <AxisX Interval="1">
                    <LabelStyle ForeColor="White" />
                </AxisX>
            </asp:ChartArea>
        </chartareas>
    </asp:Chart>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ROD_July18ConnectionString %>" 
        SelectCommand="spMonthlyChart" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:SessionParameter DefaultValue="101" Name="Metric_ID" 
                SessionField="Metric_ID" Type="Int32" />
            <asp:SessionParameter DefaultValue="2013" Name="YYYY" SessionField="YYYY" 
                Type="String" />
            <asp:SessionParameter DefaultValue="16" Name="MedCtr_ID" 
                SessionField="MedCtr_ID" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

The data from the stored proc:
ColName  ColValue       Target
2012	0.8192312	0.7
Jan	0.8309572	0.7
Feb	0.8315933	0.7
Mar	0.8157957	0.7
Apr	0.8275316	0.7
May	0.82	        0.7
Jun	0.8094095	0.7
Jul	0.8114406	0.7
Aug	0.8148234	0.7
Sep	0.8131313	0.7
Oct	0.8088762	0.7
Nov	0.8167956	0.7
Dec	0.8162665	0.7
13-YTD	0.8182085	0.7

Open in new window

Comment
Watch Question

Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
Using a 'regular' DataTable would also be possible, instead of the SqlDataSource code use:
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ROD_July18ConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand("spMonthlyChart", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Metric_ID", Session["Metric_ID"].ToString());
            cmd.Parameters.AddWithValue("@YYYY", Session["YYYY"].ToString());
            cmd.Parameters.AddWithValue("@MedCtr_ID", Session["MedCtr_ID"].ToString());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable myDataTable = new DataTable();
            da.Fill(myDataTable);

Open in new window

and then instead of
chart.DataSource = mySqlDataSource;

Open in new window

use:
chart.DataSource = myDataTable;

Open in new window

I must admit I'm not sure if there's much difference in performance, memory use or something like that, just that I hadn't worked with SqlDataSource in code behind much before so I wanted to show what I have used in the past.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Hi Robert:

re:> So I tested with only the 12 rows which seem to contain monthly figures.
The stored proc returns 14 rows for the following three parameters locked in with session variables:
ALTER PROCEDURE [dbo].[spMonthlyChart]
       @Metric_ID INT = NULL
       ,@YYYY NVARCHAR(4) = NULL
       ,@MedCtr_ID INT = NULL
      
AS
BEGIN
.

I wonder if I still need the following three lines 10, 11 and 12. Although I think this is better way to go because it seems storing the parameters in a dictionary object and using them that way is better than relying on the session variables. I have the code for the dictionary to maintain the parameters.

re:> Maybe you can do some sort of Select() over the results of the stored procedure if needed.
The select statement exists in the stored Proc.

Thanks alot. I am trying it now.

Mike
Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
If you have the parameters in a dictionary object then maybe the second piece of code, using a DataTable, is easier to implement.

> The select statement exists in the stored Proc.
That's not what I meant but forget about it for now, because I only now understand that you do need all 14 rows of output from the stored procedure because that's exactly why the first and last columns are a different color... (duh)

I gathered it wouldn't work because of differing data types but I just tested it (just returning '2012' as a string instead of a number) and it's AOK.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Beautiful. I really appreciate for teaching all this to me.

Regards,

Mike
Mike EghtebasDatabase and Application Developer

Author

Commented:
Actually, the first series read like:
LY
Jan
Feb
.
.
Dec
13-YTD

I changed it to LY to 2012 for ease of reading.
Mike EghtebasDatabase and Application Developer

Author

Commented:
I definitely owe you a lot for your help. I have to do something about it.

There is another question to conclude the chart at:

https://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_28491315.html

Thanks,

Mkie