|
|
' ___________________________________________________
'
' � Hi-Integrity Systems 2007. All rights reserved.
' www.hisystems.com.au - Toby Wicks
' ___________________________________________________
'
Option Strict On
Option Explicit On
Namespace SQL
Public Class SQLCreateTable
Inherits SQLStatement
Private pstrName As String
Private pobjFields As SQLTableFields
Public Sub New()
pobjFields = New SQLTableFields
pobjFields.AlterMode = SQLTableFields.AlterModeType.Add 'set that fields can only be added
End Sub
Public Property Name() As String
Get
Return pstrName
End Get
Set(ByVal Value As String)
pstrName = Value.Trim
End Set
End Property
Public ReadOnly Property Fields() As SQLTableFields
Get
Return pobjFields
End Get
End Property
Public Overrides ReadOnly Property SQL() As String
Get
Dim strSQL As String
If Me.Name = Nothing Then
Throw New DatabaseObjectsException("TableName has not been set.")
End If
strSQL = _
"CREATE TABLE " & _
SQLConvertIdentifierName(Me.Name, Me.ConnectionType) & " (" & pobjFields.SQL(Me.ConnectionType) & ")"
Return strSQL
End Get
End Property
End Class
Public Class SQLAlterTable
Inherits SQLStatement
Private pstrName As String
Private pobjFields As SQLTableFields = New SQLTableFields
Public Sub New()
End Sub
Public Property Name() As String
Get
Return pstrName
End Get
Set(ByVal Value As String)
pstrName = Value.Trim
End Set
End Property
Public ReadOnly Property Fields() As SQLTableFields
Get
Return pobjFields
End Get
End Property
Public Overrides ReadOnly Property SQL() As String
Get
Dim strMode As String
Dim strFields As String
If Me.Name = Nothing Then
Throw New DatabaseObjectsException("TableName has not been set.")
End If
Select Case pobjFields.AlterMode
Case SQLTableFields.AlterModeType.Add
strMode = "ADD"
Case SQLTableFields.AlterModeType.Alter
strMode = "ALTER COLUMN"
Case SQLTableFields.AlterModeType.Drop
strMode = "DROP COLUMN"
End Select
Return _
"ALTER TABLE " & SQLConvertIdentifierName(Me.Name, Me.ConnectionType) & " " & _
strMode & " " & pobjFields.SQL(Me.ConnectionType)
End Get
End Property
End Class
Public Class SQLTableFields
Friend Enum AlterModeType
Add
Alter
Drop
End Enum
Private Const pcintAlterModeUninitialized As Integer = -1
Friend AlterMode As AlterModeType = CType(pcintAlterModeUninitialized, AlterModeType)
Private pcolFields As ArrayList = New ArrayList
Public Sub New()
End Sub
Public Function Add() As SQLTableField
Return Add("", DataType.VariableCharacter, 0)
End Function
Public Function Add( _
ByVal strFieldName As String, _
ByVal eDataType As SQL.DataType, _
Optional ByVal intSize As Integer = 0) As SQLTableField
EnsureAlterModeValid(AlterModeType.Add)
Dim objField As SQLTableField = New SQLTableField
With objField
.Name = strFieldName
.DataType = eDataType
If intSize > 0 Then
.Size = intSize
End If
If .IsCharacterDataType And intSize = 0 Then
Throw New ArgumentException("Size not specified for character based field " & strFieldName)
End If
End With
pcolFields.Add(objField)
Return objField
End Function
Default Public ReadOnly Property Item(ByVal strFieldName As String) As SQLTableField
Get
EnsureAlterModeValid(AlterModeType.Alter)
Dim intIndex As Integer
strFieldName = strFieldName.Trim
intIndex = FieldNameIndex(strFieldName)
If intIndex = -1 Then
Item = New SQLTableField
Item.Name = strFieldName
pcolFields.Add(Item)
Else
Item = DirectCast(pcolFields(intIndex), SQLTableField)
End If
End Get
End Property
Public Sub Drop(ByVal strFieldName As String)
EnsureAlterModeValid(AlterModeType.Drop)
Dim objField As SQLTableField = New SQLTableField
objField.Name = strFieldName.Trim
If FieldNameIndex(strFieldName) = -1 Then
pcolFields.Add(objField)
Else
Throw New ArgumentException("Field '" & strFieldName & "' already exists")
End If
End Sub
Private Function FieldNameIndex( _
ByVal strFieldName As String) As Integer
strFieldName = strFieldName.Trim
FieldNameIndex = -1
For intIndex As Integer = 0 To pcolFields.Count - 1
If String.Compare(DirectCast(pcolFields(intIndex), SQLTableField).Name, strFieldName, True) = 0 Then
Return intIndex
End If
Next
End Function
Friend ReadOnly Property SQL(ByVal eConnectionType As Database.ConnectionType) As String
Get
Const cstrSeperator As String = ", "
Dim strSQL As String
Dim bOnlyFieldName As Boolean
bOnlyFieldName = AlterMode = AlterModeType.Drop
For Each objField As SQLTableField In pcolFields
strSQL &= objField.SQL(eConnectionType, bOnlyFieldName) & cstrSeperator
Next
Return strSQL.Substring(0, strSQL.Length - cstrSeperator.Length) 'remove the last comma and space
End Get
End Property
Private Sub EnsureAlterModeValid(ByVal eAlterMode As SQLTableFields.AlterModeType)
'if the alter mode hasn't been set then any of the modes are valid
If AlterMode = pcintAlterModeUninitialized Then
AlterMode = eAlterMode
Else
If eAlterMode <> AlterMode Then
Throw New DatabaseObjectsException("Cannot mix " & AlterModeDescription(AlterMode) & " fields and " & AlterModeDescription(eAlterMode) & " fields into one SQL statement")
End If
End If
End Sub
Private Function AlterModeDescription(ByVal eAlterMode As SQLTableFields.AlterModeType) As String
Select Case eAlterMode
Case AlterModeType.Add : Return "adding"
Case AlterModeType.Alter : Return "altering"
Case AlterModeType.Drop : Return "dropping"
End Select
End Function
End Class
Public Class SQLTableField
Private pstrName As String
Private peType As SQL.DataType = DataType.VariableCharacter
Private pintSize As Integer = 1
Private pintScale As Integer = 0
Private pintPrecision As Integer = 18
Private pbAutoIncrements As Boolean
Private pbAcceptsNull As Boolean = True
Private pobjDefault As Object
Private peKeyType As SQL.KeyType = KeyType.None
Friend Sub New()
End Sub
Public Property Name() As String
Get
Return pstrName
End Get
Set(ByVal Value As String)
pstrName = Value.Trim
End Set
End Property
Public Property DataType() As SQL.DataType
Get
Return peType
End Get
Set(ByVal Value As SQL.DataType)
peType = Value
End Set
End Property
Public Property KeyType() As SQL.KeyType
Get
Return peKeyType
End Get
Set(ByVal Value As SQL.KeyType)
peKeyType = Value
If Value = KeyType.Primary Then
pbAcceptsNull = False
End If
End Set
End Property
Public Property Size() As Integer
Set(ByVal Value As Integer)
EnsureCharacterDataType()
If Value <= 1 Then
Throw New ArgumentException
End If
pintSize = Value
End Set
Get
EnsureCharacterDataType()
Return pintSize
End Get
End Property
Public Property ScaleLength() As Integer
Get
EnsureDecimalDataType()
Return pintScale
End Get
Set(ByVal Value As Integer)
EnsureDecimalDataType()
If Value <= 0 Then
Throw New ArgumentException
End If
pintScale = Value
End Set
End Property
Public Property Precision() As Integer
Get
EnsureDecimalDataType()
Return pintPrecision
End Get
Set(ByVal Value As Integer)
EnsureDecimalDataType()
If Value <= 0 Then
Throw New ArgumentException
End If
pintPrecision = Value
End Set
End Property
Public Property AutoIncrements() As Boolean
Get
EnsureIntegerDataType()
AutoIncrements = pbAutoIncrements
End Get
Set(ByVal Value As Boolean)
EnsureIntegerDataType()
pbAutoIncrements = Value
pbAcceptsNull = Not Value
End Set
End Property
Public Property AcceptsNull() As Boolean
Get
Return pbAcceptsNull
End Get
Set(ByVal Value As Boolean)
pbAcceptsNull = Value
End Set
End Property
Public Property [Default]() As Object
Get
Return pobjDefault
End Get
Set(ByVal Value As Object)
pobjDefault = Value
End Set
End Property
Friend ReadOnly Property SQL( _
ByVal eConnectionType As Database.ConnectionType, _
ByVal bOnlyFieldName As Boolean) As String
Get
Dim strName As String
Dim strDataType As String
Dim strColumnOptions As String
Dim strSQL As String
If Me.Name = Nothing Then
Throw New DatabaseObjectsException("Field Name has not been set.")
End If
strName = SQLConvertIdentifierName(Me.Name, eConnectionType)
If bOnlyFieldName Then
strSQL = strName
Else
strDataType = DataTypeString(eConnectionType, peType)
strColumnOptions = ColumnOptions(eConnectionType)
strSQL = strName & " " & strDataType & strColumnOptions
End If
Return strSQL
End Get
End Property
Private Function ColumnOptions( _
ByVal eConnection As Database.ConnectionType) As String
Dim objOptions As ArrayList = New ArrayList
Dim strOptions As String
If pbAcceptsNull Then
objOptions.Add("NULL")
Else
objOptions.Add("NOT NULL")
End If
If Not pobjDefault Is Nothing Then
objOptions.Add("DEFAULT " & SQLConvertValue(pobjDefault, eConnection))
End If
If IsIntegerDataType() And pbAutoIncrements Then
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
objOptions.Add("IDENTITY")
Case Database.ConnectionType.MySQL
objOptions.Add("AUTO_INCREMENT")
'must be set to a key
If peKeyType = KeyType.None Then
peKeyType = KeyType.Unique
End If
Case Database.ConnectionType.SQLServer
objOptions.Add("IDENTITY")
End Select
End If
Select Case peKeyType
Case KeyType.Primary
objOptions.Add("PRIMARY KEY")
Case KeyType.Unique
objOptions.Add("UNIQUE")
End Select
For Each objOption As Object In objOptions
strOptions &= " " & DirectCast(objOption, String)
Next
ColumnOptions = strOptions
End Function
Private Sub EnsureDecimalDataType()
If peType <> DataType.Decimal Then
Throw New MethodLockedException("First set Type to " & DataType.Decimal.ToString)
End If
End Sub
Private Sub EnsureCharacterDataType()
If Not IsCharacterDataType() Then
Throw New MethodLockedException("Data type is not character based")
End If
End Sub
Private Sub EnsureIntegerDataType()
If Not IsIntegerDataType() Then
Throw New MethodLockedException
End If
End Sub
Private Function IsIntegerDataType() As Boolean
Select Case peType
Case DataType.BigInteger, DataType.Integer, DataType.SmallInteger, DataType.TinyInteger
Return True
Case Else
Return False
End Select
End Function
Friend Function IsCharacterDataType() As Boolean
Select Case peType
Case _
DataType.Character, DataType.UnicodeCharacter, _
DataType.VariableCharacter, DataType.UnicodeVariableCharacter
Return True
Case Else
Return False
End Select
End Function
Private Function DataTypeString( _
ByVal eConnection As Database.ConnectionType, _
ByVal eDataType As DataType) As String
Dim strDataType As String
Select Case eDataType
Case DataType.TinyInteger
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "BYTE"
Case Database.ConnectionType.MySQL
strDataType = "TINYINT UNSIGNED"
Case Database.ConnectionType.SQLServer
strDataType = "TINYINT"
End Select
Case DataType.SmallInteger
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "SMALLINT"
Case Database.ConnectionType.MySQL
strDataType = "SMALLINT"
Case Database.ConnectionType.SQLServer
strDataType = "SMALLINT"
End Select
Case DataType.Integer
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "INTEGER"
Case Database.ConnectionType.MySQL
strDataType = "INT"
Case Database.ConnectionType.SQLServer
strDataType = "INTEGER"
End Select
Case DataType.BigInteger
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "NUMERIC(19,0)"
Case Database.ConnectionType.MySQL
strDataType = "BIGINT"
Case Database.ConnectionType.SQLServer
strDataType = "BIGINT"
End Select
Case DataType.Character
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "TEXT(" & pintSize & ")"
Case Database.ConnectionType.MySQL
strDataType = "CHAR(" & pintSize & ")"
Case Database.ConnectionType.SQLServer
strDataType = "CHAR(" & pintSize & ")"
End Select
Case DataType.UnicodeCharacter
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
'Unicode is only supported in Microsoft Access 2000+
strDataType = "TEXT(" & pintSize & ")"
Case Database.ConnectionType.MySQL
strDataType = "NCHAR(" & pintSize & ")"
Case Database.ConnectionType.SQLServer
strDataType = "NCHAR(" & pintSize & ")"
End Select
Case DataType.VariableCharacter
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "TEXT(" & pintSize & ")"
Case Database.ConnectionType.MySQL
strDataType = "VARCHAR(" & pintSize & ")"
Case Database.ConnectionType.SQLServer
strDataType = "VARCHAR(" & pintSize & ")"
End Select
Case DataType.UnicodeVariableCharacter
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
'Unicode is only supported in Microsoft Access 2000+
strDataType = "TEXT(" & pintSize & ")"
Case Database.ConnectionType.MySQL
strDataType = "NVARCHAR(" & pintSize & ")"
Case Database.ConnectionType.SQLServer
strDataType = "NVARCHAR(" & pintSize & ")"
End Select
Case DataType.Decimal
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "NUMERIC(" & pintPrecision & "," & pintScale & ")"
Case Database.ConnectionType.MySQL
strDataType = "DECIMAL(" & pintPrecision & "," & pintScale & ")"
Case Database.ConnectionType.SQLServer
strDataType = "NUMERIC(" & pintPrecision & "," & pintScale & ")"
End Select
Case DataType.Real
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "REAL"
Case Database.ConnectionType.MySQL
strDataType = "FLOAT"
Case Database.ConnectionType.SQLServer
strDataType = "REAL"
End Select
Case DataType.Float
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "FLOAT"
Case Database.ConnectionType.MySQL
strDataType = "DOUBLE"
Case Database.ConnectionType.SQLServer
strDataType = "FLOAT"
End Select
Case DataType.SmallMoney
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "NUMERIC(10,4)"
Case Database.ConnectionType.MySQL
strDataType = "DECIMAL(10,4)"
Case Database.ConnectionType.SQLServer
strDataType = "SMALLMONEY"
End Select
Case DataType.Money
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "NUMERIC(19,4)"
Case Database.ConnectionType.MySQL
strDataType = "DECIMAL(19,4)"
Case Database.ConnectionType.SQLServer
strDataType = "MONEY"
End Select
Case DataType.Boolean
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "YESNO"
Case Database.ConnectionType.MySQL
strDataType = "BOOLEAN"
Case Database.ConnectionType.SQLServer
strDataType = "BIT"
End Select
Case DataType.SmallDateTime
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "DATETIME"
Case Database.ConnectionType.MySQL
strDataType = "DATETIME"
Case Database.ConnectionType.SQLServer
strDataType = "SMALLDATETIME"
End Select
Case DataType.DateTime
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "DATETIME"
Case Database.ConnectionType.MySQL
strDataType = "DATETIME"
Case Database.ConnectionType.SQLServer
strDataType = "DATETIME"
End Select
Case DataType.TimeStamp
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
Throw New NotSupportedException("TIMESTAMP")
Case Database.ConnectionType.MySQL
strDataType = "TIMESTAMP"
Case Database.ConnectionType.SQLServer
strDataType = "TIMESTAMP"
End Select
Case DataType.Text
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "MEMO"
Case Database.ConnectionType.MySQL
strDataType = "LONGTEXT"
Case Database.ConnectionType.SQLServer
strDataType = "TEXT"
End Select
Case DataType.UnicodeText
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
'Unicode is only supported in Microsoft Access 2000+
strDataType = "MEMO"
Case Database.ConnectionType.MySQL
strDataType = "LONGTEXT CHARACTER SET UTF8"
Case Database.ConnectionType.SQLServer
strDataType = "NTEXT"
End Select
Case DataType.Binary
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "OLEOBJECT"
Case Database.ConnectionType.MySQL
strDataType = "BLOB"
Case Database.ConnectionType.SQLServer
strDataType = "BINARY"
End Select
Case DataType.VariableBinary
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "OLEOBJECT"
Case Database.ConnectionType.MySQL
strDataType = "BLOB"
Case Database.ConnectionType.SQLServer
strDataType = "VARBINARY"
End Select
Case DataType.Image
Select Case eConnection
Case Database.ConnectionType.MicrosoftAccess
strDataType = "IMAGE"
Case Database.ConnectionType.MySQL
strDataType = "LONGBLOB"
Case Database.ConnectionType.SQLServer
strDataType = "IMAGE"
End Select
End Select
DataTypeString = strDataType
End Function
End Class
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