Actually the upper and lower Niagra are very popular dive sites.
from Dans dive shop in St.Catherines (local shop that does guided dives of the Niagra)..
Niagara River:
"Many different Drift Dive sites are located in the Mighty upper Niagara River, including favourites such as: Frenchman's Creek, The Trainbridge, Thompson's Hole and for the thrill seeks a dive through the Peace Bridge, one of the Fastest dive in the area! In the Lower River Navy Hall makes for a nice drift with a little more depth 65ft max. A popular area for our Weekly Wednesday Night Dives througout the season. We also have "Hells Hole" which is a very fast and deadly dive only for the experienced technical diver."
Now hells hole from what I gather tops out somewhere in the neighbourhood of 200ft and I've hear currents of well over 10+knots. Not something I'd do anytime soon, but Thomspns hole is a sweet little hole that produces a nice wirlpool that you drift into then get sucked down to the bottom of the hole (around 60-70ft) then catch the exit current.
The vis really varies depending on weather but can get pretty good (50-60+ft).
THe main attacctions are basically ripping current, cars, small boats, and crap loads of very large fish.
Here is a video of a dive a Thomsons hole for an idea of what it's like.
http://www.youtube.com/watch?v=_0YCP73FivY&mode=related&search=