I've always liked Theme Park trains myself.
That Dollywood Train is a good ride, used to be even better with the shootout mentioned in earlier posts in this thread. The Silver Dollar City Railroad was always a fave on childhood visits, but I haven't been on it in many moons. They used to do the holdup thing on it too.
I've always loved the Disney train rides, something cool about them and that DL one has the great dinosaurs as mentioned earlier.
A train ride I miss from my childhood is the old Opryland Trains. They were a nice ride through a much-missed park.
Busch Gardens Tampa's TV Railroad was always a fave when I used to frequent the park too. Great views of animals and cool trains.