|
|
' ___________________________________________________
'
' � Hi-Integrity Systems 2007. All rights reserved.
' www.hisystems.com.au - Toby Wicks
' ___________________________________________________
'
Option Strict On
Option Explicit On
Namespace SQL
Module Misc
Public Function SQLConvertIdentifierName( _
ByVal strIdentifierName As String, _
ByVal eConnectionType As Database.ConnectionType) As String
'This function places tags around a field name or table name to ensure it doesn't
'conflict with a reserved word or if it contains spaces it is not misinterpreted
Select Case eConnectionType
Case Database.ConnectionType.MicrosoftAccess, Database.ConnectionType.SQLServer
Return "[" & strIdentifierName.Trim & "]"
Case Database.ConnectionType.MySQL
Return "`" & strIdentifierName.Trim & "`"
End Select
End Function
Public Function SQLConvertValue( _
ByVal objValue As Object, _
ByVal eConnectionType As Database.ConnectionType) As String
If SQLValueIsNull(objValue) Then
Return "NULL"
Else
If TypeOf objValue Is Boolean Then
Dim bBoolean As Boolean = DirectCast(objValue, Boolean)
If bBoolean Then
Return "1"
Else
Return "0"
End If
ElseIf TypeOf objValue Is Char Then
Dim chChar As Char = DirectCast(objValue, Char)
If chChar.Equals("'") Then
Return "''''"
Else
Return "'" & chChar & "'"
End If
ElseIf TypeOf objValue Is DateTime Then
Dim dtDate As Date = DirectCast(objValue, DateTime)
Dim strDate As String
'If the date hasn't been set then set to the 1899-12-30 so that
'the date isn't set to 2001-01-01
If dtDate.Day = 1 And dtDate.Month = 1 And dtDate.Year = 1 Then
strDate = "1899-12-30"
Else
strDate = dtDate.Year & "-" & dtDate.Month & "-" & dtDate.Day
End If
If dtDate.Hour <> 0 Or dtDate.Minute <> 0 Or dtDate.Second <> 0 Then
strDate = strDate & " " & dtDate.Hour & ":" & dtDate.Minute & ":" & dtDate.Second
End If
Select Case eConnectionType
Case Database.ConnectionType.MicrosoftAccess
Return "#" & strDate & "#"
Case Database.ConnectionType.MySQL, Database.ConnectionType.SQLServer
Return "'" & strDate & "'"
End Select
ElseIf TypeOf objValue Is String Then
Return "'" & DirectCast(objValue, String).Replace("'", "''") & "'"
Else
Return CType(objValue, String)
End If
End If
End Function
Public Function SQLValueIsNull(ByVal objValue As Object) As Boolean
If objValue Is Nothing Then
Return True
ElseIf objValue Is DBNull.Value Then
Return True
Else
Return False
End If
End Function
Public Function SQLConvertAggregate(ByVal eAggregate As SQL.AggregateFunction) As String
Dim strAggregate As String
Select Case eAggregate
Case SQL.AggregateFunction.Average
strAggregate = "AVG"
Case AggregateFunction.Count
strAggregate = "COUNT"
Case AggregateFunction.Maximum
strAggregate = "MAX"
Case AggregateFunction.Minimum
strAggregate = "MIN"
Case AggregateFunction.StandardDeviation
strAggregate = "STDEV"
Case AggregateFunction.Sum
strAggregate = "SUM"
Case AggregateFunction.Variance
strAggregate = "VAR"
End Select
Return strAggregate
End Function
Public Function SQLConvertCompare(ByVal eCompare As SQL.ComparisonOperator) As String
Dim strCompare As String
Select Case eCompare
Case ComparisonOperator.EqualTo
strCompare = "="
Case ComparisonOperator.GreaterThan
strCompare = ">"
Case ComparisonOperator.GreaterThanOrEqualTo
strCompare = ">="
Case ComparisonOperator.LessThan
strCompare = "<"
Case ComparisonOperator.LessThanOrEqualTo
strCompare = "<="
Case ComparisonOperator.Like
strCompare = "LIKE"
Case ComparisonOperator.NotLike
strCompare = "NOT LIKE"
Case ComparisonOperator.NotEqualTo
strCompare = "<>"
Case Else
Throw New ArgumentException
End Select
Return strCompare
End Function
Public Function SQLConvertLogicalOperator(ByVal eLogicalOperator As SQL.LogicalOperator) As String
Dim strLogicalOperator As String
Select Case eLogicalOperator
Case LogicalOperator.And
strLogicalOperator = "AND"
Case LogicalOperator.Or
strLogicalOperator = "OR"
End Select
Return strLogicalOperator
End Function
Public Function SQLFieldNameAndTablePrefix( _
ByVal objTable As SQLSelectTable, _
ByVal strFieldName As String, _
ByVal eConnectionType As Database.ConnectionType) As String
Dim strTablePrefix As String
If Not objTable Is Nothing Then
strTablePrefix = objTable.Name
strTablePrefix = SQLConvertIdentifierName(strTablePrefix, eConnectionType) & "."
End If
Return strTablePrefix & SQLConvertIdentifierName(strFieldName, eConnectionType)
End Function
Public Function SQLConditionValue(ByVal objValue As Object) As Object
If TypeOf objValue Is SQLFieldValue Then
Dim objSQLFieldValue As SQLFieldValue = DirectCast(objValue, SQLFieldValue)
Return objSQLFieldValue.Value
Else
Return objValue
End If
End Function
Public Sub CompareValuePairAssertValid( _
ByVal eCompare As SQL.ComparisonOperator, _
ByVal objValue As Object)
If Not TypeOf objValue Is String And (eCompare = ComparisonOperator.Like Or eCompare = ComparisonOperator.NotLike) Then
Throw New DatabaseObjectsException("The LIKE operator cannot be used in conjunction with a non-string data type")
ElseIf TypeOf objValue Is Boolean And Not (eCompare = ComparisonOperator.EqualTo Or eCompare = ComparisonOperator.NotEqualTo) Then
Throw New DatabaseObjectsException("A boolean value can only be used in conjunction with the " & ComparisonOperator.EqualTo.ToString & " or " & ComparisonOperator.NotEqualTo.ToString & " operators")
End If
End Sub
Public Sub SQLConvertBooleanValue( _
ByRef objValue As Object, _
ByRef eCompare As SQL.ComparisonOperator)
'If a boolean variable set to true then use the NOT
'operator and compare it to 0. ie. if the condition is 'field = true' then
'SQL code should be 'field <> 0'
'-1 is true in MSAccess and 1 is true in SQLServer.
If TypeOf objValue Is Boolean Then
If DirectCast(objValue, Boolean) = True Then
If eCompare = ComparisonOperator.EqualTo Then
eCompare = ComparisonOperator.NotEqualTo
Else
eCompare = ComparisonOperator.EqualTo
End If
objValue = False
End If
End If
End Sub
End Module
End Namespace
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip