Best .NET Solution for Frequently Changed Database [closed]

Question

I am currently architecting a small CRUD applicaton. Their database is a huge mess and will be changing frequently over the course of the next 6 months to a year. What would you recommend for my data layer:

1) ORM (if so, which one?)

2) Linq2Sql

3) Stored Procedures

4) Parametrized Queries

I really need a solution that will be dynamic enough (both fast and easy) where I can replace tables and add/delete columns frequently.

Note: I do not have much experience with ORM (only a little SubSonic) and generally tend to use stored procedures so maybe that would be the way to go. I would love to learn Ling2Sql or NHibernate if either would allow for the situation I've described above.

Answer 1

One key thing to be aware of here is that if the database schema is changing frequently, you want to have some level of compile time type safety. I've found this to be a problem with NHibernate because it uses xml mapping files so if you change something in your database schema, you don't know until runtime that the mapping is broken.

It will also be a problem with stored procs.

Using Linq2Sql will give you the advantage of knowing where exactly your code is breaking when you change a schema at compile time. This for me, is something that would take precedence over everything else if I'm working with a frequently changing schema

Answer 2

I'd look at SubSonic with the build provider (Website Project) setup. That works great because it automatically regenerates the DAL objects every time you build your project, so if the database changes in a way that breaks your code, you get a build error.

It worked well until the database schema got really complex and we were hitting the limits of the ActiveRecord pattern, but as long as the schema's not hugely complex it works pretty well. Once the schema stabilizes, you can switch so that you're only building the DAL when you want to.

Answer 3

NHibernate, but only if you would be amenable to having an object-first approach wherein you define your classes, and then define your desired table structure in the mapping files, and then create a database schema using NHibernate's built in schema generation classes.

For doing it the other way around (e.g., you have a bunch of tables and then you base your object design on that) I've found MyGeneration + NHibernate to work, although I'm not too happy with the resulting classes (mainly because I'm such a stickler for true Object Oriented Programming).

Answer 4

You definitely want to use an ORM. Any ORM is ok, but you want something that will generate strongly typed classes. When fields get added, modified or deleted from a table, you want to be able to regenerate those classes, and deal with fixing compile time errors only. If you use a dynamic model, you're likely to have many nasty runtime errors. This is VERY important! I am part of the MyGeneration development team on sourceforge, and I think that is a great solution to your problem. You can generate dOOdads, NHibernate, EasyObjects, EntitySpaces, etc. If you want to go with a more expensive solution, go with CodeSmith or LLBLGen Pro. Good luck - anyone interested in using MyGeneration, feel free to contact me with questions.