It sounds dissonant because it is ! It is, and has been, for hundreds of years,the means of moving to the subdominant chord. Containing a most unstable tritone, as in E and Bb, in your example in Cmaj.,it needs to resolve to an F chord. Don't worry that Bb is not in the key of C, as it's in F, where the music is going, albeit briefly. Your idea of using a major 7th is fine, but it doesn't push towards the subdominant in any way. Think along slightly different lines - the chord that pushes back to C is actually G dom.7th, which contains a tritone, AND an F note, which isn't in the Gmaj.scale.
You'll find the #5th does the same sort of job (as in C+ - C,E,G# to move to F); again G# is not in the key. Just because you're in the key of Cmaj., doesn't mean you can ONLY use the 'white notes'.
The answer is exactly what I wrote in my answer to 'What does dominant mean in music'.
The dominant seventh chord has an extra note to the triad of 1,3 and 5. The sequence is followed, so it's a 7. That's where confusion sets in. It's not the 7th of the scale that the dominant chord would be the key of. In other words, G7, as a dominant of C, would have F natural as the 7th part of it. Not F#, which is the 7th of the G scale.
That's because G7 - a.k.a. G dominant 7th - is a chord from the C key, not its own G key. I know that sounds adaft, as we find G7 in lots of songs that are in G. However, most times, the following chord is C (or Cm), so the G7 needs the 7th part of it to be from key C rather than its own key,G.
Musically, what's happening is the 3 is a semitone below the tonic root, and the 7 is a semitone above the tonic 3. To resolve, they both move a semitone each, the smallest change possible, and everything sounds fine again. The tritone - very unstable sounding - resolves to a nice major (or minor) 3rd interval.
To put it in a simpler form - the dominant 7th is built off the dominant note of the scale, note 5. Then the next 3 notes stack, as in the 7th, 9th and 11th of the existing scale. Thus, in C, 5=G, 7=B, 9=D and 11=F. The dominant chord of C, G7. Nothing at all to do with major 7th notes. Except that note just happens to be part of the chord!!