vb.net VB.Net重用sql连接

VB.Net reuse sql connection

提问by Rick Agustin

I'm a newbie to vb.net, I'd like to ask is there a way to reuse the sql connection command?

我是vb.net的新手，请问有没有办法复用sql连接命令？

Here is the code for my main.vb:

这是我的 main.vb 的代码：

 Dim ServerString As String = "Server=localhost;User Id=root;Password=;Database=pos"
 Dim SQLConnection As MySqlConnection = New MySqlConnection

 Private Sub Main_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    SQLConnection.ConnectionString = ServerString

    Try
        If SQLConnection.State = ConnectionState.Closed Then
            SQLConnection.Open()
        Else
            SQLConnection.Close()
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

Since I'd like to use this in my other classes, i don't want to re-write this code in every form. Any help is truly appreciated. Thanks.

因为我想在我的其他类中使用它，所以我不想以每种形式重新编写这段代码。任何帮助都非常感谢。谢谢。

回答by Tim Schmelter

Reusing a connection (or any other unmanaged resource) is normally not a good idea. You should dispose them as soon as possible.

重用连接（或任何其他非托管资源）通常不是一个好主意。您应该尽快处理它们。

But there's no problem in always creating a new connection anyway, since you're using the ADO.NET connection-poolby default. So you are not creating (and opening) a new physical connection. Actually you're just telling the pool that a conenction is reusable somewhere else now when you close/dispose it. And when you open it, it cannot be used somewhere else, that's why it's important to always close it.

但是无论如何总是创建新连接没有问题，因为默认情况下您使用的是ADO.NET 连接池。所以你不是在创建（和打开）一个新的物理连接。实际上，当您关闭/处置它时，您只是告诉池现在可以在其他地方重用连接。当你打开它时，它不能在其他地方使用，这就是为什么总是关闭它很重要。

Therefore always use the Using-statement.

因此总是使用Using-statement。

Public Shared Function GetColumn1(column2 As Int32) As String
    Dim sql = "SELECT Column1 From dbo.tableName WHERE Column2=@Column2 ORDER BY Column1 ASC"
    Using con = New SqlConnection(connectionString)
        Using cmd = New SqlCommand(sql, con)
            cmd.Parameters.AddWithValue("@Column2", column2)
            Try
                con.Open()
                Using rd = cmd.ExecuteReader()
                    If rd.Read() Then
                        Dim Column1 As String = rd.GetString(0)
                        Return Column1
                    Else
                        Return Nothing
                    End If
                End Using
            Catch ex As Exception
                ' log the exception here or do not catch it '
                ' note that you don't need a Finally to close the connection '
                ' since a Using-Statement disposes the object even in case of exception(which also closes a connection implicitely)
            End Try
        End Using
    End Using
End Function

Above is a sample method to demontrate that you should not reuse anything.

以上是一个示例方法，用于证明您不应该重用任何东西。

回答by Rick Agustin

This is what I normally do: I create a class, e.g. ConnectDB, and a method within this class, e.g. GetConnection. Here is the code:

这就是我通常所做的：我创建一个类，例如 ConnectDB，并在这个类中创建一个方法，例如 GetConnection。这是代码：

Imports System.Data
Imports System.Data.SqlClient

Public Class ConnectDB

    Public Shared Function GetConnection() As SqlConnection
        Dim dbConnString As String = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
        Return New SqlConnection(dbConnString)
    End Function
End Class


Then from the method that needs a connection to the database, I call this function.  Here is a sample code:

Imports System.Data.SqlClient

Public Class EmployeeDB

    Public Shared Function GetEmployees() As List(Of Employee)
        Dim con As SqlConnection = ConnectDB.GetConnection()
        Dim selectStmt As String = "SELECT * FROM Employees"
        Dim selectCmd As New SqlCommand(selectStmt, con)
        Dim employees As New List(Of Employee)

        Try
            con.Open()
            Dim reader As SqlDataReader = selectCmd.ExecuteReader()
            Do While reader.Read
                Dim employee as New Employee

                employee.LastName = reader("LastName").ToString
                employee.FirstName = reader("FirstName").ToString
                ...
                employees.Add(employee)
            Loop
            reader.Close()
         Catch ex As Exception
             Throw ex
         Finally
             con.Close()
         End Try
         Return employees
    End Function
End Class

You can also modify the selectStmtstring to include filter conditions, parameters, and sort order just like Tim's example above and include selectCmd.Parameters.AddWithValue("@<parameterName>", value)for each of your parameters.

您还可以修改selectStmt字符串以包含过滤条件、参数和排序顺序，就像上面 Tim 的示例一样，并selectCmd.Parameters.AddWithValue("@<parameterName>", value)为每个参数包含在内。