I'm beginning served by a SQLServer database. Therefore it would appear which i should use System.Data.SqlClient namespace. But, there's an opportunity that people might shut lower our SqlServer database and visit MySql or Oracle. Because of this, I'm approaching with some standards how our .Internet applications will talk to the database, in order to allow it to be simpler emigrate to another database system later on when we needed to do this.
Here would be the standards: 1. Make use of an ORM if at all possible (eg NHibernate) (No LINQ because it only supports SqlServer, but how about Entity framework and it is support for Oracle and MySql?) 2. If ORM is definitely an over-kill, then use parameterized SQL queries. 3. Use saved methods just for lengthy running or complex actions that have to be carried out around the database.
That can bring me to my primary question at hands. Which namespace must i be utilising to code my DAL?
It appears in my experience the option is between System.Data.ODBC and Systme.Data.OleDB. - Do you know the trade-offs? - Is a preferred within the other? - What exactly are your ideas concerning the first 3 standards?
I'd only use SqlClient and re-write/re-create the DAL whether it transformed.
Unless of course you are likely to implement and test on multiple platforms at this time, I am unsure the additional effort at this time is really a large deal or any under your time and effort to redo the DAL, cheap there is a DAL whatsoever means you have everything in one location for any later change anyway.
You need to make use of the SQL Server driver. I realize what you're attempting to do but how you would accomplish supporting multiple databases is as simple as placing another layer of abstraction. This can be done many different ways. However, you place the database specific code close to your class hierarchy. Therefore, each class could possibly get the advantages of database specific functionality however the greater level phone callers have no idea or care what database has been used underneath. So far as ORMs, I favor LLBLGen, but case my preference.
Also, simply to clarify, LINQ isn't specific to SQL Server. That's LINQ-to-SQL. LINQ is really a querying technology which you can use in LINQ-to-SQL, LINQ-to-Organizations, LINQ-to-objects, as well as LLBLGen supports LINQ.
If you have any inkling that you will be changing databases (or supporting multiple backends) then an ORM is what you want. Otherwise, you'll still need to refactor/rewrite lots of your DAL to be able to offer the change. In case your application is small, it will not be bad, but anything substantial and you will be harming.
You will notice that using SQL Server the SqlClient is way faster and simpler to build up with than OleDB and ODBC - unless of course it's very likely that you'll want to aid multiple platforms you will notice that the advantages over-shadow the potential risks that you'll want to rewrite your DAL.
Furthermore, using OleDB / ODBC is just 1 method of maintaining platform independence - it may seem more efficient to possess multiple implementations of the DAL, each utilizing a client indigenous to the woking platform getting used.
I have heard it stated that unless of course it is a key feature, you should not worry an excessive amount of about maintaining platform independence. Nevertheless,
SQLClient provides you with native access and really should become more performant (it does not need to do any abstractions/translations).
The only real factor you need to switch to get OLEDB working versus. ODBC is the connection string. OLEDB has a bit more client-side intelligence therefore it offers better performance.
Whether or not you utilize SQLClient or Odbc for the time being, if you are using saved methods or any other database-specific features, you will need to rewrite individuals should you change database engines.