#Region "  Imports  "

Imports Ado
Imports System.Collections.Generic

#End Region

Public Class Orm

#Region "  Declarations  "

    Const _connectionStringKey As String = "ReportConnectionString"
    Protected Shared _cn As IDbConnection

#End Region


#Region "  Public Methods  "

    'Make database connection:
    'Note: call from page_load before calling any public function.
    Public Shared Sub Connect()

        _cn = Ado.Connection(_connectionStringKey)

    End Sub

    'Tear down database connection:
    'Note: call from page_unload.
    Public Shared Sub Disconnect()

        Ado.Dispose(_cn)

    End Sub

    'Retrieve a list of database names for the specified connection:
    '   Returns an alpha sorted list of names in a dataset
    Public Shared Function GetDatabaseNameList() As DataSet

        Dim sql As New StringBuilder
        'sql.Append("USE master ")
        'sql.Append("GO ")
        sql.Append("SELECT name AS databaseName ")
        sql.Append("FROM master..sysdatabases ")
        sql.Append("WHERE dbid > 6 ")
        sql.Append("ORDER BY name;")
        Dim cmd As IDbCommand = Ado.Command(_cn, sql.ToString)
        Dim adpt As IDbDataAdapter = Ado.Adapter(cmd)
        Dim ds As DataSet = Ado.Dataset(cmd)
        Return ds

    End Function

    'Retrieve a list of tables names for the specified connection:
    '   Returns an alpha sorted list of names in a dataset
    Public Shared Function GetTableNameList(Optional ByVal databaseName As String = "") As DataSet

        Dim sql As New StringBuilder
        'If Not databaseName = String.Empty Then
        '    'sql.Append("USE " & databaseName & "; ")
        '    sql.Append("GO " & vbCrLf & vbCrLf)
        'End If
        sql.Append("SELECT table_name AS tableName ")
        sql.Append("FROM INFORMATION_SCHEMA.Tables ")
        sql.Append("WHERE TABLE_TYPE = 'BASE TABLE' ")
        sql.Append("ORDER BY table_name;")

        'HttpContext.Current.Response.Write(sql.ToString)
        'HttpContext.Current.Response.End()


        Dim cmd As IDbCommand = Ado.Command(_cn, sql.ToString)
        Dim adpt As IDbDataAdapter = Ado.Adapter(cmd)
        Dim ds As DataSet = Ado.Dataset(cmd)
        Return ds

    End Function

    'Retrieve schema of specified database table:
    '   First argument "tableName" specifies the table of interest in the database.
    '   Returns a sorted list containing key/value pairs.
    Public Shared Function GetTableInformation(ByVal tableName As String) As SortedList

        'Key is Column Name; Value is Column Type

        Dim ds As DataSet
        Dim dr As DataRow
        Dim sl As SortedList = New SortedList()

        Dim sql As String = String.Empty
        sql = "EXEC sp_columns " & Chr(34) & tableName & Chr(34) & ";"

        Dim cmd As IDbCommand = Ado.Command(_cn, sql.ToString)
        ds = Ado.Dataset(cmd)

        For Each dr In ds.Tables(0).Rows

            sl.Add(dr.Item("COLUMN_NAME"), ConvertTypes(dr.Item("DATA_TYPE")))

        Next

        Return sl

    End Function

    'Return class code for the data provider class for a database table:
    '   First argument 'sl' is a sorted list of the tables schema.
    '   Second argument 'tableName' is the name of the table to process.
    '   Returns a text string representing the required vb code.
    Public Shared Function BuildDataProvider(ByVal sl As SortedList, ByVal tableName As String) As String

        Dim s As New StringBuilder
        s.Append(BuildImports)
        s.Append("Namespace Data" & vbCrLf & vbCrLf)
        s.Append("Public Class " & tableName & "_data_provider" & vbCrLf & vbCrLf)
        s.Append(BuildInstanceFields(sl))
        s.Append(BuildEnumerations)
        s.Append(BuildProperties(sl))
        s.Append("#Region ""  Static Methods  """ & vbCrLf & vbCrLf)
        s.Append(BuildClearMethod(sl))
        s.Append(BuildConnectMethod())
        s.Append(BuildCreateMethod(sl, tableName))
        s.Append(BuildDeleteMethod(tableName))
        s.Append(BuildDisconnectMethod())
        's.Append(BuildExportMethod(sl, tableName))
        s.Append(BuildInitializeDataTableMethod(sl, tableName))
        s.Append(BuildInitializeMethod(sl, tableName))
        s.Append(BuildUpdateMethod(sl, tableName))
        s.Append("#End Region" & vbCrLf & vbCrLf)
        s.Append("End Class" & vbCrLf & vbCrLf)
        s.Append("End Namespace")
        Return s.ToString()

    End Function

