Solved

VB.NET - Retrieve File Info From Multiple Files Via Multiple Threads?

Posted on 2010-11-19
8
494 Views
Last Modified: 2012-05-10
Hi,

I'm attempting to write a windows service that will use the filesystemwatcher to monitor for file creations, changes, renames and deletions. When it detects any of those, it will then pass information such as Date / Time, owner, full path, file name and an MD5 checksum to a SQL database. Whenever a file is created, the MD5 checksum of the new file is then used to check all previous records in the database to search for any duplicates, if a duplicate is found, an e-mail is then sent to the appropiate people.

All of the above works fine, however, if I do a large file copy (17,000 files), the service sees two records, a creation of a new file and then a change once the copy of that particular file is complete. I'd expect to see 34,000 records in the database, however, i'm only seeing somewhere in the region of 150 records.

I'm just wondering whether the files are being copied quicker than the service can run the SQL query, in which case, how would I go about making the service run the same code in multiple threads whilst ensuring that all threads don't jump on the same file?


Thanks in advance.
0
Comment
Question by:Graham2510
  • 4
  • 4
8 Comments
 

Author Comment

by:Graham2510
Comment Utility
Below is the code, I thought this might help...

Imports System.IO
Imports System.Security.AccessControl
Imports System.Security.Principal
Imports System.Management
Imports System.Security.Cryptography
Imports System.Text
Imports System.Data.SqlClient
Imports System.Net.Mail


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim RenameWatcher As FileSystemWatcher = New FileSystemWatcher()
        RenameWatcher.Path = "D:\99. Testing\DupTest"
        RenameWatcher.NotifyFilter = (NotifyFilters.FileName)
        RenameWatcher.EnableRaisingEvents = True
        RenameWatcher.IncludeSubdirectories = True
        AddHandler RenameWatcher.Renamed, AddressOf FileRenamed

        Dim CreateWatcher As FileSystemWatcher = New FileSystemWatcher()
        CreateWatcher.Path = "D:\99. Testing\DupTest"
        CreateWatcher.NotifyFilter = (NotifyFilters.FileName)
        CreateWatcher.EnableRaisingEvents = True
        CreateWatcher.IncludeSubdirectories = True
        AddHandler CreateWatcher.Created, AddressOf FileCreated

        Dim ChangeWatcher As FileSystemWatcher = New FileSystemWatcher()
        ChangeWatcher.Path = "D:\99. Testing\DupTest"
        ChangeWatcher.NotifyFilter = (NotifyFilters.Size)
        ChangeWatcher.EnableRaisingEvents = True
        ChangeWatcher.IncludeSubdirectories = True
        AddHandler ChangeWatcher.Changed, AddressOf FileChanged

        Dim DeletedWatcher As FileSystemWatcher = New FileSystemWatcher()
        DeletedWatcher.Path = "D:\99. Testing\DupTest"
        DeletedWatcher.NotifyFilter = (NotifyFilters.LastAccess Or NotifyFilters.LastWrite Or NotifyFilters.FileName Or NotifyFilters.DirectoryName)
        DeletedWatcher.EnableRaisingEvents = True
        DeletedWatcher.IncludeSubdirectories = True
        AddHandler DeletedWatcher.Deleted, AddressOf FileDeleted

    End Sub



    ''' ''''''''''''''''''''''''''''''''''''''''''' RENAMED ''''''''''''''''''''''''''''''''''''''''''''
    Private Sub FileRenamed(ByVal Sender As Object, ByVal MonitoredFile As RenamedEventArgs)
        Dim RenamedFileFullPath As String = MonitoredFile.FullPath
        Dim FileName As String = MonitoredFile.Name
        Dim OldFullPath As String = MonitoredFile.OldFullPath
        Dim MD5Hash = RenamedgetFileMd5(RenamedFileFullPath)
        Dim Owner = GetFileOwner(RenamedFileFullPath)
        Dim Date_Time As String = Date.Now
        Dim SqlStr As String
        Dim ConnString As String

        ConnString = "Data Source=SERVER\DB; UID=DOMAIN\USER; Password=MYPASS; Integrated Security=True"
        SqlStr = "INSERT into [Master].[dbo].[FileInfo]([Date_Time], [Owner], [Full_Path], [Old_Path], [File_Name], [MD5_Checksum], [Action]) VALUES('" & Date_Time & "', '" & Owner & "', '" & RenamedFileFullPath & "', '" & OldFullPath & "', '" & FileName & "', '" & MD5Hash & "', 'RENAMED')"

        Dim SqlConn As New SqlConnection()
        Dim SqlCmd As New SqlCommand()

        SqlConn.ConnectionString = ConnString
        SqlConn.Open()
        SqlCmd.Connection = SqlConn
        SqlCmd.CommandText = SqlStr
        SqlCmd.ExecuteNonQuery()
        SqlConn.Close()
    End Sub


    ''' ''''''''''''''''''''''''''''''''''''''''''' CHANGED ''''''''''''''''''''''''''''''''''''''''''''
    Private Sub FileChanged(ByVal Sender As Object, ByVal MonitoredFile As FileSystemEventArgs)
        Dim ChangedFileFullPath As String = MonitoredFile.FullPath
        Dim FileName As String = MonitoredFile.Name
        Dim MD5Hash = RenamedgetFileMd5(ChangedFileFullPath)
        Dim Owner = GetFileOwner(ChangedFileFullPath)
        Dim Date_Time As String = Date.Now


        Dim SqlStr As String
        Dim ConnString As String

        ConnString = "Data Source=SERVER\DB; UID=DOMAIN\USER; Password=MYPASS; Integrated Security=True"
        SqlStr = "INSERT into [Master].[dbo].[FileInfo]([Date_Time], [Owner], [Full_Path], [Old_Path], [File_Name], [MD5_Checksum], [Action]) VALUES('" & Date_Time & "', '" & Owner & "', '" & ChangedFileFullPath & "', 'N/A', '" & FileName & "', '" & MD5Hash & "', 'CHANGED')"

        Dim SqlConn As New SqlConnection()
        Dim SqlCmd As New SqlCommand()

        SqlConn.ConnectionString = ConnString
        SqlConn.Open()
        SqlCmd.Connection = SqlConn
        SqlCmd.CommandText = SqlStr
        SqlCmd.ExecuteNonQuery()
        SqlConn.Close()
    End Sub


    ''' ''''''''''''''''''''''''''''''''''''''''''' DELETED ''''''''''''''''''''''''''''''''''''''''''''
    Private Sub FileDeleted(ByVal Sender As Object, ByVal MonitoredFile As FileSystemEventArgs)
        Dim DeletedFileFullPath As String = MonitoredFile.FullPath
        Dim FileName As String = MonitoredFile.Name
        Dim Date_Time As String = Date.Now
        Dim SqlStr As String
        Dim ConnString As String

        ConnString = "Data Source=SERVER\DB; UID=DOMAIN\USER; Password=MYPASS; Integrated Security=True"
        SqlStr = "INSERT into [Master].[dbo].[FileInfo]([Date_Time], [Owner], [Full_Path], [Old_Path], [File_Name], [MD5_Checksum], [Action]) VALUES('" & Date_Time & "', 'N/A', '" & DeletedFileFullPath & "', 'N/A', '" & FileName & "', 'N/A', 'DELETED')"

        Dim SqlConn As New SqlConnection()
        Dim SqlCmd As New SqlCommand()

        SqlConn.ConnectionString = ConnString
        SqlConn.Open()
        SqlCmd.Connection = SqlConn
        SqlCmd.CommandText = SqlStr
        SqlCmd.ExecuteNonQuery()
        SqlConn.Close()
    End Sub


    ''' ''''''''''''''''''''''''''''''''''''''''''' CREATED ''''''''''''''''''''''''''''''''''''''''''''
    Private Sub FileCreated(ByVal Sender As Object, ByVal MonitoredFile As FileSystemEventArgs)
        Dim CreatedFileFullPath As String = MonitoredFile.FullPath
        Dim FileName As String = MonitoredFile.Name
        Dim MD5Hash = RenamedgetFileMd5(CreatedFileFullPath)
        Dim Owner = GetFileOwner(CreatedFileFullPath)
        Dim Date_Time As String = Date.Now

        Dim SqlStr2 As String
        Dim ConnString2 As String

        ConnString2 = "Data Source=SERVER\DB; UID=DOMAIN\USER; Password=MYPASS; Integrated Security=True"
        SqlStr2 = "SELECT TOP 1 * FROM Master.dbo.FileInfo WHERE (MD5_Checksum = '" & MD5Hash & "') ORDER BY Date_Time DESC"

        Dim SqlConn2 As New SqlConnection()
        Dim SQLCmd2 As New SqlCommand()
        Dim SQLdr As SqlDataReader

        SqlConn2.ConnectionString = ConnString2
        SqlConn2.Open()
        SQLCmd2.Connection = SqlConn2
        SQLCmd2.CommandText = SqlStr2
        SQLdr = SQLCmd2.ExecuteReader

        Dim CheckHash As String
        Dim CheckFileName As String
        Dim CheckFullPath As String
        Dim CheckOwner As String



        While SQLdr.Read()
            CheckHash = (SQLdr("MD5_Checksum"))
            CheckFileName = (SQLdr("File_Name"))
            CheckFullPath = (SQLdr("Full_Path"))
            CheckOwner = (SQLdr("Owner"))

            If Not CheckHash = "" Then
                Dim smtpServer As New SmtpClient()
                Dim mail As New MailMessage()
                smtpServer.Credentials = New Net.NetworkCredential("MYUSER", "MYPASSWORD")
                smtpServer.Port = 25
                smtpServer.Host = "MYMAILSERVER"
                smtpServer.EnableSsl = False
                mail = New MailMessage()
                mail.From = New MailAddress("FromAddress@Domain.com")
                mail.To.Add("ToAddress@Domain.com")
                mail.Subject = "Duplicate File Created"
                mail.Body = "A user has just created a duplicate file, the details are as follows..." & vbCrLf & vbCrLf & "New File Path: " & vbTab & CreatedFileFullPath & " " & vbCrLf & "Created By: " & vbTab & Owner & vbCrLf & vbCrLf & vbCrLf & "ORIGINAL FILE DETAILS" & vbCrLf & vbCrLf & "Full Path: " & vbTab & CheckFullPath & vbCrLf & "Owner: " & vbTab & CheckOwner
                smtpServer.Send(mail)
            End If
        End While

        SQLdr.Close()
        SqlConn2.Close()

        Dim SqlStr As String
        Dim ConnString As String
        ConnString = "Data Source=SERVER\DB; UID=DOMAIN\USER; Password=MYPASS; Integrated Security=True"
        SqlStr = "INSERT into [Master].[dbo].[FileInfo]([Date_Time], [Owner], [Full_Path], [Old_Path], [File_Name], [MD5_Checksum], [Action]) VALUES('" & Date_Time & "', '" & Owner & "', '" & CreatedFileFullPath & "', 'N/A', '" & FileName & "', '" & MD5Hash & "', 'CREATED')"
        Dim SqlConn As New SqlConnection()
        Dim SqlCmd As New SqlCommand()
        SqlConn.ConnectionString = ConnString
        SqlConn.Open()
        SqlCmd.Connection = SqlConn
        SqlCmd.CommandText = SqlStr
        SqlCmd.ExecuteNonQuery()
        SqlConn.Close()
    End Sub







    Public Function GetFileOwner(ByRef strFile As String) As String

        Try
            Dim wmiFileSecurity As New ManagementObject("Win32_LogicalFileSecuritySetting.path='" & strFile & "'")
            Dim propSecurity As ManagementBaseObject
            Dim descriptorSecurity As ManagementBaseObject
            Dim objectOwner As ManagementBaseObject

            propSecurity = wmiFileSecurity.InvokeMethod("GetSecurityDescriptor", Nothing, Nothing)
            descriptorSecurity = CType(propSecurity.Properties("Descriptor").Value(), ManagementBaseObject)
            objectOwner = CType(descriptorSecurity.Properties("Owner").Value, ManagementBaseObject)
            Return (objectOwner.Properties("Domain").Value.ToString & "\" & _
               objectOwner.Properties("Name").Value.ToString)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Function




    Private Function RenamedgetFileMd5(ByVal filePath As String) As String
        System.Threading.Thread.Sleep(1000)
        Dim File() As Byte = System.IO.File.ReadAllBytes(filePath)
        Dim Md5 As New MD5CryptoServiceProvider()
        Dim byteHash() As Byte = Md5.ComputeHash(File)
        Return Convert.ToBase64String(byteHash)
    End Function


