Best .NET Solution for Frequently Changed Database

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

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 2

You're already happy with stored procs and they might be enough to abstract away the changing schema. If ORMs aren't happy with stored procs then maybe they'd work with Views that you keep current on top of the changing schema.

Answer 3

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 4

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 5

If I were in your shoes I would try to leverage what I knew (sprocs) with Linq2Sql. Linq2Sql can still use your sprocs but then you have the added bonus of putting a new tool in your belt. I think having a grasp on the Linq2XXX (X being a random technology not adult entertainment....which isn't a bad idea now that I think of it) syntax and methodology is going to be a great addition to your skill set using Linq over a collection of objects is way sweet.

But ultimately something like NHibernate will suit you better in the long run.

Answer 6

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.

Answer 7

If the database schema changes often, prefer the Entity Framework over LINQ2SQL. If the schema changes, using L2S you have to
1) Remove and re-add you table (loosing your customizations)
2) Modify the model by hand (as done here in stackoverflow)

The EF is a super-set of L2S, giving you more flexibility of usage and dbms-independence

Answer 8

look at why it is changing, and see if you can anticipate and generalize the kinds of changes coming at you so that they don't break your code

a framework may make accomodating the changes easier, but deeper analysis will have a longer-term benefit

Answer 9

Any solution can work, what you really need is a set of tests which will guarantee that basic operation like insert, select, update and delete works. This way you can simply run your tests and check if mappings are up-to-date.

Answer 10

EntitySpaces can regenerate your DAL/Business Layer in one minute, and no code loss, see the trial version ==> HERE

No Registration Necessary, runs under Visual Studio as well.

Answer 11

Use EntitySpaces. you will send me flowers, guaranteed. simply awesome. change the db as you like. hit the button, bang. all your changes are done. without changing your custom code. I love it.

Answer 12

How simple is the application? If I were to be working with schema/design stuff for a couple of months, and not really worry about an actual app . . . I would consider using EDM and a Dynamic Data Entities Web Application project. This get your going with the least amount of effort, in my opinion. This keeps you focused on schema, data and other groovey things. I hopefully don't get too many neg bumps from this one!

Here's how the new project dialog will look like this