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
:
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.