-
Feb 14th, 2016, 04:02 PM
#1
Thread Starter
New Member
Download mysql data and loop
Hi, I create very simple mysql message system, when you enter specific tab page it opens mysql connection, downloads data to DataGridView, and that works great, what I am trying to do now, is when user clicks on that tab, program will request mysql data every 5 seconds or so, something like: Do, Loop I just don't know which place to put it.
Code:
Private Sub TabPage_chat_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPage_chat.Enter
Dim SDA As New MySqlDataAdapter
Dim dbDataSet As New DataTable
Dim bSource As New BindingSource
Dim Command As New MySqlCommand
Try
con.Open()
Dim Query As String
Query = "select * from messages ORDER BY time DESC"
Command = New MySqlCommand(Query, con)
SDA.SelectCommand = Command
SDA.Fill(dbDataSet)
bSource.DataSource = dbDataSet
DataGridView_czat.DataSource = bSource
SDA.Update(dbDataSet)
con.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
con.Dispose()
End Try
End Sub
-
Feb 14th, 2016, 04:21 PM
#2
Re: Download mysql data and loop
No loop. Use a Timer. Set the Interval, which is measured in milliseconds, to 5000. In the Tick event handler, retrieve the data. Also, use the SelectedIndexChanged event of the TabControl to Start and Stop the Timer.
Also, don't create a new DataTable each time. Just keep using the same DataTable. You can Clear its Rows and repopulate it or, if you do things right, you can retrieve only those records that have changed since the last retrieval. To achieve that you would need a column in the database table to store the last update time of the record. You can then retrieve only records that have a last update time that is later than the latest value already in the DataTable.
-
Feb 14th, 2016, 08:54 PM
#3
Thread Starter
New Member
Re: Download mysql data and loop
Can you help me with the code? After reading your suggestion this is the best I've come up with
Code:
Private Sub TheTimer()
AddHandler Timer1.Elapsed, AddressOf Timer1_Tick
Timer1.Interval = 5000
Timer1.Start()
End Sub
Private Sub Timer1_Tick(ByVal obj As Object, ByVal e As EventArgs)
Dim SDA As New MySqlDataAdapter
Dim dbDataSet As New DataTable
Dim bSource As New BindingSource
Dim Command As New MySqlCommand
Try
con.Open()
Dim Query As String
Query = "select * from messages ORDER BY time DESC"
Command = New MySqlCommand(Query, con)
SDA.SelectCommand = Command
SDA.Fill(dbDataSet)
bSource.DataSource = dbDataSet
DataGridView_chat.DataSource = bSource
SDA.Update(dbDataSet)
con.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
con.Dispose()
End Try
End Sub
Private Sub TabPage_chat_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPage_czat.Enter
TheTimer()
End Sub
Private Sub TabPage_chat_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPage_czat.Leave
Timer1.Stop()
End Sub
ofcourse it's not working, I get the debug error
Object reference not set to an instance of an object
-
Feb 14th, 2016, 09:13 PM
#4
Re: Download mysql data and loop
Firstly, you're using a different Timer to the one I was suggesting. I said to handle the Tick event, which is a member of the Windows.Forms.Timer. You are using a Timers.Timer, which is evidenced by the fact that you're handling an Elapsed event.
That said, it may not actually matter, but then again it might. How did you add this Timer? Did you do it in code or in the designer? If you did it in code then your Elapsed event handler is being executed on a secondary thread, which is going to be an issue if you're trying to update the UI. What I would suggest is that you get rid of that Timer and the code you have. Add a Timer to your form in the designer, which will be a Windows.Forms.Timer. Set the Interval in the Properties window and create the Tick event handler in the usual way. Your code should look something like this:
vb.net Code:
Private adapter As New SqlDataAdapter("SELECT * FROM Messages WHERE LastUpdatedTime > @LastUpdatedTime", "connection string here") Private parameter As SqlParameter = adapter.SelectCommand.Parameters.Add("@LastUpdatedTime", SqlDbType.DateTime2) Private table As New DataTable Private maxUpdatedTime As Date = Date.MinValue Private Sub TabControl1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged Timer1.Enabled = (TabControl1.SelectedTab Is TabPage_chat) End Sub Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick parameter.Value = maxUpdatedTime adapter.Fill(table) BindingSource1.DataSource = table DataGridView_chat.DataSource = BindingSource1 maxUpdatedTime = CDate(table.Compute("MAX(LastUpdatedTime)", String.Empty)) End Sub
That assumes that your database table has a column LastUpdatedTime that gets set to the current time whenever a record is updated. Without such a column, there's no way to tell which records are newer than the ones you already have.
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
|