ADO Helper

 

#Region “- Includes”
Imports System.Text
Imports System.Data
Imports System.Xml
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Collections
#End Region

Public Class clsDBA
Implements IDisposable
#Region “Local Variable and Objects”
Private strConnectionString As String
Private objConnection As DbConnection
Private objCommand As DbCommand
Private objFactory As DbProviderFactory = Nothing

#End Region

#Region ” – Constructors”

Public Sub New(ByVal connectionstring As String)
strConnectionString = connectionstring

objFactory = SqlClientFactory.Instance
objConnection = objFactory.CreateConnection()
objCommand = objFactory.CreateCommand()

objConnection.ConnectionString = strConnectionString
objCommand.Connection = objConnection
End Sub

#End Region

#Region ” – Command”

Public ReadOnly Property Command() As DbCommand
Get
Return objCommand
End Get
End Property

#End Region

#Region ” – AddParameter”

Public Function AddParameter(ByVal name As String, ByVal value As Object) As Integer
Dim p As DbParameter = objFactory.CreateParameter()
Try
p.ParameterName = name
p.Value = value
Catch ex As Exception

End Try

Return objCommand.Parameters.Add(p)
End Function

#End Region

#Region ” – AddParameter”

Public Function AddParameter(ByVal name As String, ByVal value As Object, ByVal direction As String) As Integer
Dim p As DbParameter = objFactory.CreateParameter()
Try
p.ParameterName = name
p.Value = value

If direction.ToUpper() = “output”.ToUpper() Then
p.Direction = ParameterDirection.Output
End If

Catch ex As Exception

End Try

Return objCommand.Parameters.Add(p)
End Function

#End Region

#Region ” – Open/CloseConnection”

Public Sub OpenConnection()
‘ Clear DB Connection Pooling
ClearDBConnectionPool(objConnection)

‘ Forcing the connection to close
objConnection.Close()

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If
End Sub

Public Sub CloseConnection()
‘ Clear DB Connection Pooling
ClearDBConnectionPool(objConnection)

‘ Forcing the connection to close
objConnection.Close()
End Sub

#End Region

#Region ” – ClearDBConnectionPool”
”’
”’ To clear the DB connection pooling
”’

”’Connection Object Private Sub ClearDBConnectionPool(ByVal objConnection As DbConnection)
Try
SqlConnection.ClearPool(DirectCast(objConnection, SqlConnection))
Catch ex As Exception

End Try
End Sub
#End Region

#Region ” – Dispose”

Public Sub Dispose() Implements IDisposable.Dispose
ClearDBConnectionPool(objConnection)
If objConnection.State = System.Data.ConnectionState.Open Then
objConnection.Close()
End If
objConnection.Dispose()
objCommand.Dispose()
End Sub

#End Region

#Region ” – ExecuteNonQuery”

Public Function ExecuteNonQuery(ByVal query As String) As Integer
Dim intIndex As Integer = -1

Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If

objCommand.ExecuteNonQuery()

For intjIndex As Integer = 0 To objCommand.Parameters.Count – 1
If objCommand.Parameters(intjIndex).Direction.ToString().ToUpper() = “output”.ToUpper() Then
intIndex = Convert.ToInt32(objCommand.Parameters(intjIndex).Value)
End If
Next
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return intIndex
End Function

#End Region

#Region ” – ExecuteScalar”

Public Function ExecuteScalar(ByVal query As String) As Object
Dim o As Object = Nothing
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure

If objConnection.State = System.Data.ConnectionState.Closed Then
objConnection.Open()
End If
o = objCommand.ExecuteScalar()
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try

Return o
End Function

#End Region

#Region ” – ExecuteDataSet”

Public Function ExecuteDataSet(ByVal query As String) As DataSet

Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter()
Dim dsResult As New DataSet()
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = objCommand

adapter.Fill(dsResult)
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return dsResult
End Function

#End Region

#Region ” – ExecuteDataTable”

Public Function ExecuteDataTable(ByVal query As String) As DataTable
Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter()
Dim dsResult As New DataSet()
Try
objCommand.CommandText = query
objCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = objCommand

adapter.Fill(dsResult)
Catch ex As Exception
Throw ex
Finally
objCommand.Parameters.Clear()
End Try
Return dsResult.Tables(0)
End Function

#End Region

End Class

 

Advertisements

One thought on “ADO Helper

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s