I agree that it seems the polite thing to do to use Canadian currency while in Canada. I have been traveling to Niagara Falls for 40 years and we would always exchange our money immediately after crossing over the Peace Bridge. There used to be a currency exchange/tourist info building that was, I believe, run by the government. It was extremely convenient. However, that closed down several years ago and I no longer exchange my currency. I normally use my debit/credit card for all transactions and thus avoid having to use paper/coin currency.
Occasionally, for smaller transactions I will use American currency and have never encountered an angry response for doing so.
If you plan on using mostly cash, I recommend that you do exchange your currency prior to going into Canada or shortly after arriving.
Lol, I understand your son's excitement about seeing "different" money. Growing up we always enjoyed going into the currency exchange after crossing over the Peace Bride. It always meant that we were in Canada and our vacation had begun. Also having a different country's money in our pockets was always exciting. And in those days, the exchange rate was quite high and I remember saving up $100 for the trip and after the exchange, I'd have almost $150. I felt so rich :-)
Not so anymore, the exchange rate is pretty much even. In fact, I just checked and today's rate is $1.00 American is $1.02 Canadian.