check database connection using backgroundworker

Question

0.00/5 (No votes)

See more:

VS2012

I already banging my head with this problem, I use backgroundWorker to check if database is OPEN and here's my code for that:

VB

Public Class Form1

    Delegate Sub SetLabelText_Delegate(ByVal [Label] As Label, ByVal [text] As String)

    Dim sqlconnection As New SqlConnection("Data Source=" & My.Settings.Server & ";Initial Catalog=" & My.Settings.Database & ";Integrated Security=false;user id=" & My.Settings.Username & ";password=" & My.Settings.Password & ";Connection Timeout=5;")

    Dim connectionStatus As String

    Private Sub SetLabelText_ThreadSafe(ByVal [Label] As Label, ByVal [text] As String)
        If [Label].InvokeRequired Then
            Dim MyDelegate As New SetLabelText_Delegate(AddressOf SetLabelText_ThreadSafe)
            Me.Invoke(MyDelegate, New Object() {[Label], [text]})
        Else
            [Label].Text = [text]
        End If
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'I store my database information to my.settings then display it on textboxes for manipulation
        TextBox1.Text = My.Settings.Server
        TextBox2.Text = My.Settings.Database
        TextBox3.Text = My.Settings.Username
        TextBox4.Text = My.Settings.Password

        'just getting my computer name
        lblCompName.Text = System.Windows.Forms.SystemInformation.ComputerName

        BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

        Try

            If sqlconnection.State = ConnectionState.Closed Then
                sqlconnection.Open()
                connectionStatus = "Online"
                'sqlconnection.Open()
                SetLabelText_ThreadSafe(Me.Label1, "Database Status: online")
            End If

        Catch ex As Exception
            connectionStatus = "Offline"
            sqlconnection.Close()
            SetLabelText_ThreadSafe(Me.Label1, "Database Status: offline")
        End Try

    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted

        If e.Error IsNot Nothing Then
            Label1.Text = "Database Status: " & connectionStatus
        Else
            Label1.Text = "Database Status: " & connectionStatus
        End If
        BackgroundWorker1.RunWorkerAsync()
    End Sub
end class

In my code above it does actually works, it does display "database is online/offline" when restarting my program within the visual studio but when I run the program then turn-off/disable my network connection(my database is on other computer) it always display "Database is ONLINE" but I'm pretty sure that there is already no connection between my pc and database(because I PING it) so it must be display as "Database is oFFLINE".

Did I missed something regarding of using backgroundWOrker? Oh BTW, I'm new in using BackgroundWorker. Any help or alternative solutions is much appreciated thanks!

Posted 16-Jul-14 21:59pm

Uknownymous

Add a Solution

3 solutions

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)

Pheonyx · Answer 1 · 2014-07-16T22:20:00

Study your code and think logically about what you are trying to do.

You do no form of polling to the database with the connection, there is nothing that truely does what you are asking for except one when the program loads. Also, I'm not sure if the "sqlconnection.State" property will get updated until you actually try and do something with the connection.

Spectulation
I suspect that when you start the program and the connection is first made the property is set to open, if you were to then disable your network connection so your PC could no longer talked to the DB server, I believe your Connection would still read as "open" until you actually tried to use it.

Loop idea
In your background worker do work, you need to put the code in some sort of endless loop (I would suggest using the Background Worker CancelAsync method either by a button press of when the form closes to end the loop at some point.

OriginalGriff · Answer 2 · 2014-07-16T22:21:00

Well...yes. You are missing something.
Look at your code and assume it works the first time.
What happens the second time it is executed?
Simple: the connection is open, so this test fails:

VB

If sqlconnection.State = ConnectionState.Closed Then

So you don't do anything in your worker the second and subsequent times, because the connection is not closed - it could be open, it could be broken - you don't know: http://msdn.microsoft.com/en-us/library/system.data.connectionstate(v=vs.110).aspx[^]

Instead of this, put a finally block in there, which closes and disposes the connection, and rebuild a new one each time.
And I'd add a Thread.Wait for a couple of seconds too - or your DB admin is going to get well and truly annoyed with you!

nilesh sawardekar · Answer 3 · 2014-07-16T22:27:00

Public Class Form1
Dim lblInfo As string

Dim sqlconnection As New SqlConnection("Data Source=" & My.Settings.Server & ";Initial Catalog=" & My.Settings.Database & ";Integrated Security=false;user id=" & My.Settings.Username & ";password=" & My.Settings.Password & ";Connection Timeout=5;")

Dim connectionStatus As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'I store my database information to my.settings then display it on textboxes for manipulation
TextBox1.Text = My.Settings.Server
TextBox2.Text = My.Settings.Database
TextBox3.Text = My.Settings.Username
TextBox4.Text = My.Settings.Password

'just getting my computer name
lblCompName.Text = System.Windows.Forms.SystemInformation.ComputerName

BackgroundWorker1.RunWorkerAsync()
End Sub

Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

Try

If sqlconnection.State = ConnectionState.Closed Then
sqlconnection.Open()
connectionStatus = "Online"
'sqlconnection.Open()
lblInfo="Database Status: online"
End If

Catch ex As Exception
connectionStatus = "Offline"
sqlconnection.Close()
lblinfo="Database Status: offline"
End Try

End Sub

Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
label1.text=lblinfo
BackgroundWorker1.RunWorkerAsync()
End Sub
end class

check database connection using backgroundworker

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0