Working with LINQ Using Visual Studio 2005
page 1 of 1
Published: 30 May 2006
Unedited - Community Contributed
Abstract
Language Integrated Query (LINQ) technology contains .NET Framework enhancements to support querying data. This is an extension to VB and C# language and integrates to Visual Studio 2005. This article examines the development of web applications using the May 2006 CTP release of LINQ.
by Satheesh Kumar
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 44850/ 25

Working with LINQ Using Visual Studio 2005

LINQ is the general-purpose standard query operator that allows traversal, filter and projection operations to be expressed in a direct yet declarative way in any .NET programming language.  The extensibility of the LINQ query language provides implementations that work over both XML and SQL data.  The query operator over XML is called XLINQ and the one over SQL is called DLINQ.  XLINQ is an efficient and in-memory XML facility to provide XPath/XQuery functionality.  DLINQ operator is for the integration of SQL based schema definitions into the CLR type System.        

LINQ will be fully integrated with the next release of the Visual Studio, which is named as Orcas now.  The best thing is that the LINQ can also be installed in Visual Studio 2005.  The May 2006 CTP release of LINQ is available and can be downloaded from this link.  Let us see how to create a new web site that uses LINQ features.

Creating C# LINQ ASP.NET Web site

After installing the May CTP download of the LINQ, Open Visual Studio 2005 and click the link for creating a new website: File | New | Web Site.

Figure 1

The project will be created with the same files we use with the normal website that we create. There is a minor difference in web.config under the configuration section.  You can see a new section as codedom to specify the compiler and the language.  The Visual Studio 2005 should use the new C# 3.0 compiler.

Listing 1

<system.codedom>
  <compilers>
  <compiler language="c#;cs;csharp" extension=".cs"
  type="Microsoft.CSharp.CSharp3CodeProvider, CSharp3CodeDomProvider"/>
 </compilers>
</system.codedom>

Open the Default.aspx file, drag and drop DropDownList and GridView controls onto the design surface.  Right click on the project folder in the solution explorer and add the App_code folder from the ASP.NET folders list.

Let us see a simple example of creating a web page, which shows the list of current open positions in an organization under different technologies.  So whenever the user selects the technology in the drop down box, the GridView should show the list of current open positions in the organization under the selected technology.  I created two SQL tables, one for storing the list of technologies and the other one for storing the current open positions for each technology.  We do not need to go into the details of creating the tables; assume that the tables are created.

Add two C# class files to the App_Code directory, one for the business logic and other one for the data access.  Create an entity class as PositionDetails to hold the open position information and add the following code to the class file.

Listing 2

using System;
using System.Collections.Generic;
using System.Data;
using DataAccessLayer;
 
namespace BusinessLogicLayer
{
  public class PositionDetails
  {
    private string _technology;
    private string _skillSet;
    private string _details;
    private string _education;
    private string _experience;
    private DateTime _openPositionDate;
    private string _numberofPosOpen;
 
    public string Technology
    {
      get
      {
        return _technology;
      }
      set
      {
        _technology = value;
      }
    }
 
    public string SkillSet
    {
      get
      {
        return _skillSet;
      }
      set
      {
        _skillSet = value;
      }
    }
 
    public string Details
    {
      get
      {
        return _details;
      }
      set
      {
        _details = value;
      }
    }
 
    public string Education
    {
      get
      {
        return _education;
      }
      set
      {
        _education = value;
      }
    }
 
    public string Experience
    {
      get
      {
        return _experience;
      }
      set
      {
        _experience = value;
      }
    }
 
    public string NumberofPosOpen
    {
      get
      {
        return _numberofPosOpen;
      }
      set
      {
        _numberofPosOpen = value;
      }
    }
 
    public DateTime OpenPositionDate
    {
      get
      {
        return _openPositionDate;
      }
      set
      {
        _openPositionDate = value;
      }
    }
 
    public PositionDetails(string technology,string skillset, string details,
      string education, string experience,string NumberofPosOpen, DateTime
      openPositionDate)
    {
      _technology = technology;
      _skillSet = skillset;
      _details = details;
      _education = education;
      _experience = experience;
      _numberofPosOpen = NumberofPosOpen;
      _openPositionDate = openPositionDate;
    }
 
  }
 
 
  public class ListPositionDetails
  {
    private int _tech;
    public int Technology
    {
      get
      {
        return _tech;
      }
      set
      {
        _tech = value;
      }
    }
 
    public List < PositionDetails >ListPosDetails
    {
      get
      {
        DataAccess DALLayer =DataAccessHelper.GetDataAccess();
        List < PositionDetails > PostDet =
         DALLayer.GetOpenPositionsforTechnology(Technology);
        return PostDet;
      }
    }
 
    public ListPositionDetails(int Tech)
    {
      Technology = Tech;
    }
  }
 
}

