I'd like to be able to watch TV in a window while working on other projects... Is the current state of tuner hardware to the point where this would be possible? What are the most popular cards out there?
Yes, it's possible to watch TV in a window. All modern tuners and their associated software should be capable of this, whether it's a PCI card or a USB device. Needless to say, you will need a reasonably modern PC to avoid any performance issues or jerkiness. If you have Windows Media Centre, e.g. on Vista Home Premium then you can use that as your viewing software.
I've got on well with Hauppauge devices in the past.
I use a "dedicated" home theater PC running Vista Media Center with a Hauppagge WinTV HVR-1800, and an over-the-air antenna to watch HDTV. Sometimes I'll watch in Windowed mode while browsing on a second monitor.
AV Science Forum is a great resource on home theater PCs and AV hobby in general.
Maybe hulu fits your needs, you don't need any fancy tuner hardware.
If you really do want to build your own computer-TV/DVR then you'll need an ATSC/ClearQAM/NTSC tuner device. Hauppauge makes some of the best under their "WinTV" line, though there are plenty of other options. Generally speaking most tuner cards come bundled with software that will let you watch TV on your computer. If you want something different or desire additional functionality there are lots of other options such as Windows Media Center, MythTV, etc.
Except for the resolution differences (the quality goes down fast when ran at full screen), TV tuners are great for watching TV on PC.
Here's two links about someone who got his gaming consoles working on his PC (where he uses to take screenshots of the games) and the quality of the TV tuner he brought:
You might also want to consider getting a Dreambox
My previous roommate had one of these babies.
Once you get it up and running they are very nice!