Yes SH3 will be a steam game, you will need to be online to activate your copy but after that you're free to play offline so your dial-up connection should not pose a problem at all
If you start up steam in offline mode you are not forced to download updates or get bogged down with delays, the game should boot up like any non steam game.