ModDatabase.vb

Imports System.Data
Imports System.IO
Imports ClosedXML.Excel
Imports DocumentFormat.OpenXml.Spreadsheet
Module ModDatabase

    Public Sub SubCreateDatabase(StrInputSpreadsheet As String)

        Dim SQLquery As New SQLite.SQLiteCommand()
        SQLquery.Connection = ConSQLite

        '================================
        'Create the tables
        '================================

        SQLquery.CommandText = "CREATE TABLE tbl_Core (Id INTEGER PRIMARY KEY ASC, fld_Name TEXT, fld_Type TEXT, fld_Abstract BOOL DEFAULT FALSE, fld_Nillable BOOL DEFAULT FALSE, fld_SubstitutionGroup TEXT, fld_PeriodType TEXT)"
        SQLquery.ExecuteNonQuery()

        SQLquery.CommandText = "CREATE TABLE tbl_Linkroles (Id INTEGER PRIMARY KEY ASC, fld_Id TEXT, fld_Definition TEXT, fld_UsedOn TEXT)"
        SQLquery.ExecuteNonQuery()

        SQLquery.CommandText = "CREATE TABLE tbl_Labels (Id INTEGER PRIMARY KEY ASC, fld_Name TEXT, fld_Language TEXT, fld_Role TEXT, fld_ArcRole TEXT, fld_Label TEXT)"
        SQLquery.ExecuteNonQuery()

        SQLquery.CommandText = "CREATE TABLE tbl_References (Id INTEGER PRIMARY KEY ASC, fld_Name TEXT, fld_Role TEXT, fld_ArcRole TEXT, fld_RefChapter TEXT, fld_RefName TEXT, fld_RefParagraph TEXT, fld_RefPublisher TEXT)"
        SQLquery.ExecuteNonQuery()

        SQLquery.CommandText = "CREATE TABLE tbl_Presentation (Id INTEGER PRIMARY KEY ASC, fld_Name TEXT, fld_ItemID INTEGER DEFAULT 0, fld_ParentID INTEGER DEFAULT 0, fld_Parent TEXT, fld_Order INTEGER DEFAULT 0, fld_Linkrole TEXT, fld_PresentationDescription TEXT)"
        SQLquery.ExecuteNonQuery()

        SQLquery.CommandText = "CREATE TABLE tbl_Definition (Id INTEGER PRIMARY KEY ASC, fld_Name TEXT, fld_ItemID INTEGER DEFAULT 0, fld_ParentID INTEGER DEFAULT 0, fld_Parent TEXT, fld_Arcrole TEXT, fld_ContextElement TEXT, fld_Closed TEXT, fld_Order INTEGER DEFAULT 0, fld_Linkrole TEXT)"
        SQLquery.ExecuteNonQuery()

        SQLquery.CommandText = "CREATE TABLE tbl_Entrypoints (Id INTEGER PRIMARY KEY ASC, fld_Name TEXT, fld_Description TEXT, fld_Version TEXT, fld_languages TEXT , fld_Prefix TEXT)"
        SQLquery.ExecuteNonQuery()


        '================================
        'Import worksheet Core from spreadsheet to tbl_Core
        '================================

        Dim strSQL As String = "SELECT * FROM tbl_Core"

        Dim da As SQLite.SQLiteDataAdapter
        Dim ds As New DataSet
        Dim dsNewRow As DataRow
        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsCore")

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            ' Get worksheet Core

            Dim Worksheet As IXLWorksheet = Workbook.Worksheet("Core")

            ' Collect data starting at row 2

            For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                dsNewRow = ds.Tables("dsCore").NewRow

                dsNewRow.Item("fld_Name") = row.Cell(1).Value
                dsNewRow.Item("fld_Type") = row.Cell(2).Value
                dsNewRow.Item("fld_Abstract") = Convert.ToBoolean(row.Cell(3).Value)
                dsNewRow.Item("fld_Nillable") = Convert.ToBoolean(row.Cell(4).Value)
                dsNewRow.Item("fld_SubstitutionGroup") = row.Cell(5).Value
                dsNewRow.Item("fld_PeriodType") = row.Cell(6).Value

                ds.Tables("dsCore").Rows.Add(dsNewRow)

            Next

        End Using

        da.Update(ds, "dsCore")

        '================================
        'Import worksheet Linkroles from spreadsheet to tbl_Linkrole
        '================================

        strSQL = "SELECT * FROM tbl_Linkroles"

        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb1 As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsLinkroles")

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            ' Get worksheet Linkroles

            Dim Worksheet As IXLWorksheet = Workbook.Worksheet("Linkroles")

            ' Collect data starting at row 2

            For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                dsNewRow = ds.Tables("dsLinkroles").NewRow

                dsNewRow.Item("fld_Id") = row.Cell(1).Value
                dsNewRow.Item("fld_Definition") = row.Cell(2).Value
                dsNewRow.Item("fld_UsedOn") = row.Cell(3).Value

                ds.Tables("dsLinkroles").Rows.Add(dsNewRow)

            Next

        End Using

        da.Update(ds, "dsLinkroles")

        '================================
        'Import worksheet Labels from spreadsheet to tbl_Labels
        '================================

        strSQL = "SELECT * FROM tbl_Labels"

        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb2 As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsLabels")

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            ' Get worksheet labels

            Dim Worksheet As IXLWorksheet = Workbook.Worksheet("Labels")

            ' Collect data starting at row 2

            For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                dsNewRow = ds.Tables("dsLabels").NewRow

                dsNewRow.Item("fld_Name") = row.Cell(1).Value
                dsNewRow.Item("fld_language") = row.Cell(2).Value
                dsNewRow.Item("fld_Role") = row.Cell(3).Value
                dsNewRow.Item("fld_ArcRole") = row.Cell(4).Value
                dsNewRow.Item("fld_Label") = row.Cell(5).Value

                ds.Tables("dsLabels").Rows.Add(dsNewRow)

            Next

        End Using

        da.Update(ds, "dsLabels")

        '================================
        'Import worksheet References from spreadsheet to tbl_References
        '================================

        strSQL = "SELECT * FROM tbl_References"

        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb3 As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsReferences")

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            ' Get worksheet References

            Dim Worksheet As IXLWorksheet = Workbook.Worksheet("References")

            ' Collect data starting at row 2

            For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                'Check if a reference is available (key is de presence of an ArcRole)

                If Not String.IsNullOrEmpty(row.Cell(2).Value) Then

                    dsNewRow = ds.Tables("dsReferences").NewRow

                    dsNewRow.Item("fld_Name") = row.Cell(1).Value
                    dsNewRow.Item("fld_Role") = row.Cell(2).Value
                    dsNewRow.Item("fld_ArcRole") = row.Cell(3).Value
                    dsNewRow.Item("fld_RefChapter") = row.Cell(4).Value
                    dsNewRow.Item("fld_RefName") = row.Cell(5).Value
                    dsNewRow.Item("fld_RefParagraph") = row.Cell(6).Value
                    dsNewRow.Item("fld_RefPublisher") = row.Cell(7).Value

                    ds.Tables("dsReferences").Rows.Add(dsNewRow)

                End If

            Next

        End Using

        da.Update(ds, "dsReferences")

        '================================
        'Import the presentation worksheets from spreadsheet to tbl_Presentation
        '================================

        'Get a list of sheets with a presentation

        Dim ArrayPresentations() As String = Nothing 'Array to store all presentation sheets
        ReDim Preserve ArrayPresentations(0)

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            For Each sheet In Workbook.Worksheets

                If sheet.Name.StartsWith("Presentation_") Then

                    ArrayPresentations(UBound(ArrayPresentations)) = sheet.Name
                    ReDim Preserve ArrayPresentations(UBound(ArrayPresentations) + 1)

                End If

            Next

        End Using

        ReDim Preserve ArrayPresentations(UBound(ArrayPresentations) - 1) 'Remove last item (is empty)

        strSQL = "SELECT * FROM tbl_Presentation"

        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb4 As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsPresentation")

        For Each presentation In ArrayPresentations

            Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

                ' Get worksheet Presentation_??

                Dim Worksheet As IXLWorksheet = Workbook.Worksheet(presentation)

                ' Collect data starting at row 2

                For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                    dsNewRow = ds.Tables("dsPresentation").NewRow

                    dsNewRow.Item("fld_Name") = Trim(row.Cell(1).Value)
                    dsNewRow.Item("fld_ItemId") = row.Cell(2).Value
                    dsNewRow.Item("fld_ParentId") = row.Cell(3).Value
                    dsNewRow.Item("fld_Order") = row.Cell(4).Value
                    dsNewRow.Item("fld_Linkrole") = row.Cell(5).Value
                    dsNewRow.Item("fld_PresentationDescription") = presentation.Substring(13)

                    ds.Tables("dsPresentation").Rows.Add(dsNewRow)

                Next

            End Using

        Next

        da.Update(ds, "dsPresentation")

        '================================
        'Import worksheet Definition from spreadsheet to tbl_Definition
        '================================

        strSQL = "SELECT * FROM tbl_Definition"

        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb5 As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsDefinition")

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            ' Get worksheet Definition

            Dim Worksheet As IXLWorksheet = Workbook.Worksheet("Definition")

            ' Collect data starting at row 2

            For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                dsNewRow = ds.Tables("dsDefinition").NewRow

                dsNewRow.Item("fld_Name") = row.Cell(1).Value
                dsNewRow.Item("fld_ItemID") = row.Cell(2).Value
                dsNewRow.Item("fld_ParentID") = row.Cell(3).Value
                dsNewRow.Item("fld_Arcrole") = row.Cell(4).Value
                dsNewRow.Item("fld_Order") = row.Cell(5).Value
                dsNewRow.Item("fld_ContextElement") = row.Cell(6).Value
                dsNewRow.Item("fld_Closed") = row.Cell(7).Value
                dsNewRow.Item("fld_Linkrole") = row.Cell(8).Value

                ds.Tables("dsDefinition").Rows.Add(dsNewRow)

            Next

        End Using

        da.Update(ds, "dsDefinition")

        '================================
        'Import worksheet Entrypoints from spreadsheet to tbl_Entrypoints
        '================================

        strSQL = "SELECT * FROM tbl_Entrypoints"

        da = New SQLite.SQLiteDataAdapter(strSQL, ConSQLite)
        Dim cb6 As New SQLite.SQLiteCommandBuilder(da)

        da.Fill(ds, "dsEntrypoints")

        Using Workbook As XLWorkbook = New XLWorkbook(StrInputSpreadsheet)

            ' Get worksheet Definition

            Dim Worksheet As IXLWorksheet = Workbook.Worksheet("Entrypoints")

            ' Collect data starting at row 2

            For Each row As IXLRow In Worksheet.RowsUsed.Skip(1)

                dsNewRow = ds.Tables("dsEntrypoints").NewRow

                dsNewRow.Item("fld_Name") = row.Cell(1).Value
                dsNewRow.Item("fld_Description") = row.Cell(2).Value
                dsNewRow.Item("fld_Version") = row.Cell(3).Value
                dsNewRow.Item("fld_Languages") = row.Cell(4).Value
                dsNewRow.Item("fld_Prefix") = row.Cell(5).Value

                ds.Tables("dsEntrypoints").Rows.Add(dsNewRow)

            Next

        End Using

        da.Update(ds, "dsEntrypoints")

    End Sub

End Module