Architectural Design DAL Layer

Question

I am working on architecture of mid sized web application & for my DAL layer i am having 3 options

1) Traditional Stored proc Based Architecture (Using NTiers Template of Codesmith)

2) LINQ To SQL (or PLINQO Template of codesmith)

3) LINQ To Entity

From above LINQ to Entity is out of reach as we need to start application very quickly and we don't have the sufficient skillset for the same and as team has never worked on any OR/M tools it will be steep learning curve for them (This is what i read some where)

I prefer to go ahead with LINQ to SQL (But only fear is microsoft is not going to support or enhance LINQ to SQL further), from my point of view if microsoft is not going to enhance it further i am not having any issue as whatever feature i require in my project it is sufficient.

Now my issue is should i use linq to sql or should i stick to traditional architecture ?

OR else any other option is there ...

EDIT : I am going to use SQL Server as database and it does not require to interact with any other database

One of the most important objective in designing DAL Layer is faster development and maintainability for future database table changes, as there are chances that field may increase or decrease in future.

Also if you feel that any ORM tool is really good and does not have steep learning curve then also we can use

Please provide suggestions

Answer 1

There is no absolutely preffered way of writing DAL. These are all options. Which one to choose depends on your project, your skills and your inclinations.

Normally, with LINQ you can expect to be more productive. On the other hand, the DAL built with stored procedures can be expected to perform faster.

The issue only comes when you need some specific queries that the default LINQ to SQL provider won't be able to generate to be blazingly fast. In that case you will have to tap into your LINQ code to plug in your custom stored procedures where needed.

Regarding LINQ to SQL support and further development, it was grounded a long time ago already. So no official further development. Note: that is true for LINQ to SQL (it will be taken over by EF) relational solution, not for the main LINQ functionality.

Entity Framework in its v.1 only received massive critics. You're advised to wait until v2 comes out.

The most important limitation with LINQ (over Entity Framework or any other popular ORM) is that it doesn't support 1 to n mappings. That is, each your LINQ class can only map to a single table, not represent some sort of view over several others. Maybe it's not important to you, but maybe it is. Depends on your project.

Answer 2

As you are working in medium size project, I would suggest you to use LINQ-TO-SQL because of these advantages

Advantages using LINQ to SQL:

•No magic strings, like you have in SQL queries •Intellisense •Compile check when database changes •Faster development •Unit of work pattern (context) •Auto-generated domain objects that are usable small projects •Lazy loading. •Learning to write linq queries/lambdas is a must learn for .NET developers. Regarding performance:

•Most likely the performance is not going to be a problem in most solutions. To pre-optimize is an anti-pattern. If you later see that some areas of the application are to slow, you can analyze these parts, and in some cases even swap some linq queries with stored procedures or ADO.NET. •In many cases the lazy loading feature can speed up performance, or at least simplify the code a lot. Regarding debuging:

•In my opinion debuging Linq2Sql is much easier than both stored procedures and ADO.NET. I recommend that you take a look at Linq2Sql Debug Visualizer, which enables you to see the query, and even trigger an execute to see the result when debugging. •You can also configure the context to write all sql queries to the console window, more information here Regarding another layer:

•Linq2Sql can be seen as another layer, but it is a purely data access layer. Stored procedures is also another layer of code, and I have seen many cases where part of the business logic has been implemented into stored procedures. This is much worse in my opinion because you are then splitting the business layer into two places, and it will be harder for developers to get a clear view of the business domain.

Answer 3

With the limitations mention I would say just stick to adhoc/Custom queries and ADO.NET and not go for any jazzy stuff. ALso Storeproc based DAL are faster is a notion based lame arguments like stored procs are precompiled but they are not. All that they have is query plan cache . So lesser the ivestment in stored procs the better you are. My advice ADO.Net and custom dynamic queries constructed from entity objects.

Answer 4

Just to expand on @Developer Art, using the traditional stored proc approach enables you to put business logic in the database. Usually you will want to avoid this, but sometimes it is necessary to do. Not to mention you could also enforce constraints and permissions at the database level using this approach. It all depends on your requirements.

Answer 5

The argument of stored procedures vs ORM's is long-standing and unlikely to be resolved any time soon. My recommendation would be to go with an ORM (Linq-to-Sql in your case).

Yes, stored procedures will always be faster since the queries are precompiled. The real question you have to ask yourself is whether you have such a performance-intensive system that your users will actually notice the difference. Keep in mind that using stored procedures means that you will need to manually write all your own queries where using an ORM does this for you. This usually means that an ORM will speed up your development.

Since you mention that speeding up development time is one of your goals I would recommend Linq-to-Sql - otherwise you will basically write the entire DAL yourself.

Answer 6

All of the options you've provided have significant drawbacks. None of them meet the requirements you've set out.

You need to prioritize what is most important for you.

If learning curve is your biggest issue, stay away from all ORMs if you are already comfortable with ADO.NET, DataTables, etc.

If development speed is your biggest issue, you should learn an ORM and go that route. The easiest ORM to recommend is NHibernate. Every other ORM has significant weaknesses. NHibernate works in the vast majority of projects, whereas other ORMs are much more situationally appropriate (depending on your DB design, model design, agility requirements, legacy schema support, etc.). All ORMs have learning curves, they just come into play at different times and in different ways.