ok, one example is Entity Framework with Oracle - EF generates invalid SQL as far as any non-SQLServer DB is concerned (Microsoft extended sql with a new statement - Cross Apply). So in this case, using the EF ORM with Oracle or MySQL can be a disastrous choice.
Usually people like DB-independant client libraries, the cost is some performance and occasionaly data type related issues (eg a date can be represented differently for different DBs), occasionally you get some limitations in different DBs features (eg "select top 10 x from y" doesn't work with Oracle but does with SQLServer, you have to "select x from y where rowid < 10" instead. Usually this is allowed for in the client library but sometimes they won't offer that feature at all for either DB).
I think the only times you really want a DB-specific provider now is when performance is very important, especially so you don't have to worry about what the client library does with memory. Most DB accesses are slow, but re-allocating huge chunks of memory is even slower. The other aspect is if you need specialist features that are provided by a single DB, in this case you're more likely to need the providers library.