Wriju's BLOG

.NET, Cloud and everything

LINQ to SQL vs. ADO.NET – A Comparison

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!!!