End Class

Open in new window

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
The FileSystemWatcher is not a fool proof way of monitoring files as it has many known issues.

Anyway, you can move all the code to a function and then in the file created event, just create a new thread and pass it the path of the file and it would then handle the rest. But this could get ugly as you dont want to create 10000 threads in your app so I would suggest you use a Queue and a single thread which keeps running and processing the files from the queue.

Examples

http://vb.net-informations.com/collections/vb.net_Queue.htm

http://visualbasic.about.com/od/usingvbnet/a/NewCalc01.htm
0
 

Author Comment

by:Graham2510
Comment Utility
Hi CodeCruiser,

Thanks for the suggestion. I've read the two links that you supplied but unfortunately, as my programming skills are at a novice level, i'm not 100% sure on how to implement a queue into my app. Would you be able to offer some assistance?


Thanks.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
You can declare a queue at the class level. Then in the FileCreated event, just add the file name to the queue, for example

    Private Sub FileCreated(ByVal Sender As Object, ByVal MonitoredFile As FileSystemEventArgs)
     QueueName.Enqueue(MonitoredFile.Name)
    End Sub

Then in the thread method, you can use

FileToProcess = QueueName.DeQueue()
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Graham2510
Comment Utility
Hi,

Sorry if this question seems dumb, it's Friday afternoon and my brain doesn't work too well at the best of times!

What do you mean by thread method?
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Leave it till Monday and read about MultiThreading on monday morning.
0
 

Author Comment

by:Graham2510
Comment Utility
Hi9 CodeCruiser,

I've finally managed to get back onto this project and with a clear head I worked out the queueing. Everything runs smoothly now.

Thanks.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Glad to help :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now