Partager via

LINQ to SQL and ADO.NET

  • Article

Here I am going to compare the two technologies and point some missing areas.

 

Let us use the Northwind database and get the list of Customers from city London. The ADO.NET approach would be,

 

static void Main(string[] args)

{

  string sConn = @"ConnectionString";

    using (SqlConnection conn = new SqlConnection(sConn))

    {

        using (SqlCommand comm = new SqlCommand

             ("SELECT CustomerId, CompanyName FROM " +

                   " Customers WHERE City = @City", conn))

        {

            conn.Open();

            comm.Parameters.AddWithValue("@City", "London");

            SqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())

            {

                string sCID = reader[0].ToString();

                string sCompanyName = reader[1].ToString();

            }

        }

    }

}

 

Now this code will compile and execute perfectly. But if I change this code a bit the compiler will not throw any error but runtime errors will be there.

 

using (SqlConnection conn = new SqlConnection(sConn))

{

    using (SqlCommand comm = new SqlCommand

                ("SELECTBlah CustomerId, CompanyName FROM " +

                 " Customers WHERE City = @City", conn))

    {

        conn.Open();

        comm.Parameters.AddWithValue("@City", 12.4);

        SqlDataReader reader = comm.ExecuteReader();

        while (reader.Read())

        {

            string sCID = reader[0].ToString();

            string sCompanyName = reader[1].ToString();

            string sXYZ = reader[2].ToString();

        }

    }

}

 

Highlighted areas will throw me runtime error.

 

Ø Not only the SQL keywords (SELECTBlah) but any problem there will throw error at runtime.

Ø Adding parameter with value which is not supported (float)

Ø Reading the third column value which I am actually not retrieving through my SQL query.

Ø I am converting everything to String but there might be null values and moreover this is not strongly typed.

 

Now with this approach people already have developed lot of projects. ADO.NET is powerful but these are the commonly faced drawbacks. To avoid all the errors we need to invest in Testing. This increases the product cost in the market.

 

In LINQ to SQL how this can be achieved easily,

 

Create mapping class for the customer Table

[Table(Name="Customers")]

class Customer

{

    [Column]

    public string CustomerId { get; set; }

    [Column]

    public string CompanyName { get; set; }

}

 

Now directly using the DataContext I can get the output.

 

DataContext db = new DataContext(sConn);

var q = from c in db.GetTable<Customer>()

        where c.City == "London"

        select new

        {

            CustomerId = c.CustomerId,

            CompanyName = c.CompanyName

        };

foreach (var k in q)

{

    Console.WriteLine(k.CustomerId + " : " + k.CompanyName);

}

 

Here things I cannot do,

Ø Cannot mistype any query keyword as it is native statement (not in “”)

Ø Cannot pass type to condition which is not supported

Ø Cannot project the output with any value

Ø Moreover the output is IEnumerable of some type so the output is strongly typed

 

There are lot of other benefits like, you will get intellisense and the statement completion. Moreover all the previous ADO.NET runtime errors will become compile time errors.

 

Namoskar!!!

Comments