One database reusable class for SQL Server communication

create two classes
1. 'SQLConnection' (main class to communicate with db)
2. 'SQLStoredProcedureParameterInfo' to be called from main class to get parameter info.


Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Collections.Generic
Imports System.Data.SqlClient

'class 1
Public Class SQLConnection
Inherits MarshalByRefObject
Implements IDisposable

'Private variables
Private m_sConnectionString As String

Private m_objConnection As SqlClient.SqlConnection = Nothing
Private m_objTransaction As SqlClient.SqlTransaction = Nothing

Private disposedValue As Boolean = False ' To detect redundant calls

Private Shared m_objCommandParameterInfo As New Hashtable

Public Sub New(ByVal ConnectionString As String)
m_sConnectionString = ConnectionString
End Sub

Public Function TestConnection() As Boolean
Dim bRetVal As Boolean = False

Const iTestTimeout As Integer = 5 'seconds

Try
With OpenConnection(iTestTimeout)
bRetVal = (.State = ConnectionState.Open)
End With
Catch ex As Exception
bRetVal = False
End Try

Return bRetVal
End Function

Public Function OpenConnection(Optional ByVal Timeout As Integer = 15) As SqlClient.SqlConnection
If (m_objConnection Is Nothing) Then
Dim sConnString As New SqlClient.SqlConnectionStringBuilder(m_sConnectionString)

sConnString.ConnectTimeout = Timeout

m_objConnection = New SqlClient.SqlConnection(sConnString.ToString)
End If

If m_objConnection.State = ConnectionState.Closed Then
With m_objConnection
.Open()
End With
End If

Return m_objConnection
End Function

Public Sub CloseConnection()
If (m_objConnection IsNot Nothing) Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If

m_objConnection = Nothing
End If
End Sub

Public Function ConnectSP(ByVal Name As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand

'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(Name, OpenConnection, m_objTransaction)
End If

With objRetVal
.CommandType = CommandType.StoredProcedure

RetrieveCommandParameters(objRetVal)
End With

Return objRetVal
End Function

Public Function ConnectSQL(ByVal SQL As String) As SqlClient.SqlCommand
Dim objRetVal As SqlClient.SqlCommand

'Set up SQL command
If m_objTransaction Is Nothing Then
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection)
Else
objRetVal = New SqlClient.SqlCommand(SQL, OpenConnection, m_objTransaction)
End If

With objRetVal
.CommandType = CommandType.Text
End With

Return objRetVal
End Function


Public Function BeginTransaction() As Boolean
Dim bRetVal As Boolean = True

Try
With OpenConnection()
m_objTransaction = .BeginTransaction()
End With
Catch
bRetVal = False
End Try

Return bRetVal
End Function

Public Function CommitTransaction() As Boolean
Dim bRetVal As Boolean = True

Try
m_objTransaction.Commit()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try

Return bRetVal
End Function

Public Function RollbackTransaction() As Boolean
Dim bRetVal As Boolean = True

Try
m_objTransaction.Rollback()
m_objTransaction = Nothing
Catch
bRetVal = False
End Try

Return bRetVal
End Function

Private Sub RetrieveCommandParameters(ByVal SQLCommand As SqlClient.SqlCommand)
Dim lstParameterInfo As List(Of SQLStoredProcedureParameterInfo)

With SQLCommand
If m_objCommandParameterInfo.Contains(.CommandText) Then
'Get cached parameter info
lstParameterInfo = m_objCommandParameterInfo(.CommandText)

'Loop through and add a new parameter
For Each objParameterInfo As SQLStoredProcedureParameterInfo In lstParameterInfo
.Parameters.Add(objParameterInfo.CreateParameter())
Next
Else
'Retrieve parameters from database server
SqlClient.SqlCommandBuilder.DeriveParameters(SQLCommand)

'Build parameter info list
lstParameterInfo = New List(Of SQLStoredProcedureParameterInfo)
For Each objParameter As SqlClient.SqlParameter In .Parameters
lstParameterInfo.Add(New SQLStoredProcedureParameterInfo(objParameter))
Next

'...and add into cache for next time
m_objCommandParameterInfo.Add(.CommandText, lstParameterInfo)
End If
End With
End Sub

' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
m_objTransaction = Nothing

If m_objConnection IsNot Nothing Then
If m_objConnection.State <> ConnectionState.Closed Then
m_objConnection.Close()
End If

m_objConnection = Nothing
End If
End If

' TODO: free shared unmanaged resources
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

'Class 2
Public Class SQLStoredProcedureParameterInfo
Public Name As String
Public Type As System.Data.SqlDbType
Public Direction As System.Data.ParameterDirection
Public DefaultValue As Object
Public Size As Integer

Public Sub New(ByVal Parameter As SqlClient.SqlParameter)
With Parameter
Name = .ParameterName
Type = .SqlDbType
Direction = .Direction
DefaultValue = .Value
Size = .Size
End With
End Sub

Public Function CreateParameter() As SqlClient.SqlParameter
Dim objRetVal As New SqlClient.SqlParameter

With objRetVal
.ParameterName = Name
.SqlDbType = Type
.Direction = Direction
.Value = DefaultValue
.Size = Size
End With

Return objRetVal
End Function
End Class

''''method to use this class as below:

Using objSqlConnection As SQLConnection = New SQLConnection(ConfigurationManager.AppSettings.Item("SQLConnString").ToString)
Dim strSQL As String

'for SQL
Dim BusinessRegionIntroducerGroupId As Integer
strSQL = "select * from table1 where employid=" & Session("empId")
With objSqlConnection.ConnectSQL(strSQL)
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
If objSQLdatareader.Read Then
empname= objSQLdatareader.Item("empname")
End If
objSQLdatareader.Close()
End With


'for SP
strSQL = "spSaveEmployee"
With objSqlConnection.ConnectSP(strSQL)
.Parameters("@DeptId").Value = DeptId
.Parameters("@empid").Value = Session("empid").ToString
Dim objSQLdatareader As SqlClient.SqlDataReader = .ExecuteReader()
objSQLdatareader.Close()
End With
End Using