Stars with masses between about 0.7 and about 8 Solar masses become Red Giants before becoming White Dwarfs, without a supernova taking place.
Below that limit we have totally convective Red Dwarfs, that will use up *all* their Hydrogen. But those stars are not massive enough to start Helium fusion in their cores. They become White Dwarfs directly.
Star above the upper limit can fuse Helium but also heavier stuff, all the way to Iron. The point is that an inert iron core can not support the outer layers, and the result is a supernova.
The CNO cycle is only another way to fuse hydrogen, it doesn't change the final fate of the star.
Finally the Sun's category is those of the low mass stars, up to 3 Solar masses, at which point fusion beyond Helium becomes possible.
Edit: I saw in another question that Phoenixshade doesn't agree. The two sorts of White Dwarfs (with or without Red Giant stage) don't start with identical temperatures. The Red Giants shed their outer layers and start at a hotter temperature. What characterizes White Dwarfs is the nature of the equilibrium, rather than the temperature. The counterforce to gravity is given by the electron degeneracy of the core.
Edit2: Currently the Sun is in equilibrium. The outward pressure produced because Hydrogen is fusing into Helium exactly compensates the compressing force of gravity. When the core Hydrogen has burned, gravity wins once more and the core compresses until T and P are high enough for He fusion to start (the details are a little bit more complicated than this and depend on the mass of the star) or Hydrogen fusion to restart in a shell outside the still inert core.
In the case of the Sun, when Helium fusion starts, pressure wins out and the Sun expands into a Red Giant, where a new equilibrium is reached.