DBHelper Class

Lately, I've been busy doing some of the codes for the system that we're working with my team at the office. I just wanted to share this VB.NET class that I made and is being used as the SQL Connection Helper Class for the project that I am into. Enjoy!., comments are welcome.. :)


Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
''' <summary>
''' DBHelper Class
''' </summary>
''' <remarks></remarks>
Public Class DBHelper
    Implements IDisposable
    
    Private _SQLConnectionString As New SqlConnectionStringBuilder
    Private _SQLConnection As New SqlConnection
    Private _SQLCommand As SqlCommand
    Private _DataAdapter As SqlDataAdapter
    Private _DataSet As DataSet
    Private _SQLParameters As SqlParameterCollection = New SqlCommand().Parameters
    Private _AffectedRows As Integer

    ''' <summary>
    ''' SQL Connection string builder for an instance of this class
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ConnectionStringBuilder() As SqlConnectionStringBuilder
        Get
            Return _SQLConnectionString
        End Get
    End Property

    ''' <summary>
    ''' Row count of affected rows from executing an SQL query
    ''' </summary>
    ''' <value></value>
    ''' <returns>returns an integer value</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property AffectedRows() As Integer
        Get
            Return _AffectedRows
        End Get
    End Property

    ''' <summary>
    ''' Result dataset from a query
    ''' </summary>
    ''' <value></value>
    ''' <returns>returns DataSet object</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ReturnedDataSet() As DataSet
        Get
            Return _DataSet
        End Get
    End Property

    ''' <summary>
    ''' DBHelper Constructor
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub New()
        With _SQLConnectionString
            .DataSource = "(local)\sqlexpress"
            .InitialCatalog = "PacificPOS_db_yhan"
            .UserID = "sa"
            .Password = "password01"
        End With
        _SQLConnection.ConnectionString = _SQLConnectionString.ConnectionString
    End Sub

    ''' <summary>
    ''' DBHelper Constructor
    ''' </summary>
    ''' <param name="connectionString">SQL Connection String</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal connectionString As String)
        _SQLConnection.ConnectionString = connectionString
    End Sub

    ''' <summary>
    ''' Method for adding SQL Parameters
    ''' </summary>
    ''' <param name="parameterName"></param>
    ''' <param name="value"></param>
    ''' <remarks></remarks>
    Public Sub AddParameter(ByVal parameterName As String, ByVal value As Object)
        _SQLParameters.AddWithValue(parameterName, value)
    End Sub

    ''' <summary>
    ''' Method for exectution of SQL Queries
    ''' </summary>
    ''' <param name="query">SQL query string</param>
    ''' <param name="hasResult">if this method will have a DataSet result</param>
    ''' <param name="sqlCommandType">type of sql command</param>
    ''' <remarks></remarks>
    Public Sub ExecuteQuery(ByVal query As String, _
                                 ByVal hasResult As GlobalDeclaration.HasDSResult, _
                                 ByVal sqlCommandType As CommandType)
        Try
            _SQLConnection.Open()

            _SQLCommand = New SqlCommand
            _SQLCommand.Connection = _SQLConnection
            _SQLCommand.CommandType = sqlCommandType
            _SQLCommand.CommandText = query

            For Each sqlParam As SqlParameter In _SQLParameters
                _SQLCommand.Parameters.AddWithValue(sqlParam.ParameterName, sqlParam.Value)
            Next

            If hasResult = GlobalDeclaration.HasDSResult.Yes Then
                _DataAdapter = New SqlDataAdapter(_SQLCommand)
                _DataSet = New DataSet
                _DataAdapter.Fill(_DataSet)
            Else
                _AffectedRows = _SQLCommand.ExecuteNonQuery()
            End If

            'Reset parameters
            _SQLParameters = Nothing
            _SQLParameters = New SqlCommand().Parameters

            _SQLConnection.Close()
        Catch ex As Exception
            'Reset parameters
            If _SQLParameters IsNot Nothing Then
                _SQLParameters = Nothing
                _SQLParameters = New SqlCommand().Parameters
            End If

            _SQLConnection.Close()
            Throw
        End Try
    End Sub

    Public Shared ReadOnly Property GetDataSources() As DataTable
        Get
            Return SqlDataSourceEnumerator.Instance.GetDataSources
        End Get
    End Property

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        Dispose()
        Me._SQLCommand.Dispose()
        Me._SQLConnection.Dispose()
        Me._DataAdapter.Dispose()
        Me._DataSet.Dispose()
    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