在表单之间传递连接对象的最佳方法？

背景:我正在将使用MS Access进行数据存储的VB6应用程序重写为使用VB.NET和MS SQL Server的应用程序.

我很好奇在我的应用程序中需要连接到数据库的不同表单之间传递连接的最佳方法.现在我已经构建了一个类来管理连接字符串,以便以安全的方式在表单之间传递:

Public Class LoginCredientials
    Private uname As String
    Private password_hash() As Byte = {0}
    Private server_name As String  'not used in access style databases
    Private dbname As String
    Private st As ServerType  'enum that would allow for different connections
    Private tdes As TripleDES 'encryption class to encrypt password in memory

    Public Sub New()
        uname = ""
        server_name = ""
        dbname = ""
        st = ServerType.stNotDefined
    End Sub
    Public Sub New(ByVal Username As String, _
                   ByVal Password As String, _
                   ByVal ServerName As String, _
                   ByVal DatabaseName As String, _
                   ByVal ServType As ServerType)
        tdes = New TripleDES
        uname = Username
        password_hash = tdes.Encrypt(Password)
        server_name = ServerName
        dbname = DatabaseName
        st = ServType
       tdes = Nothing
    End Sub

    Public ReadOnly Property Server_Type() As ServerType
        Get
            Return st
        End Get
    End Property
    Public ReadOnly Property CompanyName() As String
        Get
            Return dbname.Remove(0, 4)
        End Get
    End Property
    Public Property UserName() As String
        Get
            Return uname
        End Get
        Set(ByVal value As String)
            uname = value
        End Set
    End Property
    Public Property Password() As String
        Get
            tdes = New TripleDES
            Return tdes.Decrypt(password_hash)
            tdes = Nothing
        End Get
        Set(ByVal value As String)
            tdes = New TripleDES
            password_hash = tdes.Encrypt(value)
            tdes = Nothing
        End Set
    End Property
    Public Property ServerName() As String
        Get
            Return server_name
        End Get
        Set(ByVal value As String)
            server_name = value
        End Set
    End Property
    Public Property DatabaseName() As String
        Get
            Return dbname
        End Get
        Set(ByVal value As String)
            dbname = value
        End Set
    End Property

    Public Function GetConnectionString() As String
        Dim cstring As String = ""
        tdes = New TripleDES
        Select Case st
            Case ServerType.stSQLServer
                cstring = "User ID=" & uname & ";" & _
                        "Password=" & tdes.Decrypt(password_hash) & ";" & _
                        "Initial Catalog=" & dbname & ";" & _
                        "Data Source=" & server_name
        End Select
        tdes = Nothing
        Return cstring
    End Function
End Class

我一直在将对象的引用传递给需要连接到数据库的任何表单,如下所示:

'in the form declaration
Private myLC As LoginCredientials
Public Sub New(ByRef lc As LoginCredientials)
    InitializeComponent()
    myLC = lc
End Sub

然后我会创建一个新的连接对象,做我需要做的事情,然后关闭连接并销毁连接对象.当我很久以前在ADO中使用VB6完成此操作时,连接创建的进程在连接对象被销毁时被终止,但这似乎不再是这种情况了.现在,每次创建新的连接对象并连接到我的服务器时,都会创建一个新进程,然后在关闭连接时进入休眠状态.一段时间后,服务器将开始拒绝连接,直到我登录并终止我的应用程序创建的所有进程.显然这不是正确的,我想学习正确的方法.

简单地通过引用(或在包装器类中)在我的表单中传递相同的连接对象,让连接对象保持打开状态会更好吗？

什么是关闭我的连接的正确方法,以便我最终不会在我的SQL服务器上获得一堆休眠进程？SQL服务器中是否有设置我可以调整以在一段时间不活动后自动终止进程？

你会考虑在运行时内存中加密密码吗？

感谢您的任何帮助.:)

您不应该在表单之间传递连接对象.基本上,使用SQL Server连接时的模式是创建连接,打开它,执行操作,然后关闭连接.

为此,你应该有一个公共静态方法,它将生成你将在Using语句中使用的SqlConnection,如下所示:

Using connection As SqlConnection = GetConnection
    ' Use connection here.

End Using

这应该可以防止进程在服务器上堆叠.