AnsweredAssumed Answered

Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way

Question asked by shekar on Jun 29, 2015
Latest reply on Jul 7, 2015 by shekar

Hi Team,

 

I am developing the Microsoft Line Chart control to show the 24 hours data by using PI OLEDB for the particular 2 tags. For time being I have taken 'Sinusoid and CDT158' tags. I have binded the data to the Line Chart control by using the below code.

 

.ASPX PAGE

----------------

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication5.WebForm2" %>

<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

 

<table>

                    <tr>

                        <td align="center" style="background-color:rgb(46,117,182);font-family:Microsoft Sans Serif;font-size:15px">

                            <asp:Label ID="lbl_WP" runat="server" Text="Water Production (m3/h)" Width="300px" ForeColor="White">

                            </asp:Label>

                            <asp:Label ID="lbl" runat="server"></asp:Label>

                        </td>

                    </tr>

                    <tr>

                        <td>               

                <asp:Chart ID="Chart1" runat="server">

                    <Series>

                        <asp:Series Name="Series1" ChartType="Line" Color="Green" YValuesPerPoint="6" ToolTip="Y Value: #VALY \n X Value: #VALX"> <%--XValueType="DateTime"--%>

                        </asp:Series>

                        <asp:Series Name="Series2" ChartType="Line" Color="#0c9eff" YValuesPerPoint="6" ToolTip="Y Value: #VALY \n X Value: #VALX"> <%--XValueType="DateTime"--%>

                        </asp:Series>

                    </Series>

                    <ChartAreas>

                        <asp:ChartArea Name="ChartArea1" BorderDashStyle="Dash" BorderWidth="1">

                            <AxisY>

                                <MajorGrid Enabled="true" />

                                <%--<LabelStyle Format="{#}%" />--%>                                                              

                            </AxisY>

                            <AxisX >                               

                                <MajorGrid Enabled="false" />

                                <%--<LabelStyle Format="hh:mm:ss" IntervalType="Minutes"  Interval="10"/>--%>                                                                                                                             

                            </AxisX>

                        </asp:ChartArea>

                    </ChartAreas>

                </asp:Chart>               

                        </td>

                    </tr>

                </table>

 

.ASPX.CS PAGE

---------------------

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Web.UI.DataVisualization.Charting;

using System.Data;

 

namespace WebApplication5

{

    public partial class WebForm2 : System.Web.UI.Page

    {

        string strConPI = "Data Source=E20052000;Integrated Security=true;Initial Catalog=piarchive;Provider=PIOLEDB.1;Always Return Rowset=True";

        DataTable dt = new DataTable();

        DataTable dt2 = new DataTable();

        DataView dv = new DataView();

        double intervalSet;

        string[] x, p = null;

        int[] y, q = null;

        protected void Page_Load(object sender, EventArgs e)

        {

            //Label1.Text = "Page loaded time : " + DateTime.Now.ToString();

            UpdateLineChartData();

            //else

            //    lblMsgLoad.Text = "No Data...!";

        }

       

        private void UpdateLineChartData()

        {

            lbl.Text = DateTime.Now.ToString();

            //Series 1

            string strCmd = "SELECT time,value "

                            + "FROM [piarchive].[picomp2] "

                            + "WHERE tag = 'sinusoid' "

                            + "AND time BETWEEN '*-24h' AND '*'";

            OleDbDataAdapter oledbDA = new OleDbDataAdapter(strCmd, strConPI);

            oledbDA.Fill(dt);

            if (dt != null && dt.Rows.Count > 0)

            {

                //dv = dt.DefaultView;

                x = new string[dt.Rows.Count];

                y = new int[dt.Rows.Count];

                for (int i = 0; i < dt.Rows.Count; i++)

                {

                    if (!string.IsNullOrEmpty(dt.Rows[i][0].ToString()))

                    {

                        x[i] = Convert.ToDateTime(dt.Rows[i][0].ToString()).ToString("hh");

                        x[i] = x[i] + ":00";

                    }

 

                    if (!string.IsNullOrEmpty(dt.Rows[i][1].ToString()))

                        y[i] = Convert.ToInt32(dt.Rows[i][1]);

                    //break;

                }

               

                //Chart1.Series["Series1"].Points.DataBindXY(dv, "hour", dv, "value");

            }

            //Series 2

            string strCmd_2 = "SELECT time,value "

                    + "FROM [piarchive].[picomp2] "

                    + "WHERE tag = 'cdt158' "

                    + "AND time BETWEEN '*-24h' AND '*'";

            OleDbDataAdapter oledbDA_2 = new OleDbDataAdapter(strCmd_2, strConPI);

            oledbDA_2.Fill(dt2);

            if (dt2 != null && dt2.Rows.Count > 0)

            {

                //dv = dt2.DefaultView;

                p = new string[dt2.Rows.Count];

                q = new int[dt2.Rows.Count];

                for (int i = 0; i < dt2.Rows.Count; i++)

                {

                    if (!string.IsNullOrEmpty(dt2.Rows[i][0].ToString()))

                    {

                        p[i] = Convert.ToDateTime(dt2.Rows[i][0].ToString()).ToString("hh");

                        p[i] = p[i] + ":00";

                    }

 

                    if (!string.IsNullOrEmpty(dt2.Rows[i][1].ToString()))

                        q[i] = Convert.ToInt32(dt2.Rows[i][1]);

                    //break;

                }               

                //Chart1.Series["Series2"].Points.DataBindXY(dv, "hour", dv, "value");

            }

            if (dt.Rows.Count > dt2.Rows.Count)

                Chart1.ChartAreas[0].AxisX.Interval = Convert.ToInt32(dt.Rows.Count)/5;

            else

                Chart1.ChartAreas[0].AxisX.Interval = Convert.ToInt32(dt2.Rows.Count)/5;

 

            if(y!=null && y.Length > 0)

                Chart1.Series["Series1"].Points.DataBindXY(x, y);

            if (q != null && q.Length > 0)

                Chart1.Series["Series2"].Points.DataBindXY(p, q);

        }      

    }

}

 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

By using the above PI SQL query it is taking lot of time to load the output page if there are more than 50 values for the tags. I want to optimize the above code to load the the page immediately.

 

Also is there any query to retrieve the data in interval times like every 4 hours data upto 24 hours.

 

Request to provide any better solution to optimise the above code.

 

thanks....

Outcomes