As the other poster pointed out, MythTV works quite well and the hauppage cards are quite good. The pcHDTV card from http://www.pchdtv.com/ also works well from my experience, I've used both. But Windows Media Center also works surprisingly well. I switched to that so I could move the PC to another room and only have my Xbox 360 hooked up to my TV (actually, I had a projector, but that's beside the point) and stream the video to that.
MCE is a little less useful than MythTv because it adds DRM to your recordings (though I hear its not too hard to remove), but if all you're planning on doing is watching it and then deleting it, then it works beautifully. It has the added benefit of it "just works" with the Xbox 360 and its remote (My experience with getting a remote to work with MythTV was less than pleasant).
Of course, I recently broke down and got DirectTV because (ironically) when the digital transition finally happened, my reception of the digital channels (in my apartment) suddenly became less reliable.