Option Explicit On
Option Strict Off
Option Compare Text
'=============================================================================================================
'
' cSqlClient.vb
' -------------
'
' Created By : Kevin Wilson
' http://www.TheVBZone.com ( The VB Zone )
' http://www.TheVBZone.net ( The VB Zone .net )
'
' Last Update : April 20, 2005
' Created On : October 03, 2003
'
' VB Versions : VB.NET 1.1 (VS.NET 2003)
'
' Requires : The .NET Framework v1.1
'
' Description : This class module makes it easy to access your database via the optimized "System.Data.SqlClient"
' interface and manage the information returned.
'
' See Also : http://msdn.microsoft.com/vstudio/using/understand/data/default.aspx?pull=/library/en-us/dndotnet/html/usingadonet.asp
' http://msdn.microsoft.com/vstudio/using/understand/data/default.aspx?pull=/library/en-us/dnbda/html/daag.asp
' http://msdn.microsoft.com/vcsharp/using/understanding/data/default.aspx?pull=/library/en-us/dnadonet/html/adonetbest.asp
' http://msdn.microsoft.com/code/list/ado.asp
'
' Example Use :
'
'-------------------------------------------------------------------------------------------------------------
' "DataSet" object returned
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cSqlClient("SERVER=127.0.0.1;Database=pubs;UID=pubs;PWD=password;", 30)
' Dim objDS As Data.DataSet
' Dim objDR As Data.DataRow
' Dim intRows As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDS, False, intRows, intErrNum, strErrSrc, strErrDesc) = False Then
' Response.Write("The following error occured:
Error Number = " & intErrNum & "
Error Source = " & strErrSrc & "
Error Description = " & strErrDesc)
' Else
' For Each objDR In objDS.Tables(0).Rows
' Response.Write(CStr(objDR("job_id") & "") & " / " & CStr(objDR("job_desc") & "") & "
")
' Next
' objDR = Nothing
' objDS = Nothing
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' "DataTable" object returned
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cSqlClient("SERVER=127.0.0.1;Database=pubs;UID=pubs;PWD=password;", 30)
' Dim objDT As Data.DataTable
' Dim objDR As Data.DataRow
' Dim intRows As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDT, False, intRows, intErrNum, strErrSrc, strErrDesc) = False Then
' Response.Write("The following error occured:
Error Number = " & intErrNum & "
Error Source = " & strErrSrc & "
Error Description = " & strErrDesc)
' Else
' For Each objDR In objDT.Rows
' Response.Write(CStr(objDR("job_id") & "") & " / " & CStr(objDR("job_desc") & "") & "
")
' Next
' objDR = Nothing
' objDT = Nothing
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' "SqlDataReader" object returned
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cSqlClient("SERVER=127.0.0.1;Database=pubs;UID=pubs;PWD=password;", 30)
' Dim objDR As SqlClient.SqlDataReader
' Dim intEffected As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDR, False, intEffected, intErrNum, strErrSrc, strErrDesc) = False Then
' Response.Write("The following error occured:
Error Number = " & intErrNum & "
Error Source = " & strErrSrc & "
Error Description = " & strErrDesc)
' Else
' Do While objDR.Read = True
' Response.Write(CStr(objDR("job_id") & "") & " / " & CStr(objDR("job_desc") & "") & "
")
' Loop
' objDR = Nothing
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' DELETE SQL statement executed using "SqlDataReader"
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cSqlClient("SERVER=127.0.0.1;Database=pubs;UID=pubs;PWD=password;", 30)
' Dim objDR As SqlClient.SqlDataReader
' Dim intEffected As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("DELETE FROM [jobs] WHERE [job_id] = 16", CommandType.Text, objDR, False, intEffected, intErrNum, strErrSrc, strErrDesc) = False Then
' Response.Write("The following error occured:
Error Number = " & intErrNum & "
Error Source = " & strErrSrc & "
Error Description = " & strErrDesc)
' Else
' Response.Write("Successfully deleted " & intEffected & " record(s)")
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' DataGrid control populated by a "DataSet"
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As cSqlClient
' Dim objDS As DataSet
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If Page.IsPostBack = False Then
' objDB = New cSqlClient("SERVER=127.0.0.1;Database=pubs;UID=pubs;PWD=password;", 30)
' If objDB.ExecuteSQL("SELECT * FROM [authors]", CommandType.Text, objDS, False, , intErrNum, strErrSrc, strErrDesc) = False Then
' Response.Write("The following error occured:
Error Number = " & intErrNum & "
Error Source = " & strErrSrc & "
Error Description = " & strErrDesc)
' Else
' DataGrid1.DataSource = objDS
' DataGrid1.DataBind()
' End If
' objDB = Nothing
' End If
'
'=============================================================================================================
'
' LEGAL:
'
' You are free to use this code as long as you keep the above heading information intact and unchanged. Credit
' given where credit is due. Also, it is not required, but it would be appreciated if you would mention
' somewhere in your compiled program that that your program makes use of code written and distributed by
' Kevin Wilson (www.TheVBZone.com). Feel free to link to this code via your web site or articles.
'
' You may NOT take this code and pass it off as your own. You may NOT distribute this code on your own server
' or web site. You may NOT take code created by Kevin Wilson (www.TheVBZone.com) and use it to create products,
' utilities, or applications that directly compete with products, utilities, and applications created by Kevin
' Wilson, TheVBZone.com, or Wilson Media. You may NOT take this code and sell it for profit without first
' obtaining the written consent of the author Kevin Wilson.
'
' These conditions are subject to change at the discretion of the owner Kevin Wilson at any time without
' warning or notice. Copyright© by Kevin Wilson. All rights reserved.
'
'=============================================================================================================
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft
Imports Microsoft.VisualBasic
Public Class cSqlClient
Private Enum ReturnType
rt_DataReader = 0
rt_DataTable = 1
rt_DataSet = 2
End Enum
#Region "Property Variable Declarations"
Private WithEvents m_oDbConnection As SqlConnection
Private m_oTransaction As SqlTransaction
Private m_sConnString As String
Private m_iTimeOutConn As Integer
Private m_iTimeOutCmd As Integer
#End Region
#Region "Public Events"
Public Event StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs)
Public Event InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
#End Region
#Region "Private Events"
Private Sub m_oDbConnection_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles m_oDbConnection.StateChange
RaiseEvent StateChange(sender, e)
End Sub
Private Sub m_oDbConnection_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs) Handles m_oDbConnection.InfoMessage
RaiseEvent InfoMessage(sender, e)
End Sub
#End Region
#Region "Constructors"
Public Sub New()
m_iTimeOutConn = 30
m_iTimeOutCmd = 120
m_sConnString = ""
End Sub
Public Sub New(ByVal strConnectionString As String, Optional ByVal intTimeOut_Conn As Integer = 30, Optional ByVal intTimeOut_Cmd As Integer = 120)
m_iTimeOutConn = intTimeOut_Conn
m_iTimeOutCmd = intTimeOut_Cmd
m_sConnString = strConnectionString
If Right(m_sConnString, 1) <> ";" Then m_sConnString = m_sConnString & ";"
End Sub
#End Region
#Region "Deconstructors"
Protected Overrides Sub Finalize()
CleanUpConnection(m_oDbConnection)
MyBase.Finalize()
End Sub
#End Region
#Region "Public Properties"
' The following is an example of a connection string:
' "Data Source=127.0.0.1;Initial Catalog=Pubs;Connection Timeout=30;Integrated Security=false;Persist Security Info=false;User ID=;Password=;"
Public Property ConnectionString() As String
Get
Return m_sConnString
End Get
Set(ByVal Value As String)
m_sConnString = Value
If Right(m_sConnString, 1) <> ";" Then m_sConnString = m_sConnString & ";"
End Set
End Property
' Gets or sets the number of seconds to wait for a connection to complete before timing out
Public Property TimeOut_Connect() As Integer
Get
Return m_iTimeOutConn
End Get
Set(ByVal Value As Integer)
If Value >= 0 Then m_iTimeOutConn = Value
End Set
End Property
' Gets or sets the number of seconds to wait for a SQL query to execute before timing out
Public Property TimeOut_Execute() As Integer
Get
Return m_iTimeOutCmd
End Get
Set(ByVal Value As Integer)
If Value >= 0 Then m_iTimeOutCmd = Value
End Set
End Property
' Gets or sets the connection object that this class uses to communicate with the database
Public Property ConnectionObject() As SqlConnection
Get
Return m_oDbConnection
End Get
Set(ByVal Value As SqlConnection)
m_oDbConnection = Value
End Set
End Property
' Gets the current transaction object which was created via the "TransactionBegin" method
Public ReadOnly Property TransactionObject() As SqlTransaction
Get
Return m_oTransaction
End Get
End Property
#End Region
#Region "Public Methods"
' Begins a transaction. All queries executed after this, and before calling "TransactionCommit" or "TransactionRollback", will be wrapped in a transaction (all execute, or none do)
Public Function TransactionBegin(Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
On Error GoTo ErrorTrap
' Set default values
Return_ErrNum = 0
Return_ErrSrc = ""
Return_ErrDesc = ""
' Make sure there's a valid connection
If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False
' If a transaction was already started, commit it before creating another
If Not m_oTransaction Is Nothing Then
m_oTransaction.Commit()
m_oTransaction = Nothing
End If
' Start the transaction
m_oTransaction = m_oDbConnection.BeginTransaction
' Function executed successfully
Return True
ErrorTrap:
Return_ErrNum = Err.Number
Return_ErrSrc = Err.Source
If Return_ErrSrc = "" Then
Return_ErrSrc = "TransactionBegin()"
Else
Return_ErrSrc = Return_ErrSrc & " [TransactionBegin()]"
End If
Return_ErrDesc = Err.Description
Err.Clear()
End Function
' Commits all SQL queries that have occured since calling the "TransactionBegin" method
Public Function TransactionCommit(Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
On Error GoTo ErrorTrap
' Set default values
Return_ErrNum = 0
Return_ErrSrc = ""
Return_ErrDesc = ""
' Start the transaction
If m_oTransaction Is Nothing Then
Err.Raise(-1, "", "No transaction to commit")
Else
m_oTransaction.Commit()
m_oTransaction = Nothing
End If
' Function executed successfully
Return True
ErrorTrap:
Return_ErrNum = Err.Number
Return_ErrSrc = Err.Source
If Return_ErrSrc = "" Then
Return_ErrSrc = "TransactionCommit()"
Else
Return_ErrSrc = Return_ErrSrc & " [TransactionCommit()]"
End If
Return_ErrDesc = Err.Description
Err.Clear()
m_oTransaction = Nothing
End Function
' Rolls back all SQL queries that have occured since calling the "TransactionBegin" method
Public Function TransactionRollback(Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
On Error GoTo ErrorTrap
' Set default values
Return_ErrNum = 0
Return_ErrSrc = ""
Return_ErrDesc = ""
' Start the transaction
If m_oTransaction Is Nothing Then
Err.Raise(-1, "", "No transaction to rollback")
Else
m_oTransaction.Rollback()
m_oTransaction = Nothing
End If
' Function executed successfully
Return True
ErrorTrap:
Return_ErrNum = Err.Number
Return_ErrSrc = Err.Source
If Return_ErrSrc = "" Then
Return_ErrSrc = "TransactionRollback()"
Else
Return_ErrSrc = Return_ErrSrc & " [TransactionRollback()]"
End If
Return_ErrDesc = Err.Description
Err.Clear()
m_oTransaction = Nothing
End Function
' (see documentation for "ExecuteSQL_" method)
Public Overloads Function ExecuteSQL(ByVal sSQL As String, _
ByVal CommandType As CommandType, _
ByRef Return_DataSet As DataSet, _
Optional ByRef Return_RowsEffected As Integer = -1, _
Optional ByRef Return_RowsReturned As Integer = -1, _
Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
Return ExecuteSQL_(sSQL, CommandType, ReturnType.rt_DataSet, Return_DataSet, Return_RowsEffected, Return_RowsReturned, Return_ErrNum, Return_ErrSrc, Return_ErrDesc)
End Function
' (see documentation for "ExecuteSQL_" method)
Public Overloads Function ExecuteSQL(ByVal sSQL As String, _
ByVal CommandType As CommandType, _
ByRef Return_DataTable As DataTable, _
Optional ByRef Return_RowsEffected As Integer = -1, _
Optional ByRef Return_RowsReturned As Integer = -1, _
Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
Return ExecuteSQL_(sSQL, CommandType, ReturnType.rt_DataTable, Return_DataTable, Return_RowsEffected, Return_RowsReturned, Return_ErrNum, Return_ErrSrc, Return_ErrDesc)
End Function
' (see documentation for "ExecuteSQL_" method)
Public Overloads Function ExecuteSQL(ByVal sSQL As String, _
ByVal CommandType As CommandType, _
ByRef Return_DataReader As SqlDataReader, _
Optional ByRef Return_RowsEffected As Integer = -1, _
Optional ByRef Return_RowsReturned As Integer = -1, _
Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
Return ExecuteSQL_(sSQL, CommandType, ReturnType.rt_DataReader, Return_DataReader, Return_RowsEffected, Return_RowsReturned, Return_ErrNum, Return_ErrSrc, Return_ErrDesc)
End Function
' Executes the specified SQL Query and returns back an "SqlDataReader" object along with how many records were effected
Public Overloads Function GetSingleValue(ByVal sSQL As String, _
ByVal CommandType As CommandType, _
ByRef Return_Value As Object, _
Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
On Error GoTo ErrorTrap
Dim oDbCommand As SqlCommand
' Set default values
Return_Value = Nothing
Return_ErrNum = 0
Return_ErrSrc = ""
Return_ErrDesc = ""
' Validate parameters
If sSQL.Trim = "" Then Err.Raise(-1, "", "No SQL statement specified to execute")
' Make sure there's a valid connection
If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False
' Create the command object to use
oDbCommand = m_oDbConnection.CreateCommand
With oDbCommand
.CommandText = sSQL
.CommandTimeout = m_iTimeOutCmd
.CommandType = CommandType
If Not m_oTransaction Is Nothing Then .Transaction = m_oTransaction
End With
' Execute the SQL statement
Return_Value = oDbCommand.ExecuteScalar
' Clean up
If Not oDbCommand Is Nothing Then
oDbCommand.Dispose()
oDbCommand = Nothing
End If
' Function executed successfully
Return True
ErrorTrap:
Return_ErrNum = Err.Number
Return_ErrSrc = Err.Source
If Return_ErrSrc = "" Then
Return_ErrSrc = "cDataAccess.GetSingleValue()"
Else
Return_ErrSrc = Return_ErrSrc & " [cDataAccess.GetSingleValue()]"
End If
Return_ErrDesc = Err.Description
Return_Value = Nothing
Err.Clear()
If Not oDbCommand Is Nothing Then
oDbCommand.Dispose()
oDbCommand = Nothing
End If
End Function
' Makes sure that there's an active connection established. The "ExecuteSQL" method automatically establishes
' a connection to the database if one was not previously established.
Public Function Connect(Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
' Make sure there's a valid connection
Return CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc)
End Function
' Makes sure that the SQL statement passed in is properly qualified (Single quotes are escaped)
Public Function CleanSQL(ByVal sSQL As String) As String
CleanSQL = Replace(sSQL, "'", "''")
End Function
' Returns TRUE if the specified "SqlDataReader" object is valid and contains data. Optionallly
' cleans up the object if it is invalid or contains no data.
Public Function IsDatareaderValid(ByRef oDataReader As SqlDataReader, _
Optional ByVal bDeleteIfInvalid As Boolean = False) As Boolean
If Not oDataReader Is Nothing Then
If oDataReader.IsClosed = False Then
If oDataReader.HasRows = True Then
Return True
End If
End If
End If
If bDeleteIfInvalid = True Then
Call CleanUpDataObject(oDataReader)
End If
End Function
' Cleans up the specified SqlConnection object
Public Sub CleanUpConnection(ByRef oConnection As SqlConnection)
On Error Resume Next
If Not oConnection Is Nothing Then
oConnection.Close()
oConnection.Dispose()
oConnection = Nothing
End If
Err.Clear()
End Sub
' Cleans up the specified SqlDataReader object
Public Overloads Sub CleanUpDataObject(ByRef oDatareader As SqlDataReader)
On Error Resume Next
If Not oDatareader Is Nothing Then
If oDatareader.IsClosed = False Then oDatareader.Close()
oDatareader = Nothing
End If
Err.Clear()
End Sub
' Cleans up the specified DataTable object
Public Overloads Sub CleanUpDataObject(ByRef oDataTable As DataTable)
On Error Resume Next
If Not oDataTable Is Nothing Then
oDataTable.Clear()
oDataTable.Dispose()
oDataTable = Nothing
End If
Err.Clear()
End Sub
' Cleans up the specified DataTable object
Public Overloads Sub CleanUpDataObject(ByRef oDataSet As DataSet)
On Error Resume Next
If Not oDataSet Is Nothing Then
oDataSet.Dispose()
oDataSet = Nothing
End If
Err.Clear()
End Sub
#End Region
#Region "Private Helper Functions"
'==========================================================================================================
' ExecuteSQL
'
' Executes the specified SQL Query and returns back an "SqlDataReader" object along with how many records
' were effected
'
' Parameter: Definition:
' --------------------------------------------------
' sSQL The SQL query string, or stored procedure name to execute
' CommandType Specifies what the "sSQL" query is
' DataReturnType Optional. Specifies what data object should be returned (DataReader, DataTable, or DataSet)
' Return_DataObject Optional. If something is returned from the query, this returns is in
' the form specified by the "DataReturnType" parameter
' Return_RowsEffected Optional. If the "DataReturnType" parameter is set to "rt_DataReader",
' this parameter returns how many records where effected. Otherwise, it
' returns -1.
' Return_RowsReturned Optional. If the "DataReturnType" parameter is set to "rt_DataReader",
' this parameter returns -1. Otherwise, it returns how many records where
' returned in the DataTable or DataSet object.
' Return_ErrNum Optional. If an error occurs, this returns the number of the error.
' Return_ErrSrc Optional. If an error occurs, this returns the source of the error.
' Return_ErrDesc Optional. If an error occurs, this returns the description of the error.
'
' Return:
' -------
' TRUE = The function executed without errors, FALSE = an error occured
'==========================================================================================================
Private Function ExecuteSQL_(ByVal sSQL As String, _
ByVal CommandType As CommandType, _
Optional ByVal DataReturnType As ReturnType = ReturnType.rt_DataReader, _
Optional ByRef Return_DataObject As Object = Nothing, _
Optional ByRef Return_RowsEffected As Integer = -1, _
Optional ByRef Return_RowsReturned As Integer = -1, _
Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
On Error GoTo ErrorTrap
Dim oDataAdapter As SqlDataAdapter
Dim oDbCommand As SqlCommand
' Set default values
Return_DataObject = Nothing
Return_RowsEffected = -1
Return_RowsReturned = -1
Return_ErrNum = 0
Return_ErrSrc = ""
Return_ErrDesc = ""
' Validate parameters
If sSQL.Trim = "" Then Err.Raise(-1, "", "No SQL statement specified to execute")
' Make sure there's a valid connection
If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False
' Create the command object to use
oDbCommand = m_oDbConnection.CreateCommand
With oDbCommand
.CommandText = sSQL
.CommandTimeout = m_iTimeOutCmd
.CommandType = CommandType
If Not m_oTransaction Is Nothing Then .Transaction = m_oTransaction
End With
Select Case DataReturnType
Case ReturnType.rt_DataTable
Return_DataObject = New DataTable
oDataAdapter = New SqlDataAdapter
oDataAdapter.SelectCommand = oDbCommand
oDataAdapter.Fill(Return_DataObject)
If Not Return_DataObject Is Nothing Then Return_RowsReturned = Return_DataObject.Rows.Count
Case ReturnType.rt_DataSet
Return_DataObject = New DataSet
oDataAdapter = New SqlDataAdapter
oDataAdapter.SelectCommand = oDbCommand
oDataAdapter.Fill(Return_DataObject)
If Not Return_DataObject Is Nothing AndAlso Return_DataObject.Tables.Count > 0 Then Return_RowsReturned = Return_DataObject.Tables(0).Rows.Count
Case Else ' ReturnType.rt_DataReader
Return_DataObject = oDbCommand.ExecuteReader
Return_RowsEffected = Return_DataObject.RecordsAffected
End Select
' Clean up
If Not oDataAdapter Is Nothing Then
oDataAdapter.SelectCommand = Nothing
oDataAdapter.Dispose()
oDataAdapter = Nothing
End If
If Not oDbCommand Is Nothing Then
oDbCommand.Dispose()
oDbCommand = Nothing
End If
' Function executed successfully
Return True
ErrorTrap:
Return_ErrNum = Err.Number
Return_ErrSrc = Err.Source
If Return_ErrSrc = "" Then
Return_ErrSrc = "cDataAccess.ExecuteSQL()"
Else
Return_ErrSrc = Return_ErrSrc & " [cDataAccess.ExecuteSQL()]"
End If
Return_ErrDesc = Err.Description
Return_RowsEffected = -1
Return_RowsReturned = -1
Return_DataObject = Nothing
Err.Clear()
If Not oDataAdapter Is Nothing Then
oDataAdapter.SelectCommand = Nothing
oDataAdapter.Dispose()
oDataAdapter = Nothing
End If
If Not oDbCommand Is Nothing Then
oDbCommand.Dispose()
oDbCommand = Nothing
End If
End Function
' Checks to make sure a connection was previously established. If not, it creates a new one.
Private Function CheckConnection(Optional ByRef Return_ErrNum As Integer = 0, _
Optional ByRef Return_ErrSrc As String = "", _
Optional ByRef Return_ErrDesc As String = "") As Boolean
On Error GoTo ErrorTrap
' Set default values
Return_ErrNum = 0
Return_ErrSrc = ""
Return_ErrDesc = ""
' Validate parameters
If Not m_oDbConnection Is Nothing Then
Return True
ElseIf m_sConnString = "" Then
Err.Raise(-1, "", "No connection string specified")
End If
' Create the Connection objects to use
m_oDbConnection = New SqlConnection
If InStr(1, m_sConnString, "Connect Timeout", CompareMethod.Text) < 1 Then
m_oDbConnection.ConnectionString = m_sConnString & "Connect Timeout=" & CStr(m_iTimeOutConn) & ";"
Else
m_oDbConnection.ConnectionString = m_sConnString
End If
m_oDbConnection.Open()
' Function executed successfully
Return True
ErrorTrap:
Return_ErrNum = Err.Number
If Return_ErrSrc = "" Then
Return_ErrSrc = "cDataAccess.CheckConnection()"
Else
Return_ErrSrc = Return_ErrSrc & " [cDataAccess.CheckConnection()]"
End If
Return_ErrDesc = Err.Description
Err.Clear()
End Function
#End Region
End Class