Best Data Access Methods for New Web Application

I'm building a new web application project and am confused by the numerous methods of performing data access. I'm backending on SQL and a bit confused whether to use LINQ to SQL or trtaditional ADO.net ?

what are the advantages and disadvantages of using LINQ/SQL over ADO.net?

If it is ADO.net,then what is the best way to retrieve data means either calling the stored procedures or directly calling the t-sql code?

My question is what is cleanes and most effiecient and professional way of cre开发者_如何学JAVAating DAL for webapplication in asp.net?

Thanks

---

What are the advantages and disadvantages of using LINQ/SQL over ADO.net?

• Linq2sql generates a series of classes that are 1-to-1 mappings of your (selected) database tables - this means you don't have to write tedious and error prone data access code using ado.net yourself.
• Linq2sql may not provide enough value for you if you intend on using a custom object-to-relational mapping (non 1-to-1) - of course you could still use linq2sql, but it would mean having an extra layer in between.
• Linq2sql allows you to easily query the database using powerful linq expressions. Writing linq queries provides you with intellisense that you wouldn't get if you embedded your queries as strings inside ado.net commands, or wrote stored procs in management studio.
• Using linq, you don't need to know t-sql while you will if you use ado.net (although it can definitely an advantage if your linq queries start doing strange things!). An example of this the complexity of writing t-sql queries that provide paging resultsets simply becomes .Skip(page * size).Take(size).
• Linq2sql automatically creates t-sql that uses parameterised queries which is much more secure against sql injection attacks than handwritten ado.net code which builds up a query using a string.
• Linq2sql doesn't work very well with stored procedures - you are probably better off not bothering with linq2sql if using sprocs.
• Linq2sql could require your database tables to be less-tightly locked down than would be possible writing ado.net code using stored procedures.

If it is ADO.net,then what is the best way to retrieve data means either calling the stored procedures or directly calling the t-sql code?

• If you'd ruled out linq2sql, and ado.net happened to the better choice for data retrieval, I would be surprised if you were directly calling t-sql code very often or even at all. I would almost certainly expect you to be using stored procedures for reasons that you have queries that are too complex using linq, and/or security requirements.

My question is what is cleanest and most effiecient and professional way of creating DAL for webapplication in asp.net?

• In my opinion, the cleanest DAL would probably use linq2sql as it is the lightest and most targeted ORM for SQL Server (assuming your still interested in SQL Server for this specific question of course).
• The most efficient could be the handwritten one using ado.net, but this is probably a waste of time as more often than not, you will find a tool such as linq2sql writing better queries than 90% of developers.
• In my opinion, the most professional DAL could be linq2sql, but it is more likely to be the Entity Framework of NHibernate (as other answers have suggested) due to more flexibility.
• My last choice DAL in terms of cleanliness and professionalism would definitely be a handwritten ado.net one.

---

The best way to go is O/RM. Small apps Linq2Sql, larger apps Entity Framework 4 or NHibernate (Fluent NHibernate).

Calling SPs from your code means that your app logic is placed somewhere else than in the app code. It's a way to go but at present less and less popular because of TDD.

The best way is to create DAL into a separated logic layer, own assembly.

---

I would without doubt go for Linq2Sql.

Download Linqpad and play around with the included samples to get started.

---

You should check out some ORM frameworks, like NHibernate: http://nhibernate.info

---

If you want efficient data access in terms of performance than there is nothing faster than pure ADO.NET. You chan check it out here: http://ormbattle.net/.