Actually, I use a water cooled system in my "regular" computer. It's extremely quiet (except for my power supply fan, which I need to change). I had been using one (forgot who made it) for a couple of years, but it was extremely noisy (inefficient heat exchanger and huge fans) and it was a do-it-yourself system that required some case mods... I wouldn't recommend that kind unless you know what you are doing.
The one I have now is a Koolance (here's a link to the model I have:
http://www.koolance.com/products/product.html?code=CCS-A04&category_id=2 ). It came, case and cooling system, all put together. The only thing I had to do was install the motherboard and CPU, cut some tubing, attach the tubing to the heatsink, grease the heatsink and install it on my CPU... and add the coolant (which it came with). They have options for cooling video cards and hard drives that connect to the same system. My only complaint with this case is that it doesn't have USB ports on the front.
You do have to be very careful when working with this kind of system though. Leak checking is an absolute must before connecting power to the motherboard. They supply a jumper that you put on the power supply that allows the water pump to run (without supplying power to everything else).
The unit I have has a temperature sensor and fan controller that will increase the speed (and the noise) of the cooling fans if the CPU starts to get hot, but under normal use, it rarely happens.
Either Koolance or Coolermaster (or maybe both) have a stand alone unit you can add to your existing computer that looks interesting (you run the tubing through an available expansion slot cover). I may, if needed, get one of these for my HTPC. But...
My HTPC is currently on hold. I may be moving to a new job soon and I don't want to get into ordering parts until I know what is happening for sure. I'll be sure, though, to post my experiences when I do get around to building it.