Imports System.Data
Imports system.Data.SqlClient


Public Class FileBatch

    Private Const StatusNone = 0
    Private Const StatusProcessed = 1
    Private Const StatusSkip = 2


    Private Sub CreateNewDatabase(ByVal dbPath As String)
        ' delete the file first
        If System.IO.File.Exists(dbPath) = True Then
            System.IO.File.Delete(dbPath)
        End If

        Dim dbCatalog As New ADOX.Catalog()
        dbCatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath)

        Dim objFirstTable As New ADOX.Table()
        objFirstTable.Name = "FileBatch"
        objFirstTable.Columns.Append("File", ADOX.DataTypeEnum.adLongVarWChar, 1024)
        objFirstTable.Columns.Append("DestinationFile", ADOX.DataTypeEnum.adLongVarWChar, 1024)
        objFirstTable.Columns.Append("Status", ADOX.DataTypeEnum.adInteger)
        objFirstTable.Columns.Append("ProcessingDate", ADOX.DataTypeEnum.adDate)
        objFirstTable.Columns.Append("Comment", ADOX.DataTypeEnum.adVarWChar, 255)
        objFirstTable.Keys.Append("PK_File", 1, "File")

        dbCatalog.Tables.Append(objFirstTable)

        'cleanup
        dbCatalog = Nothing
        objFirstTable = Nothing
    End Sub


    Public Function CreateBatch(ByVal dbPath As String, ByVal pathStart As String, ByVal ext As String, Optional ByVal statusBox As TextBox = Nothing)
        Dim ar, element

        CreateNewDatabase(dbPath)

        If statusBox IsNot Nothing Then
            statusBox.Text = "Scanning for *." & ext & " in " & pathStart & "."
            statusBox.Refresh()
        End If

        ar = System.IO.Directory.GetFiles(pathStart, "*." & ext, IO.SearchOption.AllDirectories)

        Dim cs
        Dim conn As OleDb.OleDbConnection
        Dim command As OleDb.OleDbCommand
        Dim sql As String

        cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
        conn = New OleDb.OleDbConnection(cs)
        conn.Open()

        For Each element In ar
            sql = "INSERT INTO FileBatch (File,DestinationFile,Status,ProcessingDate,Comment) VALUES ('" & element & "','',0,'1/1/1899','')"
            ' Console.WriteLine(sql)
            command = New OleDb.OleDbCommand()
            With command
                .Connection = conn
                .CommandText = sql
                .ExecuteNonQuery()
                .Dispose()
            End With
        Next
        conn.Close()


        CreateBatch = 1
    End Function

End Class