When should you create sprocs? As often as possible?

[SlurrerOfSpeech]

I really don't get ASP.NET MVC because the entire point of the Model is to interact with the database through C# code (or any other language that gets compiled to the CLR) and yet I know it's possible to, and considered good practice to, write all your business logic in the database to the point where the only thing your C# code does is say "Go!", making LINQ and all that fancy jazz pointless.

Let me give a real-life example. I'm writing an application that has a table representing comments in a comment thread and another table representing ancestor-descendant relationships of said comments. When a new comment is created, a new ancestor-descendant relationship is created as well (If the comment is at the top-level, it's ancestor is itself). When a comment is deleted, all its ancestors are deleted as well as the map of their relationship in the other table.

I can make all this logic possible with sprocs, triggers, etc. when I set up my database. Or I can just create the tables in as static a form as possible and use my C# model to handle the aforementioend logic. By doing as much on the database as possible, the problem is that if I decide to revise or add anything to the database, including sprogs/triggers/cascades/etc., my C# model is out of sync and I have to regenerate it based on the updated database and rewrite all the C# code that was handling the logic. The advantage is (supposedly) efficiency from not having raw queries thrown at the database. On the other hand, if I keep all that complicated in the C# code then it's less work to maintain when the database is changed because I don't have to do all this again.

In short, I don't know how to justify an MVC model if I'm a SQL expert (which I'm not but hope to be some day). It's like a reversal of traditional roles: The database handles the programming logic and the language of the model (C#, VB, etc.) acts as a talker that sets the logic in flow.

 

[Silicon Waffle]

...On the other hand, if I keep all that complicated in the C# code then it's less work to maintain when the database is changed because I don't have to do all this again..
In short, I don't know how to justify an MVC model if I'm a SQL expert (which I'm not but hope to be some day). It's like a reversal of traditional roles: The database handles the programming logic and the language of the model (C#, VB, etc.) acts as a talker that sets the logic in flow.

What if your site has to handle up to hundred thousands of transactions per some hours? It's an overloaded work for db server then.
On the business side, the employer has to hire a DB programmer for the design and making such scripts and a C# programmer to work as the site's back-end developer.
Plus, DB scripting is difficult and error-prone.

 

[Fooality]

I really don't get ASP.NET MVC because the entire point of the Model is to interact with the
...
In short, I don't know how to justify an MVC model if I'm a SQL expert (which I'm not but hope to be some day). It's like a reversal of traditional roles: The database handles the programming logic and the language of the model (C#, VB, etc.) acts as a talker that sets the logic in flow.

First, I think you're right about logic in the database, disagreeing with many around here. The other great thing is DB functions lend themselves to massively parallel processing easily, letting you use GPUS and such.

The main thing about MVC is controller handles safe and secure routing of requests, view handles the processing of the data into the appropriate medium for the request, and model handles the database connectivity, and they are separate. Model can and should be simple, but the key is to separate it from view: The information pulled from the database should be independent from the view medium. If a model query provides user account info, it should give the same data whether it renders it as PDF or JSON or HTML in the view. Last time I was working in Visual Studio, I remember bypassing a lot of the built in stuff to do my own complex queries, because it was more efficient than learning it all, but don't throw out the MVC paradigm just for that reason, it really is best.

 

[Silicon Waffle]

You may not really need to justify the pattern itself because it is designed or invented to resolve your specific software architecture under development already. What you would need to do is follow the standards and what are best practices to implement your software requirements in accordance with your business needs.

...
The main thing about MVC is controller handles safe and secure routing of requests, view handles the processing of the data into the appropriate medium for the request, and model handles the database connectivity, and they are separate. Model can and should be simple, but the key is to separate it from view...

I think ASP.NET's MVC follows this interactive workflow.
View <==> Controler <==> Model
Some people also reason that Model can connect or disconnect to View e.g in case of authorized and unauthorized users access but this sounds to me like a violation of data access or encapsulation rule. You can make your components as fat as you would want to, which is contradictory to such misconceptions about fat model and skinny controller as a rule of thumb people often spread during product development phases. All are beautiful to me as long as the outcome is functional, optimized, and above all matches with what have been required.

 

[Fooality]

You may not really need to justify the pattern itself because it is designed or invented to resolve your specific software architecture under development already. What you would need to do is follow the standards and what are best practices to implement your software requirements in accordance with your business needs.I think ASP.NET's MVC follows this interactive workflow.
View <==> Controler <==> Model
Some people also reason that Model can connect or disconnect to View e.g in case of authorized and unauthorized users access but this sounds to me like a violation of data access or encapsulation rule. You can make your components as fat as you would want to, which is contradictory to such misconceptions about fat model and skinny controller as a rule of thumb people often spread during product development phases. All are beautiful to me as long as the outcome is functional, optimized, and above all matches with what have been required.

Yeah, there are different frameworks with different interpretaions of view and controller i think django does it that way too, with separate logic for routing. I think the paradigm comes from gui programming, so how its mapped to web is a bit open to interpretation. For me and other frameworks, controller connects requests to model via views, controller is top level handling routes and requests. It doesn't matter though, the key thing is that database functionality is abstracted and kept separate from the rest by models, and some separate mechanism ( i call controller) is handling decisions about different views for different media (pdf json etc).

 

[harborsparrow]

It doesn't really matter where your business logic resides unless there is a performance constraint--but for maintenance purposes, you'll want it all in the SAME place, written with best practices so that there is information-hiding and no side effects, for example. And the business logic should be separated from the presentation. Say you want to change all the colors or look and feel of your website (or application)--can you do it without changing the business logic?

I've never warmed up to the MVC framework; it was developed by someone else, and my mind doesn't easily wrap around it. But I've warmed up very much to the idea of separation of concerns, which is what the conception model-view-controller pattern intends to accomplish. So--think separation of concerns, and keep your look and feel completely detached from your biz. logic. At all costs. You won't regret it.

Requiring use of MVC is just a way to try and enforce this good design principle.