SQL Server Connection

[DBlade]

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...

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

[Mr.No]

You are returning an open connection

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.

[DBlade]

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?