Introduction
Even though on a low level, LINQ may itself use subsets of ADO - this article focuses more on the programming aspect as a comparison between the two. I am also aware of the slight difference in "run-time" speed, in terms of data access times. However, again - we focus on differences during the development or "design-time" aspect thereof.
About ADO.Net
ADO.Net is an acronym for Advanced Data Objects. It is a subset of the .Net framework. The first versions which were "non .Net", were separate libraries for accessing data. However, as the of .Net Framework it has been incorporated as part thereof.
Accessing SQL based databases using ADO.Net is fast and efficient. However, data is often returned as different types or custom types, and have to be cast or formatted into usable or .Net or native types.
However, when working with web services, like WCF Services and Silverlight, it's a breeze to query data, compile the objects and send them across the service.
I will not go into a too detailed discussion regarding ADO.Net as it's been used for a long time and that is how most of the .Net developers access data.
About LINQ
LINQ is an acronym for Language INtegrated Query. For the purpose of this article, which focuses on C#, .Net and MS-SQL based data mainly, you may understand LINQ as the following: C# Syntax like SQL.
You must understand that any possible collection of objects can be queries with LINQ, where SQL is mostly related to data and querying databases. For example, you may query the Windows file system (like your C drive) as if it were a database or a collection of data - however, we will not look at that during this article.
The reason why LINQ is (or can be) so popular with developers, and me at least, is that it allows you to stay in your native programming language (C# in this instance) and query data, or any other collection of objects for that matter. Another nice feature, is the comparable native types it uses to build and return these objects.
Another problem I have with LINQ, is that it basically creates "it's own" version of the objects returned. This is not really a problem - and actually, it's one of the best features. It does a lot of the work for you. Let me explain. If you go through the process of creating a new database, it's data structures, communication layers, business rules, data entry and exist points, and start working with larger projects where such data libraries are shared between teams, you can imagine how complicated the SQL, Stored Procedures, etc. can get. As well as where things (like business rules) are located, in the database, or in the libraries. (But that is another can of worms for another article)
But let's get back to that problem I started the previous paragraph with - the issue I have, if you query (lets say) the user table, it will build user objects based on the data structure. If you then want to send this across a WCF service for instance, it creates a very hairy and difficult scenario to overcome. You have to either work some black magic with inheritance or even darker magic with WCF data contracts and really get your hands dirty in the WCF frameworks. However, it's so cumbersome you may actually completely get side-tracked and forget you are actually working with data, fetching it and returning it - and not communication layers and protocols. Another way, is to recreate each class or object entity, and write interfaces, or perhaps try and only work with native types in the methods. Other ways may exist, but this entire situation is a bit of a sticky one - I'm working on better ways to handle them every year. I will publish an article on my best solution in the near future.
However, if your application is less widely scoped, lets say, you have a typical .Net Windows App (WPF or Win-Forms) over a fast company network, linked to a database server - you will regret not using LINQ. It's only problematic when you start combining WCF or web services and LINQ.
Data Structure for Article
For the comparative nature of this article we will look at a single table with the following structure:
Practical ADO.Net
Lets have a look at the ADO.Net code. It should look very familiar if you have accessed databases before.
First, we create a data entity object, by creating a class with the same structure as the table. This is automatically done when using LINQ:
public class ADO_UserEntity
{
public ADO_UserEntity()
{
id = 0;
firstNames = "";
lastName = "";
emailAddress = "";
password = "";
}
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string firstNames;
public string FirstNames
{
get { return firstNames; }
set { firstNames = value; }
}
private string lastName;
public string LastName
{
get { return lastName; }
set { lastName = value; }
}
private string emailAddress;
public string EmailAddress
{
get { return emailAddress; }
set { emailAddress = value; }
}
private string password;
public string Password
{
get { return password; }
set { password = value; }
}
}
Now, the code for accessing the data, and building the object as defined in the class above:
static List<ADO_UserEntity> GetDataUsingADO()
{
List<ADO_UserEntity> result = new List<ADO_UserEntity>();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("MyConnectionString");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Users", conn);
conn.Open();
System.Data.SqlClient.SqlDataReader sqldr = cmd.ExecuteReader();
while (sqldr.Read())
{
ADO_UserEntity entity = new ADO_UserEntity();
entity.Id = (int)sqldr["Id"];
entity.FirstNames = sqldr["FirstNames"].ToString();
entity.LastName = sqldr["LastName"].ToString();
entity.Password = sqldr["Password"].ToString();
result.Add(entity);
}
return result;
}
Practical LINQ
Lets have a look at the LINQ code. There are some steps not yet shown, but I will explain them in the LINQ to SQL tutorial section below.
static System.Data.Linq.Table<LINQ_VS_ADO.User> GetUsingLINQ()
{
LINQ_VS_ADO.LINQ_VS_ADODataContext db = new LINQ_VS_ADODataContext();
System.Data.Linq.Table<LINQ_VS_ADO.User> result = db.Users;
db.Dispose();
return result;
}
It is incomparable, the simplicity and ease. I have not done searches or filters, just basic selects - but info for more complex selects and so forth can easily be obtained from the net.
LINQ to SQL Tutorial
Step 1: Open Solution Explorer
Step 2: Right-click on project, select Add, select New Item
Step 3: Under the Installed Templates tree, look for Data
Step 4: In the main view, locate and select LINQ to SQL Classes
Step 5: Give the item an appropriate name, and click Add
Step 6: You now have a blank LINQ to SQL Classes object in the project. Locate it, and double-click on it
Step 7: You should now see a blank page, with Server Explorer in the middle, click on and open it
Step 8: Under Data Connections, add your SQL Server's connection
Step 9: Drag the desired table (Users, in this case) onto the blank page.
Step 10: Save the entire solution
Ready and Done! But, how do I start using the "code"?
At this stage, all code for insert, update, delete, select, etc. has been done automatically for you by LINQ. By saving the document or project, you actually kick off the process that makes the desired tables now available in code, and you can now start using the entities and functions. You reference them in your code by the same name you used to add the LINQ to SQL Classes item.
Good luck! Watch out for my next article - more on LINQ and Relational Databases
