Detect File Then Write to DB

[Anjari]

I have a folder with 13000 .jpg images - I need to loop through these images and write a value back to my database.

For Example image 12345.jpg would match my "ProductID" field in my Product table.

In a nutshell read image 12345.jpg - locate ProductID "12345" and mark the "HasImage" field "Yes"

Any help would be wonderful!

Thanks!

Anjari

[brin351]

I'm not sure if you are working with a win program or website or nether but in VB.NET you could do something like below but there will be 13000 update statements run. There may be other ways if your more specific

Imports System.IO

sub.....
        Dim di As New DirectoryInfo("folder path")
        Dim fi As FileInfo
        For Each fi In di.GetFiles
            Dim id As String = fi.Name.TrimEnd(".jpg")
            'you have located an image so update database here.....
        Next
end sub....

[Anjari]

thanks for the response, I have server access so I can run a vb.net app directly on the server.

Thanks!

[szlamany]

Your VB program should create a one-column text file of all the id's.

Use BULK INSERT to load them into a STAGING TABLE - a temp table for instance.

Then update your production database all in one shot!

Update ProductionTable Set HasImage=1
     From #TempTable TT
     Left Join ProductionTable PT on PT.ProductId=TT.ProductId

[Anjari]

Im still not getting it... I guess im not following the concept

Anjari

[szlamany]

Is the backend database SQL SERVER?

[Anjari]

Yes, sorry SQL 2005 server... sorry forgot to mention this helpful bit of info

Anjari

[szlamany]

ok - my feeling is that doing 16000 updates is not a quick and easy way to change 16000 rows.

So instead I suggested a more straight forward approach - so let's review it in steps.

You have to have a VB program to extract the "product id's" from the JPG filenames in the folder - I do not think that you can avoid this.

But you do not have to UPDATE as you loop through the filenames in the folder.

Instead - create a .TXT text file - with each product id on a new line

00001
00123
00999
12345

and so on - you will have 16000 lines in this .TXT file. Let's call it Prods.Txt for example.

Now the fastest way to get this info into SQL is to use BULK INSERT. And you can use a TEMPORARY TABLE to load this into (or create a permanent table if you want - really doesn't matter).

Create Table #TempLoad (ProdId varchar(5))
Bulk Insert #TempLoad From "C:\Some Folder\Prods.Txt"

The really nice thing about this method so far is it's really fast (creating the .TXT file with VB should be instant) - and the BULK INSERT is a special command in SQL that is not "logged" to the transaction log.

Now once you get that #TempLoad table created and populated you can UPDATE the production table in one UPDATE statement - that's the way to use SQL properly.

Update ProductionTable Set HasImage=1
     From #TempLoad TT
     Left Join ProductionTable PT on PT.ProductId=TT.ProductId

Does this make sense now??