Click here to Skip to main content
15,867,833 members
Articles / Programming Languages / SQL

LINQ vs ADO.Net - A Practical Overview

Rate me:
Please Sign up or sign in to vote.
4.09/5 (3 votes)
17 Apr 2013CPOL5 min read 44.2K   480   18   9
From a developer's perspective, a comparative article

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:

Image 1

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:

C#
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:

C#
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.

C#
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

Image 2

Image 3

Image 4

 Image 5

Step 9: Drag the desired table (Users, in this case) onto the blank page.

Image 6

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Private
South Africa South Africa
I am addicted to knowledge, technology, gadgets and tinkering, and everything and anything science related. I am a real propeller-head and a geek, and proud at that.

Recently - I find myself tinkering again - back to basics! Integrating low level hardware with high level user experience. Full stack and mainframe development gets repetitive sometimes

Comments and Discussions

 
GeneralMy vote of 2 Pin
cjb11017-Apr-13 21:28
cjb11017-Apr-13 21:28 
GeneralRe: My vote of 2 Pin
J van Rooyen17-Apr-13 23:46
J van Rooyen17-Apr-13 23:46 
Hi,

Thanks. I'll review and improve all articles based on comments.


Regards,

Jeann
QuestionSuggestion Pin
Ranjan.D17-Apr-13 5:25
professionalRanjan.D17-Apr-13 5:25 
AnswerRe: Suggestion Pin
J van Rooyen17-Apr-13 23:51
J van Rooyen17-Apr-13 23:51 
GeneralRe: Suggestion Pin
Ranjan.D18-Apr-13 1:25
professionalRanjan.D18-Apr-13 1:25 
QuestionGetDataUsingADO Pin
Paulo Zemek17-Apr-13 3:52
mvaPaulo Zemek17-Apr-13 3:52 
AnswerRe: GetDataUsingADO Pin
J van Rooyen17-Apr-13 23:58
J van Rooyen17-Apr-13 23:58 
GeneralRe: GetDataUsingADO Pin
Paulo Zemek18-Apr-13 3:33
mvaPaulo Zemek18-Apr-13 3:33 
GeneralRe: GetDataUsingADO Pin
J van Rooyen18-Apr-13 22:20
J van Rooyen18-Apr-13 22:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.