Database connection every 1000 milliseconds by Timer interval problem

Question

0.00/5 (No votes)

See more:

Hi Programmers
I have an application that i want to Connect and get updates from access database Every 1000 milliseconds by Timer interval .The problem here is that the form is not Active most of the time . can anyone give me code for Multitreading
to execute my process as new thread or any other solution to this problem .
I want to know something else
is true to open the connection to the database all at once or should remain an open connection

This is an illustration of code :

VB

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
       GetUpdates()
    End sub

    Private sub GetUpdates()
        Dim sql as string ="Select * From UpdatTable Where ID =' " & Textbox1.text &" ' "


        If con.State() Then con.Close()
        con.Open()
        Try
            Dim cmd As New OleDbCommand(sql, con)
            Dim dr As OleDbDataReader
            dr = cmd.ExecuteReader()
            Dim s As String = ""


            While dr.Read
                str &= Convert.ToString(dr.Item("UserName"))  & vbCrLf
            End While
            TextBox1.Text = str

  Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try
        con.Close()

      End sub

Posted 18-Sep-13 6:37am

Michael Azzar

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-09-18T07:07:00

I strongly suggest leaving the database open instead of closing and re-opening it every second.

Below are snippets from a working program that demonstrates how to start nine threads and then wait for them all to finish.

Also, there is a .NET Framework BackgroundWorker Class[^] that is well documented in the help files. It may fit your needs better than my example.

Declare the nine threads

VB

Dim WithEvents tws1 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws2 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws3 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws4 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws5 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws6 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws7 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws8 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents tws9 As New ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
Dim WithEvents t1 As New Thread(AddressOf tws1.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t2 As New Thread(AddressOf tws2.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t3 As New Thread(AddressOf tws3.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t4 As New Thread(AddressOf tws4.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t5 As New Thread(AddressOf tws5.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t6 As New Thread(AddressOf tws6.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t7 As New Thread(AddressOf tws7.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t8 As New Thread(AddressOf tws8.CreateCablecast_ScheduleTable_From_City_Servers)
Dim WithEvents t9 As New Thread(AddressOf tws9.CreateCablecast_ScheduleTable_From_City_Servers)

Starts the nine threads

For idx = 0 To objChannels.GetUpperBound(0)
    ' Only the one Channel selected in the Channels form.
    If CityChannelID = objChannels(idx).ChannelID Then
        idxThread += 1
        sbStatus.Panels(1).Text = "Initiating thread to process " & CityName
        sbStatus.Refresh()
        Select Case idxThread
            Case 1
                tws1.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t1.Start()
            Case 2
                tws2.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t2.Start()
            Case 3
                tws3.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t3.Start()
            Case 4
                tws4.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' n start the thread.
                t4.Start()
            Case 5
                tws5.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t5.Start()
            Case 6
                tws6.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t6.Start()
            Case 7
                tws7.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t7.Start()
            Case 8
                tws8.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t8.Start()
            Case 9
                tws9.SetParameters(objChannels(idx).ChannelID, _
                    objChannels(idx).PrimaryLocationID, CityURL, CityName, _
                    objChannels(idx).Name, _
                    DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel())
                ' start the thread.
                t9.Start()
        End Select
        AddToLog("Processing " & CityName & " Channel: '" & DirectCast(clnChannels.Item(idxCityChannel), ChannelClass).Channel() & "'")
        sbStatus.Panels(1).Text = "Gathering city schedule data"
        sbStatus.Refresh()
    End If
Next

Wait for all nine threads to be completed

    'Note: idxThread previously set to 9 - The number of threads that were started
    Do Until idxThread < 1
        Application.DoEvents()
        ThreadCheckJoin(t1, b1, idxThread, "T1", tws1)
        ThreadCheckJoin(t2, b2, idxThread, "T2", tws2)
        ThreadCheckJoin(t3, b3, idxThread, "T3", tws3)
        ThreadCheckJoin(t4, b4, idxThread, "T4", tws4)
        ThreadCheckJoin(t5, b5, idxThread, "T5", tws5)
        ThreadCheckJoin(t6, b6, idxThread, "T6", tws6)
        ThreadCheckJoin(t7, b7, idxThread, "T7", tws7)
        ThreadCheckJoin(t8, b8, idxThread, "T8", tws8)
        ThreadCheckJoin(t9, b9, idxThread, "T9", tws9)
    Loop
End Sub

Private Sub ThreadCheckJoin(ByRef t As Thread, ByRef b As Boolean, _
                            ByRef idxThread As Integer, _
                            ByVal strName As String, _
                            ByRef tws As ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers)
    Const JoinWait As Integer = 1000
    If Not b Then
        t.Join(JoinWait)
        Application.DoEvents()
        If (Not t.IsAlive) Then
            Dim strErrorMessage As String = tws.GetErrorMessage
            If strErrorMessage.Length > 0 Then
                AddToLog(tws.GetCityName & ": Error Occurred. " & strErrorMessage)
            Else
                AddToLog(tws.GetCityName & ": Completed.")
            End If
            t.Join()
            idxThread -= 1
            Debug.WriteLine(strName & " joined")
            b = True
        End If
        Application.DoEvents()
    End If
End Sub

The Class that is run in a separate thread
Note that the database and variables are local to the class and its subroutines.

Public Class ThreadClass_CreateCablecast_ScheduleTable_From_City_Servers
    Private m_intChannelID As Integer
    Private m_intLocationID As Integer
    Private m_strCityURL As String
    Private m_strCityName As String
    Private m_strChannel As String
    Private m_CityChannel As String
    Private m_ErrorMessage As String = ""

    Friend Sub SetParameters(ByVal intChannelID As Integer, ByVal intlocationID As Integer, ByVal strCityURL As String, ByVal strCityName As String, ByVal strChannel As String, ByVal CityChannel As String)
        m_intChannelID = intChannelID
        m_intLocationID = intlocationID
        m_strCityURL = strCityURL
        m_strCityName = strCityName
        m_strChannel = strChannel
        m_CityChannel = CityChannel
    End Sub

    Friend ReadOnly Property GetCityName() As String
        Get
            GetCityName = m_strCityName
        End Get
    End Property

    Friend ReadOnly Property GetErrorMessage As String
        Get
            GetErrorMessage = m_ErrorMessage
        End Get
    End Property

    ' The thread procedure performs the task
    Friend Sub CreateCablecast_ScheduleTable_From_City_Servers()
        Dim cn As New SqlConnection ' Each thread has its own connection to the database


......  A whole lot of code here   .......


        Try
            cn.Close()
        Catch ex As Exception
        End Try
    End Sub
End Class