SQL Server: Fast way to load a directory tree (name, date modified)

Question

(I looked for an answer, but nothing really solved my issue so far.)

I want to keep a table up to date with a file directory tree within Microsoft SQL Server 2005. I do the norm now:

insert into #resultTable (Path)
exec master.dbo.xp_cmdshell 'dir/s/b G:\FileLibrary'

but this takes about 4 1/2 minutes to load each time (not to mention I don't know how to grab the modified date yet). Yes it's a network drive, but running it locally takes 1 minute 16 seconds, and that's not the only directory I want to check.

Is there something out there I can load in to SQL Server 2005 as a DLL or maybe C# code I can compile to provide a fast scan of files changed since a period in time (including daylight savings changes)? The files are stored on the same server as SQL Server runs.

Answer 1

There are many examples online of running managed code from inside of SQL Server. This is one example.

With that, you just need to have the C# code monitor the directory for changes. Maybe you can use these articles as starting points: Here and Here.

There are many other links too that a quick Google search will uncover.