Database (and ORM) choice for an small-medium size .NET Application

Question

Hi

I was hoping to get some advice from the panel.

I have a requirement to develop a .NET-based application whose data requirements are, in the future, likely to exceed the 4 gig limit of SQL 2005 Express Edition.

There may be other customers of the same application in the future witwh a requirement to use a specific DB platform (such as Oracle or SQL Server) due to in-house DBA expertise.

My questions are as follows:

• What RDBMS would you guys recommend? From the looks of it the major choices are PostGreSQL, MySQL or FireBird. I've only got experience of MYSQL from these.

• Which ORM tool (if any) would you recommend using - ideally one that can be swapped out between DB platforms with minimal effort? I like the look of the entity framework but unsure as to the degree to which platforms other than MS SQL are supported. If it helps, we'll be using the 3.5 version of the Framework. I'm open to the idea of using a tool such as NHibernate. On the other hand, if it's going to be easier, I'm happy to write my own stored procedures / DAL code - there won't be that many tables (perhaps 30-35).

I really appreciate any advice, or insight, people are able to offer - especially from those that have been in the same situation and made a succesful choice!

Many thanks, Jim

Answer 1

I suggest using NHibernate with postgres.

You can do all your testing and development on sqlite so you don't need to have an instance of postgres running on your dev machine.

If you aren't sure if you want to use mysql or postgres, I suggest trying them both out. Postgres is more compliant, but if you're comfortable with mysql (and you're using an ORM), you should probably use that.

Answer 2

I've evaluated a lot of possible ORM solutions for .NET lately. I if I were you, I wouldn't go on the Entity Framework route when using anything other than MS SQL - apart from conceptual entity model problems (it's meant to be generic, but there's no way to catch something like sequence with this model) you are strongly dependent on the third party database driver provider developers - definitely to much variables in this equation for me.

NHibernate, on the other hand, allows being both generic in code and specific for chosen DB dialect. EG. you can use such attribute for a Id property:

[Generator( Class = "native")]
//...
public virtual int CustomerId {get; set; }

Seeing"Native" generator option with SQL Server NH will assume that it's an identity field (autoincrement) and for Oracle it will assume, that this property uses sequence with the same name (you can change sequence name if you want).

Also, just a few days ago MS ditched LINQ to SQL entry level ORM completely. You can never be sure whether they won't do the same with Entity Framework. No such problem with open source NHibernate.

I cannot propose any DB not knowing your specific needs, but if you want to use a free software (which excludes Oracle) and you're not already experienced with MySql, you should try Postgres, which is more powerful than MySql.

Anyway, with NHibernate you can switch the DB later without much pain.

Here is the list of database dialects supported by NHibernate: http://www.hibernate.org/361.html

EDIT: Having noticed that you already know MySQL - since you're not going to use DB directly much anyway with NHibernate I think you should start with MySql first and focus on the ORM part - it will be easier for you to control what exactly NHibernate is doing with the DB.

But you should know that NHibernate lets you write your data classes first and generate your DB schema automatically - if you follow this route (which is a good think from the DDD perspective) the DB layer will really become transparent for you.

Answer 3

Llbgen is very nice for your ORM mapper. At first there is quite a bit of a learning curve, but once you get the hang of it it's very good. It supports the databases you mentioned here, plus a few more.

Answer 4

I used MyGenerationSoftware with NHibernate to autogenerate the Classes/ OR specifics for a mySQL database. The nice thing about MyGenerationSoftware is that it can peer at your database and build your classes from that.

MySQL is (in my opinion), a production quality database, and plenty of commercial sites use mySQL, including Slashdot.

For NHibernate, once you have it set up, you can easily edit the XML to point to a different database, and that's all it takes to switch databases.

Answer 5

Why not Firebird? Firebird has more features than the other free databases. And its supported by NHibernate and Entity Framework. And Firebird could be embedded in you application and the licensing model is less restrictive than MySQL one.

It's only my humble opinion.

Answer 6

EntityORM - http://entityorm.uuuq.com meets your needs and not just.