SQL Server Connection

I'm trying to create a function that will return a vaild sql server connection. I want to check that a sql server connection is available every the program will access the server. If this works, I can trap any errors even when the server get shutdown while the client app is still running. If there are better ideas, I'd like to hear them.

The problem I'm having is that this function will return an open connection even if you try to close it.

Here is what I have so far...

Code:

---

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Win32
Module modSQL
        Public gblSQLConnectionString As String


        Public Function cn() As SqlConnection
                'This function checks a sql connection and returns a valid connection
                Dim mySqlConnection As New SqlConnection(gblSQLConnectionString)
                Try
                        mySqlConnection.Open()                        'test the connection - if this doesn't work it will jump to Catch
                        Return mySqlConnection                        'if it connects, then return the connection

                Catch ex As SqlException
                        Dim msg As String
                        Dim i As Integer

                        For i = 0 To ex.Errors.Count - 1
                                msg = "Unable to connect to the database" & ControlChars.CrLf
                                msg &= "Server: " & ex.Server & ControlChars.CrLf
                                msg &= "Message: " & ex.Message & ControlChars.CrLf
                        Next

                        MessageBox.Show(msg, "Connection Problem", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
                Catch ex As Exception
                        MessageBox.Show(ex.Message, "Connection Problem", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
                Finally
                        'mySqlConnection.Close()
                End Try

        End Function

        Sub CreateSQLConnectionString(ByVal strServer As String, ByVal strDatabase As String)
                gblSQLConnectionString = "data source= " & strServer & ";initial catalog=" & strDatabase & ";integrated security=SSPI;persist security info=False;packet size=4096"
        End Sub

---

You are returning an open connection

VB Code:

mySqlConnection.Open()           'test the connection - if this doesn't work it will jump to Catch
            Return mySqlConnection           'if it connects, then return the connection

So why do you expect it to be closed when you collect the return value of your function call cn() ?

As for alternatives I would personally use a class instead of a module to encapsulate the necessary code for all database operations, i.e. expose a method to accept an SQL statement to return a dataset, another to execute UPDATE statements etc.

Well, I'm new to this and I can't seem to find a way to to have a function work.

Do you have an example of doing something like this in a class?