#End Region

#Region "  Private Methods  "

    'Convert sql datatype ID's to VB.NET datatypes
    '   First argument "typeID" specifies the id to convert
    '   Returns a string containing the vb type or an [ERROR] message.
    Private Shared Function ConvertTypes(ByVal typeId As Integer) As String

        Select Case typeId
            Case -150
                Return "Object"
            Case -11
                Return "Guid"
            Case -10
                Return "String"
            Case -9
                Return "String"
            Case -8
                Return "String"
            Case -7
                Return "Boolean"
            Case -6
                Return "Byte"
            Case -5
                Return "Int64"
            Case -4
                Return "Object"
            Case -3
                Return "Byte()"
            Case -2
                Return "Byte()"
            Case -1
                Return "String"
            Case 1
                Return "String"
            Case 2
                Return "Decimal"
            Case 3
                Return "Decimal"
            Case 4
                Return "Int32"
            Case 5
                Return "Int16"
            Case 6
                Return "Double"
            Case 7
                Return "Single"
            Case 11
                Return "DateTime"
            Case 12
                Return "String"
            Case Else
                Return "[ERROR]"
        End Select

    End Function

    'Convert VB.NET datatypes to initialization string
    '   First argument "typeID" specifies the type to initialize
    '   Returns a string containing the initializatin string.
    Private Shared Function ConvertInitialization(ByVal typeId As String) As String

        Select Case typeId

            Case "String"
                Return " = String.Empty"
            Case "Boolean"
                Return " = False"
            Case "Byte"
                Return " = &H00"
            Case "Int64"
                Return " = -1"
            Case "Byte()"
                Return " = New Byte(size-1) {}"
            Case "Decimal"
                Return " = -1"
            Case "Int32"
                Return " = -1"
            Case "Int16"
                Return " = -1"
            Case "Double"
                Return " = -1"
            Case "Single"
                Return " = -1"
            Case "DateTime"
                Return " = Date.Parse('01/01/1001')"
            Case Else
                Return String.Empty
        End Select

    End Function

    'Get Cast type by VB.NET datatypes
    '   First argument "typeID" specifies the cast needed
    '   Returns a string containing the Cast type
    Private Shared Function GetCast(ByVal typeId As String) As String

        Select Case typeId

            Case "String"
                Return "CStr"
            Case "Boolean"
                Return "CBool"
            Case "Byte"
                Return "CByte"
            Case "Int64"
                Return "CInt"
            Case "Decimal"
                Return "CDec"
            Case "Int32"
                Return "CInt"
            Case "Int16"
                Return "CInt"
            Case "Double"
                Return "CDbl"
            Case "Single"
                Return "CSng"
            Case "DateTime"
                Return "CDate"
            Case Else
                Return String.Empty
        End Select

    End Function

    'The following methods assemble code for each section:
    Private Shared Function BuildInstanceFields(ByVal sl As SortedList) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("#Region ""  Instance Fields  """ & vbCrLf & vbCrLf)
        s.Append("Const _connectionStringKey As String = ""DefaultConnectionString""" & vbCrLf)
        For i As Integer = 0 To sl.Count - 1
            s.Append("Shared _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & " As " & sl.GetByIndex(i).ToString & ConvertInitialization(sl.GetByIndex(i).ToString) & vbCrLf)
            s.Append("Shared _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & "IsSet As Boolean = False" & vbCrLf)
        Next i
        s.Append("Shared _isValid As Boolean = False")
        s.Append(vbCrLf & "#End Region" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildImports() As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("#Region ""  Imports  """ & vbCrLf & vbCrLf)
        s.Append("Imports Ado" & vbCrLf)
        s.Append("Imports System.Data" & vbCrLf)
        s.Append("Imports System.Text" & vbCrLf)
        s.Append(vbCrLf & "#End Region" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildEnumerations() As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("#Region ""  Enumerations  """ & vbCrLf & vbCrLf)
        s.Append("Public Enum Selection As Integer" & vbCrLf)
        s.Append("AllRecords" & vbCrLf)
        s.Append("End Enum" & vbCrLf)
        s.Append(vbCrLf & "#End Region" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildProperties(ByVal sl As SortedList) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("#Region ""  Properties  """ & vbCrLf & vbCrLf)
        For i As Integer = 0 To sl.Count - 1
            s.Append("Public Shared Property " & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & "() As " & sl.GetByIndex(i).ToString & vbCrLf)
            s.Append("Get" & vbCrLf)
            s.Append("Return _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & vbCrLf)
            s.Append("End Get" & vbCrLf)
            s.Append("Set (ByVal value As " & sl.GetByIndex(i).ToString & ")" & vbCrLf)
            s.Append("If Not value = _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & " Then" & vbCrLf)
            s.Append("_" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & " = True" & vbCrLf)
            s.Append("_isValid = False" & vbCrLf)
            s.Append("_" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & " = value" & vbCrLf)
            s.Append("End If" & vbCrLf)
            s.Append("End Set" & vbCrLf)
            s.Append("End Property" & vbCrLf)
        Next i
        s.Append(vbCrLf & "#End Region" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildClearMethod(ByVal sl As SortedList) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Shared Sub Clear()" & vbCrLf)
        For i As Integer = 0 To sl.Count - 1
            s.Append("_" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & ConvertInitialization(sl.GetByIndex(i).ToString) & vbCrLf)
        Next i
        s.Append("End Sub" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildConnectMethod() As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Shared Function Connect() As IDbConnection" & vbCrLf)
        s.Append("Return Connection(_connectionStringKey" & vbCrLf)
        s.Append("End Function" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildCreateMethod(ByVal sl As SortedList, ByVal tableName As String) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Shared Function Create(ByRef cn As IDbConnection) As Integer" & vbCrLf)
        s.Append("Dim sql As New StringBuilder" & vbCrLf)
        s.Append("sql.Append(""INSERT INTO " & tableName & " ("")" & vbCrLf)
        For i As Integer = 0 To sl.Count - 2
            s.Append("If _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & "IsSet Then sal.Append(""" & sl.GetKey(i).ToString & ", "")" & vbCrLf)
        Next i
        s.Append("sql.Append(""" & StrConv(sl.GetKey(sl.Count - 1).ToString, VbStrConv.Lowercase) & " "")" & vbCrLf)
        s.Append("sql.Append("") VALUES ("")" & vbCrLf)
        For i As Integer = 0 To sl.Count - 2
            s.Append("If _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & "IsSet Then sal.Append(""@" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & ", "")" & vbCrLf)
        Next i
        s.Append("sql.Append(""@" & StrConv(sl.GetKey(sl.Count - 1).ToString, VbStrConv.Lowercase) & "); "")" & vbCrLf & vbCrLf)
        s.Append("Sql.Append(""SELECT @id = scope_identity();"")" & vbCrLf)
        s.Append("Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString)" & vbCrLf)
        For i As Integer = 0 To sl.Count - 2
            s.Append("If _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & "IsSet Then Parameter(cmd, ""@" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & """, _" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & ")" & vbCrLf)
        Next i
        s.Append("Parameter(cmd, ""@" & StrConv(sl.GetKey(sl.Count - 1).ToString, VbStrConv.Lowercase) & """, _" & StrConv(sl.GetKey(sl.Count - 1).ToString, VbStrConv.Lowercase) & ")" & vbCrLf)
        s.Append("Parameter(cmd, ""@id"", DbType.Int32)" & vbCrLf)
        s.Append("Execute(cmd)" & vbCrLf)
        s.Append("_isValid = True" & vbCrLf)
        s.Append("Return cmd.Parameters.Item(""@id"").Value" & vbCrLf)
        s.Append("End Function" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildDeleteMethod(ByVal tableName As String) As String

        Dim s As StringBuilder = New StringBuilder
        s.Append("Public Shared Sub Delete(ByRef cn As IDbConnection, ByVal id As Integer)" & vbCrLf)
        s.Append("Dim sql As New StringBuilder" & vbCrLf)
        s.Append("sql.Append(""DELETE "")" & vbCrLf)
        s.Append("sql.Append(""FROM "" & " & tableName & " & "" "")" & vbCrLf)
        s.Append("sql.Append(""WHERE id = @id"")" & vbCrLf)
        s.Append("sql.Append("";"")" & vbCrLf)
        s.Append("Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString)" & vbCrLf)
        s.Append("Parameter(cmd, ""@id"", id)" & vbCrLf)
        s.Append("Execute(cmd)" & vbCrLf)
        s.Append("End Sub" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildDisconnectMethod() As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Shared Sub Disconnect(ByRef cn As IDBConnection)" & vbCrLf)
        s.Append("Dispose(cn)" & vbCrLf)
        s.Append("End Sub" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildExportMethod(ByVal sl As SortedList, ByVal tableName As String) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Export Code Goes Here" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildInitializeDataTableMethod(ByVal sl As SortedList, ByVal tableName As String) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Overloads Shared Function Initialize(ByRef cn As IDbConnection, ByVal SelectBy As Selection, Optional ByVal sortOrder As String = """") As DataTable" & vbCrLf)
        s.Append("Dim sql As New StringBuilder " & vbCrLf)
        s.Append("sql.Append(""SELECT "") " & vbCrLf)
        For i As Integer = 0 To sl.Count - 2
            s.Append("sql.Append(""" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & ", "")" & vbCrLf)
        Next i
        s.Append("sql.Append(""" & StrConv(sl.GetKey(sl.Count - 1).ToString, VbStrConv.Lowercase) & " "")" & vbCrLf)
        s.Append("sql.Append(""FROM " & tableName & " "")" & vbCrLf)
        s.Append("'Select Case SelectBy " & vbCrLf)
        s.Append("'    Case" & vbCrLf)
        s.Append("'    Case Else" & vbCrLf)
        s.Append("sql.Append("" "") " & vbCrLf)
        s.Append("'End Select " & vbCrLf)
        s.Append("If Not sortOrder = String.Empty Then" & vbCrLf)
        s.Append("sql.Append("" ORDER BY "" & sortOrder)" & vbCrLf)
        s.Append("End If" & vbCrLf)
        s.Append("sql.Append("";"")" & vbCrLf)
        s.Append("Dim cmd As IDbCommand = Command(cn, sql.ToString)" & vbCrLf)
        s.Append("'Select Case SelectBy " & vbCrLf)
        s.Append("'    Case" & vbCrLf)
        s.Append("'    Case Else" & vbCrLf)
        s.Append("'End Select " & vbCrLf)
        s.Append("Dim dt As DataTable = Ado.DataTable(cmd)" & vbCrLf)
        s.Append("Return dt" & vbCrLf)
        s.Append("End Function " & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildInitializeMethod(ByVal sl As SortedList, ByVal tableName As String) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Overloads Shared Sub Initialize(ByRef cn As IDbConnection, ByVal id As Integer)" & vbCrLf)
        s.Append("Dim sql As New StringBuilder " & vbCrLf)
        s.Append("sql.Append(""SELECT "") " & vbCrLf)
        For i As Integer = 0 To sl.Count - 2
            s.Append("sql.Append(""" & StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase) & ", "")" & vbCrLf)
        Next i
        s.Append("sql.Append(""" & StrConv(sl.GetKey(sl.Count - 1).ToString, VbStrConv.Lowercase) & " "")" & vbCrLf)
        s.Append("sql.Append(""FROM " & tableName & " "")" & vbCrLf)
        s.Append("sql.Append(""WHERE id = @id"")" & vbCrLf)
        s.Append("Dim cmd As IDbCommand = Command(cn, sql.ToString)" & vbCrLf)
        s.Append("Parameter(cmd, ""@id"", id))" & vbCrLf)
        s.Append("Dim dt As DataTable = Ado.Datatable(cmd)" & vbCrLf)
        s.Append("If dt.Rows.Count > 0 Then " & vbCrLf & vbCrLf)
        For i As Integer = 0 To sl.Count - 1
            Dim field As String = sl.GetKey(i).ToString
            s.Append("If Not dt.Rows(0).Item(""" & field & """) Is DBNull.Value Then" & vbCrLf)
            s.Append("_" & StrConv(field, VbStrConv.Lowercase) & " = " & GetCast(sl.GetByIndex(i).ToString) & "(dt.Rows(0).Item(""" & field & """))" & vbCrLf)
            s.Append("End If" & vbCrLf & vbCrLf)
        Next i
        s.Append("_isValid = True" & vbCrLf)
        s.Append("End If" & vbCrLf)
        s.Append("End Sub " & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

    Private Shared Function BuildUpdateMethod(ByVal sl As SortedList, ByVal tableName As String) As String

        Dim s As StringBuilder = New StringBuilder()
        s.Append("Public Overloads Shared Sub Update(ByRef cn As IDbConnection, ByVal id As Integer)" & vbCrLf)
        s.Append("sql.Append(""UPDATE " & tableName & " SET "")" & vbCrLf)
        For i As Integer = 0 To sl.Count - 2
            Dim field As String = sl.GetKey(i).ToString
            s.Append("If _" & StrConv(field, VbStrConv.Lowercase) & "IsSet Then sql.Append(""" & field & " = @" & StrConv(field, VbStrConv.Lowercase) & ", "")" & vbCrLf)
        Next i
        Dim lastField As String = sl.GetKey(sl.Count - 1).ToString
        s.Append("If _" & StrConv(lastField, VbStrConv.Lowercase) & "IsSet Then sql.Append(""" & lastField & " = @" & StrConv(lastField, VbStrConv.Lowercase) & " "")" & vbCrLf)
        s.Append("sql.Append(""WHERE ID = @id;"")" & vbCrLf)
        s.Append("Dim cmd As IDbCommand = Command(cn, sql.ToString)" & vbCrLf)
        For i As Integer = 0 To sl.Count - 1
            Dim field As String = StrConv(sl.GetKey(i).ToString, VbStrConv.Lowercase)
            s.Append("If _" & field & "IsSet Then Parameter(cmd, ""@" & field & """, _" & field & ")" & vbCrLf)
        Next i
        s.Append("Parameter(cmd, ""@id"", id)" & vbCrLf)
        s.Append("Execute(cmd)" & vbCrLf)
        s.Append("_isValid = True" & vbCrLf)
        s.Append("End Sub" & vbCrLf & vbCrLf)
        Return s.ToString()

    End Function

#End Region

End Class