I'm focusing on a company application (asp.internet). Right now i'm using sql server. However I intend to support a minimum of mysql and postgresql in the future. Do you know the problems that I ought to envisage to avoid future head aches? Especially about datatypes (column types). E.g. I believe BIT column isn't supported on some dbs and so i use tinyint?

I mostly use plain sql (no entity framework or linq, etc) and then try to ensure that it stays as easy as I'm able to. I'm not using such things as triggers, etc. I actually do use saved methods but they may be changed with plain sql if I must.

Your hope would be to separate data access right into a proper data access layer, as Remus Rusanu indicates. The information access layer might have one consistent interface towards the relaxation of the code, and become transformed out for other versions for every DB platform. Keeping the SQL fairly standard can help, but it is not necessarily easy to write one body of SQL code and also have it work everywhere (the SQL standard is not that well implemented.)

Consider (with a few costs in term of learning curve) the adoption of the Domain Model along with a data access layer according to an OR/M like NHibernate (https://www.hibernate.org/343.html)

Make certain you are writing all of your client code while using abstract IDbConnection, IDbCommand, IDataReader rather than the concrete. Additionally, you will have and also hardwearing . SQL claims under control constantly to make sure you only use compatible syntax.

You may also try hooking up through the OdbcConnection/OdbcCommand components and employ generic ODBC syntax and generic ODBC data types (ie. the {fn SUBSTRING(...)} stuff, also known as. the ODBC Escaped Syntax).

As a substitute things i approach would be to isolate the information access and make specific DAL courses of instruction for each back finish. I personally use XML and XSLT to create the DAL code. Much like this the technique of integrating XSLT code generation from the blog, however with XSLTs targeted particularly for every back-finish specific code.