The best Water types in SoulSilver are Gyarados and Feraligatr. This is because both have good matchups into important Trainers, can OHKO opponents quickly, and are fairly easy to train.

Gyarados can be captured at Level 50 on Route 43, effectively giving you the only Pokemon you'll need to beat the game. sumwun explains it better than me:
When you surf on this route, it is not possible to find anything between levels 26 and 49, but it is possible to find a level 50 Magikarp. So you can put any Pokemon between levels 26 and 50 in your first slot and use some repels, and it should be pretty easy to find this Magikarp in 3 or 4 minutes. Then use one rare candy, and you have a level 51 Gyarados before the fifth gym. It is very possible that the specific combination of Typhlosion, Gyarados, Heracross, Tauros, Krabby, and Farfetch'd is not the best team in HGSS, but I don't think anyone can seriously argue that any other Pokemon in HGSS is better than this level 51 Gyarados.
Strength is Gyarados's strongest move before waterfall, and it remains useful for covering stuff that resists waterfall. Surf covers stuff that resists strength. Waterfall is Gyarados's strongest STAB move. Dragon dance raises Gyarados's game-breaking physical attack to even more game-breaking amounts. You can use ice fang if you think strength is not doing enough damage against grass and dragon.
Gyarados can solo any trainer battle between Route 43 and Lance using only strength, surf, waterfall, and dragon dance. It doesn't need ice fang, items, or EVs. It doesn't even need to reach level 52. I included the extra unnecessary stuff just to optimize this moveset a little bit. I'd say the most difficult battles (ie. the ones that require the most luck to solo) are Will and Karen. Both of them lead with confuse ray users, so it's dangerous to use more than one dragon dance against them. Both have another Pokemon that can survive any move after one dragon dance and then use hypnosis or stun spore.
Alternatively, you can use the classic Gyarados at the Lake of Rage, but it comes later and at a lower level.
Availability: Mid-game (Lake of Rage, requires Surf, level 30).
Typing: Water / Flying gives Gyarados all-around neutral matchups, save for Lance and Clair who resist its Water attacks.
Stats: Gyarados has great stats save for a mediocre Special Attack, though Choice Specs can help fix it up.
Movepool: Gyarados starts off with Ice Fang and can be taught Thunder and Surf via TMs, the latter of which should be deleted through the move deleter and replaced with Waterfall. Dragon Dance at level 44 helps it sweep the end-game opponents. Alternatively, Gyarados can be used as an HM slave, as it learns all three Water HMs and Strength.
Major Battles: Gyarados performs reasonably well against Gyms 5-7 and tends to have a favorable matchup against the Team Rocket Executives. At the Pokemon League, Gyarados can set up as many Dragon Dances as needed and sweep every single opponent there, including Lance, though it may occassionally need one healing item if setting up for a long time.
Additional Comments: While Gyarados doesn't come very early, it is nevertheless one of the best Pokemon to use, as it has a reasonable Gym performance and is the only Pokemon that can sweep the entre Elite Four and Lance. Magikarp can also be encountered at level 50 on Route 43 by repel tricking with a level 26 or higher Pokemon, although it will require a Rare Candy to evolve quickly, a Heart Scale to relearn Dragon Dance, and potentially Proteins. Magikarp can be caught as early as Route 32 via Old Rod, although the effort to evolve it makes it a worse option, though it performs well against Whitney and Morty after evolving.

Availability: Early-game (New Bark Town, level 5)
Typing: Water typing is all-around neutral, save for being resisted by Clair, Lance, and 2/3 of Pryce's team.
Stats: Totodile is a tank, meaning it has good Attack and Defense and decent HP and Special Defense. Overall, it's an all arounder outside of Attack and Defense.
Movepool: Totodile has a versatile movepool via level up and TMs, consisting of Water Gun at level 6, Bite at level 13, Ice Fang at level 21, Crunch at level 32 and Waterfall, Strength, Rock Tomb, and Earthquake via TM/HM. This enables Totodile to hit everything it needs to neutrally or super effectively.
Major Battles: Totodile can handle Falkner, Bugsy, Whitney with Rage, and Morty before starting to have issues in the mid-game to early late-game. Chuck's Poliwrath, Pryce, Clair, and Lance all give it issues.
Additional Comments: Totodile is one of the best Waters in the game, only really struggling in the mid-game and the beginning of the late-game, a trait shared with other Water-types.
There are other good Water types, like Vaporeon, Lapras, and Tentacruel, but in most cases Gyarados and Feraligatr are more efficient choices.
What is a good in-game team for HeartGold and SoulSilver? (Pokemon Database)
HGSS In-Game Writeups (Smogon)