Best way to pass a connection object among forms?

Question

Background: I'm rewriting a VB6 app that used MS Access for data storage to one that uses VB.NET and MS SQL Server.

I'm curious as to the best way to pass a connection among the different forms in my application that need a connection to the database. Right now I've built a class to manage the connection string to pass that between forms in a secure manner:

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

I had been passing a reference to my object to any of my forms that needed a connection to the database like so:

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

And then I would create a new connection object, did what I needed to do, and then closed the connection and destroyed the connection object. When I've done this before long ago in ADO with VB6, the process created by the connection was killed when the connection object was destroyed, but that doesn't appear to be the case anymore. Now every time I create a new connection object and connect to my server, a new process is created and then put to sleep when I close my connection. After a while the server will start refusing connections until I log in and kill all the processes my app created. Obviously this isn't being done right, and I would like to learn the right way.

Would it be better to simply pass the same connection object by reference (or inside a wrapper class) among my forms, leaving the connection object open?

What is the correct way to close my connection so that I don't eventually get a bunch of sleeping processes on my SQL server? Is there a setting in SQL server I can adjust to automatically kill processes after a certain period of inactivity?

Would you consider encrypting the password in runtime memory overkill?

Thank you for any help. :)

Answer 1

You should NOT pass the connection object among forms. Basically, the pattern when using a connection to SQL Server is to create the connection, open it, perform your operation, then close the connection.

To that end, you should have a public static method somewhere which will generate your SqlConnection which you would use in a Using statement, like so:

Using connection As SqlConnection = GetConnection
    ' Use connection here.

End Using

That should prevent the processes from stacking up on the server.

Answer 2

You can use the Using statement, it will close and dispose of the connection when its done.

Using _conn as New SqlConnection(<connstring>)
  _conn.Open()
  'get your data'

End Using

If you arent calling .Close(), that may be the problem.

Answer 3

I agree with Casper. If you do need to share the object between pages to reduce load for example then you can use a static member variable to do it. Just make sure to close the connection when the last statement has executed. You can also create a connection scope that can dispose when the last transaction has finished. If you don't have the experience to do this then just open and close your connection at the earliest opportunity and don't pass it around.

I have a web app and to reduce some of the latency there are cases where I use a scope that I've created for my DAL so that if there are calls in child functions they can use the same connection and not get promoted to MSDTC. This however is really only necessary in a transactional system.

Answer 4

Being it's VB.NET that you are using, try this (code is from memory, not copied from an app):

Namespace Helpers

Public NotInheritable Class Connections

    Private Sub New()

    End Sub

    Public Shared Function GetConnection(ByVal connString As String) As SqlConnection
        Dim c as New SqlConnection(connString)
        c.Open
        Return c
    End Sub

    Public Shared Sub AdoCleanup(cn As SqlConnection, cmd As SqlCommand)
        cmd.Dispose
        cn.Close
    End Sub

End Class

End Namespace

And then use it like so:

Private Sub LoadMyData()

    Dim connString As String = <your conn string>
    Dim cn As SqlConnection = Helpers.Connections.GetConnection(connString)
    Dim cmd As New SqlCommand

    Try
        ' data access code
    Catch ex As Exception
        ' handle exception
    Finally
        Helpers.Connections.AdoCleanup(cn, cmd)
    End Try

End Sub

You could even put the code to get the connection string into GetConnection, unless you need the flexibility of opening multiple connections with different connection strings.

Answer 5

NEVER PASS A CONNECTION.

what i usually do, i create a main controller that every controller inherits from it, and i defined on the connection over there. then when every i initiate a connection object i use (using).