Depends on what your requirements are. If you need to store data even at rest then encryption in SQL is the only way to do that. Otherwise anyone with DB access can read the unencrypted data. But it gets a lot harder as you'll also need to ensure that only specific accounts can decrypt the data otherwise, again, anyone with DB access can read it. Of course this level of security is really only needed for sensitive data that you have no choice but to store for whatever business reasons such as SSNs or CC numbers. Of course the best option is to never store any of this and then encryption isn't needed.
If you need to share data between a client and a server (whatever that means to you) then C# is probably the better route. For example if you're communicating between two machines then HTTPS (already encrypted) is expected. For non-HTTPS then you should encrypt on one side and decrypt on the other, if needed.
As for the mot secure encryption then the general recommendation is asymmetric with the largest key both sides support. Refer to this helpful link in MSDN. You'll want the most secure algorithm with the largest key that you can manage. Of course this is expensive so if you don't need as much security then symmetric is easier but more vulnerable.
.NET docs have a good summary of all this to read.