1. Home
  2. Forum
  3. WinForms
  4. Temperature Chart that populates coming from Database

Temperature Chart that populates coming from Database

Hi there,

is there an example of Syncfusion Chart Winform with
Temperature Chart that populates coming from Database

I am doing a project that needs to read Temperature Data and Dates from the database. hope someone could help me.

Regards,
Mel

26 Replies 1 reply marked as answer
YP Yuvaraj Palanisamy Syncfusion Team April 27, 2021 10:00 AM UTC

Hi Mel Ceniza, 
 
Greetings from Syncfusion. 
 
 We have in-built support for binding the chart with a BindingSource, DataTable, DataSet, implementations of IEnumerable and IList. To directly import SQLite data source and bind with chart, we have created custom data binding model by implementing the IChartSeriesModel interface as per the below code snippet.   
  
[C#]  
  
            foreach (ChartSeries series in this.chartControl1.Series)  
            {  
                series.SeriesModel = new SQLiteDataBindingModel(connectionString, command, "Year", new string[]{ "Sales" });  
            }  
  
  
Please refer the following code snippet to create a custom data bind model for binding chart with SQLite database  
  
[C#]  
  
    /// <summary>  
    /// Custom implementation of IChartSeriesModel to directly bind with SQLite data base  
    /// </summary>  
    public class SQLiteDataBindingModel : IChartSeriesModel, IDisposable  
    {  
        #region Private variables  
        DataTable table = null;  
        string m_connectionString = "";  
        string m_SelectCommand = "";  
        #endregion  
  
        #region Constructor  
        /// <summary>  
        /// Constructor to get the required parameters for database connection  
        /// </summary>  
        /// <param name="connectionString">Connection string to connect with the database</param>  
        /// <param name="command">Command to retrieve data from database</param>  
        /// <param name="xName">Field name in table corresponding to X value in chart series</param>  
        /// <param name="yNames">Field name in table corresponding to Y value in chart series</param>  
        public SQLiteDataBindingModel(string connectionString, string command, string xName, string[] yNames)  
        {  
            XName = xName;  
            YNames = yNames;  
            ConnectionString = connectionString;  
            Command = command;  
        }  
        #endregion  
  
        #region Public Properties  
        /// <summary>  
        /// Connection string for the database  
        /// </summary>  
        public string ConnectionString {   
            get {  
                return m_connectionString;  
            }  
            set {  
                m_connectionString = value;  
                if (!string.IsNullOrEmpty(m_connectionString) && !string.IsNullOrEmpty(m_SelectCommand))  
                {  
                    this.DataBind();  
                }  
            }  
        }  
  
        /// <summary>  
        /// Command to be executed on the database  
        /// </summary>  
        public string Command  
        {  
            get  
            {  
                return m_SelectCommand;  
            }  
            set  
            {  
                m_SelectCommand = value;  
                if (!string.IsNullOrEmpty(m_connectionString) && !string.IsNullOrEmpty(m_SelectCommand))  
                {  
                    this.DataBind();  
                }  
            }  
        }  
  
        /// <summary>  
        /// Field name to bind with X values in chart series  
        /// </summary>  
        public string XName { get; set; }  
  
        /// <summary>  
        /// Field name to bind with Y values in chart series  
        /// </summary>  
        public string[] YNames { get; set; }  
        #endregion  
  
        #region Public Methods  
        /// <summary>  
        /// Opens connection, executes command and retrieves the data from SQLite data base  
        /// </summary>  
        public void DataBind()  
        {  
            try  
            {  
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(Command, ConnectionString);  
                if (table == null)  
                    table = new DataTable();  
                else  
                {  
                    table.Clear();  
                }  
                adapter.Fill(table);  
               adapter.Dispose();  
            }  
            catch (Exception ex)  
            {  
                throw ex;  
            }  
        }  
        #endregion  
  
        #region IChartSeriesModel implementation  
        /// <summary>  
        /// Data count  
        /// </summary>  
        public int Count  
        {  
            get  
            {  
                if (table != null)  
                {  
                    return table.Rows.Count;  
                }  
                return 0;  
            }  
        }  
  
        public event ListChangedEventHandler Changed;  
  
        /// <summary>  
        /// Checks for empty data  
        /// </summary>  
        /// <param name="xIndex"></param>  
        /// <returns></returns>  
        public bool GetEmpty(int xIndex)  
        {  
            //Modify this code if empty data is returned from database  
            return table != null ? xIndex >= table.Rows.Count : true;  
        }  
  
        /// <summary>  
        /// Gets the X value in data table and bind with chart series  
        /// </summary>  
        /// <param name="xIndex">Index of the data point</param>  
        /// <returns>X value of the data point</returns>  
        public double GetX(int xIndex)  
        {  
            if (table != null)  
            {     
                if (!string.IsNullOrEmpty(XName))  
                    return double.Parse(table.Rows[xIndex][XName].ToString());  
                else  
                    return xIndex;  
            }  
            else  
                throw new Exception("Call DataBind method before accessing X values");  
        }  
  
        /// <summary>  
        /// Gets the Y values from data table and bind it with chart series  
        /// </summary>  
        /// <param name="yIndex">Index of the data point</param>  
        /// <returns>Y values of the data point</returns>  
        public double[] GetY(int yIndex)  
        {  
            if (table != null)  
            {  
                int count = YNames.Length, index = 0;  
                if (count > 0)  
                {  
                    double[] result = new double[count];  
                    foreach (string yName in YNames)  
                    {  
                        try  
                        {  
                            result[index++] = double.Parse(table.Rows[yIndex][yName].ToString());  
                        }  
                        catch (Exception ex)  
                        {  
                            throw ex;  
                        }  
  
                    }  
                    return result;  
                }  
                else  
                    throw new Exception("A field name for Y value must be specified");  
            }  
            else  
                throw new Exception("Call DataBind method before accessing Y values");  
        }  
        #endregion  
  
        #region IDisposable implementation  
        /// <summary>  
        /// Dispose the data table after using it  
        /// </summary>  
        public void Dispose()  
        {  
            if (table != null)  
            {  
                table.Dispose();  
                table = null;  
            }  
        }  
        #endregion  
    }  
  
  
We have prepared a simple sample for binding Chart directly with SQLite data source and it can be downloaded from the following link  
  
 
For more details, please refer the below link 
 
Regards, 
Yuvaraj. 


Marked as answer
ME Mel April 27, 2021 10:59 PM UTC

Hi Yuvaraj,

I am using MS SQL server 2019. not sure if they have the same function with SQLite. 

Also I attached my screenshot that describe of what i want to achieve.
Y axis is temperature
X axis is date
and There's a comment in the cross point where the temperature reading is above the normal temperature limit

Please see attached image


SM Saravanan Madheswaran Syncfusion Team April 28, 2021 10:37 AM UTC

Hi Ceniza, 
 
We would like to let you know once again that the Windows Forms Chart has in-built support for binding the chart with a BindingSource, DataTable, DataSet, implementations of IEnumerable and IList. Hence, we need to convert our DB to any one of these formats as discussed above.  
 
Please check the guidelines described above and check below links for example. 
 
 
 
Query: There's a comment in the cross point where the temperature reading is above the normal temperature 
 
This will be achieved through cross hair interaction support or can draw custom view, please check the guidelines below.  
 
 
 
Regards, 
Saravanan.  


ME Mel April 28, 2021 08:36 PM UTC

I've been through those guidelines but nothings work, I just need a working example to follow along. and get on with my project

ME Mel replied to Saravanan Madheswaran April 28, 2021 08:37 PM UTC

Hi Ceniza, 
 
We would like to let you know once again that the Windows Forms Chart has in-built support for binding the chart with a BindingSource, DataTable, DataSet, implementations of IEnumerable and IList. Hence, we need to convert our DB to any one of these formats as discussed above.  
 
Please check the guidelines described above and check below links for example. 
 
 
 
Query: There's a comment in the cross point where the temperature reading is above the normal temperature 
 
This will be achieved through cross hair interaction support or can draw custom view, please check the guidelines below.  
 
 
 
Regards, 
Saravanan.  


I've been through those guidelines but nothings work, I just need a working example to follow along. and get on with my project

SM Saravanan Madheswaran Syncfusion Team April 29, 2021 04:00 PM UTC

Hi Ceniza, 
 
We check for the possibility to provided working sample and update you the status in two business days (3rd May 2021). We appreciate your patience until then.  
 
Regards, 
Saravanan.  


YP Yuvaraj Palanisamy Syncfusion Team May 3, 2021 01:59 PM UTC

Hi Mel Ceniza, 
 
Thanks for your patience. 
 
We have prepared the sample for your requirement “Chart with Sql server data”. Please find the code example below. 
 
CodeSnippet: 
//Get the data from database and store it in data table 
string connectionString = @"Data Source=ChartData.sdf"; 
string queryString = "Select * from SimpleTable"; 
SqlCeConnection connection = new SqlCeConnection(connectionString); 
SqlCeCommand command = new SqlCeCommand(queryString, connection); 
SqlCeDataAdapter adapter = new SqlCeDataAdapter(command); 
DataTable data = new DataTable(); 
adapter.Fill(data); 
 
#region Chart control for tab 1 
 
//Bind data table with Chart Series 
ChartSeries series = new ChartSeries("Series", ChartSeriesType.Column);             
series.SeriesModel = new ChartDataBindModel(data) {  
     
    //Name of the field in data source to bind with X-values (values along X-axis) 
    XName = "LineNo", 
 
    //Name of the field in data source to bind with Y-values (values along Y-axis) 
    //Here Y value is an array because series like HiLoOpenClose, Candle, etc.., requires more than 1 Y value 
    YNames = new string[]{ "StdWt" } }; 
 
chart.Series.Add(series); 
 
Also, we have attached the sample for your reference. Please find the sample from the below link. 
 
  
Regards, 
Yuvaraj. 


ME Mel May 3, 2021 09:10 PM UTC

Hi Yuvaraj,

Your sample is too complicated.. I just need a simple solution.. with x and y axis which is the temperature and the dates of the chart... 
so it won't work for me. but thank you though for trying.

Also I am using the latest version of Syncfusion.. I can not run your sample it gives me error. because you bundle the old version..




YP Yuvaraj Palanisamy Syncfusion Team May 4, 2021 02:34 PM UTC

Hi Mel Ceniza, 
 
We have modified the simple chart sample with SQL server data by removing the Combo box for selected item change the chart data. Please find the modified sample from the below link. 
 
  
Regards, 
Yuvaraj 


ME Mel replied to Yuvaraj Palanisamy May 4, 2021 11:09 PM UTC


Hi Mel Ceniza, 
 
We have modified the simple chart sample with SQL server data by removing the Combo box for selected item change the chart data. Please find the modified sample from the below link. 
 
  
Regards, 
Yuvaraj 


Hi Yuvaraj ,

I manage to show the temperature date in the chart, but I need to start  the date with the date of my Query. If you could refer my screen shot, the date start at 02/05/2021 instead of starting at 05/05/2021 and end at the same date 05/05/2021 and display the time.

I want to occupy the whole chart with the date timestamp of my query and also I want the temperature to start with 0 to -30.  I included the sample data below so that you can re create the chart with my query



Code..... and table sample below

      string queryString = @"SELECT TimeStamp, Temperature FROM   BuildingTemperatures WHERE Channel = '304' and  YEAR(TimeStamp) = 2021 and MONTH(TimeStamp) = 5 and DAY(TImeStamp) = 3";
      SqlCommand cmd2 = new SqlCommand(queryString, con);
      SqlDataAdapter  adapter = new SqlDataAdapter(cmd2);
      DataTable data = new DataTable();
      adapter.Fill(data);

      chartControl1.Legends.Clear();
      //Bind data table with Chart Series
      ChartSeries series = new ChartSeries("Series", ChartSeriesType.Spline);
      
      series.SeriesModel = new ChartDataBindModel(data)
      {

        //Name of the field in data source to bind with X-values (values along X-axis)
        XName = "TimeStamp",

        //Name of the field in data source to bind with Y-values (values along Y-axis)
        //Here Y value is an array because series like HiLoOpenClose, Candle, etc.., requires more than 1 Y value
        YNames = new string[] { "Temperature" }

      };


Table Sample
----------------------------------------------------------
TimeStamp                             Temperature
2021-05-03 14:31:57.327 -22.2
2021-05-03 15:48:02.527 -21.74
2021-05-03 15:58:02.710 -22.47
2021-05-03 16:08:02.647 -22.96
2021-05-03 17:38:02.797 -24.81
2021-05-03 17:41:57.530 -24.81
2021-05-03 17:48:02.697 -24.96
2021-05-03 17:51:57.513 -25.06
2021-05-03 17:58:02.700 -25.06
2021-05-03 18:01:59.310 -25.08
2021-05-03 18:08:02.773 -25.16
2021-05-03 18:11:59.280 -25.25
2021-05-03 18:18:02.780 -25.35
2021-05-03 18:21:59.340 -25.35
2021-05-03 18:28:02.777 -25.45
2021-05-03 18:31:59.293 -25.42
2021-05-03 18:38:02.860 -25.55
2021-05-03 18:41:59.320 -25.57
2021-05-03 14:51:57.827 -21.98
2021-05-03 15:11:57.317 -19.25
2021-05-03 18:48:02.823 -25.62
2021-05-03 18:58:02.793 -25.67
2021-05-03 15:01:57.310 -21.88
2021-05-03 15:28:03.633 -20.59
2021-05-03 18:51:59.323 -25.62
2021-05-03 19:01:59.310 -25.77
2021-05-03 19:11:59.330 -25.79
2021-05-03 19:21:59.333 -25.81
2021-05-03 19:31:59.337 -25.94
2021-05-03 19:41:59.340 -25.96
2021-05-03 19:51:59.343 -25.91
2021-05-03 20:01:59.330 -25.89
2021-05-03 20:11:59.393 -25.94
2021-05-03 20:21:59.367 -26.01
2021-05-03 20:31:59.383 -26.06




YP Yuvaraj Palanisamy Syncfusion Team May 5, 2021 04:19 PM UTC

Hi Mel Ceniza, 
 
Currently we are working on this and we will update you with complete details on or before 7th May 2021. 
 
Regards, 
Yuvaraj. 


YP Yuvaraj Palanisamy Syncfusion Team May 7, 2021 03:37 PM UTC

Hi Mel Ceniza, 

Thanks for your patience. 

We have prepared the chart sample and chart data retrieve from SQL database server with DataTime data in Windows forms. Also, we have attached the database script file for your reference. Please find the sample from the below link. 

  
Please find the database script file from the below link 

  
Output: 
 

Regards, 
Yuvaraj.

ME Mel May 10, 2021 01:43 AM UTC

Hi Yuvaraj,

another favor.. ho could i put comments box in the highest point  which says "this is the highest point in the graph.

Regards,
Mel


YP Yuvaraj Palanisamy Syncfusion Team May 10, 2021 12:49 PM UTC

Hi Mel Ceniza, 

We have achieved your requirement “To show text at highest datapoint value” with the help of ChartCustomPoint support in Windows forms Chart. Please find the code example below for your reference. 

CodeSnippet: 
List<double> yValueList = new List<double>(); 

foreach (DataRow row in data.Rows) 
{ 
    yValueList.Add((double)row.ItemArray[1]); 
} 

ChartCustomPoint cp = new ChartCustomPoint(); 
cp.Alignment = ChartTextOrientation.Up; 
cp.PointIndex = yValueList.IndexOf(yValueList.Max()); 
cp.SeriesIndex = 0; 
cp.Text = "Highest Point"; 
cp.CustomType = Syncfusion.Windows.Forms.Chart.ChartCustomPointType.PointFollow; 
cp.Symbol.Shape = ChartSymbolShape.Diamond; 
cp.Offset = 10; 
cp.Font.Bold = true; 
cp.Font.Facename = "Verdana"; 
cp.Font.Size = 10F; 
this.chartControl1.CustomPoints.Add(cp); 
this.chartControl1.Series.Add(series); 

Output: 
 
 
For more details, please refer the below link. 
 
Regards, 
Yuvaraj. 


ME Mel May 12, 2021 02:11 AM UTC

Hi Yuvaraj,

I still not get the starting point of the X axis that the date should start at 0 point fix..  as when i put another starting date the starting point of the graph is ahead of 4 points  not starting the 0 point in the beginning of the Y axis

YP Yuvaraj Palanisamy Syncfusion Team May 12, 2021 12:48 PM UTC

Hi Mel, 
 
We would like to let you know that the default RangePaddingType becomes Calculate to add extra interval of start and end range of axis for better understanding. You can overcome this by setting of RangePaddingType to be None as below code example. 
 
 
this.chart.PrimaryXAxis.RangePaddingType = ChartAxisRangePaddingType.None; 
 
 
For more details, please refer the below link 
 
 
 
Regards, 
Yuvaraj. 


ME Mel May 12, 2021 08:49 PM UTC

Hi Yuvaraj,

I tried 
the code 
this.chart.PrimaryXAxis.RangePaddingType = ChartAxisRangePaddingType.None; 

but still giving me a padding  see the attached image below


YP Yuvaraj Palanisamy Syncfusion Team May 13, 2021 05:43 PM UTC

Hi Mel, 
 
We can achieve your requirement to show the accurate data range of datapoint value in axis by setting RangeType to Set and DateTimeRange as per the below code example. 
 
CodeSnippet: 
 
var primaryXAxis = this.chartControl1.PrimaryXAxis; 
this.chartControl1.PrimaryXAxis.RangeType = ChartAxisRangeType.Set; 
 
List<ChartPoint> chartPoints = new List<ChartPoint>(); 
for(int i=0; i < series1.Points.Count; i++) 
{ 
    chartPoints.Add(series1.Points[i]); 
} 
var minDate = new DateTime(chartPoints.Min(s => s.DateX.Ticks)); 
var MaxDate = new DateTime(chartPoints.Max(s => s.DateX.Ticks)); 
this.chartControl1.PrimaryXAxis.DateTimeRange = new ChartDateTimeRange(minDate, MaxDate, primaryXAxis.DesiredIntervals, primaryXAxis.IntervalType); 
 
Output: 
 
 
For more details, please refer the below link 
 
Regards, 
Yuvaraj. 


ME Mel May 20, 2021 04:08 AM UTC

Hi Yuvaraj,

Just one more favor about the chart

How to change the color of every line in the chart that correspond the data. the rendering of the color in series chart line are random.. just need to set color every line in the chart. please see the below image.

Chart Color

YP Yuvaraj Palanisamy Syncfusion Team May 21, 2021 09:37 AM UTC

Hi Mel, 
 
We can change the color of every line in the chart by using the Interior property of chart series style. Please find the code example below.  
 
CodeSnippet: 
 
chartseries.Style.Interior = new BrushInfo(Color.Blue); 
 
 
Please let us know if you have any further assistance. 
 
Regards, 
Yuvaraj. 


ME Mel June 2, 2021 01:39 AM UTC

Hi Yuvaraj,

Have you tried sfdatagrid to load the data faster that query from the database?

Regards,
Mel


GM Gayathri Manickam Syncfusion Team June 2, 2021 04:01 PM UTC

Hi Mel Ceniza,  
 
We are validating your query and we will update you the complete status along with runnable sample in two business days. We appreciate your patience until then.  
 
Regards, 
Gayathri M. 


GM Gayathri Manickam Syncfusion Team June 7, 2021 02:49 AM UTC

Hi Mel Ceniza,   
 
Sorry for the delay. We are facing some difficulties and we need some more time to achieve your requirement. Hence, we will update the complete details on 8th June 2021. 
 
Regards, 
Gayathri M. 


GM Gayathri Manickam Syncfusion Team June 8, 2021 01:50 PM UTC

Hi Mel Ceniza,    
 
Thanks for your patience. We have prepared a chart sample based on your query to fetch data from the SfDatagrid. Please find the sample in the given below link.  
 
 
Please share us more details if we misunderstood your requirement.  
 
Regards, 
Gayathri M. 


ME Mel June 10, 2021 01:38 AM UTC

HI Gayathri M,

It's great the sample, but i need a paging which is a million data reading records... as the temperature reading will save into the database every minute.
I just need to know how to make it efficient when loading the data from the database.

SM Saravanan Madheswaran Syncfusion Team June 10, 2021 12:29 PM UTC

Hi Mel,  
 
Thanks for the update. We have converted data grid source into chart points in the most recent updated sample; as you stated, that need to load chart each time data is updated in the database; this will be accomplished by adding new chart points to the Series Points collection. 
 
Please check some of our populating data methods for chart suggested in the below link.   
 
  
If we misunderstood your query, please clarify your requirement with more details.  
 
Regards, 
Saravanan.  


Loader.
Up arrow icon