Partilhar via

LINQ to SQL vs. ADO.NET – A Comparison

  • Artigo

ADO.NET is our contemporary data access component and now we have written many applications. Now there has been a lot of talk on LINQ to SQL. So we are little skeptical about this component and trying to find some relation between existing ADO.NET technology.

Purpose of this post is to give some quick brief on some major differences between ADO.Net and LINQ to SQL. I have not used 100% code as this is just to give an idea. The database I have used here is Northwind (SQL Server 2000 Sample database).

Some comparison,

Scenario 1

+++++++

Establishing connection between database and application,

ADO.NET

using(SqlConnection conn = new SqlConnection("Connection String"))

{

    conn.Open();

}

LINQ to SQL

You create a .dbml file and give a name (assume “NW.dml”). Then there will be a DataContext class created. So you need to initialize the instance of an object.

NWDataContext db = new NWDataContext("Connection String");

You do not need to call any Open() method. Datacontext handles well the open and close method.

Scenario 2

+++++++

Getting data from database,

ADO.NET

using(SqlConnection conn = new SqlConnection("Connection String"))

{

    using (SqlCommand comm = new SqlCommand("Select * from Customers"))

    {

        conn.Open();

        SqlDataReader reader = comm.ExecuteReader();

        DataTable dt = new DataTable("New Table");

        dt.Load(reader);

    }

}

LINQ to SQL

using (NorthwindDataContext db = new NorthwindDataContext())

{

    //You can also use "var" at "IEnumerable<Customer>"

    IEnumerable<Customer> custs = from c in db.Customers

                                  select c;

    foreach (Customer c in custs)

    {

        Console.WriteLine(c.CompanyName);

   }

}

 

WOW!!! I have received comment from our Senior Program Manager Dinesh Kulkarni from LINQ to SQL team when I requested him to visit my blog. He mentioned,

DataTable provides you a cache that can be re-enumerated without DB roundtrip while LINQ to SQL results need to be explicitly cached with something like a ToList()/ToArray(). Identity caching in DataContext aside, the L2S code is closer to enumerating a DataReader. Although DataReader does not allow you to re-enumerate and requires another ExecuteReader, the impact of reenumerating L2S query is the same – another roundtrip to DB.

Scenario 3

+++++++

Inserting into the database,

ADO.NET

using(SqlConnection conn = new SqlConnection())

{

    conn.Open();

    SqlCommand comm = new SqlCommand("INSERT INTO...", conn);

    comm.ExecuteNonQuery();

}

LINQ to SQL

using (NorthwindDataContext db = new NorthwindDataContext())

{

    //Create a new object

    Customer c = new Customer();

    c.CustomerID = "ABCDE";

    //.... add all the properties you need to add while inserting

    //Add it to the collection

    db.Customers.InsertOnSubmit(c);

    //Save the changes to the database

    db.SubmitChanges();

}

Scenario 4

+++++++

Updating database,

ADO.NET

Same as Scenario 3

LINQ to SQL

using (NorthwindDataContext db = new NorthwindDataContext())

{

    //Get the object from database

    Customer cust = (from c in db.Customers where c.CustomerID == "ALFKI" select c).First();

    //Update the exsisting value

    cust.CompanyName = "I do not know?";

   

       

    //Save the changes to the database

    db.SubmitChanges();

}

Scenario 5

+++++++

Deleting records from the database,

ADO.NET

Same as Scenario 3

LINQ to SQL

using (NorthwindDataContext db = new NorthwindDataContext())

{

    //Get the object from database

    Customer cust = (from c in db.Customers where c.CustomerID == "ALFKI" select c).First();

    //Remove it from collection

    db.Customers.DeleteOnSubmit(cust);

       

    //Save the changes to the database

    db.SubmitChanges();

}

Scenario 5

+++++++

Executing stored proc which returns record(s),

ADO.NET

using(SqlConnection conn = new SqlConnection())

{

    conn.Open();

    using (SqlCommand comm = new SqlCommand("SalesByCategory", conn))

    {

        comm.CommandType = CommandType.StoredProcedure;

        comm.Parameters.AddWithValue("@param1", "value1");

        comm.Parameters.AddWithValue("@param2", "value2");

        SqlDataReader reader = comm.ExecuteReader();

    }

}

LINQ to SQL

In LINQ to SQL it becomes metod as you drag and drop it to .dbml file,

using (NorthwindDataContext db = new NorthwindDataContext())

{

    var outPut = db.SalesByCategory("SeaFood", "1998");

}

Performance of LINQ to SQL and ADO.NET

LINQ to SQL Performance Part 1

LINQ to SQL Performance Part 2

LINQ to SQL Performance Part 3

LINQ to SQL Performance Part 4

LINQ to SQL Performance Part 5

 

There are a many to discuss. Hope you have enjoyed it.

 

Namoskar!!!

Comments

  • Anonymous
    July 14, 2008
    PingBack from http://blog.a-foton.ru/2008/07/linq-to-sql-vs-adonet-%e2%80%93-a-comparison/

  • Anonymous
    July 14, 2008
    It would be nice to get some performance metrics for each of these scenarios.  Especially with ADO.NET using Stored Procedures.

  • Anonymous
    July 14, 2008
    LINQ-to-SQL e ADO .NET a confronto

  • Anonymous
    July 16, 2008
    @msDeveloper, I have updated the post with appropriate link. -Wriju

  • Anonymous
    July 17, 2008
    So many good articles and resources I have found over the last month or so. Here is a good sampling of

  • Anonymous
    May 11, 2009
    im wb developer from egypt i think that linq to sql make evolution in programming

  • Anonymous
    April 21, 2010
    Its too nice article between the linq to sql with Ado.net ,especilay for beginners.

  • Anonymous
    August 12, 2010
    This is the best article for a beginner like me to know what are the exact differences between ADO.Net and LINQ

  • Anonymous
    April 05, 2011
    Very useful. Thanks

  • Anonymous
    April 09, 2011
    VERY GOOD ARTICLE luqman_qau@yahoo.com

  • Anonymous
    May 15, 2011
    does any one knows!! DID MS implemented LINQ as a wrapper on Existing ADO.NET Technology, or this is totally different from ADO.NET?? if its build from scratch, then i guess we are gonna get lots of version of it very soon!!!!

  • Anonymous
    June 05, 2011
    Dear ,Too Good but I Actualy intrested to lern linkq but before start i want to know performance of linkq against to dataset,datareader for access and insert,update delete if u have any usefull link please send me to my emailID - sanjay30784@gmail.com

  • Anonymous
    August 15, 2011
    Well. you explained the difference b/w linq and ado.net very well.But which is best to use when we want to connect with the database....

  • Anonymous
    January 04, 2013
    But... LINQ in dotnet work with ADO.NET.

  • Anonymous
    October 24, 2013
    Really An Amazing Article for Beginners. Thank You Man :)

  • Anonymous
    November 10, 2013
    Why not simplify IEnumerable<Customer> custs = from c in db.Customers                              select c; foreach (Customer c in custs) {    Console.WriteLine(c.CompanyName); } to foreach (Customer c in db.Customers) {    Console.WriteLine(c.CompanyName); } It will do exactly the same, since LINQ-to-SQL queries are deferred until they're used, but it's less code.

  • Anonymous
    November 17, 2013
    Nice...