Cross platform SQL? (sqlite+mysql+tsql)

Question

Is there a cross platform solution for sql? My prototype was in sqlite. I am switching to a server that offers tsql and i was considering mysql in the past for my webservers(maybe i should stick to tsql and sqlite). I am wondering if theres a .NET lib that allows me to write sql compatible with all.

Some annoyance i had was in create table. I thought primary keys auto increased but they dont. I have to write identity(1,1). When i ported my sqlite code to mysql i had issues also with create table but i am sure there will be other places once my sql statements get more complex. So i thought trying a lib may be a good idea.

Answer 1

An ORM like NHibernate or Entity Framework will support all those sql backends, and more. It will also save you from writing all your SQL queries yourself.

Answer 2

You might want to take a look at ADO.NET Entity Framework. It supports a lot of different backends.

Answer 3

I personally use SubSonic as an ORM. It supports both SQL-Server and MySQL, as well as SQLite. Database creation is always a tricky question, but you generally don't perform it often. I have chosen SQLite, as it is very portable, and should also be available when I port code using Mono. TSQL (SQL-Server) is a windows-only product, and is not portable. My databases also won't be very large (< 100 Mb) and this may also need to figure in your choice.

I would recommend (for the moment, until VS 2010 is out) that you don't use Entity Framework, as it is a traditional v1.0 MS product, and very problematic. Essential features are not present, and artificial restrictions make it less useful than other ORMs. No doubt the next version will be better, but the current version is not worth your time compared to something like SubSonic.