Imports System
Imports System.Collections
Imports System.Text
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Configuration
Namespace MSAccessDB
#Region "WhereCondition"
Public Class WhereCondition
#Region "Private Variables"
Private _leftfield As Field = Nothing
Private _rightfield As Field = Nothing
Private _operator As String = Operators.EQUAL
#End Region
#Region "Constructor"
Public Sub New()
End Sub
Public Sub New(ByVal pLeftField As Field, ByVal pRightField As Field, ByVal pOperator As String)
LeftField = pLeftField
RightField = pRightField
[Operator] = pOperator
End Sub
Public Sub New(ByVal pLeftField As Field, ByVal pRightField As Field)
LeftField = pLeftField
RightField = pRightField
End Sub
#End Region
#Region "Properties"
Public Property LeftField() As Field
Get
Return _leftfield
End Get
Set(ByVal Value As Field)
_leftfield = Value
End Set
End Property
Public Property RightField() As Field
Get
Return _rightfield
End Get
Set(ByVal Value As Field)
_rightfield = Value
End Set
End Property
Public Property [Operator]() As String
Get
Return _operator
End Get
Set(ByVal Value As String)
_operator = Value
End Set
End Property
#End Region
End Class
Public Class WhereClause
Inherits CollectionBase
#Region "Properties"
Default Public Property Item(ByVal pIndex As Integer) As WhereCondition
Get
Return Me.List(pIndex)
End Get
Set(ByVal Value As WhereCondition)
Me.List(pIndex) = Value
End Set
End Property
#End Region
#Region "Methods"
Public Sub Add(ByVal pWhereCondition As WhereCondition)
Me.List.Add(pWhereCondition)
End Sub
Public Sub Add(ByVal pOperator As String)
Add(New WhereCondition(New Field(""), New Field(""), pOperator))
End Sub
#End Region
End Class
#End Region
#Region "Statement"
Public Class Statement
#Region "Private Variables"
Private _databaseobject As Database = Nothing
Private _tablenames As New ArrayList
Private _fields As FieldCollection = New FieldCollection
Private _orderbyfields As FieldCollection = New FieldCollection
Private _groupbyfields As FieldCollection = New FieldCollection
Private _whereclause As WhereClause = New WhereClause
#End Region
#Region "Properties"
Public Property TableNames() As ArrayList
Get
Return _tablenames
End Get
Set(ByVal Value As ArrayList)
_tablenames = Value
End Set
End Property
Public Property Fields() As FieldCollection
Get
Return _fields
End Get
Set(ByVal Value As FieldCollection)
_fields = Value
End Set
End Property
Public Property OrderByFields() As FieldCollection
Get
Return _orderbyfields
End Get
Set(ByVal Value As FieldCollection)
_orderbyfields = Value
End Set
End Property
Public Property GroupByFields() As FieldCollection
Get
Return _groupbyfields
End Get
Set(ByVal Value As FieldCollection)
_groupbyfields = Value
End Set
End Property
Public Property DatabaseObject() As Database
Get
Return _databaseobject
End Get
Set(ByVal Value As Database)
_databaseobject = Value
End Set
End Property
Public Property Where() As WhereClause
Get
Return _whereclause
End Get
Set(ByVal Value As WhereClause)
_whereclause = Value
End Set
End Property
#End Region
#Region "Methods"
Public Overridable Function GetStatement() As String
Return Nothing
End Function
'Following procedure creates name for parameter using supplied FieldName. For example Employee.Name will be Employee_Name
Public Function GetParameterName(ByVal FieldName As String) As String
Return "@" + Replace(FieldName, ".", "_")
End Function
Public Function ExecuteAndReturnDataset() As DataSet
Dim retDataset As New DataSet
Try
Dim strTablename As String = ""
If TableNames.Count > 0 Then strTablename = CType(TableNames(0), String)
retDataset = DatabaseObject.ExecuteAndReturnDataSet(GetStatement(), GetParameterCollection(Fields, Where))
Catch eSelectStatementMSAccess As Exception
DataAccessErrorLog.LogError(eSelectStatementMSAccess.ToString())
Throw
End Try
Return retDataset
End Function
Public Function ExecuteAndReturnDataReader(ByRef pConnection As OleDbConnection) As IDataReader
Dim retDatareader As IDataReader = Nothing
Try
retDatareader = DatabaseObject.ExecuteAndReturnDataReader(GetStatement(), GetParameterCollection(Fields, Where), pConnection)
Catch eSelectStatementMSAccess As Exception
DataAccessErrorLog.LogError(eSelectStatementMSAccess.ToString())
Throw
End Try
Return retDatareader
End Function
Public Function ExecuteAndReturnDataTable() As DataTable
Dim retDataTable As New DataTable
Try
retDataTable = DatabaseObject.ExecuteAndReturnDataTable(GetStatement(), GetParameterCollection(Fields, Where))
Catch eSelectStatementMSAccess As Exception
DataAccessErrorLog.LogError(eSelectStatementMSAccess.ToString())
Throw
End Try
Return retDataTable
End Function
Public Function ExecuteWithReturnRecordsAffected() As Integer
Dim nRecords As Integer = 0
Try
nRecords = DatabaseObject.ExecuteNonQuery(GetStatement(), GetParameterCollection(Fields, Where))
Catch e As Exception
DataAccessErrorLog.LogError(e.ToString())
Throw
End Try
Return nRecords
End Function
Public Function GetFieldNamesAsStatement(ByVal pFields As FieldCollection) As String
Dim statement As New StringBuilder("")
If (pFields.Count > 0) Then
For i As Integer = 0 To pFields.Count - 1
If pFields(i).TableName <> "" Then
statement.Append(pFields(i).TableName)
statement.Append(".")
End If
If (pFields(i).SelectFieldName) Then
statement.Append(pFields(i).FieldName)
Else
statement.Append(pFields(i).FieldValue)
End If
If pFields(i).FieldNameAs <> "" Then
statement.Append(" AS [")
statement.Append(pFields(i).FieldNameAs)
statement.Append("]")
End If
statement.Append(",")
Next
statement.Remove(statement.Length - 1, 1)
Else
statement.Append("*")
End If
Return statement.ToString()
End Function
Public Function GetOrderByStatement(ByVal pFields As FieldCollection) As String
Dim statement As New StringBuilder("")
If (pFields.Count > 0) Then
For i As Integer = 0 To pFields.Count - 1
If pFields(i).TableName <> "" Then
statement.Append(pFields(i).TableName)
statement.Append(".")
End If
statement.Append(pFields(i).FieldName)
statement.Append(",")
Next
statement.Remove(statement.Length - 1, 1)
End If
Return statement.ToString()
End Function
Public Function GetParameterNamesAsStatement(ByVal pFields As FieldCollection) As String
Dim statement As New StringBuilder("")
Dim i As Integer
For i = 0 To Fields.Count - 1
statement.Append(GetParameterName(pFields(i).FieldName))
statement.Append(",")
Next
statement.Remove(statement.Length - 1, 1)
Return statement.ToString()
End Function
'Added code -------
Public Function GetParameterCollection(ByVal pFields As FieldCollection, ByVal pWhereStatement As WhereClause) As ParameterCollection
Dim parameters As New ParameterCollection()
Dim i As Integer
For i = 0 To pFields.Count - 1
If Not IsNothing(pFields(i).FieldValue) Then
Dim param As New OleDbParameter(GetParameterName(pFields(i).FieldName), pFields(i).FieldValue)
parameters.Add(param)
End If
Next
If Not IsNothing(pWhereStatement) Then
For i = 0 To pWhereStatement.Count - 1
If Not IsNothing(pWhereStatement(i).LeftField.FieldValue) Then
Dim param As New OleDbParameter(GetParameterName(pWhereStatement(i).LeftField.FieldName), pWhereStatement(i).LeftField.FieldValue)
parameters.Add(param)
End If
If Not IsNothing(pWhereStatement(i).RightField.FieldValue) Then
Dim param As New OleDbParameter(GetParameterName(pWhereStatement(i).RightField.FieldName), pWhereStatement(i).RightField.FieldValue)
parameters.Add(param)
End If
Next
End If
Return parameters
End Function
Public Function GetWhereStatement() As String
Dim Statement As New StringBuilder("")
If (Where.Count > 0) Then
Statement.Append(" WHERE ")
Dim i As Integer
For i = 0 To Where.Count - 1
If Where(i).LeftField.FieldName <> "" Then
Statement.Append(Operators.OPENING_BRACKET)
End If
If (Where(i).LeftField.SelectFieldName) Then
If Where(i).LeftField.TableName <> "" Then
Statement.Append(Where(i).LeftField.TableName)
Statement.Append(".")
End If
Statement.Append(Where(i).LeftField.FieldName)
Else
Statement.Append(GetParameterName(Where(i).LeftField.FieldName))
End If
Statement.Append(Where(i).[Operator])
If (Where(i).RightField.SelectFieldName) Then
If Where(i).RightField.TableName <> "" Then
Statement.Append(Where(i).RightField.TableName)
Statement.Append(".")
End If
Statement.Append(Where(i).RightField.FieldName)
Else
Statement.Append(GetParameterName(Where(i).RightField.FieldName))
End If
If Where(i).LeftField.FieldName <> "" Then
Statement.Append(Operators.CLOSING_BRACKET)
End If
Next
End If
Return Statement.ToString()
End Function
#End Region
End Class
#End Region
#Region "SelectStatement"
Public Class SelectStatement
Inherits Statement
Public Sub New()
End Sub
#Region "GetStatement"
Public Overrides Function GetStatement() As String
Dim query As New StringBuilder("SELECT ")
Dim i As Integer
Try
query.Append(GetFieldNamesAsStatement(Fields))
query.Append(" FROM ")
For i = 0 To TableNames.Count - 1
query.Append(TableNames(i))
query.Append(",")
Next
query.Remove(query.Length - 1, 1)
query.Append(GetWhereStatement())
If GroupByFields.Count > 0 Then
query.Append(" GROUP BY ")
query.Append(GetFieldNamesAsStatement(GroupByFields))
End If
If OrderByFields.Count > 0 Then
query.Append(" ORDER BY ")
query.Append(GetOrderByStatement(OrderByFields))
End If
Catch e As Exception
DataAccessErrorLog.LogError(e.ToString())
Throw
End Try
Return query.ToString()
End Function 'GetStatement
#End Region
End Class 'SelectStatement
#End Region
#Region "Field"
Public Class Field
#Region "Private Variables"
Private _selectfieldname As Boolean = True
Private _fieldname As String
'table name associated with this field
Private _tablename As String
'new name of field we want to use in query
Private _fieldnameas As String
Private _fieldvalue As Object
#End Region
#Region "Constructors"
Public Sub New()
End Sub
Public Sub New(ByVal pFieldName As String)
FieldName = pFieldName
End Sub
Public Sub New(ByVal pFieldName As String, ByVal pFieldValue As Object)
FieldName = pFieldName
FieldValue = pFieldValue
End Sub
'Following constructors includes pIsTableNameSupplied to ensure tablename is not considered as field value
Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pIsTableNameSupplied As Boolean)
FieldName = pFieldName
If pIsTableNameSupplied Then TableName = pTableName
End Sub
Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pFieldNameNew As String)
FieldName = pFieldName
TableName = pTableName
FieldNameAs = pFieldNameNew
End Sub
'Public Sub New(ByVal pFieldName As String, ByVal pFieldType As Constants.FIELDTYPES)
' FieldName = pFieldName
' FieldType = pFieldType
'End Sub
'Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pFieldValue As Object)
' FieldName = pFieldName
' TableName = pTableName
' FieldValue = pFieldValue
'End Sub
'Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pFieldNameNew As String, ByVal pFieldType As Constants.FIELDTYPES)
' FieldName = pFieldName
' FieldType = pFieldType
' TableName = pTableName
' FieldNameAs = pFieldNameNew
'End Sub
'Public Sub New(ByVal pFieldName As String, ByVal pFieldNameNew As String, ByVal pFieldType As Constants.FIELDTYPES)
' FieldName = pFieldName
' FieldType = pFieldType
' FieldNameAs = pFieldNameNew
'End Sub
#End Region
#Region "FieldName"
Public Property FieldName() As String
Get
Return _fieldname
End Get
Set(ByVal Value As String)
_fieldname = Value
End Set
End Property
#End Region
#Region "FieldNameAs"
Public Property FieldNameAs() As String
Get
Return _fieldnameas
End Get
Set(ByVal Value As String)
_fieldnameas = Value
End Set
End Property
#End Region
#Region "TableName"
Public Property TableName() As String
Get
Return _tablename
End Get
Set(ByVal Value As String)
_tablename = Value
End Set
End Property
#End Region
#Region "FieldValue"
Public Property FieldValue() As Object
Get
Return _fieldvalue
End Get
Set(ByVal Value As Object)
_fieldvalue = Value
End Set
End Property
#End Region
#Region "SelectFieldName"
Public Property SelectFieldName() As Boolean
Get
Return _selectfieldname
End Get
Set(ByVal Value As Boolean)
_selectfieldname = Value
End Set
End Property
#End Region
#Region "Methods"
'Public Overridable Function getParameterValue() As String
' If IsNothing(FieldValue) Then FieldValue = ""
' Dim retValue As New StringBuilder(FieldValue.ToString())
' If FieldType = Constants.FIELDTYPES.STRING Then
' retValue = New StringBuilder("'").Append(retValue)
' retValue.Append("'")
' End If
' If FieldType = Constants.FIELDTYPES.DATE Then
' 'If retValue.ToString = "1/1/1900 12:00:00 AM" Then
' 'retValue = New StringBuilder("null")
' 'Else
' If IsDate(retValue.ToString) Then
' If Year(CDate(retValue.ToString)) = 1 Then
' retValue = New StringBuilder("1/1/1900 12:00:00 AM")
' End If
' End If
' If (Not IsDate(retValue.ToString)) Then retValue = New StringBuilder("1/1/1900 12:00:00 AM")
' retValue = New StringBuilder("'").Append(retValue).Append("'")
' 'retValue.Append("'")
' 'End If
' End If
' If (FieldType = Constants.FIELDTYPES.BOOLEAN) Then
' Select Case (FieldValue.ToString().ToLower())
' Case "0"
' retValue = New StringBuilder("0")
' Case "1"
' retValue = New StringBuilder("1")
' Case "false"
' retValue = New StringBuilder("0")
' Case "true"
' retValue = New StringBuilder("1")
' Case Else
' retValue = New StringBuilder("0")
' End Select
' End If
' Return retValue.ToString()
'End Function
#End Region
End Class
#End Region
#Region "FieldCollection"
Public Class FieldCollection
Inherits CollectionBase
#Region "Properties"
Default Public Property Item(ByVal pIndex As Integer) As Field
Get
Return Me.List(pIndex)
End Get
Set(ByVal Value As Field)
Me.List(pIndex) = Value
End Set
End Property
#End Region
#Region "Methods"
Public Sub Add(ByVal pField As Field)
Me.List.Add(pField)
End Sub
Public Sub Add(ByVal pFieldName As String)
Me.List.Add(New Field(pFieldName))
End Sub
Public Sub Add(ByVal pFieldName As String, ByVal pFieldValue As Object)
Me.List.Add(New Field(pFieldName, pFieldValue))
End Sub
#End Region
End Class
#End Region
#Region "FieldValue"
Public Class FieldValue
Inherits Field
#Region "Constructors"
Public Sub New(ByVal pFieldValue As Object, ByVal pParameterName As String)
SelectFieldName = False
FieldValue = pFieldValue
FieldName = pParameterName
End Sub
#End Region
End Class
#End Region
#Region "InsertStatement"
Public Class InsertStatement
Inherits Statement
#Region "GetStatement()"
Public Overrides Function GetStatement() As String
Dim query As New StringBuilder("insert into ")
Try
If Fields.Count <= 0 Then
Throw New Exception("There are no fields in Insert Statement. Please check Insert.Fields.Add() code")
End If
Dim i As Integer
For i = 0 To TableNames.Count - 1
query.Append(TableNames(i))
Next
query.Append(" (")
query.Append(GetFieldNamesAsStatement(Fields))
query.Append(") values(")
query.Append(GetParameterNamesAsStatement(Fields))
query.Append(")")
query.Append(GetWhereStatement())
Catch e As Exception
DataAccessErrorLog.LogError(e.ToString())
Throw
End Try
Return query.ToString()
End Function 'GetStatement
#End Region
End Class 'InsertStatement
#End Region
#Region "DeleteStatement"
Public Class DeleteStatement
Inherits Statement
Public Sub New()
End Sub
#Region "Methods"
Public Overrides Function GetStatement() As String
Dim query As New StringBuilder("delete from ")
Try
Dim i As Integer
For i = 0 To TableNames.Count - 1
query.Append(TableNames(i))
query.Append(",")
Next
query.Remove(query.Length - 1, 1)
query.Append(GetWhereStatement())
Catch e As Exception
DataAccessErrorLog.LogError(e.ToString())
Throw
End Try
Return query.ToString()
End Function
#End Region
End Class
#End Region
#Region "Database"
Public Class Database
#Region "PrivateVariables"
Private _connectionstring As String
Private _databasename As String
Private _databasetype As String
#End Region
#Region "Constructors"
Public Sub New()
End Sub
Public Sub New(ByVal pConnectionString As String)
ConnectionString = pConnectionString
End Sub
#End Region
#Region "ConnectionString"
Public Property ConnectionString() As String
Get
Return _connectionstring
End Get
Set(ByVal Value As String)
_connectionstring = Value
End Set
End Property
#End Region
#Region "DatabaseName"
Public Property DatabaseName() As String
Get
Return _databasename
End Get
Set(ByVal Value As String)
_databasename = Value
End Set
End Property
#End Region
#Region "DatabaseType"
Public Property DatabaseType() As String
Get
Return _databasetype
End Get
Set(ByVal Value As String)
_databasetype = Value
End Set
End Property
#End Region
#Region "Virtual Metods"
'Public Sub Connect(ByVal pConnectionString As String)
' ConnectionString = pConnectionString
'End Sub
Public Function CheckConnection() As Boolean
Dim connection As New OleDbConnection(ConnectionString)
Try
connection.Open()
Catch ex As OleDbException
DataAccessErrorLog.LogError(ex.ToString)
Return False
Finally
connection.Close()
End Try
Return True
End Function
Public Function ExecuteNonQuery(ByVal pQuery As String, ByRef pParams As ParameterCollection) As Integer
Dim connection As New OleDbConnection(ConnectionString)
Dim retValue As Integer = 0
Try
connection.Open()
retValue = ExecuteNonQuery(pQuery, pParams, connection)
Catch eDatabaseMSAccess As Exception
DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
Finally
connection.Close()
End Try
Return retValue
End Function
Public Function ExecuteNonQuery(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByRef pConnection As OleDbConnection) As Integer
Dim retValue As Integer = 0
Try
Dim executeCommand As OleDbCommand = New OleDbCommand(pQuery, pConnection)
AddParameters(pParams, executeCommand.Parameters)
retValue = executeCommand.ExecuteNonQuery()
Catch eDatabaseMSAccess As OleDbException
DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
End Try
Return retValue
End Function
Public Function ExecuteScalar(ByVal pQuery As String, ByRef pParams As ParameterCollection) As Object
Dim connection As New OleDbConnection(ConnectionString)
Dim retValue As Integer = 0
Try
connection.Open()
retValue = ExecuteScalar(pQuery, pParams, connection)
Catch eDatabaseMSAccess As Exception
DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
Finally
connection.Close()
End Try
Return retValue
End Function
Public Function ExecuteScalar(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByRef pConnection As OleDbConnection) As Object
Dim retValue As Integer = 0
Try
Dim executeCommand As OleDbCommand = New OleDbCommand(pQuery, pConnection)
AddParameters(pParams, executeCommand.Parameters)
retValue = executeCommand.ExecuteScalar
Catch eDatabaseMSAccess As Exception
DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
End Try
Return retValue
End Function
Public Function ExecuteAndReturnDataSet(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByVal pTableName As String) As DataSet
Dim selectDataset As DataSet = Nothing
Dim connection As New OleDbConnection(ConnectionString)
Try
Dim selectAdapter As OleDbDataAdapter = New OleDbDataAdapter(pQuery, connection)
selectDataset = New DataSet
selectAdapter.Fill(selectDataset)
If pTableName <> "" Then selectDataset.Tables(0).TableName = pTableName
Catch eDatabaseMSAccess As Exception
DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
Finally
connection.Close()
End Try
Return selectDataset
End Function
Public Function ExecuteAndReturnDataSet(ByVal pQuery As String, ByRef pParams As ParameterCollection) As DataSet
Return ExecuteAndReturnDataSet(pQuery, pParams)
End Function
Public Function ExecuteAndReturnDataReader(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByRef pConnection As OleDbConnection) As OleDbDataReader
Dim selectReader As OleDbDataReader = Nothing
Try
Dim selectCommand As OleDbCommand = New OleDbCommand(pQuery, pConnection)
AddParameters(pParams, selectCommand.Parameters)
selectReader = selectCommand.ExecuteReader()
Catch exSQL As OleDbException
DataAccessErrorLog.LogError(exSQL.ToString)
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
End Try
Return selectReader
End Function
Public Function ExecuteAndReturnDataTable(ByVal pQuery As String, ByRef pParams As ParameterCollection) As DataTable
Dim table As New DataTable
Dim connection As New OleDbConnection(ConnectionString)
Try
connection.Open()
Dim selectReader As OleDbDataReader
selectReader = ExecuteAndReturnDataReader(pQuery, pParams, connection)
Dim i As Integer
For i = 0 To selectReader.FieldCount - 1
table.Columns.Add(selectReader.GetName(i), selectReader.GetFieldType(i), "")
Next
While selectReader.Read()
Dim row As DataRow
row = table.NewRow
For i = 0 To selectReader.FieldCount - 1
row(i) = selectReader.GetValue(i)
Next
table.Rows.Add(row)
End While
Catch exDataAccess As Exception
DataAccessErrorLog.LogError(exDataAccess.ToString)
DataAccessErrorLog.LogError("Query: " + pQuery)
Throw
Finally
connection.Close()
End Try
Return table
End Function
Public Function ExecuteAndReturnDataTable(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByVal pTableName As String) As DataTable
Dim table As DataTable
table = ExecuteAndReturnDataTable(pQuery, pParams)
table.TableName = pTableName
Return table
End Function
Public Function AddParameters(ByVal pSourceParameters As ParameterCollection, ByRef pDestinationParameters As OleDbParameterCollection) As Boolean
Dim i As Integer
For i = 0 To pSourceParameters.Count - 1
Dim param As New OleDbParameter(pSourceParameters(i).ParameterName, pSourceParameters(i).Value)
pDestinationParameters.Add(param)
Next
Return True
End Function
#End Region
End Class
#End Region
#Region "Operators"
Public NotInheritable Class Operators
#Region "Operator Constants"
Public Const [AND] As String = " AND "
Public Const [OR] As String = " OR "
Public Const [LESS_THAN] As String = " < "
Public Const [LESS_THAN_EQUAL] As String = " <= "
Public Const [GREATER_THAN] As String = " > "
Public Const [GREATER_THAN_EQUAL] As String = " >= "
Public Const [EQUAL] As String = " = "
Public Const [NOT_EQUAL] As String = " <> "
Public Const [OPENING_BRACKET] As String = " ( "
Public Const [CLOSING_BRACKET] As String = " ) "
#End Region
End Class
Public NotInheritable Class CommonFunctions
#Region "Functions"
Public Shared Function GetTrueFalse(ByVal pValue As Object) As Boolean
If pValue.ToString() = "1" Or pValue.ToString().ToUpper() = "TRUE" Then
Return True
Else
Return False
End If
End Function
#End Region
End Class
#End Region
#Region "DataAccessErrorLog"
Public NotInheritable Class DataAccessErrorLog
#Region "Methods"
Public Shared Sub LogError(ByVal pMessage As String)
Dim streamWriter As StreamWriter
Dim fileInfo As FileInfo
Try
fileInfo = New FileInfo("DataAccessErrorLog" + Format$(Date.Now, "_ddMMMyy") + ".txt")
streamWriter = fileInfo.AppendText()
streamWriter.WriteLine("")
streamWriter.WriteLine(DateTime.Now.ToString())
streamWriter.WriteLine(pMessage)
streamWriter.Flush()
Catch
Throw
Finally
streamWriter.Close()
End Try
End Sub
#End Region
End Class
#End Region
#Region "StoredProcedure"
Public Class StoredProcedure
#Region "Private Variables"
Inherits Statement
Private _procedurename As String
Private _parameters As FieldCollection
#End Region
Public Sub New()
End Sub
#Region "Properties"
Public Property ProcedureName() As String
Get
Return _procedurename
End Get
Set(ByVal value As String)
_procedurename = value
End Set
End Property
Public Property Parameters() As FieldCollection
Get
Return _parameters
End Get
Set(ByVal value As FieldCollection)
_parameters = value
End Set
End Property
#End Region
#Region "GetStatement"
Public Overrides Function GetStatement() As String
Dim query As New StringBuilder(_procedurename)
Try
If _parameters.Count > 0 Then
query.Append(" ")
End If
query.Append(GetParameterNamesAsStatement(Parameters))
Catch e As Exception
DataAccessErrorLog.LogError(e.ToString())
Throw
End Try
Return query.ToString()
End Function 'GetStatement
#End Region
End Class
#End Region
#Region "UpdateStatement"
Public Class UpdateStatement
Inherits Statement
#Region "Constructor"
Public Sub New()
End Sub 'New
#End Region
#Region "GetStatement"
Public Overrides Function GetStatement() As String
Dim query As New StringBuilder("update ")
Try
If Fields.Count <= 0 Then
Throw New Exception("There are no fields in Update Statement. Please check Update.Fields.Add() code.")
End If
Dim i As Integer
For i = 0 To TableNames.Count - 1
query.Append(TableNames(i))
Next
query.Append(" set ")
For i = 0 To Fields.Count - 1
query.Append(Fields(i).FieldName)
query.Append("=")
query.Append(GetParameterName(Fields(i).FieldName))
query.Append(",")
Next i
query.Remove(query.Length - 1, 1)
query.Append(GetWhereStatement())
Return query.ToString()
Catch e As Exception
DataAccessErrorLog.LogError(e.ToString())
Throw
End Try
End Function 'GetStatement
#End Region
End Class 'UpdateStatement
Public Class ParameterCollection
Inherits CollectionBase
#Region "Properties"
Default Public Property Item(ByVal pIndex As Integer) As OleDbParameter
Get
Return Me.List(pIndex)
End Get
Set(ByVal Value As OleDbParameter)
Me.List(pIndex) = Value
End Set
End Property
#End Region
#Region "Methods"
Public Sub Add(ByVal pParam As OleDbParameter)
Me.List.Add(pParam)
End Sub
Public Sub Add(ByVal pParamName As String, ByVal pParamValue As Object)
Me.List.Add(New OleDbParameter(pParamName, pParamValue))
End Sub
#End Region
End Class
#End Region
End Namespace
Submit an article or tip