I went recently and stayed at Hotel Charles Lindbergh. As a unique experience I can really recommend it, at least for one night, and I also think the price is pretty reasonable for what you get if you're a party of two. It includes two days of entrance to the park, so if you only have one night to stay you can show up in the morning of the first day, drop your bags (or, in our case, simply put them in the room as it was already available), and enjoy two full days on park, which should be more than enough to see and do everything. Looking out of your room window to a huge roller coaster a couple meters away is an incredible experience, and spending the night within the general themed area of Rookburgh is spectacular as well. The exclusive bar was great, having a drink and looking out onto the coaster and the surrounding area at night was really incredible.
The major downsides for me were the included dinner and the room itself. The dinner had great food but the service was terrible, it took almost 45 minutes for someone to come take our order, and we were seated 15 minutes after our reserved time. The staff that were on did a great job, so I blame the park for this as it seemed like the restaurant simply did not have enough people working to handle the number of people dining. It wasn't the best way to end the evening. As for the room, we went in the hottest part of summer and it was a little stuffy to the point that I had trouble sleeping. In the colder months I imagine this isn't as much of an issue.
Overall I would highly recommend doing it... once!