Part 2: a VB.NET Version of this Project

After a while, it became obvious that there was no way to drive the ArcMap application from Excel — timeouts from errors wouldn’t get handled, so bad runs would hang.

A real app could raise errors on timeouts, so, I had to learn VB OLE programming. Fortunately there’s a free version of VB called VB Express Edition. It’s a complete VB environment, that uses .NET. Unfortunately, there aren’t references for the old VB classes included. .NET is, in parts, a bit more complex than VB – it’s a victim of feature-itis. There are also fewer VB.NET tutorials out there.

Here’s a diagram of the “new” system, which is, mostly, going to be an iteration of the “old”system.

The app is broken into three parts. One part manages a list of files. One part is a bunch of “scripts” that do the actual work of analyzing, copying, and deleting files. One part is a scheduler that will run the scripts only at specified times, so that it won’t interrupt the normal workday.

File Batching

This code fits into the larger goal of a project that will reliably run an application on a set of files, over the course of several nights.

The first thing I’ve written, so far, is something that will scan the file system for file names, to create a “batch”. The batch is stored in a Microsoft Access .mdb file.

The coolest feature is that you don’t need Access to run it. It creates the .mdb file from scratch, and inserts data into it.

Another cool feature is the call to System.IO.Directory.GetFiles. That does all the scanning that, in the original project, required custom code.

This is very alpha code, but, it might help someone out there.

FileBatch.vb

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

Here’s the code that calls it (from a form button):

    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
        Dim fb As FileBatch
        fb = New FileBatch
        fb.CreateBatch("C:tmptext.mdb", "C:Documents and Settingsjohnkuser", "jpg", Me.StatusMessage)
        Close()
    End Sub

References

http://www.4guysfromrolla.com/webtech/013101-1.2.shtml

Attachment	Size
Form1.vb.txt	343 bytes
Form1.Designer.vb.txt	2.24 KB
FileBatch.vb.txt	2.49 KB
filebatcher.jpg	8.02 KB