Difference between Linq to Sql, Linq, Typed Datasets, ADO.NET

Question

I been wondering about this for a while. It seems like there are so many ways now I don't know when to use what? Or if there is even a point to learn them. Like I don't know if they basically do all the same things and just basically stick with one till you master it then maybe look at other ones.

So when I was taking an ASP.NET course that was part of my program.

We first did like ADO.NET where we just wrote everything with SQL statements in the code. We then moved onto a 3 tier architecture. This was done by making like classes and having datasets that return stuff.

The SQL was written in the class. I personally never really liked this way since I always found it annoying with trying to get the quotes right and just overall did not like it.

I then found on the Asp.net site their tutorial of 3 tier arch that I really liked. They where using typed data sets. You add the dataset file to you DAL folder and you would make table adapters and stuff through the GUI. You would then write you code in these GUI, I found it the perfect solution since now my SQL code was away from my code and I did not have to worry about quotes and all that stuff not being right or closing the connections and stuff plus it even had a SQL GUI builder!

Then I would just make file in the BLL folder and make a property to grab the table adapter and write my business layer logic.

The only thing I did not like was since it was typed if my stuff tried to return some new rows it would get mad.

So when I had to join tables I usually had to make a new table adapter.

Now there seems to be so many of them.

• Linq -> what some people said would replace ADO.NET and some said it would not.
• Linq to sql
• ado.net

I am not sure if that is all of them probably not.

Before writing this post I did a quick check to see what linq to sql was about and saw some posts saying that MS was killing it off. They where from like 2008 so I don't know if this is true or not but I noticed that almost all the MVC books use like linq to sql so I don't think it is.

So is it worth switching to something a different one then typed datasets? Or are each used for different situations?

Solution 1

LINQ by itself is just a base technology ("Language Integrated Query") that's baked into C# 3.0 - it has nothing to do per se with databases. LINQ can be used against a variety of things - databases, XML, objects in memory, Entity Framework entities, Active Directory - you name it.

Linq-To-SQL is the lightweight, straightforward, MS-SQLServer only technology which allows you to use SQL Server tables easily and nicely as real objects in your .NET application. It's a "Object-relational mapper" that makes dealing with databases easier. It's SQL Server only, and Microsoft will not extend it much further - it's available, also in .NET 4.0, but won't be further developed any more.

ADO.NET is the base data access technology in .NET - it gives you access to a wide variety of data stores, relational and non-relational. It's the very basic technology - you deal with your data in a very low-level, raw manner.

On top of that, you have the ADO.NET datasets, which are a bit like Linq-to-SQL in that they make it somewhat easier to deal with databases. Contrary to Linq-to-SQL, you're not dealing with your objects from your domain model in your .NET code, but instead you're dealing with the database-oriented rows and columns just like they exist in the database. It's a more direct representation of what's in the database, it's on a lower level, it's very tightly coupled to your database layout, and it's not as "nice" and easy to use as Linq-To-SQL objects - you deal with lowlevel rows and columns and their values.

If you have the choice right now, and don't need anything but SQL Server, I would highly recommend checking out Linq-to-SQL - the mapping from the raw database tables to nice and easy to use .NET objects really makes your life a whole lot easier!

Marc

Solution 2

To clarify the LINQ-to-SQL story; it isn't "dead" - it is very much a fully supported part of the .NET framework, and under active development (there is a LINQ-to-SQL team at Redmond).

The point is that the new feature development is mainly going into EF, including (hopefully) bridging the gap between LINQ-to-SQL (which is generally very popular) and EF (which has been criticised in a lot of ways).

For example, EF in 4.0 supports POCO objects, like LINQ-to-SQL.

Personally, I'm still a fan of LINQ-to-SQL, and would happily use it for new builds - but I would hide all of this behind a repository interface so that I can swap it at will, to any tool:

• raw ADO.NET
• LINQ-to-SQL
• NHibernate
• LLBLGenPro
• Entity Framework

The one thing I wouldn't touch is DataSet ;-p

Solution 3

I'd say that if you're having trouble with getting quotes right in your SQL statements using ADO.NET, you're probably building the SQL statements in the wrong way. Prone to SQL injection or at the very least: messy code.

LINQ to SQL uses ADO.NET to do it's thing. It's a combination of an Object-relational mapping (ORM) tool combined with a new query syntax basically. I wouldn't invest time in LINQ to SQL as Microsoft has declared it end of life.

Entity Framework is the LINQ to SQL replacement. It is an ORM tool which comes with it's own SQL like query language, Entity SQL. You can use LINQ on top of it, so that most simple queries are exactly the same as in LINQ to SQL.

What method to use depends largely on what you're trying to do. I would not use typed DataSets (or any DataSets really). It's a personal preference I guess, but if you can create typed DataSets, you're better off doing full scale object-relational mapping.

Knowing basic ADO.NET is a useful skill however you look at it though. Especially if you need to update multiple records in a database without first retrieving that data, you'll always end up writing SQL statements. I'd recommend creating stored procedures for those cases, which you can call with plain ADO.NET, or you can you can add them to your model in Entity Framework and call them through there.

Entity Framework gives you some database independence (unlike LINQ to SQL). There are implementations for Oracle around, but I have no experience with them personally. From what I've heard they impose some undesired limitations if you're doing semi-complicated work.