Comparing LINQ to SQL vs the classic SqlCommand

Tags: | Categories: Blog Posted by admin on 3/7/2010 3:43 PM | Comments (3)


When you are coming from using SqlCommand and SqlConnection is difficult to move to another library for your database needs. For those people still in the limbo to make the decision to move to another DAL, here is a comparison to help you see the light or to move away for ever.

 

How to do a select query using SqlCommand:

   1: SqlConnection myConnection = new SqlConnection(@"Data Source=PROG-3407\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True;Pooling=False");
   2:  
   3:             string sQuery = "SELECT * FROM Table1";
   4:  
   5:             SqlCommand myCommand = new SqlCommand(sQuery);
   6:             myCommand.Connection = myConnection;
   7:  
   8:             myConnection.Open();
   9:  
  10:             SqlDataReader dataReader = myCommand.ExecuteReader();
  11:  
  12:             while (dataReader.Read() == true)
  13:             {                
  14:                 string sID = dataReader[0].ToString();
  15:                 string sName = dataReader[1].ToString();
  16:                 string sCity = dataReader[2].ToString();
  17:  
  18:                 MyData dat = new MyData()
  19:                 {
  20:                     ID = Int32.Parse(sID),
  21:                     Name = sName,
  22:                     City = sCity
  23:                 };
  24:                 myDataCollection.Add(dat);                
  25:             }
  26:  
  27:             GridView1.DataSource = myDataCollection;
  28:             GridView1.DataBind();
  29:  
  30:             dataReader.Close();
  31:             myConnection.Close();

And now a select query in LINQ to SQL

 

   1: Table1DataContext dataContext = new Table1DataContext();
   2:                        
   3:            var all = from p in dataContext.Table1s orderby p.City select p;
   4:            
   5:            GridView1.DataSource = all;
   6:            GridView1.DataBind();

Pretty short isn’t it?, the select statement is inline with a little help from intellisense.

 

Insert in SqlCommand

   1: using (SqlConnection myConnection = new SqlConnection(@"Data Source=PROG-3407\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True;Pooling=False"))
   2:             {                
   3:                 string sQuery = "INSERT INTO Table1 (Name,City) values(@Name,@City)";
   4:  
   5:                 SqlCommand myCommand = new SqlCommand(sQuery);
   6:                 myCommand.Connection = myConnection;
   7:  
   8:                 myConnection.Open();
   9:                                 
  10:                 myCommand.Parameters.AddWithValue("@Name", TextBoxName.Text);
  11:                 myCommand.Parameters.AddWithValue("@City", TextBoxCity.Text);
  12:  
  13:                 myCommand.ExecuteNonQuery();
  14:             }

 

Insert in LINQ in SQL

   1: Table1DataContext dataContext = new Table1DataContext();
   2:  
   3:             Table1 row = new Table1()
   4:             {
   5:                 City = TextBoxCity.Text,
   6:                 Name = TextBoxName.Text
   7:             };
   8:  
   9:             dataContext.Table1s.InsertOnSubmit(row);
  10:             dataContext.SubmitChanges();

 

Hope this helps

Cheers

Al

Comments

on 3/8/2010 9:59 AM

Not really impressed. How do you set all 50,000 records that have WhateverId set to 3 in the 5th state in LINQ to SQL ? Or maybe it's not 50,000 records but 5,000,000 records? You retrieve every f#@($ record and update it then submit it. Not nice.


EF/L2S is not really the silver bullet.


on 3/10/2010 4:28 AM

@andrei: You still have DataContext.ExecuteCommonad and DataContext.ExecuteQuery for bulk operations like you mentioned. No reason to give up the advantages pointed out by Al, far more common scenarios in application development, to deal with your edge case example.


on 3/10/2010 6:13 AM

@andrei: There are some very specific things that you are not mentioning here. Why would your client application be updating that much data ?


More importantly, why wouldn't you call a stored procedure on a bulk operation that large.


What you are describing would not be a wise choice for either SQLCommand or LINQ2SQL unless you wrote a specific query and did a straight execute.


Comments are closed