How to catch a specific SqlException error?

If you want list of error messages met in Sql server, you can see with

SELECT *
FROM master.dbo.sysmessages

The SqlException has a Number property that you can check. For duplicate error the number is 2601.

catch (SqlException e)
{
   switch (e.Number)
   {
      case 2601:
         // Do something.
         break;
      default:
         throw;
   }
 }

To get a list of all SQL errors from you server, try this:

 SELECT * FROM sysmessages

Update

This can now be simplified in C# 6.0

catch (SqlException e) when (e.Number == 2601)
{
   // Do something.
}

It is better to use error codes, you don't have to parse.

try
{
}
catch (SqlException exception)
{
    if (exception.Number == 208)
    {

    }
    else
        throw;
}

How to find out that 208 should be used:

select message_id
from sys.messages
where text like 'Invalid object name%'

Sort of, kind of. See Cause and Resolution of Database Engine Errors

class SqllErrorNumbers
{ 
   public const int BadObject = 208;
   public const int DupKey = 2627;
}

try
{
   ...
}
catch(SqlException sex)
{
   foreach(SqlErrorCode err in sex.Errors)
   {
      switch (err.Number)
      {
      case SqlErrorNumber.BadObject:...
      case SqllErrorNumbers.DupKey: ...
      }
   }
}

The problem though is that a good DAL layer would us TRY/CATCH inside the T-SQL (stored procedures), with a pattern like Exception handling and nested transactions. Alas a T-SQL TRY/CATCH block cannot raise the original error code, will have to raise a new error, with code above 50000. This makes client side handling a problem. In the next version of SQL Server there is a new THROW construct that allow to re-raise the original exception from T-SQL catch blocks.