When should I be using Odbc, OleDb, SQLClient? What are the trade-offs

System.Data.SQLClient

Connects to SQL Server 2000 and later only, but you will get optimal performance when connecting to those databases.

System.Data.OledbClient

Connects to SQL 6.5

OLEDBClient gives you ability to connect to other database like ORACLE or Access. But for working with SQL Server you will get better performance using SQLClient.

Note: For connecting to ORACLE, Microsoft also has ORACLEClient.

System.Data.ODBCClient

Connects to legacy databases only, using ODBC drivers. (E.g. MS Access 97.)

Original source

You want to use the SQL Server driver. I understand what you are trying to do but the way you would accomplish supporting multiple databases is by inserting another layer of abstraction. You can do this many ways. But you put the database specific code at the edge of your class hierarchy. Therefore, each class can get the benefits of database specific functionality but the higher level callers don't know or care what database is being used underneath. As far as ORMs, I prefer LLBLGen, but this is just my preference.

Also, just to clarify, LINQ is not specific to SQL Server. That is LINQ-to-SQL. LINQ is a querying technology that you can use in LINQ-to-SQL, LINQ-to-Entities, LINQ-to-objects, and even LLBLGen supports LINQ.

No matter whether you use SQLClient or Odbc for now, if you use stored procedures or other database-specific features, you'll have to rewrite those if you change database engines.