使用backgroundworker检查数据库连接 [英] check database connection using backgroundworker

问题描述

我已经在解决这个问题，我使用backgroundWorker检查数据库是否已打开，这是我的代码：

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

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

在上面的代码中，它实际上是工作时，它显示数据库在线/离线在Visual Studio中重新启动我的程序但是当我运行该程序然后关闭/禁用我的网络连接（我的数据库在其他计算机上）它始终显示数据库在线但我很确定我的电脑和数据库之间已经没有连接（因为我打它）所以它必须显示为数据库是oFFLINE。



我是否错过了使用backgroundWOrker的相关信息？哦顺便说一下，我是使用BackgroundWorker的新手。任何帮助或替代解决方案非常感谢谢谢！

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!

推荐答案

研究您的代码并从逻辑上思考您的目标。



您没有使用连接对数据库进行任何形式的轮询，除了程序加载之外，没有什么能够真正满足您的要求。此外，我不确定sqlconnection.State属性是否会更新，直到您实际尝试使用该连接执行某些操作。



观察

我怀疑你启动程序和连接时首先将属性设置为打开，如果你要禁用网络连接，这样你的PC就不能再与数据库服务器通信了，我相信你的Connection仍会读作打开，直到你真正尝试使用它为止。



循环理念

在你的后台工作人员做的工作中，你需要将代码置于某种无限循环中（我建议使用Background Worker CancelAsync方法，或者在窗体关闭时按下按钮以在某个时刻结束循环。

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.

嗯。 ..yes。你错过了什么。

看看你的代码并假设它第一次工作。

第二次执行会发生什么？

简单：连接已打开，因此此测试失败：

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:

If sqlconnection.State = ConnectionState.Closed Then

所以你不要在你的工作人员做任何事情。随后的时间，因为连接没有关闭 - 它可能是打开的，它可能会被打破 - 你不知道： http://msdn.microsoft.com/en-us/library/system.data.connectionstate（v = vs.110）的.aspx [<一个href =http://msdn.microsoft.com/en-us/library/system.data.connectionstate(v=vs.110).aspx\"target =_ blanktitle =New Window> ^ ]



而不是这个，在那里放一个finally块，关闭并处理连接，每次重建一个新的。

我也会添加一个Thread.Wait几秒钟 - 或者你的数据库管理员会好起来并且真的很烦你！

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!

公共类Form1

Dim lblInfo作为字符串



Dim sqlconnection作为新的SqlConnection（数据源= &My.Settings.Server&; Initial Catalog =&My.Settings.Database&; Integrated Security = false; user id =&My.Settings.Username&; password =&My.Settings.Password &;连接超时= 5;）



Dim connectionStatus As String



Private Sub Form1_Load（ ByVal sender As System.Object，ByVal e As System.EventArgs）Handles MyBase.Load

'我将我的数据库信息存储到my.settings然后将其显示在文本框上进行操作

TextBox1.Text = My.Settings.Server

TextBox2.Text = My.Settings.Database

TextBox3.Text = My.Settings.Username

TextBox4.Text = My.Settings.Password



'只是获取我的电脑名称

lblCompName.Text = System.Windows.Forms .SystemInformation.ComputerName



BackgroundWorker1.RunWorkerAsync（）

结束子



Pr ivate Sub BackgroundWorker1_DoWork（sender As Object，e As System.ComponentModel.DoWorkEventArgs）处理BackgroundWorker1.DoWork



试试



如果sqlconnection.State = ConnectionState.Closed那么

sqlconnection.Open（）

connectionStatus =在线

'sqlconnection.Open （）

lblInfo =数据库状态：在线

结束如果



Catch ex As Exception

connectionStatus =离线

sqlconnection.Close（）

lblinfo =数据库状态：离线

结束尝试



结束子



私有子BackgroundWorker1_RunWorkerCompleted（发送者为对象，e为System.ComponentModel.RunWorkerCompletedEventArgs）处理BackgroundWorker1.RunWorkerCompleted

label1.text = lblinfo

Backgroun dWorker1.RunWorkerAsync（）

End Sub

end class

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

这篇关于使用backgroundworker检查数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！