I have implemented my own DataAccess Layer and classes for the Data-Centric GIS applications I build/maintain. While these are typically N-Tier applications that are also "GIS", I tend to consolodate my db connections (across servers sometimes) into their own classes. Here is an example of a class you could implement that you'd add to your project/assembly (or into another assembly you could reference -- it doesn't really matter).
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class vrConn
Implements IDisposable
Private disposedValue As Boolean = False
Private _LastException As SqlException
Private sqlCn As New SqlConnection()
Public Sub New()
MyBase.New()
Me.openConnection(GetConnectionString)
End Sub
Public Property sqlConn() As SqlConnection
Get
Return sqlCn
End Get
Set(ByVal value As SqlConnection)
sqlCn = value
End Set
End Property
Public Overrides Function ToString() As String
If Me.sqlCn Is Nothing Then
Return ""
Else
Return Me.sqlCn.ToString
End If
End Function
Public Sub openConnection(ByVal cnnString As String)
If sqlCn.State <> ConnectionState.Open Then
Try
If Me.sqlCn Is Nothing Then
Me.sqlCn = New SqlConnection
End If
sqlCn.ConnectionString = cnnString
sqlCn.Open()
Catch sqlEx As SqlException
Me._LastException = sqlEx
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
End Sub
Public Sub CloseConnection()
If sqlCn.State = ConnectionState.Open Then
sqlCn.Close()
End If
Me.Dispose()
End Sub
Private Function GetConnectionString() As String
Return "Data Source=ServerName;" _
& "Initial Catalog=DataBaseName; Integrated Security=True;"
End Function
Public ReadOnly Property LastException() As SqlException
Get
Return _LastException
End Get
End Property
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
Me.sqlCn = Nothing
GC.Collect()
End If
End If
Me.disposedValue = True
End Sub
#Region " IDisposable Support "
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class
Ok, so once you have this class in your project, you can just reference this and open the connection from whatever other class/form/whatever you are trying to run the StoredProcedure from. This happens to be a "DALC" class (DataAccessLayerClass) I use in one of my assemblies:
Imports System.Data.Sql
Imports System.Data.SqlClient
Private sqlCn As New SqlConnection()
Private connSvc As vrConn
Public Sub New()
MyBase.New()
connSvc = New vrConn
sqlCn = connSvc.sqlConn
End Sub
'Here is a funciton that fills a DataTable of "Species" via a Stored Procedure
Public Function Hydrate_speciesList() As DataTable
Dim spCmdSpec As SqlCommand = New SqlCommand()
spCmdSpec.CommandText = "dbo.SpeciesSelect"
spCmdSpec.CommandType = CommandType.StoredProcedure
Dim ds As New DataSet()
Dim da As New SqlDataAdapter
Try
Me.OpenConnection()
Using spCmdSpec
spCmdSpec.Connection = Me.sqlCn
da.SelectCommand = spCmdSpec
da.Fill(ds, "species")
End Using
Me.CloseConnection()
Dim tb As DataTable = ds.Tables("species")
tb.Rows.RemoveAt(0)
Return tb
Catch ex As Exception
MsgBox(ex.ToString)
Me.CloseConnection()
Return Nothing
End Try
End Function
'database connectivity
Private Sub CloseConnection()
If sqlCn.State = ConnectionState.Open Then
sqlCn.Close()
End If
End Sub
Private Sub OpenConnection()
If sqlCn.State = ConnectionState.Closed Then
sqlCn.Open()
End If
End Sub