I'm building a C#.NET application which needs to run simple SQL queries, potentially against any database (SQL Server, Oracle, MS Access, Other).
I know that I can use a number of methods to build the connection (and I do currently):
SQL Server: SqlConnection
but because I can't be certain of the database that's used (this is really simple SQL to pull data out, so none of the proprietary SQL code differences between DBs will cause a problem), I'm considering using this:
and setting up a System DSN on the PC to handle the connection.
I assume the native providers will run quicker if anything but from my testing it's a negligable difference.
My question is:
Is there a difference between these two methods and do both types allow: Updates, Deletions, Insertions, Stored Procedures (I'm not using these yet but might soon), Parameters, anything else that might be different?
If I can use the ODBC connection for all of the calls I might want to do, why would I want to use the native ones? I presume that the DSN is merely a pointer to the same drivers anyway.