The genius architect, who died 300 years ago, brought a theatrical flourish to some of Britain’s grandest buildings, from Blenheim Palace to Castle Howard. His dramatic life and work is celebrated in a new exhibition