vb.net 通过类对象连接到数据库 Visual Basic 2010

Connecting To A Database Via Class Object Visual Basic 2010

提问by user2274752

i have 2 classes

我有 2 节课

connect class

连接类

Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class connect
Dim dbCon As MySqlConnection
Dim strQuery As String = ""
Dim SqlCmd As MySqlCommand
Dim DR As MySqlDataReader

Public Function Con2Db() As Boolean

    Try
        'Prepare connection and query
        dbCon = New MySqlConnection("Server=localhost; User Id = root; Pwd = 12345; Database = digitallibrary")
        If dbCon.State = ConnectionState.Closed Then
            dbCon.Open()
            Return True
        Else
            dbCon.Close()
            splash.Label1.Text = "Connection is Close"
            Return False
        End If
    Catch ex As Exception
        MsgBox("FAIL")
        Return False
    End Try

End Function


End Class

And query Class

并查询类

Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class query
Dim dbCon As MySqlConnection
Dim strQuery As String = ""
Dim SqlCmd As MySqlCommand
Dim DR As MySqlDataReader
Public Sub insert(ByVal ln As String, ByVal fn As String, ByVal mn As String, ByVal user As String, ByVal email As String, ByVal bdate As String, ByVal jdate As String, ByVal jtime As String, ByVal pwd As String)
    Try
        strQuery = "INSERT INTO user_tbl(user_ln,user_fn,user_mn,username,user_email,user_bdate, user_jdate, user_jtime)VALUES('" + ln + "','" + fn + "','" + mn + "','" + user + "','" + email + "','" + bdate + "','" + jdate + "','" + jtime + "' );" & _
            "INSERT INTO login_tbl(username,password)VALUES('" + user + "','" + pwd + "')"
        SqlCmd = New MySqlCommand(strQuery, dbCon)
        SqlCmd.ExecuteNonQuery()
        dbCon.Close()

    Catch ex As Exception
        MsgBox("Error " & ex.Message)
    End Try
End Sub

End Class

Also A registration form

还有一个注册表

Public Class registration

Private Sub registration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim con As New connect
    If (con.Con2Db = True) Then
        Label13.Text = "Connected To Database"
    Else
        Label13.Text = "Not Connected To Database"
    End If
End Sub

Private Sub submit_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submit_btn.Click
    Dim ins As New query
    Dim ln As String = ln_txt.Text
    Dim fn As String = fn_txt.Text
    Dim mn As String = mn_txt.Text
    Dim user As String = user_txt.Text
    Dim pwd As String = pwd_txt.Text
    Dim cpwd As String = cpwd_txt.Text
    Dim email As String = email_txt.Text
    Dim year As String = year_cbx.Text
    Dim month As String = month_cbx.Text
    Dim day As String = day_cbx.Text
    Dim bdate As String = year + "-" + month + "-" + day
    Dim jdate As String = Format(Date.Now, "yyyy-MM-dd")
    Dim jtime As String = Format(Date.Now, "HH:mm:ss")
    ins.insert(ln, fn, mn, user, email, bdate, jdate, jtime, pwd)
End Sub
End Class

Everything is okay and it is saying on the label that the connection is successful but when I run the code it gives an error

一切正常，标签上说连接成功，但是当我运行代码时出现错误

Error Connection must be valid and open

错误 连接必须有效且打开

I don't understand why the connection is said to be closed when the function is returning true...

我不明白为什么在函数返回 true 时会说连接已关闭...

If people are wondering why separate it to each classes it's because i'm trying to code cleanly(i hope and think) and i want to be more flexible in programming

如果人们想知道为什么将它分成每个类，那是因为我正在尝试干净地编码（我希望并认为）并且我希望在编程中更加灵活

回答by Nicholas Post

The problem is that your 'connect' class has no relationship to your 'insert' function, meaning you are creating a new connection in each one. What you would have to do is create a Shared connection to your database that your connect class would manage, and your 'insert' function would use the existing connection.

问题是您的“connect”类与“insert”函数没有关系，这意味着您要在每个类中创建一个新的连接。您需要做的是创建一个到您的连接类将管理的数据库的共享连接，并且您的“插入”函数将使用现有连接。

There is nothing wrong with separating code into various sections, but you must know how to do it efficiently. I typically keep connection management and query execution in the same sections to avoid passing around extra objects. Here is what I would do:

将代码分成不同的部分并没有错，但您必须知道如何有效地做到这一点。我通常将连接管理和查询执行放在同一部分，以避免传递额外的对象。这是我会做的：

Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class QueryManager

Dim dbCon As MySqlConnection

Public Sub ManageConnection(ByVal CloseConnection As Boolean)
    Try
        'Prepare connection and query'
        dbCon = New MySqlConnection("Server=localhost; User Id = root; Pwd = 12345; Database = digitallibrary")
        If CloseConnection = False Then
            If dbCon.State = ConnectionState.Closed Then _
                dbCon.Open()
        Else
            dbCon.Close()
        End If
    Catch ex As Exception
        MsgBox("FAIL")
    End Try

End Sub

Public Sub Insert(ByVal ln As String, ByVal fn As String, ByVal mn As String, ByVal user As String, ByVal email As String, ByVal bdate As String, ByVal jdate As String, ByVal jtime As String, ByVal pwd As String)
    Try
        ManageConnection(True) 'Open connection'

        Dim strQuery As String = "INSERT INTO user_tbl(user_ln,user_fn,user_mn,username,user_email,user_bdate, user_jdate, user_jtime)" & _
            "VALUES('" + ln + "','" + fn + "','" + mn + "','" + user + "','" + email + "','" + bdate + "','" + jdate + "','" + jtime + "' );" & _
            "INSERT INTO login_tbl(username,password)VALUES('" + user + "','" + pwd + "')"

        Dim SqlCmd As New MySqlCommand(strQuery, dbCon)
        SqlCmd.ExecuteNonQuery()

        ManageConnection(False) 'Close connection'

    Catch ex As Exception
        MsgBox("Error " & ex.Message)
    End Try
End Sub

End Class

You would also no longer require the 'registration_Load' sub since the connection will only be opened while it's being used. If you wanted to create one shared connection and persist it through the entire application, you could adjust the functions to reflect your needs.

您也将不再需要“registration_Load”子，因为连接只会在使用时打开。如果您想创建一个共享连接并在整个应用程序中保持它，您可以调整函数以反映您的需求。