Best way to pass a connection object among forms?

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.