I think both methods have their pros and cons. I personally never use dynamic SQL as I like to maintain strict interfaces to the database. If you decide to go with stored procedures, have a look at my tool nTierGen.NET - [url]http://www.nTierGen.NET/[/url]. It generates the stored procedures and data access code.

Gavin Joyce

__________________________________________________ _________
nTierGen.NET Code Generator - [url]http://www.nTierGen.NET/[/url]

Stored Procedures (Get, GetPaged, Insert, Update, Delete)
Data Access Layer - C#
Business Rules Layer - C# & VB.NET
Strongly-Typed DataSets - C#
Web Services - C#
__________________________________________________ _________
"Ashish Kanoongo" <ashishkanoongorediffmail.com> wrote in message news:eoP5p4YODHA.1624tk2msftngp13.phx.gbl...
Helo Friends

We would like to hear your opinion on what Data Access Layer (DAL) strategy you think is best: Stored Proceedurs vs. Dynamic SQL as Strong Typed Classes. Keep in mind we are more concerned about ease of management vs. performance because the target customer for the portal application is less than 250 total users, 100 or less concurrent users, and on average the common customer will be no more than 50 users total.

So far, a Strong Typed Classes DAL that creates run-time SQL seems to be catching my fancy, because I can get a code-generation tool that will do the repetative coding of creating the CRUD classes, and it also creates an additional Business Logic Layer with classes that a developer can use without interfering with the DAL. Not to mention I don't have to recode the DAL classes everytime I have a data model change - ORM.NET will do that for me. That tool is [url]www.orm.net[/url]. Check it out and let me know what you think.

My though here is I can make any neccessary data model change, create the DAL objects, and then hand over the VS.NET project to my team to code the neccessary business logic based on a written functional spec. This reduces my development time every time we have a custom implementation for a client.

I met with the lead developer of ORM.NET and here is what he had to say:

We think generating the SQL code at run-time is better from a code maintenance perspective than Stored Procedures, and only in certain instances will run-time SQL be less performant than SP's (i.e.Reporting & ytics). The biggest advantages with run-time SQL is we side step the following long-term maintenance issues:
a.. Creating dozens and dozens of stored procedures that create a whole other layer of complexity that needs to manage. - source safe, etc
b.. It is harder catching errors within Stored procedures until run-time which makes unit testing a lot harder.
c.. To save on the number of stored procedures and round-trips, developers often intermingle important business logic within the stored procedures which compromises the whole point of n-teir design.
d.. As changes to the database are made you also have to make sure you track down and test those changes within stored procedures as well as your code base.

So please let me know your opinion and suggestion.

Ashish Kanoongo