The first one is the entity class with the name PositionDetails and it has a constructor in it to initialize the properties.  The second class file is the one that holds the list of Position details which has a method which calls a method in the Data access layer.  This connects to the database and retrieves the list of current open positions for the selected technology in the dropdown box, which is passed as a parameter to the method. The Data Access layer will look like the one below.

Listing 3

public override List < PositionDetails >GetOpenPositionsforTechnology(int
  Technology)
{
  PositionDetails temp1 = null;
 
  using(SqlConnection connection = newSqlConnection
   (ConfigurationManager.ConnectionStrings["connection"].ConnectionString))
  {
    using(SqlCommand command = newSqlCommand("GetPositionDetailsforTechnology",
      connection))
    {
      command.CommandType =CommandType.StoredProcedure;
      command.Parameters.Add(newSqlParameter("@Technology", Technology));
      connection.Open();
      List < PositionDetails > list = newList < PositionDetails > ();
      using(SqlDataReader reader =command.ExecuteReader())
      {
        if (reader.HasRows)
        {
          while (reader.Read())
          {
            temp1 = newPositionDetails((string)reader["Technology"], (string)
              reader["SkillSet"],(string)reader["Details"], (string)reader[
              "Education"],(string)reader["Experience"], (string)reader[
              "NumberofPosOpen"],(DateTime)reader["OpenPositionDate"]);
            list.Add(temp1);
          }
        }
        return list;
      }
    }
  }
}

Now using the above class files, let us see how we can make use of LINQ operations to bind the data to the GridView control.

Select the DropDownList, which shows the list of technologies, and add the following code for the SelectedIndexChanged event.

Listing 4

ListPositionDetails PosDetList = newListPositionDetails(Convert.ToInt32
  (TechnologyList.SelectedValue));
GridViewLINQ.DataSource = from PositionDetailsin PosDetList.ListPosDetails
  orderby PositionDetails.Experience selectPositionDetails;
GridViewLINQ.DataBind();

The DataSource for the GridView component is a direct SQL like statement, which makes use of the PosDetList object.  The PosDetList has the collection of current open positions for the selected technology.  This is similar to an SQL Statement and is dynamic.

The best thing is that the LINQ is strongly-typed. If I used SQL statement to fetch the details, I would not be able to get the errors in my SQL statement at design time. But here, the compiler will give the errors in the queries during compile time itself.

Figure 2

 

We can have conditions in queries.  For example, if I want to list all open positions for the selected .NET technology which requires more than 3 years of experience, then I can change the query as shown below.

Listing 5

ListPositionDetails PosDetList = newListPositionDetails(Convert.ToInt32
  (TechnologyList.SelectedValue));
GridViewLINQ.DataSource = from PositionDetailsin PosDetList.ListPosDetails
  whereConvert.ToInt32(PositionDetails.Experience) > 3 orderby
  PositionDetails.Experience selectPositionDetails;
GridViewLINQ.DataBind();

Now, the Grid displays only the records which have the Experience field value greater than 3.

Figure 3

Now we will change the query so that only two records are listed in the grid.  The query will look like the following.

Listing 6

ListPositionDetails PosDetList = newListPositionDetails(Convert.ToInt32
  (TechnologyList.SelectedValue));
GridViewLINQ.DataSource = (from PositionDetailsin PosDetList.ListPosDetails
  orderby PositionDetails.Experience selectPositionDetails).Take(2);
GridViewLINQ.DataBind();

Visual Studio 2005 provides the intellisense help when you write queries as give below.

Figure 4

Using Anonymous Types

LINQ takes the advantage of the Anonymous types.  This allows us to create types and use it inline without declaring the object model as we do normally.  This is very useful to customize the data.  In all the above examples you can see the total number of fields shown in the grid is six.  We may not need all the fields at all times.  Sometimes we may want to hide or show only the required fields.  Let us see how to do this easily using LINQ.  Change the query like the one below.

Listing 7

ListPositionDetails PosDetList = newListPositionDetails(Convert.ToInt32(TechnologyList.SelectedValue)); 
 
GridViewLINQ.DataSource = from PositionDetailsin PosDetList.ListPosDetails
orderby PositionDetails.Experience
select new {
 PositionDetails.SkillSet,
 PositionDetails.Experience, 
 PositionDetails.Details
};
GridViewLINQ.DataBind();

In the above code we can see only three fields in the select statement, which retrieves only three fields instead of all six fields.

Summary

The above examples are just an introduction to LINQ.  There are lots of cool features that come with DLINQ and XLINQ.  Using LINQ we do not need to create more controls and entity classes as we do normally.  There are lots of properties and methods provided for the LINQ queries.  There is a DLINQ designer by which we can create the LINQ Objects without writing any code and creating the user interface forms for the applications much faster.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 9/20/2014 1:56:28 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search