Model for ASP.NET MVC

Question

Hi

I just started with ASP.NET MVC 1.0. I've read through some tutorials but I don't have any good ideas on how to build my model.

I've been experimenting with LINQ to SQL. Can't say I like it that much, but I'll give it a try. I prefer using SQL Stored Procedures, but it doesn't seem to work very good together with the optional parameters in my stored procedures.

The project is a prototype, but I would like to start with a good design right away.

The database will probably contain around 50 tables and stored procedures will be used for all queries and updates. Some procedures will also return multiple results. How would the best way to design this model?

All and any ideas are welcome. Should I even use LINQ to SQL? Should I design my stored procedures to only return one result? I feel a little bit lost.

Answer 1

You can use stored procedures with LINQ. You simply need to replace the autogenerated statements on your entities with your stored procs. Look at the properties for the entity (table) in the designer to change these. Your stored procedures won't be able to return multiple results as they need to map onto a collection of either one of your entities or an autogenerated entity, i.e, onto a collection of a single class.

Having said all that, I'd give it a go without using stored procedures. The LINQ code is fully parameterized so you already have this benefit. You'll find that LINQ allows you to easily build up queries alleviating your need for the optional parameters in your stored procs and, likely, resulting in more efficient queries as you don't need to build extra logic into your query to handle the optional parameters. Once you start using LINQ I think you'll find that the power and expressiveness in the code will make you forget about using your stored procedures.

You may still have a need for stored procs or table-valued functions for complex queries -- that's ok. If they map onto an existing entity, you can simply drag the proc/function onto the entity in the designer and you get a nice method on your data context that runs your SQL and returns collections of that entity. If not, I often create a view for the proc/function schema, use that as an entity, and attach the proc/function to it.

Answer 2

May I ask why you need to use stored procedures for this project? They have almost no value today if you are working with a modern database. Parameterized SQL using a good ORM will give you the same cached execution plans and security benefits that you are looking for with stored procedures.

I would recommend NHibernate because it's a far better way to achieve persistence ignorance. And IMHO this is the goal of anyone designing a model. You should be thinking object first instead of data first.

The best resource around is a screencast series by Steve Bohlen "The summer of nhibernate"

Also LINQ to NHibernate is available and works great for any dynamic query that you might need to write.

The only pain you will notice with NHibernate is the XML, but if you really enjoy the ORM you could write a fluent interface instead using FNH.

I found NHibernate to be the best tool around for modeling a domain because your objects are 100% infrastructure free and this allows you to do anything you want with them without having to worry about the database. Also if you are into unit testing of any kind this will make your life much easier :P

Edit

Part of the reason I replied to your question is that I have built many a system using stored procedures and found to regret that decision later. But in my situation I had a requirement by a dba so I couldn't step away from the norm.

If you are already looking at LINQ to SQL I would simply encourage you to look at NHibernate as another great option to achieve the goal of persistence ignorance.

When I got away from thinking about the problem data first it allowed me to work at a much higher level and take advantage of the .net platform for solving problems. TSQL has its place but if you are writing an application and want to maintain it - try to think about how your domain objects will work together in memory first.

Edit 2

Not sure why I got a down vote for suggesting NHibernate - after all you said that you were open to something other than L2S

"All and any ideas are welcome. Should I even use LINQ to SQL? Should I design my stored procedures to only return one result? I feel a little bit lost."

The point of my answer was just this - to provide another "welcome" idea ...