If you use the Windows Image Acquisition Library, you'll see events there for capturing camera new picture events. I had a similar requirement and wrote a test rig; we went down to the local camera store and tried every camera they had. The only cameras we could find that supported this functionality were the Nikon D-series cameras.
We found that with most cameras, you can't even take a picture when they are plugged in. When you plug them in to the USB port, most cameras will switch into a mode where the only thing they'll do is transfer data. The quick way to find out if a camera will work at all is to plug it into a PC, then try to snap a picture. If it lets you do that you have a chance. It also needs to support PTP.