Imports System.DataImports System.Data.SqlImports 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
