We did the Dolphin swim on Grand Cayman, but not the Stingray City. We also added on the Turtle Farm, which is across the street. That was nice too. It was short, but worth it to us as you got to see the big turtles close up and hold the smaller, 1 ft size, ones. Plus they had 5 baby turtles too.
As for the Dolphins, we did the "extreme" swim, which included the dolphin "encounter" shaking their hands, giving/receiving dolphin kisses, etc., the dolphin "swim" where you hold onto their flippers and they swim backwards with you holding on, and the "extreme swim" which is you holding a boogie board and the dolphin pushes you by your feet. This one definitely gives you the most thrilling speed, but the regular swim was the most "close up" experience.
As for the water, in our case it was a man-made lagoon, next to the ocean. Water was plenty clear.
As for the cost of the photos. Yes, they are extremely expensive, and no, you can't take your own pictures. They are very clear about that. I think it was $200 for our family of four to have their pictures. I can't tell you if that is worth it to you, but I can give you some advice. Decide ahead of time if you are going to buy the pictures or not, and stick to that. If you decide to buy them, great. Start setting aside money for it now just like all of your other vacation expenses. If you don't want to buy them, great too. Just don't even go over to the photo area and look at them. I saw some families really angsting over their decision there at the counter and the sales people trying to make the sale. Who needs that? Decide ahead of time. You'll be happier.