SQL Data Reader Binding Error ( X axis )

[kiolol]

I used TeeChart for C#.net and Data source is SqlDataReader

(Version=3.2.2894.29191)

X axis value type is Datetime ( MS-SQL Schema type "Datetime")

When i use Data-reader Binding to X axis value

Tchart Shows an abnormal data like below

* X axis value
real value 2008-03-02 ( in SQL Database )
result value 1900-01-01 ( in Teechart shows )


My source code is..

ex)

tChart.Series[0].XValues.DataMember = _xvalue;
tChart.Series[0].XValues.DateTime = true;
tChart.Series[0].YValues.DataMember = _yvalue;
tChart.Series[0].DataSource = reader;


How can i fix this Problem?

[Narcís]

Hi kiolol,

How are datetime values stored in your database? Can you reproduce the issue in a simple example as the one Christopher Ireland posted here?

Thanks in advance.

[kiolol]

When I use Dataset & Datatable Chart Drawing,
The Problem (X-axis Datetime abnormal output ) don't happen.
and using loop method (like below) don't happen also..
===========================================
while (reader.Read())
{
tChart[0].Add(Convert.ToDateTime(reader[_xvalue])
, Convert.ToDouble(reader[_yvalue]));
}
============================================

But Using Datareader Binding, Datetime shows an abnormal data

tChart.Series[0].XValues.DataMember = _xvalue;
tChart.Series[0].XValues.DateTime = true;
tChart.Series[0].YValues.DataMember = _yvalue;
tChart.Series[0].DataSource = reader;

In Database, Time data format is "2008-03-02 오후 6:14:15"
"오후" --> Korean language ( PM )

Do you want more source code ?

If you can't reproduce same situation, i'll post more code..

[Narcís]

Hi kiolol,

Could you please export your SQL table to a known format, for example XML, and send it at news://www.steema.net/steema.public.attachments newsgroup or at our upload page?

Also, have you tried reproducing the issue with an example like Christopher Ireland's?

Thanks in advance.

[kiolol]

Our MS-SQL DB Table Data is below..xml format..

//ChartTable Data WriteXml =>chartTable.xml
<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<ChartTable>
<TKINTIME>2008-02-26T10:19:00+09:00</TKINTIME>
<VALUE>21.346</VALUE>
</ChartTable>
<ChartTable>
<TKINTIME>2008-02-26T10:31:00+09:00</TKINTIME>
<VALUE>20.871</VALUE>
</ChartTable>
<ChartTable>
<TKINTIME>2008-02-26T10:42:00+09:00</TKINTIME>
<VALUE>20.602</VALUE>
</ChartTable>
<ChartTable>
<TKINTIME>2008-02-26T10:54:00+09:00</TKINTIME>
<VALUE>20.505</VALUE>
</ChartTable>
</DocumentElement>

and DB Schema is below..

//ChartTable Data WriteXmlSchema => chartschema.xml
<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="ChartTable" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="temp">
<xs:complexType>
<xs:sequence>
<xs:element name="TKINTIME" type="xs:dateTime" minOccurs="0" />
<xs:element name="VALUE" type="xs:double" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>


When we use DataReader Binding to Teechart, We use this code

//SqlDataReader Binding...
SqlDataReader reader = GetSqlReader(qurystring);
tChart.GetSeries(); //Add Series
tChart.Series[tChart.Series.Count - 1].Title = strlegnd;
tChart.Series[tChart.Series.Count - 1].XValues.DataMember = xvalue;//DateTime
tChart.Series[tChart.Series.Count - 1].YValues.DataMember = yvalue;
tChart[tChart.Series.Count - 1].XValues.DateTime = true;

tChart.Series[tChart.Series.Count - 1].DataSource = reader;
//Xvalue Binding Error
//Real Xvalue = "2008-03-27 23:05:03"
//DateTime.FromOADate(series[valueIndex].X).ToString("F") = "1900-01-03 00:00:00";// Binding Xvalue

But when we use Datatable Bind to Teechart, That error don't occur

//DataTable Binding...
DataTable dt = new DataTable;
dt = GetDataTAble();//Data Load
tChart.GetSeries(); //Add Series
tChart.Series[tChart.Series.Count - 1].Title = strlegnd;
tChart.Series[tChart.Series.Count - 1].XValues.DataMember = xvalue;//DateTime
tChart.Series[tChart.Series.Count - 1].YValues.DataMember = yvalue;
tChart[tChart.Series.Count - 1].XValues.DateTime = true;

