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 |
