There may be a number of reasons, but the one that stands out to me is found in the words of Psalm 51:11: "Do not cast me away from Your presence and do not take Your Holy Spirit from me." That prayer says a lot about David's heart. He wasn't focused on the devastating consequences of his moral collapse--he worried most about his standing with God. He loved God above all else.
David's psalms make that clear. His relationship with God was not about hollow platitudes tossed like a bone at God; it was raw, deep, and authentic. And it meant more to him than anything.
Personally, I don't care that much for dutiful acts done on my behalf. If somebody had to do it, it usually just makes me feel a little guilty. But I really appreciate spontaneous acts of love or kindness, no matter how small. I also like it when people ask me for help, because it means that they decided that I could and would help them.
God has emotions just like we do. He wants us to love him. He wants us to trust him with our innermost struggles. He wants us to ask him to meet our needs, with the expectation that he can and will help. And he wants us to obey, not out of a sense of duty, but to please him. He wants all this because he loves us.
In spite of all his flaws, David was God's friend. And God wants each of us to be his friend. (John 15:15)