tChart.Series[tChart.Series.Count - 1].DataSource = dt;
//Real Xvalue = "2008-03-27 23:05:03"
//DateTime.FromOADate(series[valueIndex].X).ToString("F") = "2008-03-27 23:05:03";// Binding Xvalue


Our goal is Drawing 50 charts with about 2 million data
( each have 40000 records ) in 10 minites.

Our Test about teechart drawing speed shows that datatable binding
is too slow and heavy ( memory resource ) for attaining our goal..

So.. we must use Datareader Binding but time output's error is obstacle

If you have another recommendation to this problem
( Speedy Drawing for 2 million datas ) .. PLZ help me...

[Narcís]

Hi kiolol,

We are trying to reproduce the issue here.

Could you please go to your SQL Server database, right-click on it, select "Tasks->Export Data...", select your SQL Server database as datasource and press "next", choose "Flat File" as Destination and create a .txt file when prompted, then press "next" and choose "Copy data from one or more tables or views", press "next", select problematic table at "Source table or view" and pres "Finish" to export the table into a text file.

Could you please post the file at http://www.steema.net/upload ?

Regarding the performance issues I strongly recommend you to read the "Real-time charting" article at http://www.teechart.net/reference/articles/index.php . This is a Delphi article written using TeeChart VCL but most of it is also appliable to TeeChart for .NET.

Thanks in advance.

[kiolol]

My Company's information security rule is too rigid, I can't export any
Files to outside site.

So.. I'm posting about making temporary SQL DB and Chart Drawing Full Source Code for Your reproducing.

1. Make SQL DB Table

(We use MS SQL Express 2005 )

/*Create Table*/
USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TChartTable](
[EXTKEY] [int] IDENTITY(1,1) NOT NULL,
[TKINTIME] [datetime] NULL,
[VALUE] [float] NULL,
[TABLEIDX] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

/* Test Data Insert */
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,1,getdate()),0.001,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,5,getdate()),0.003,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(m,1,getdate()),0.009,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,8,getdate()),0.02,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,3,getdate()),0.0051,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,11,getdate()),0.04,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,20,getdate()),0.007,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,16,getdate()),0.004,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,26,getdate()),0.011,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,2,getdate()),0.003,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,10,getdate()),0.011,1)
insert into [dbo].[TChartTable](TKINTIME,VALUE,TABLEIDX)
values(dateadd(d,15,getdate()),0.006,1)


2. Chart Drawing Source Code

/* C# Code - TChart Drawing */
private static string _ConnectionStr1 = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=True;Pooling=False";
private static SqlConnection _con;
private static SqlCommand _cmd;
private Steema.TeeChart.TChart _tChart;

private SqlDataReader SqlReader(string strqury)
{
_con = new SqlConnection(_ConnectionStr1); ;
_cmd = new SqlCommand();
_cmd.Connection = _con;
SqlDataReader reader = null;

try
{
_cmd.Parameters.Clear();
_cmd.CommandText = strqury;
reader = _cmd.ExecuteReader();
return reader;
}
catch (Exception e)
{
new MassException(e.Message);
return reader;
}
}

private void ChartDraw()
{
Initialize();
SqlDataReader reader = SqlReader("select TKINTIME,VALUE FROM TChartTable");
if(reader != null && reader.HasRows)
{
GetPoint();
_tChart.Series[_tChart.Series.Count - 1].Title = strlegnd;
_tChart.Series[_tChart.Series.Count - 1].Marks.Text = strqury;
_tChart.Series[_tChart.Series.Count - 1].XValues.DataMember = "TKINTIME";
_tChart.Series[_tChart.Series.Count - 1].YValues.DataMember = "VALUE";
_tChart[_tChart.Series.Count - 1].XValues.DateTime = true;

_tChart.Series[_tChart.Series.Count - 1].DataSource = reader;
}

Form frm = new Form();
tChart.Dock = DockStyle.Fill;
frm.Controls.Add(tChart);
frm.Size = new Size(500, 300);
frm.Show();
}

