1

If i have a db table in sql server with the following structure :

ID        int
emp_photo image

and i have unique photos in a folder ,each photo named with employee name .

How to get the following result by comparing the image in db by the image in file system :

ID |emp_name

1    john
2    Michael
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • How would comparing the image in the db with the image in the file system give you that result? Are you trying to get a list of all people whose image exists on the file system? – mikeyq6 Mar 30 '16 at 12:38
  • What is the type of the image you get from db ? can you convert it to bitmap ? – Felix D. Mar 30 '16 at 12:38
  • @mikeyq6 : i want to join the table in db with file system through photos to get the employee name of each id – Anyname Donotcare Mar 30 '16 at 12:40
  • @FeDe : it's `image sql server db type` – Anyname Donotcare Mar 30 '16 at 12:41
  • So the image in your database seems to be binary. You could read the file from disk and get a stream and then get bytes and compare those. Another solution is to save the dbImage temporary and compare pixels ..?! – Felix D. Mar 30 '16 at 12:52
  • I'm quite unclear on what you're asking. Is your database `image` column really of type `int`? What does that `int` represent? – Matt Gibson Mar 30 '16 at 13:21
  • @MattGibson : i have a table with two columns :ID of type `INT` and photo_emp of type `Image` . – Anyname Donotcare Mar 30 '16 at 13:23
  • 1
    And do you think the image data in the column will be *exactly* the same as the contents of a file when the images match? If so, I would probably generate a simple hash of the data in both systems and compare it. – Matt Gibson Mar 30 '16 at 13:25
  • @MattGibson : yea they are exactly the same – Anyname Donotcare Mar 30 '16 at 13:59
  • So, for an image you know to match, are the values of (from the command line): `certUtil -hashfile "example employee image file.jpg" SHA1` and, from the database: `SELECT HASHBYTES('SHA1', CAST(image AS VARBINARY(MAX))) FROM your_image_table WHERE emp_photo = whatever;` the same? – Matt Gibson Mar 30 '16 at 14:36
  • @MattGibson : yea , the same image in db exist in hard disk – Anyname Donotcare Mar 30 '16 at 14:45
  • On the C# side, here's how to compute an MD5 hash of a file. http://stackoverflow.com/questions/10520048/calculate-md5-checksum-for-a-file or http://stackoverflow.com/questions/1993903/how-do-i-do-a-sha1-file-checksum-in-c for SHA1 Also, just FYI `IMAGE` is deprecated: https://msdn.microsoft.com/en-AU/library/ms187993.aspx – Liesel Mar 31 '16 at 07:39
  • Yeah, so if this is a one-off, I'd just generate a file of the MD5s of the image files along with their file names, import that to a table in SQL Server and do a join on it. If it's an ongoing need (why would it be? Surely you'd just import the names along with the images!) then yes, perhaps a C# program to look up the image hashes in the DB would be the best approach (maybe add a computed column for the image hash and index it if there's a lot of images.) – Matt Gibson Mar 31 '16 at 08:24

0 Answers0