Find all our projects in development below.
All source code is GNU General Public License (GPL)
Browsing clsSqlClient.vb (9.34 KB)
Option Explicit On
Imports System.Data.SqlClient
Public Class clsSqlClient
Implements IDisposable
' clsQueryResult: query result wrapper class
Public Class clsQueryResult
Implements IDisposable
' class object references
Public thisResult As SqlDataReader = Nothing
Public Sub New(ByVal objReader As SqlDataReader)
' store a reference to the result reader
thisResult = objReader
End Sub
Public Function Read() As Boolean
' read the next result
Return thisResult.Read()
End Function
Public Function GetOrdinal(ByVal colname As String) As Integer
' return the column index for the column name
Return thisResult.GetOrdinal(colname)
End Function
Public Function GetName(ByVal colnum As Integer) As String
' return the col name as string
Return thisResult.GetName(colnum)
End Function
Public Overloads Function GetString(ByVal colnum As Integer) As String
' return the col value as string by ordinal
Return thisResult.GetString(colnum)
End Function
Public Overloads Function GetString(ByVal colname As String) As String
' return the col value as string by name
Return Me.GetString(Me.GetOrdinal(colname))
End Function
Public Overloads Function GetValue(ByVal colnum As Integer) As Object
' return the col value as object by ordinal
Return thisResult.GetValue(colnum)
End Function
Public Overloads Function GetValue(ByVal colname As String) As Object
' return the col value as object by name
Return Me.GetValue(Me.GetOrdinal(colname))
End Function
Public Overloads Function GetInt16(ByVal colnum As Integer) As Short
' return the col value as short by ordinal
Return thisResult.GetInt16(colnum)
End Function
Public Overloads Function GetInt16(ByVal colname As String) As Short
' return the col value as short by name
Return Me.GetInt16(Me.GetOrdinal(colname))
End Function
Public Overloads Function GetInt32(ByVal colnum As Integer) As Integer
' return the col value as integer by ordinal
Return thisResult.GetInt32(colnum)
End Function
Public Overloads Function GetInt32(ByVal colname As String) As Integer
' return the col value as integer by name
Return Me.GetInt32(Me.GetOrdinal(colname))
End Function
Public Overloads Function GetByte(ByVal colnum As Integer) As Byte
' return the col value as Byte by ordinal
Return thisResult.GetByte(colnum)
End Function
Public Overloads Function GetByte(ByVal colname As String) As Byte
' return the col value as Byte by name
Return Me.GetByte(Me.GetOrdinal(colname))
End Function
Public Overloads Function GetBoolean(ByVal colnum As Integer) As Boolean
' return the col value as Boolean by ordinal
Return thisResult.GetBoolean(colnum)
End Function
Public Overloads Function GetBoolean(ByVal colname As String) As Boolean
' return the col value as Boolean by name
Return Me.GetBoolean(Me.GetOrdinal(colname))
End Function
Public Overloads Function GetDateTime(ByVal colnum As Integer) As Date
' return the col value as Date by ordinal
Return thisResult.GetDateTime(colnum)
End Function
Public Overloads Function GetDateTime(ByVal colname As String) As Date
' return the col value as Date by name
Return Me.GetDateTime(Me.GetOrdinal(colname))
End Function
Public Sub Advance(ByVal iOffset As Integer)
' advance iOffset number of records
Do While iOffset > 0
Me.Read()
iOffset -= 1
Loop
End Sub
Public Sub Dispose() Implements IDisposable.Dispose
' close the datareader
thisResult.Close()
End Sub
End Class
' class object references
Friend ReadOnly CONN_STR As String = ""
Public Conn As SqlConnection = Nothing
Public LastErrorException As Exception = Nothing
Public CommandTimeout As Integer = 60 ' seconds
Private Const COUNT_QUERY As String = "select count({0}) from {1}"
Public Sub New(ByVal ConnString As String)
' store the connection string
CONN_STR = ConnString
' initialize the db connection object
_initDBConn()
End Sub
Private Sub _initDBConn()
' instantiate the db connection class
Conn = New SqlConnection()
End Sub
Public Function RunQuery(ByVal strQuery As String) As clsQueryResult
Try
SyncLock Me.Conn
' instantiate a new db command
Dim thisCommand As New SqlCommand(strQuery, Me.Conn)
' set the timeout for this command
thisCommand.CommandTimeout = CommandTimeout
' run the query and store result
Dim retResult As SqlDataReader = thisCommand.ExecuteReader()
thisCommand.Dispose()
' instantiate the clsQueryResult class with the query result
Return New clsQueryResult(retResult)
End SyncLock
Catch ex As Exception
LastErrorException = ex
Return Nothing
End Try
End Function
Public Function ExecScalar(ByVal strQuery As String) As Object
Try
SyncLock Me.Conn
' instantiate a new db command
Dim thisCommand As New SqlCommand(strQuery, Me.Conn)
' set the timeout for this command
thisCommand.CommandTimeout = CommandTimeout
' run the query and store result
Dim retObject As Object = thisCommand.ExecuteScalar()
thisCommand.Dispose()
' return scalar object
Return retObject
End SyncLock
Catch ex As Exception
LastErrorException = ex
Return Nothing
End Try
End Function
Public Function ExecNonQuery(ByVal strQuery As String) As Integer
Try
SyncLock Me.Conn
' instantiate a new db command
Dim thisCommand As New SqlCommand(strQuery, Me.Conn)
' set the timeout for this command
thisCommand.CommandTimeout = CommandTimeout
' run the query and store result
Dim retInteger As Integer = thisCommand.ExecuteNonQuery()
thisCommand.Dispose()
' return rows affected
Return retInteger
End SyncLock
Catch ex As Exception
LastErrorException = ex
Return 0
End Try
End Function
Public Function GetRecordCount(ByVal strTable As String, _
Optional ByVal strWhere As String = "", _
Optional ByVal colName As String = "*") As Integer
Try
' get the record count of the comments for this post
Return CType(Me.ExecScalar(String.Format(COUNT_QUERY, _
colName, strTable + IIf(strWhere = "", "", " where " + _
strWhere))), Integer)
Catch ex As Exception
LastErrorException = ex
Return 0
End Try
End Function
Public Shared Function SanitizeText(ByVal strInput As String) As String
' prep the var for db insertion/update
Return strInput.Replace("'", "''").Replace("%", "%%")
End Function
Public Function ConnectDb() As Boolean
Try
If My.Computer.Network.IsAvailable Then
If Not (Conn Is Nothing) Then
' configure the connection
Conn.ConnectionString = CONN_STR
' open the connection
Conn.Open()
Return Me.IsConnected
Else
Throw New Exception("Unable to create a new connection object.")
End If
Else
Throw New Exception("No internet connectivity available.")
End If
Catch ex As Exception
LastErrorException = ex
Return False
End Try
End Function
Public Sub ReConnectDB()
' terminate the connection
Me.Dispose()
' reset the db connection object
_initDbConn()
' retry the connection
Me.ConnectDb()
End Sub
Public Sub Dispose() Implements IDisposable.Dispose
' terminate the connection
Conn.Close()
Conn.Dispose()
End Sub
Public ReadOnly Property IsConnected() As Boolean
Get
Return (Conn.State = Data.ConnectionState.Open)
End Get
End Property
End Class