I am starting off with a SQLServer database. So it would seem that I should use System.Data.SqlClient
namespace. But, there is a chance that we might shut down our SqlServer database and go to MySql or Oracle. For this reason, I am coming up with a set of standards on how our .Net apps will communicate with the database, so as to make it easier to migrate to a different database system in the future if we needed to do so.
So here are the standards:
Which brings me to my main question at hand. Which namespace should I be using to code my DAL?
It looks to me that the choice is between System.Data.ODBC
and System.Data.OleDB
:
Connects to SQL Server 2000 and later only, but you will get optimal performance when connecting to those databases.
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.
Connects to legacy databases only, using ODBC drivers. (E.g. MS Access 97.)