public void Initialize()
{
_tChart = new Steema.TeeChart.TChart();
_tChart.Axes.Bottom.Labels.Angle = 90;
_tChart.Axes.Bottom.MaximumOffset = 10;
_tChart.Axes.Bottom.MinimumOffset = 10;
_tChart.Axes.Left.MaximumOffset = 10;
_tChart.Axes.Left.MinimumOffset = 10;
_tChart.Aspect.Chart3DPercent = 0;
_tChart.Aspect.Orthogonal = false;
_tChart.Aspect.View3D = false;
_tChart.Axes.Bottom.Grid.Visible = false;
_tChart.Axes.Bottom.Labels.Font.Name = "Tahoma";
_tChart.Axes.Bottom.Labels.Font.Size = _defaultfontsize;
_tChart.Axes.Bottom.Labels.Font.SizeFloat = _defaultfontsize;
_tChart.Axes.Bottom.Title.Font.Name = "Arial";
_tChart.Axes.Left.Grid.Style = System.Drawing.Drawing2D.DashStyle.DashDotDot;
_tChart.Axes.Left.Labels.Font.Name = "Tahoma";
_tChart.Axes.Left.Labels.Font.Size = _defaultfontsize;
_tChart.Axes.Left.Labels.Font.SizeFloat = _defaultfontsize;
_tChart.Axes.Left.Title.Font.Name = "Arial";
_tChart.Axes.Right.Labels.Font.Name = "Tahoma";
_tChart.Axes.Right.Labels.Font.Size = _defaultfontsize;
_tChart.Axes.Right.Labels.Font.SizeFloat = _defaultfontsize;
_tChart.BackColor = System.Drawing.Color.White;
_tChart.Cursor = System.Windows.Forms.Cursors.Default;
_tChart.Header.Font.Bold = true;
_tChart.Header.Font.Brush.Color = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(0)))), ((int)(((byte)(0)))));
_tChart.Header.Font.Name = "Arial";
_tChart.Header.Lines = new string[] {
""};
_tChart.Header.Shadow.Height = 1;
_tChart.Header.Shadow.Width = 1;
_tChart.Legend.Font.Size = _defaultfontsize;
_tChart.Legend.Font.SizeFloat = _defaultfontsize;
_tChart.Legend.Pen.Color = System.Drawing.Color.FromArgb(((int)(((byte)(128)))), ((int)(((byte)(128)))), ((int)(((byte)(128)))));
_tChart.Legend.Shadow.Visible = false;
_tChart.Legend.Title.Font.Bold = false;
_tChart.Legend.Title.Font.Name = "Arial";
_tChart.Legend.Title.Font.Size = _defaultfontsize;
_tChart.Legend.Title.Font.SizeFloat = _defaultfontsize;
_tChart.Legend.Title.Pen.Visible = false;
_tChart.Location = new System.Drawing.Point(0, 0);
_tChart.Margin = new System.Windows.Forms.Padding(0);
_tChart.Name = "tChart";
_tChart.Panel.Brush.Color = System.Drawing.Color.White;
_tChart.Panel.Brush.Gradient.Visible = false;
_tChart.Panel.MarginBottom = 1;
_tChart.Panel.MarginLeft = 1;
_tChart.Panel.MarginRight = 1;
_tChart.Panel.MarginTop = 1;
_tChart.Size = new System.Drawing.Size(466, 264);
_tChart.SubHeader.Font.Name = "Arial";
_tChart.SubHeader.Visible = true;
_tChart.TabIndex = _index;
_tChart.Walls.Back.Brush.Color = System.Drawing.Color.White;
_tChart.Walls.Back.Brush.Gradient.Visible = false;
_tChart.Walls.Back.Pen.Color = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(192)))), ((int)(((byte)(192)))));
_tChart.ClickSeries += new Steema.TeeChart.TChart.SeriesEventHandler(tChart_ClickSeries);

_tChart.Panel.Bevel.Outer = Steema.TeeChart.Drawing.BevelStyles.None;
_tChart.Panel.Pen.Visible = true;
_tChart.Panel.Pen.Width = 1;
_tChart.Panel.Pen.Color = Color.Black;

_tChart.Zoom.Allow = false;
_tChart.Zoom.KeyShift = (Keys)Enum.Parse(typeof(Keys), "Shift");
_tChart.Axes.Bottom.Labels.DateTimeFormat = "MM-dd";
}

