如何在整个项目中处理数据库连接

I have created a function in a module to connect to database for a windows application

我在模块中创建了一个函数来连接到Windows应用程序的数据库

Imports System.Data.SqlClient
Module mod_main
Public Function connectDB() As SqlConnection
        Dim Connection As New SqlConnection
        Try
            If Connection.State = ConnectionState.Open Then
                Connection.Close()
            End If
            If IntegratedSecurity Then
                Connection.ConnectionString = "Data Source = " & server & ";Initial Catalog = " & db & ";Connection TimeOut =0;Integrated Security=True"
            Else
                Connection.ConnectionString = "Data Source = " & server & ";Initial Catalog = " & db & ";Connection TimeOut =0;User ID='" & usr & "';Password='" & pwd & "'"
            End If
            Connection.Open()
            Return Connection
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
End Module

I have so many functions and classes that uses plethora of db activities for that I use aforementioned connection function.For exmample:

我有很多函数和类使用了大量的db活动,因为我使用了上面提到的连接函数。例如:

 Public Sub FillComboBox(ByVal ComboBox As C1.Win.C1List.C1Combo, ByVal Query As String, ByVal DisplayMember As String, ByVal ValueMember As String)
        Dim SourceDataSet As New DataSet
        Dim adapter As New SqlDataAdapter(Query, connectDB) /*Assigning connection here */
        adapter.Fill(SourceDataSet)
        ComboBox.DataSource = SourceDataSet.Tables(0)
        ComboBox.ColumnHeaders = False
        ComboBox.ColumnWidth = 0
        ComboBox.ExtendRightColumn = True
        ComboBox.DisplayMember = DisplayMember
        ComboBox.ValueMember = ValueMember
    End Sub

Since I'm a beginner in programming my question is , Is this a correct way of handling db connection?

由于我是编程的初学者,我的问题是,这是处理数据库连接的正确方法吗?

1 个解决方案

#1

I suggest you to make following changes:

我建议你进行以下更改:

make Connection as public for global accessibility

使Connection成为公共的全局可访问性

save connection string in config file and access it from there

在配置文件中保存连接字符串并从那里访问它

need not to close and re open connection open connection only when there is no available connection

只有在没有可用连接时才需要关闭并重新打开连接打开连接

In your case all time it creates a new connection when the function is invoked since you are declaring and initializing connection inside the function. so checking connection state is meaning less:

在您的情况下,它一直在调用函数时创建新连接,因为您在函数内声明并初始化连接。所以检查连接状态意味着更少:

so your function looks like following:

所以你的功能如下:

public Connection As New SqlConnection
Public Function connectDB() As SqlConnection
      Try
            Dim Constr As String =""
            If IntegratedSecurity Then
               Constr = ConfigurationManager.AppSetting("IconnectionString")
            Else
               Constr  = ConfigurationManager.AppSetting("connectionString")
            End If
            If Connection Is Nothing Then
                Connection = New SqlConnection(Constr)
            End If
            If Connection.State <> ConnectionState.Open Then
              Connection.Open()
            End If           
            Return Connection
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Function

#1