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

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

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
I hope I understand this correctly. I guess you want everything from the mark-up in code behind (instead of just using a DataTable). The data itself is a bit of a problem because it seems to contain some extra rows which will prevent the chart from using it. So I tested with only the 12 rows which seem to contain monthly figures. Maybe you can do some sort of Select() over the results of the stored procedure if needed.
            // some dummy values...
            Session["Metric_ID"] = "1";
            Session["YYYY"] = "2013";
            Session["MedCtr_ID"] = "123";

            SqlDataSource mySqlDataSource = new SqlDataSource();
            mySqlDataSource.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ROD_July18ConnectionString"].ConnectionString;
            mySqlDataSource.SelectCommand = "spMonthlyChart";
            mySqlDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
            mySqlDataSource.SelectParameters.Add(new SessionParameter("Metric_ID", DbType.Int32, "Metric_ID"));
            mySqlDataSource.SelectParameters.Add(new SessionParameter("YYYY", DbType.String, "YYYY"));
            mySqlDataSource.SelectParameters.Add(new SessionParameter("MedCtr_ID", DbType.Int32, "MedCtr_ID"));

            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);

            series.XValueMember = "ColName"; // X
            series.YValueMembers = "ColValue"; // Y1
            series2.XValueMember = "ColName"; // X
            series2.YValueMembers = "Target"; // Y2
            chart.DataSource = mySqlDataSource;
            chart.DataBind();

            chart.Series["Default"].Color = System.Drawing.Color.Blue;
            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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert SchuttSoftware EngineerCommented:
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.
0
Mike EghtebasDatabase and Application DeveloperAuthor 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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Robert SchuttSoftware EngineerCommented:
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.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Beautiful. I really appreciate for teaching all this to me.

Regards,

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Actually, the first series read like:
LY
Jan
Feb
.
.
Dec
13-YTD

I changed it to LY to 2012 for ease of reading.
0
Mike EghtebasDatabase and Application DeveloperAuthor 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:

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

Thanks,

Mkie
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.