Integrating with sql server data

Wiki Link: [discussion:286773]
ghostme
Jan 20, 2012 at 6:22 AM
Edited Jan 20, 2012 at 6:23 AM

Hello guys,

Thank you for this wondering charting option.

However, I am having difficulties integrating this with sql server.

 

Below is my current code

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;
using Highchart.Core.Data.Chart;
using Highchart.Core;
using Highchart;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace EntityFramework
{
    public partial class LineChart : System.Web.UI.Page
    {
        DataSet dsSeries = new DataSet();
        public string hidValues1;
        public string hidXCategories1;
        protected void Page_Load(object sender, EventArgs e)
        {
            //Exemplo01();
           
           

              
            Exemplo03();
        }
        
        public DataSet BindData()
        {
            string connString = ConfigurationManager.ConnectionStrings["Chartdata"].ToString();
            SqlConnection con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT symbol,value FROM Ticker";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void Exemplo03()
        {
              dsSeries = BindData();

              if (dsSeries == null) return;

              foreach (DataRow dr in dsSeries.Tables[0].Rows)
              {
                  hidXCategories1 =  hidXCategories1 + dr["symbol"].ToString() ;

              }

              foreach (DataRow dr1 in dsSeries.Tables[0].Rows)
              {
                  hidValues1 = hidValues1 +dr1["value"].ToString();
              }
             
             
            //t�tulo
            hcTemperatura.Title = new Title("Consumo de energia");

            //defini��es de eixos
            hcTemperatura.YAxis.Add(new YAxisItem { title = new Title("Kw/hora") });
            hcTemperatura.XAxis.Add(new XAxisItem { categories = new[] {hidXCategories1} });

            //dados
            var series = new Collection<Serie>();
            series.Add(new Serie { name = "geladeira", data = new object[]{hidValues1} });
           // series.Add(new Serie { name = "televis�o", data = new object[] { 4, 6, 7, 7, 8, 13, 11 } });

            //op��o para empilhar os gr�ficos
            hcTemperatura.PlotOptions = new Highchart.Core.PlotOptions.PlotOptionsAreaSpline { stacking = Stacking.normal, fillOpacity = 0.3 };

            //customiza��o de tooltip
            hcTemperatura.Tooltip = new ToolTip("this.x +': '+ this.y +' Kw/hora'");

            //customiza��o de legenda
            hcTemperatura.Legend = new Legend
            {
                layout = Layout.vertical,
                borderWidth = 3,
                align = Align.right,
                y = 20,
                x = -20,
                verticalAlign = Highchart.Core.VerticalAlign.top,
                shadow = true,
                backgroundColor = "#e3e6be"
            };

            //bind do controle
            hcTemperatura.DataSource = series;
            hcTemperatura.DataBind();
        }
    }
}

When the chart renders, I can the xAxis values as this string1,string2(with the commas). Please help
testmyo
Developer
Jan 20, 2012 at 7:28 AM

Hi Ghostme,

A slight problem that i can see with the code is that both hidXCategories1 and hidValues1need to be arrays whereas you have used strings for both. Changing them to arrays should fix your problem.

Let us know if you still face problems.

Regards,

Rahul

Side Note:

I personally prefer to use point objects to achieve this, instead of populating the X-Axis as you have done.

Instead of trying to assign values to Serie.data as "data = new object[]{hidValues1}", try using the PointCollection class to get a collection of Point objects (where you can set the X and Y values for each Point) that can then be used to set data for the Serie object. This is especially useful for a situation where you have multiple series in one chart, and all of them have different update frequencies (ie. most cases where I've used this library)

Alternately, you can also take all your datapoints in an numeric array and then set Serie.data with that array.

The advantage of using a simple numeric array is that it will be lighter in terms of the server load and faster to render on the client browser. Both of these are more visible when you are trying to populate a graph with 50,000-100,000 datapoints.

On the other hand, if you use the Point/PointCollection solution, you will be able to get a much more customizable point object where you can specify the x/y values of each point individually, as well as CSS styles and images for points meeting specific criteria. The disadvantage of this approach, however will be that you will need to do a lot more processing as you will need to populate objects for each point that you need to show. It will definitely be slower.

 
ghostme
Jan 20, 2012 at 7:32 PM

Hello Rahul,

 

Thanks for the quick response, I have modified the code as instructed and converted the dataset rows into an arraylist, however nothing seem to show up on the chart.

 

View the modified code 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;
using Highchart.Core.Data.Chart;
using Highchart.Core;
using Highchart;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace EntityFramework
{
    public partial class LineChart : System.Web.UI.Page
    {
        DataSet dsSeries = new DataSet();
        ArrayList hidValues1 = new ArrayList();
        ArrayList hidXCategories1 = new ArrayList();
        string[] xValues;
        int[] yValues;
        //public string hidValues1;
       // public string hidXCategories1;
        protected void Page_Load(object sender, EventArgs e)
        {
            //Exemplo01();
           
           

              
            Exemplo03();
        }
        
        public DataSet BindData()
        {
            string connString = ConfigurationManager.ConnectionStrings["Chartdata"].ToString();
            SqlConnection con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT symbol,value FROM Ticker";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void Exemplo03()
        {
              dsSeries = BindData();

              if (dsSeries == null) return;

              foreach (DataRow dr in dsSeries.Tables[0].Rows)
              {
                 hidXCategories1.Add(dr["symbol"]);
                  

              }

              foreach (DataRow dr1 in dsSeries.Tables[0].Rows)
              {
                  hidValues1.Add(dr1["value"]);
              }
             
             
            //t�tulo
            hcTemperatura.Title = new Title("Consumo de energia");

            //defini��es de eixos
            hcTemperatura.YAxis.Add(new YAxisItem { title = new Title("Kw/hora") });
            hcTemperatura.XAxis.Add(new XAxisItem { categories = new[] {hidXCategories1}});

            //dados
            var series = new Collection<Serie>();
            series.Add(new Serie { name = "geladeira", data = new object[]{hidValues1} });
           // series.Add(new Serie { name = "televis�o", data = new object[] { 4, 6, 7, 7, 8, 13, 11 } });

            //op��o para empilhar os gr�ficos
            hcTemperatura.PlotOptions = new Highchart.Core.PlotOptions.PlotOptionsAreaSpline { stacking = Stacking.normal, fillOpacity = 0.3 };

            //customiza��o de tooltip
            hcTemperatura.Tooltip = new ToolTip("this.x +': '+ this.y +' Kw/hora'");

            //customiza��o de legenda
            hcTemperatura.Legend = new Legend
            {
                layout = Layout.vertical,
                borderWidth = 3,
                align = Align.right,
                y = 20,
                x = -20,
                verticalAlign = Highchart.Core.VerticalAlign.top,
                shadow = true,
                backgroundColor = "#e3e6be"
            };

            //bind do controle
            hcTemperatura.DataSource = series;
            hcTemperatura.DataBind();
        }
    }
}
Thanks in advance

testmyo
Developer
Jan 21, 2012 at 12:46 PM

Try replacing
data = new object[]{hidValues1}

With
data = hidValues1

‘data’ needs to be an array where each element in the array represents a datapoint. Your current usage turns the first element of ‘data’ into an array, that contains the datapoints.

Try making the change and let us know if it still doesn’t work for you.

Regards,

Rahul
ghostme
Jan 22, 2012 at 10:53 PM

Hello Rahul,

 

Thank you very much for the response. Finally got it working

 

 

Here is the modified code

 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;
using Highchart.Core.Data.Chart;
using Highchart.Core;
using Highchart;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace EntityFramework
{
    public partial class LineChart : System.Web.UI.Page
    {
        DataSet dsSeries = new DataSet();
        ArrayList hidValues11 = new ArrayList();
        ArrayList hidXCategories11 = new ArrayList();
       
        object [] yValues;
        public object hidValues1;
        public string hidXCategories1;
       
        protected void Page_Load(object sender, EventArgs e)
        {
            //Exemplo01();
           
           

              
            Exemplo03();
        }
        
        public DataSet BindData()
        {
            string connString = ConfigurationManager.ConnectionStrings["Chartdata"].ToString();
            SqlConnection con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT symbol,value FROM Ticker";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void Exemplo03()
        {
              dsSeries = BindData();

              if (dsSeries == null) return;

              foreach (DataRow dr in dsSeries.Tables[0].Rows)
              {
                 hidXCategories11.Add(dr["symbol"]);
                

              }

              foreach (DataRow dr1 in dsSeries.Tables[0].Rows)
              {
                  hidValues11.Add(Convert.ToInt32( dr1["value"]));
                  yValues = hidValues11.ToArray(typeof(object)) as object[];
                  //hidValues1 = hidValues1 + dr1["value"].ToString();
                  
              }
            
            
            //t�tulo
            hcTemperatura.Title = new Title("Consumo de energia");

            //defini��es de eixos
            hcTemperatura.YAxis.Add(new YAxisItem { title = new Title("Kw/hora") });
            hcTemperatura.XAxis.Add(new XAxisItem { categories =hidXCategories11.ToArray(typeof(string))as string[] });

            //dados
            var series = new Collection<Serie>();
            series.Add(new Serie { name = "geladeira", data =yValues });
          //  series.Add(new Serie { name = "televis�o", data = new object[] { 4, 6, 7, 7, 8, 13, 11 } });

            //op��o para empilhar os gr�ficos
            hcTemperatura.PlotOptions = new Highchart.Core.PlotOptions.PlotOptionsLine { dataLabels = new Highchart.Core.PlotOptions.DataLabels { enabled = true } };

            //customiza��o de tooltip
            hcTemperatura.Tooltip = new ToolTip("this.x +': '+ this.y");

            //customiza��o de legenda
            hcTemperatura.Legend = new Legend
            {
                layout = Layout.vertical,
                borderWidth = 3,
                align = Align.right,
                y = 20,
                x = -20,
                verticalAlign = Highchart.Core.VerticalAlign.top,
                shadow = true,
                backgroundColor = "#e3e6be"
            };

            //bind do controle
            hcTemperatura.DataSource = series;
            hcTemperatura.DataBind();
        }
    }
}

I think this should be added to the example set for other asp.net developers.

 

Thank you so much
flepka
Jun 8, 2012 at 5:26 PM

Thank you for help us. In this article.
Sign in to post message or set email notifications