А як на вашу він повинен його скасувати? ви посилаєте sql-сервера просто текст по суті, він його парсит і виконує якщо запит коректний. інакше повертається помилка яка видається як Exception в коді.
BeginExecuteReader і EndExecuteReader потрібні для асинхронних запитів.
Транзакції в будь-якому випадку йде на стороні Sql. net її просто відкриває і далі або коммітов або відкочується на ваш розсуд.
якщо ви відкрили транзакцію, то при визначеній рівні ізоляції запити на вибірку включатимуть ті зміни, які ви вносите в транзакції. на скільки я пам'ятаю є рівень коли запити на вибірки не будут включати зміни. Почитайте в документації про це.
Cancel намагається скасувати запит і намагається передати це на SQLServer. Якщо запит тривалий і складається з окремих кроків, і ці кроки ще на почали виконуватися, то команда перерветься. Якщо йде видача, то вона теж може урватися.
Вам же, якщо ви перервалися, не обов'язково продовжувати зчитувати - переривається, більше не викликайте Read (), закривайте reader і сповіщає користувача. При використанні BeginExecuteReader ви зможете простіше організувати процес очікування і переривання, але трохи складніше процес отримання даних.
В одному проекті я поступав дещо по-іншому. Я швидко отримував ідентифікатори об'єктів, релевантних запиту, а потім спокійно в окремому потоці начитував рядки з даними (багато повільних колонок). Процес цей контролювався мною і міг перерватися в будь-який момент. Як бонус виходить правильний прогрессбар.