Why database independence

MSSQL server is expensive for a start-up project. There are a few “free products“: MSDE and MySQL, and to a less degree, Access database. MSDE has the advantage that is mostly compatible with SQL server and thus could be later upgraded into MS SQL server.

Access mdb is the easiest one to setup and require no maintainence.

Use MySQL if need to run lots of queries but do noe want to spend money on a dedicated Windows Server. If serious thinking Linux, before sure the read this IDC study.

I think the database independent thing is mostly a cultural thing, e.g, some companies would not allow MSSQL.

Developing a database independent layer

Both the ADO.NET DataReader and DataSet are database independent.

SQL statements: Select, update and delete statements are not big problems. Many databases supports SQL92 style syntax.  Identity insert is a problem because each product uses a different mechanism. Need to encapsulate the identity insert.

Commands: Need to encapsulate. SQL server and Oracle has different stored procedure syntax. Access supports Parameterized Query definition. MySQL does not support any.

Querying the catalog: Need to encapsulate. Each database has a different mechanism for querying catalog.

Some Links

http://weblogs.asp.net/cnagel/archive/2003/10/30/34657.aspx

http://www.michaelrichardson.org/articles/Using%20Interfaces%20to%20Develop%20a%20Generic%20Data%20Access%20Layer.pdf

http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp