The best practice is to catch specific exception first and then move on to more generic ones.
Exception Handling (C# Programming Guide)
Multiple catch blocks with different exception filters can be chained
together. The catch blocks are evaluated from top to bottom in your
code, but only one catch block is executed for each exception that is
thrown. The first catch block that specifies the exact type or a base
class of the thrown exception is executed. If no catch block specifies
a matching exception filter, a catch block that does not have a filter
is selected, if one is present in the statement. It is important to
position catch blocks with the most specific (that is, the most
derived) exception types first.
For your question:
Why is it adviced most of the time that we should not trap errors like
"Exception" but trap errors that we expect as developers.
An example would be to catch NullReferenceException. Its never a better practice to catch NullReferenceException, instead one should always check for object being null before using its instance members. For example in case of string.
string str = null;
try
{
Console.WriteLine(str.Length)
}
catch(NullReferenceException ne)
{
//exception handling.
}
instead a check should be in place for checking against null.
if(str != null)
Console.WriteLine(str.Length);
EDIT:
I think I got the question wrong, If you are asking which exception should be caught and which shouldn't then IMO, those exceptions which can be dealt with should be caught and rest should be left in the library so they can bubble up to upper layer where appropriate handling would be done. An example would be Violation of Primary key constraint. If the application is taking input(including primary key) from the user and that date is being inserted into the database, then that exception can be caught and a message can be shown to user "Record already exists" and then let the user enter some different value.
But if the exception is related to the foreign key constraint (e.g. Some value from the dropdown list is considered invalid foreign key) then that exception should bubble up and a generic exception handler should log it in appropriate place.
For example in ASP.Net applications, these exception can be logged in Application_Error event and a general error page can be shown to the user.
EDIT 2:
For the OP's comment:
if at a low level if there would be a performance degeradation in
catching a generic error inspite of knowing if the error is
sqlexception
Even if there is going to be any performance difference it should be negligible. But Catch the specific exception, if you know that the exception is going to be SqlException
then catch that.