Normally at my local hospital, if they believe you are a suicide risk they have security guards there trained specifically for people in crisis, so I have only dealt with the police once and that was when I was 5150'd. The guards have always been very nice; bringing me heated blankets when I need them as long as my parents or a nurse was there to watch in the meantime (the paper clothes that the hospital makes you wear are not exactly warm).
My single experience with the police was when I was fifteen. I called a crisis line, but hadn't actually harmed myself in any way, and the woman on the line managed to talk me into giving the phone to my dad (convinced me that I was wrong and my parents wouldn't be mad, but I was right :P). Of course, they had to take me to the hospital. They weren't going to do anything but make me see my pdoc asap but I refused to sign a safety contract because I knew I could not and would not keep it. The second I said I couldn't sign that, they 5150'd me. I had to wait about 12 hours with no food, no drink, wasn't allowed to go to the bathroom without supervision but wasn't provided with a female guard. My mom was pissed and not very sympathetic, so she wasn't helpful. The guard was very nice, however. He did what he could to help. I really believe that, despite the fact that the situation I was in was pretty shitty.
The police showed up around 5 am, and I finally got to leave. They put me in a van; I wasn't cuffed or anything, though I really should've been. I'd been cooperative up until that point, so it's really not their fault. Anyhow, I went batshit in the van, kept trying to break things and break out of the van because I did not want to go to hospital. The officer was very nice and calm about the whole thing; she probably knew I couldn't exactly bust out of a damn armored van. Once we got to the hospital (an hour's drive), I immediately stopped being violent-- I had no desire to hurt anyone but myself. The policewoman was very kind as she escorted me to the check in at the acute crisis wing.
Honestly, what was really traumatic was the hospital stay itself; I had an excellent experience with the police officer who transported me. Of course, as my father is a cop with tenure who's trained half the officers, I feel like I'm unlikely to have a bad experience in my town, beyond cops being dickish-- I've dealt with a lot of asshole cops.