LINQ to SQL versus ADO.NET

• Download source code - 37.4 KB

Introduction

The article shows a simple comparison between LINQ to SQL and ADO.NET with regards to the execution time of selecting data from a SQL Server database. So, what is LINQ to SQL? LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically. LINQ to SQL gives you your database, tables, and columns as objects that you can use directly in your C# or VB.NET code. Here, we are going to test it with ADO.NET:

myDatabase.CustomersTable.CustomerNameColumn;

How to add LINQ

• LINQ is shipped with Microsoft Visual Studio 2008, you can download the beta for free from Microsoft.
• Open Visual Studio and create a new Windows Application project.
• Add New Item and choose LINQ to SQL classes.



• You will get a class .dbml that is inherited from DataContext.
• Now, from the Server Explorer, make a connection to the AdventureWorks database and then drag and drop the table Product in the designer.



• Move to the code..

Using the code

This application uses both ADO and LINQ, and calculates the time they take to select data from the AdventureWorks database. We are going to do something like select * from Products. In the LINQ button click event..

//AdventureWorksDataContext is the dbml class we have already added above

AdventureWorksDataContext AdventureDB = new AdventureWorksDataContext(
            "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
//Products is the table we draged and dropped in the dbml designer
var _data = from _products in AdventureDB.Products 
            select _products;
dataGridViewLinq.DataSource = _data;
//Thats it!

In the ADO button click event:

SqlDataAdapter adapter = new SqlDataAdapter("select * from Production.Products",
            "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
DataSet ds = new DataSet();
int x = adapter.Fill(ds);
dataGridViewADO.DataSource = ds.Tables[0];

Let's make it more complex; drag and drop the ProductCategroy table and the ProductSubCategory table in the DBML designer:

Using LINQ:

var _t = from o in AdventureDB.ProductSubcategories
                     //here we use the relations between these tables

                     where o.ProductCategory.ProductSubcategories.Count > 3
                     select new
                     {
                         o.ProductCategoryID,
                         o.Name,
                     };
                     
dataGridViewLinq.DataSource = _t;

Using ADO.NET:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT [t0].[ProductCategoryID],
     [t0].[Name]"
    + "FROM [Production].[ProductSubcategory] AS [t0]"
    + "WHERE (("
    + "SELECT COUNT(*)"
    + "FROM [Production].[ProductCategory] AS [t1],
         [Production].[ProductSubcategory] AS [t2]"
    + "WHERE ([t1].[ProductCategoryID] = [t0].[ProductCategoryID]) AND (
         [t2].[ProductCategoryID] = [t1].[ProductCategoryID])"
    + ")) > 3",
         "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
DataSet ds = new DataSet();
int x = adapter.Fill(ds);

dataGridViewADO.DataSource = ds.Tables[0];

The code:

private void buttonLinq_Click(object sender, EventArgs e)
{
    int startS = DateTime.Now.Second;
    int startM = DateTime.Now.Millisecond;


    AdventureWorksDataContext AdventureDB = new AdventureWorksDataContext(
   "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
    //var _data = from _products in AdventureDB.Products
    //                 select _products;
    //dataGridViewLinq.DataSource = _data;

    var _t = from o in AdventureDB.ProductSubcategories
             where o.ProductCategory.ProductSubcategories.Count > 3
             select new
             {
                 o.ProductCategoryID,
                 o.Name,
             };
    dataGridViewLinq.DataSource = _t;

    #region time_Calculation
    int endS = DateTime.Now.Second;
    int endM = DateTime.Now.Millisecond;
    if (endS == startS)
    {
        labelLinqTime.Text = (endM - startM).ToString();
    }
    else if (endS > startS && endS < startS + 2)
    {
        labelLinqTime.Text = (endM + 1000 - startM).ToString();
    }
    else
    {
        //if it took more than 2 seconds.. 
        throw new Exception("timeout");
    }
    #endregion

}

private void buttonADO_Click(object sender, EventArgs e)
{
    int startS = DateTime.Now.Second;
    int startM = DateTime.Now.Millisecond;

    SqlDataAdapter adapter = new SqlDataAdapter(
          "SELECT [t0].[ProductCategoryID], [t0].[Name]"
        + "FROM [Production].[ProductSubcategory] AS [t0]"
        + "WHERE (("
        + "SELECT COUNT(*)"
        + "FROM [Production].[ProductCategory] AS [t1],
            [Production].[ProductSubcategory] AS [t2]"
        + "WHERE ([t1].[ProductCategoryID] = [t0].[ProductCategoryID]) AND (
            [t2].[ProductCategoryID] = [t1].[ProductCategoryID])"
        + ")) > 3",
          "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
    DataSet ds = new DataSet();
    int x = adapter.Fill(ds);
    dataGridViewADO.DataSource = ds.Tables[0];

    #region time_calculation
    int endS = DateTime.Now.Second;
    int endM = DateTime.Now.Millisecond;
    if (endS == startS)
    {
        labelADOTime.Text = (endM - startM).ToString();
    }
    else if (endS > startS && endS < startS + 2)
    {
        labelADOTime.Text = (endM + 1000 - startM).ToString();
    }
    else
    {
        //if it took more than 2 seconds.. 

        throw new Exception("timeout");
    }
    #endregion
}

Change the query each time to avoid caching. Learn more about LINQ to SQL here.