[.NET] How to talk to a database in a portable way?

Question

I'd like to start an application that makes heavy use of a database, and is supposed to run on Windows under .NET as well as Mac/Linux under Mono.

Having done some research, I've realised that neither LINQ-to-SQL nor LINQ-to-Entities are production-ready in Mono at the moment (here and here). According to the former link, LINQ-to-SQL may at least make it into Mono 2.6, due some time in Nov 2009.

Mono also implements ADO.NET 2.0, as far as I can tell, although the test results are not looking great (assuming the page is up-to-date). Also, using raw ADO.NET is going to be very painful compared to LINQ.

Which database access API would you guys recommend in this situation?

Answer 1

I'd recommend coding by interface here. One of the great things about .NET is that it makes heavy use of interfaces, so you could code against IDbConnection, IDbCommand and so on. This means that you can code your application in ADO.NET to be virtually database/provider agnostic.

Answer 2

Here is a link with the ADO.Net Data providers that Mono supports:

http://www.mono-project.com/Database%5FAccess

You do not say which database you will be using when running on Mono. Linq to SQL is only supported for MS SQL Server.

As others have said, it looks like nHibernate is the way to go.

Answer 3

If you want an ORM, use NHibernate. If you don't need an ORM, use ADO.NET.

Answer 4

After much research I have discovered SubSonic, which looks to be much closer to the LINQ-to-SQL model than nHibernate. Just mentioning it since nobody else has. Not sure yet if this will be the final choice.

Answer 5

Consider these SO links:

• http://stackoverflow.com/questions/587159/has-anyone-used-any-net-code-generation-frameworks-in-mono-subsonic-nettiers
• http://stackoverflow.com/questions/409290/anybody-using-nettiers

Answer 6

For the record, we settled on IQToolkit together with a custom tool to generate classes from a database. The result is more low-level than ActiveRecord-style approaches; essentially nothing more than adding strong typing and LINQ support on top of SQL.