-
Sep 29th, 2015, 04:44 AM
#1
Thread Starter
Member
[RESOLVED] VB.Net Multiple DataSources for Single Line Type Chart
I am having a single chart control on a form that works fine during runtime. However, I need to use the same chart control to show data from different datasources (rather different SQL statements). The number of datasources can range upto 25 or even more. With each datasource, the title of the graph needs to change. The X-Axis and Y-Axis values are of the same type.
How can I use a timer to display graph for one datasource for a specified period of time and then it changes to another datasource?
Any tips would be appreciated.
Regards,
-
Sep 29th, 2015, 06:25 AM
#2
Re: VB.Net Multiple DataSources for Single Line Type Chart
you can create Chart objects via code. this is some snippet from existing code i have that queries the data, sets up the Chart and exports it as png:
(hope you dont feel offended by the german comments...)
Code:
Private Sub WriteChart(ByVal CenterCode As String, ByVal DataType As String, ByVal Title As String, ByVal FileName As String)
'Charting
Dim oChart As New System.Windows.Forms.DataVisualization.Charting.Chart
oChart.ChartAreas.Add("dummy")
'Die maximale Balkenhöhe/Punktwert ist auf 80 Minuten gekappt!
Dim oChartCmd As New OleDb.OleDbCommand("SELECT ChartTime" &
",IIF(ISNULL(T1.avgMinutes),0,IIF(T1.avgMinutes>80,80,T1.avgMinutes)) AS avgMin" &
",IIF(ISNULL(minMinutes),0,IIF(minMinutes>80,80,minMinutes)) AS minMin" &
",IIF(ISNULL(maxMinutes),0,IIF(maxMinutes>80,80,maxMinutes)) AS maxMin" &
",IIF(ISNULL(CountRows),0,CountRows) AS ct " &
",IIF(ISNULL(T2.avgMinutes),0,IIF(T2.avgMinutes>80,80,T2.avgMinutes)) AS avgMinDay " &
",IIF(ISNULL(T1.avgMinutes),0,IIF(T1.avgMinutes>80,80,T1.avgMinutes)) AS avgMinLabel " &
"FROM (tblChartTimes " &
"LEFT JOIN (SELECT Format(dtRetrieved,'HH:MM') as dt,CountRows,avgMinutes,minMinutes,maxMinutes FROM tblCookedData WHERE CenterCode=@CenterCode" &
" AND idDataType=(SELECT TOP 1 id FROM tblDataTypes WHERE Code=@DataType1)) " &
" T1 ON tblChartTimes.ChartTime=T1.dt) " &
"LEFT JOIN (SELECT Format(dtRetrieved,'HH:MM') as dt,avgMinutes FROM tblCookedData WHERE CenterCode=@CenterCode " &
" AND idDataType=(SELECT TOP 1 id FROM tblDataTypes WHERE Code=@DataType2)) " &
" T2 ON tblChartTimes.ChartTime=T2.dt " &
"ORDER BY ChartTime", cnLDI)
oChartCmd.Parameters.AddWithValue("@DataType1", "DLZ15m_" & DataType)
oChartCmd.Parameters.AddWithValue("@CenterCode", CenterCode)
oChartCmd.Parameters.AddWithValue("@DataType2", "DLZ1d_" & DataType)
Dim dr As OleDb.OleDbDataReader = oChartCmd.ExecuteReader
oChart.Series.Clear()
oChart.DataBindTable(dr, "ChartTime")
With oChart
.Width = 1200
.Height = 500
.ChartAreas(0).AxisX.Interval = 1
.ChartAreas(0).AxisX.MajorGrid.Enabled = False
.Legends.Add("Legende")
.Legends(0).Docking = DataVisualization.Charting.Docking.Bottom
.Titles.Add(Title)
.Titles(0).Font = New Font("Arial", 15)
End With
With oChart.Series(0) 'der Balken der Intervall DLZ ohne Labels
.Name = "Ø DLZ"
End With
With oChart.Series(1) 'der grüne Punkt für den Intervall Minimum DLZ Wert
.Name = "min DLZ"
.ChartType = DataVisualization.Charting.SeriesChartType.Point
.Color = Color.DarkGreen
End With
With oChart.Series(2) 'der rote Punkt für den Intervall Maximum DLZ Wert
.Name = "max DLZ"
.ChartType = DataVisualization.Charting.SeriesChartType.Point
.Color = Color.Red
End With
With oChart.Series(3) 'die blaue Linie mit Labels für die Anzahl der Spender im Intervall
.Name = "# Spender"
.ChartType = DataVisualization.Charting.SeriesChartType.Line
.Color = Color.Blue
.IsValueShownAsLabel = True
.LabelForeColor = Color.Blue
End With
With oChart.Series(4) 'die braune Linie für die Tages DLZ ohne Labels
.Name = "Tages DLZ"
.ChartType = DataVisualization.Charting.SeriesChartType.Line
.Color = Color.Chocolate
.BorderWidth = 2
End With
With oChart.Series(5) 'nur die Labels für die Intervall DLZ ganz am Schluss damit die nicht verdeckt werden
.Name = ""
.ChartType = DataVisualization.Charting.SeriesChartType.Point
.Color = Color.Transparent
.IsValueShownAsLabel = True
.LabelFormat = "0.0"
.SmartLabelStyle.Enabled = False
.LabelAngle = -90
End With
oChart.SaveImage(FileName, System.Drawing.Imaging.ImageFormat.Png)
dr.Close()
End Sub
-
Sep 30th, 2015, 12:45 AM
#3
Thread Starter
Member
Re: VB.Net Multiple DataSources for Single Line Type Chart
Hi Shaman,
Thanks for the code snippet. I already have sub routines to create different graphs. I am trying to figure out how do i cycle through them. If i display the first graph, i want the display to wait for a set time interval and then display another graph (based on a different datasource). The again wait for a set time interval and display the 3rd graph. Its slightly different than showing images so I cannot use that logic. There are about 25 or so graphs. I tried to put a Thread.Sleep(15000) at the end of each sub routine but it does not help.
Last edited by tomahawk; Sep 30th, 2015 at 12:55 AM.
-
Sep 30th, 2015, 04:19 AM
#4
Re: VB.Net Multiple DataSources for Single Line Type Chart
you would use a timer for that. set it to whatever interval you want one graph to be displayed. then in the tick Event call your function to create the Chart object. design the function so that it Returns the Chart object. then back in the tick Event take what the function Returns, remove any previous Chart object that is in the forms control collection with Name "myChart" (check if there is one first) then add what the function returned to your forms control collection with the Name "myChart". thats all in the tick Event. the new Chart should then be visible on the form until the timer elapsed again and the tick Event Switches to the next Chart.
you could also use the same Chart object over and over again and just Change the datasource.
-
Sep 30th, 2015, 05:16 AM
#5
Thread Starter
Member
Re: VB.Net Multiple DataSources for Single Line Type Chart
Below is the sub to create and display graphs for one set of information
Code:
Private Sub ShowGraph_1()
Dim MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=user;" _
& "password=pwd;" _
& "database=databasename"
Dim rdrTemp As MySqlDataReader
Try
MysqlConn.Open()
Dim Query As String
Query = "SELECT CONCAT (tblname.ColumnB,' ',tblname.ColumnC) as DateAndTime, tblname.ColumnK FROM tblname " _
& "WHERE tblname.MetNo = 'Met1' ORDER BY (tblname.ColumnB + tblname.ColumnC) DESC LIMIT 24"
Dim Command = New MySqlCommand(Query, MysqlConn)
rdrTemp = Command.ExecuteReader
While rdrTemp.Read
graMeter_BarGraph.Series("Series1").Points.AddXY(rdrTemp.GetString("DateAndTime"), rdrTemp.GetInt32("ColumnK"))
End While
Dim strTitle As Title = graMeter_BarGraph.Titles.Add("Location Name - Met No. 1")
With strTitle
.ForeColor = Color.Black
.BackColor = Color.Honeydew
.Font = New System.Drawing.Font("Verdana", 11.0F, FontStyle.Bold)
End With
graMeter_BarGraph.Series("Series1").Font = New System.Drawing.Font("Tahoma", 9.0F, FontStyle.Regular)
graMeter_BarGraph.Series("Series1").IsValueShownAsLabel = True
graMeter_BarGraph.Series("Series1").LabelForeColor = Color.Red
graMeter_BarGraph.Series("Series1").BorderWidth = 2
MysqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
MysqlConn.Dispose()
End Try
End Sub
Likewise I have multiple graphs to display with corresponding Title Name etc. where the tblname.MetNo value will change for each graph and can be passed as an argument. However, if I return a graph from a function, how will I change the datasource for the next graph alongwith the Title Name etc.
How to change the function argument in the timer event?
Last edited by tomahawk; Sep 30th, 2015 at 05:20 AM.
-
Sep 30th, 2015, 07:49 AM
#6
Re: VB.Net Multiple DataSources for Single Line Type Chart
so you got 25 functions ShowGraph_1 to ShowGraph_25 like the one you posted, and you got a Chart control graMeter_BarGraph on your form which you want to use for displaying the data. each function ShowGraph_1 to ShowGraph_25 works nicely for itself but you got a Problem when calling ShowGraph_1 first and then e.g. ShowGraph_2
so far correct?
to make it work that you call ShowGraph_1 then ShowGraph_2 i think you Need to clear at least the Chart.series before you set the new data to the Chart. go through the Chart and Series Methods, there should be some 'clear' methods. when this works, then i'd do something like that (pseudo code) in the timer tick event
Code:
public sub Timer_Tick () Handles Timer_Tick
Static iDiagram as int32=0
iDiagram += 1
select case iDiagram
case 1
ShowGraph_1
case 2
ShowGraph_2
case 3
....
case 25
ShowGraph_25
iDiagram=0
end select
end sub
if you are asking how you can make your ShowGraph_1 function to work for different tblname.MetNo Values then the solution would look something like that:
Code:
Private Sub ShowGraph(MetNo as string,ChartTitle as string)
Dim MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=user;" _
& "password=pwd;" _
& "database=databasename"
Dim rdrTemp As MySqlDataReader
Try
MysqlConn.Open()
Dim Query As String
Query = "SELECT CONCAT (tblname.ColumnB,' ',tblname.ColumnC) as DateAndTime, tblname.ColumnK FROM tblname " _
& "WHERE tblname.MetNo = @MetNo ORDER BY (tblname.ColumnB + tblname.ColumnC) DESC LIMIT 24" '<-- !
Dim Command = New MySqlCommand(Query, MysqlConn)
Command.Parameters.AddwithValue("@MetNo",MetNo) '<-- !
rdrTemp = Command.ExecuteReader
graMeter_BarGraph.Series.Clear '<-- ! or whatever the correct command is
While rdrTemp.Read
graMeter_BarGraph.Series("Series1").Points.AddXY(rdrTemp.GetString("DateAndTime"), rdrTemp.GetInt32("ColumnK"))
End While
Dim strTitle As Title = graMeter_BarGraph.Titles.Add(ChartTitle)'<-- !
With strTitle
.ForeColor = Color.Black
.BackColor = Color.Honeydew
.Font = New System.Drawing.Font("Verdana", 11.0F, FontStyle.Bold)
End With
graMeter_BarGraph.Series("Series1").Font = New System.Drawing.Font("Tahoma", 9.0F, FontStyle.Regular)
graMeter_BarGraph.Series("Series1").IsValueShownAsLabel = True
graMeter_BarGraph.Series("Series1").LabelForeColor = Color.Red
graMeter_BarGraph.Series("Series1").BorderWidth = 2
MysqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
MysqlConn.Dispose()
End Try
End Sub
and your tick event:
Code:
public sub Timer_Tick () Handles Timer_Tick
Static iDiagram as int32=0
iDiagram += 1
select case iDiagram
case 1
ShowGraph("Met1","Location Name - Met No. 1")
case 2
ShowGraph("Met2","Location Name - Met No. 2")
case 3
....
case 25
ShowGraph("Met25","Location Name - Met No. 25")
iDiagram=0
end select
end sub
although i somehow have the gut feeling i did not really understand your requirements, i still hope that helps.
-
Oct 1st, 2015, 01:20 AM
#7
Thread Starter
Member
Re: VB.Net Multiple DataSources for Single Line Type Chart
Hi Shaman,
It works perfectly.
Danke!!!!!
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|