We stayed at "Canyonlands RV Park" in town, mainly for the convenient location. Near City Market for groceries, near Moab Brewery for good food, etc. No particular ambience but full hookups.
For maximum atmosphere in that region, it's definitely worth it to live without full hookups for at least a couple of days at Devils Garden ...at the far end of Arches, it is beautiful, set within the red rocks, but no hookups. Some sites are large.
We have also stayed at Dead Horse Point State Park and love it...electric, dump station, but they don't have water so you need to fill your tank before going. Incredible views there. I saw a couple of large class As there.