• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3741
  • Last Modified:

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

0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 4
  • 3
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now