public void GetPoint()
{
Steema.TeeChart.Styles.Points pointSerie = new Steema.TeeChart.Styles.Points();
pointSerie.ClickPointer += new Steema.TeeChart.Styles.CustomPoint.ClickPointerEventHandler(Series_Clicked);
pointSerie.MouseEnter += new EventHandler(Series_MouseEnter);
pointSerie.MouseLeave += new EventHandler(Series_MouseLeave);

pointSerie.Pointer.Brush.Color = Color.Blue;
pointSerie.Pointer.Style = Steema.TeeChart.Styles.PointerStyles.Circle;

pointSerie.Pointer.Visible = true;
pointSerie.Pointer.VertSize = _statusSeriessize; ;
pointSerie.Pointer.HorizSize = _statusSeriessize; ;
_tChart.Series.Add(pointSerie);
}

I think that this code's performance ( Chart Drawing Speed ) is very good

but X-value output error is only one remaining problem for appling to our

system.

[Narcís]

Hi kiolol,

Thanks for database information and code.

We couldn't reproduce the problem here using the code below in a Korean Windows 2003 Server, Korean Visual Studio 2005 and Korean SQL Server 2005.

Does the code below work fine at your end? Should we change anything to reproduce the problem here?

Thanks in advance.

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace KoreanSQLServer
{
	public partial class Form1 : Form
	{
		public Form1()
		{
			InitializeComponent();
			InitializeChart();
		}

		private void InitializeChart()
		{
			ChartDraw();
		}

		/* C# Code - TChart Drawing */
		//private static string _ConnectionStr1 = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=True;Pooling=False"; 
		private static string _ConnectionStr1 = @"Data Source=localhost;Initial Catalog=tempdb;Integrated Security=True;Pooling=False";
		private static System.Data.SqlClient.SqlConnection _con;
		private static System.Data.SqlClient.SqlCommand _cmd;
		private Steema.TeeChart.TChart _tChart;

		private System.Data.SqlClient.SqlDataReader SqlReader(string strqury)
		{
			_con = new System.Data.SqlClient.SqlConnection(_ConnectionStr1);
			_con.Open();
			_cmd = new System.Data.SqlClient.SqlCommand();
			_cmd.Connection = _con;
			System.Data.SqlClient.SqlDataReader reader = null;

			try
			{
				_cmd.Parameters.Clear();
				_cmd.CommandText = strqury;
				reader = _cmd.ExecuteReader();
				return reader;
			}
			catch (Exception e)
			{
				//new MassException(e.Message);
				MessageBox.Show(e.Message);
				return reader;
			}
		}

		private void ChartDraw()
		{
			Initialize();
			System.Data.SqlClient.SqlDataReader reader = SqlReader("select TKINTIME,VALUE FROM TChartTable");
			if (reader != null && reader.HasRows)
			{
				GetPoint();
				//_tChart.Series[_tChart.Series.Count - 1].Title = "";
				//_tChart.Series[_tChart.Series.Count - 1].Marks.Text = "";
				_tChart.Series[_tChart.Series.Count - 1].XValues.DataMember = "TKINTIME";
				_tChart.Series[_tChart.Series.Count - 1].YValues.DataMember = "VALUE";
				_tChart[_tChart.Series.Count - 1].XValues.DateTime = true;

				_tChart.Series[_tChart.Series.Count - 1].DataSource = reader;
			}

			//Form frm = new Form();
			_tChart.Dock = DockStyle.Fill;
			this.Controls.Add(_tChart);
			this.Size = new Size(500, 300);
			//this.Show();
		}

		public void Initialize()
		{
			_tChart = new Steema.TeeChart.TChart();
			_tChart.Axes.Bottom.Labels.Angle = 90;
			_tChart.Axes.Bottom.MaximumOffset = 10;
			_tChart.Axes.Bottom.MinimumOffset = 10;
			_tChart.Axes.Left.MaximumOffset = 10;
			_tChart.Axes.Left.MinimumOffset = 10;
			_tChart.Aspect.Chart3DPercent = 0;
			_tChart.Aspect.Orthogonal = false;
			_tChart.Aspect.View3D = false;
			_tChart.Axes.Bottom.Grid.Visible = false;
			_tChart.Axes.Bottom.Labels.Font.Name = "Tahoma";
			//_tChart.Axes.Bottom.Labels.Font.Size = _defaultfontsize;
			//_tChart.Axes.Bottom.Labels.Font.SizeFloat = _defaultfontsize;
			_tChart.Axes.Bottom.Title.Font.Name = "Arial";
			_tChart.Axes.Left.Grid.Style = System.Drawing.Drawing2D.DashStyle.DashDotDot;
			_tChart.Axes.Left.Labels.Font.Name = "Tahoma";
			//_tChart.Axes.Left.Labels.Font.Size = _defaultfontsize;
			//_tChart.Axes.Left.Labels.Font.SizeFloat = _defaultfontsize;
			_tChart.Axes.Left.Title.Font.Name = "Arial";
			_tChart.Axes.Right.Labels.Font.Name = "Tahoma";
			//_tChart.Axes.Right.Labels.Font.Size = _defaultfontsize;
			//_tChart.Axes.Right.Labels.Font.SizeFloat = _defaultfontsize;
			_tChart.BackColor = System.Drawing.Color.White;
			_tChart.Cursor = System.Windows.Forms.Cursors.Default;
			_tChart.Header.Font.Bold = true;
			_tChart.Header.Font.Brush.Color = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(0)))), ((int)(((byte)(0)))));
			_tChart.Header.Font.Name = "Arial";
			_tChart.Header.Lines = new string[] {
""};
			_tChart.Header.Shadow.Height = 1;
			_tChart.Header.Shadow.Width = 1;
			//_tChart.Legend.Font.Size = _defaultfontsize;
			//_tChart.Legend.Font.SizeFloat = _defaultfontsize;
			_tChart.Legend.Pen.Color = System.Drawing.Color.FromArgb(((int)(((byte)(128)))), ((int)(((byte)(128)))), ((int)(((byte)(128)))));
			_tChart.Legend.Shadow.Visible = false;
			_tChart.Legend.Title.Font.Bold = false;
			_tChart.Legend.Title.Font.Name = "Arial";
			//_tChart.Legend.Title.Font.Size = _defaultfontsize;
			//_tChart.Legend.Title.Font.SizeFloat = _defaultfontsize;
			_tChart.Legend.Title.Pen.Visible = false;
			_tChart.Location = new System.Drawing.Point(0, 0);
			_tChart.Margin = new System.Windows.Forms.Padding(0);
			_tChart.Name = "tChart";
			_tChart.Panel.Brush.Color = System.Drawing.Color.White;
			_tChart.Panel.Brush.Gradient.Visible = false;
			_tChart.Panel.MarginBottom = 1;
			_tChart.Panel.MarginLeft = 1;
			_tChart.Panel.MarginRight = 1;
			_tChart.Panel.MarginTop = 1;
			_tChart.Size = new System.Drawing.Size(466, 264);
			_tChart.SubHeader.Font.Name = "Arial";
			_tChart.SubHeader.Visible = true;
			//_tChart.TabIndex = _index;
			_tChart.Walls.Back.Brush.Color = System.Drawing.Color.White;
			_tChart.Walls.Back.Brush.Gradient.Visible = false;
			_tChart.Walls.Back.Pen.Color = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(192)))), ((int)(((byte)(192)))));
			//_tChart.ClickSeries += new Steema.TeeChart.TChart.SeriesEventHandler(tChart_ClickSeries);

			_tChart.Panel.Bevel.Outer = Steema.TeeChart.Drawing.BevelStyles.None;
			_tChart.Panel.Pen.Visible = true;
			_tChart.Panel.Pen.Width = 1;
			_tChart.Panel.Pen.Color = Color.Black;

			_tChart.Zoom.Allow = false;
			_tChart.Zoom.KeyShift = (Keys)Enum.Parse(typeof(Keys), "Shift");
			_tChart.Axes.Bottom.Labels.DateTimeFormat = "MM-dd";

			_tChart.DoubleClick += new EventHandler(_tChart_DoubleClick);
		}

		void _tChart_DoubleClick(object sender, EventArgs e)
		{
			Steema.TeeChart.Editor.Show(_tChart);
		}

		public void GetPoint()
		{
			Steema.TeeChart.Styles.Points pointSerie = new Steema.TeeChart.Styles.Points();
			//pointSerie.ClickPointer += new Steema.TeeChart.Styles.CustomPoint.ClickPointerEventHandler(Series_Clicked);
			//pointSerie.MouseEnter += new EventHandler(Series_MouseEnter);
			//pointSerie.MouseLeave += new EventHandler(Series_MouseLeave);

			pointSerie.Pointer.Brush.Color = Color.Blue;
			pointSerie.Pointer.Style = Steema.TeeChart.Styles.PointerStyles.Circle;

			pointSerie.Pointer.Visible = true;
			//pointSerie.Pointer.VertSize = _statusSeriessize; ;
			//pointSerie.Pointer.HorizSize = _statusSeriessize; ;
			_tChart.Series.Add(pointSerie);
		} 
	}
}