LINQ - Retrive data from database using Linq to SQL

LINQ to SQL is a facility for managing and accessing relational data as objects. It’s logically similar to ADO.NET in some ways but views data from a more abstract perspective that simplifies many operations. It connects to a database, converts LINQ constructs into SQL, submits the SQL, transforms results into objects, and can even track changes and automatically request database updates.

A simple LINQ query requires three things:

• Entity Class
• Data Context
• Linq Query

Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server

Step 3
Create a Web application and give solution name as SolLinqToSql.

Step 4
In this example we have to bind employee data from northwind database into a gridview. add a gridview on page it is look like this

<asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>  

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                
                <asp:GridView ID="GvEmployee" runat="server" AllowPaging="True" 
                    BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" 
                    CellPadding="3" EnableModelValidation="True" ForeColor="Black" 
                    GridLines="Vertical" PageSize="5" 
                    onpageindexchanging="GvEmployee_PageIndexChanging">
                    <AlternatingRowStyle BackColor="#CCCCCC" />
                    <FooterStyle BackColor="#CCCCCC" />
                    <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                </asp:GridView>
                
            </ContentTemplate> 
        </asp:UpdatePanel>   

Step 5
Add a connectionstring on web.config file,it is look like this

<connectionStrings>
        <add name="ConStr" connectionString="Data Source=shree\shree;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Step 6
Add app_code folder in the solution and add a new folder inside the app_code folder and give folder name as ORD,it is look like this

Click on image for better view

Step 7

Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this

Click on image for better view

Step 8

Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this

Click on image for better view

Click on image for better view

Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 9

Create a Employee object.

in this example we have to work with employees table from the northwind database,create a employee object that will use LINQ to SQL to map to this table.go to the server explorer,select northwind database,go to the tables and select Employees table,it is look like this

Click on image for better view

Drag and drop Employee table from Server explorer onto the design surface of the O/R Designer,it is look like this

Click on image for better view

Step 10

Create a Employee static class in app_code folder for retriving an employee data from database,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;   

public static class Employee
{
    #region Methods

    /// <summary>
    /// Get the Employee data from northwind database
    /// </summary>
    /// <returns>IList</returns>
    public static IList GetEmployeeData()
    {
        try
        {
            // Create a object of DataContext and specify the connectionstring in datacontext constructor
            // The NorthwindDCDataContext object is an object of type of DataContext
            // This object works with the connectionstring and connect to the database for any required operation
            ORD.NorthwindDCDataContext DC = new ORD.NorthwindDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);

            // Create a select Linq Query
            var Query = from Q in DC.Employees
                        select new 
                        { 
                            FirstName=Q.FirstName,
                            LastName=Q.LastName,
                            Title=Q.Title,
                            City=Q.City,
                            Country=Q.Country 
                        };

            return Query.ToList();  // return the linq query
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion
}

Step 11
Bind the employee data in gridview.it is look like this

 protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                BindGridView(); 
            }
        }
        catch (Exception)
        { }
    }
    #region Methods
    /// <summary>
    /// Bind the Employee data in GridView
    /// </summary>
    private void BindGridView()
    {
        try
        {
            IList ILEmployeeData = Employee.GetEmployeeData();

            if (ILEmployeeData != null)
            {
                if (ILEmployeeData.Count > 0)
                {
                    GvEmployee.DataSource = ILEmployeeData;
                    GvEmployee.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion

Run the project

Full Code

1. .aspx code

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>  

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                
                <asp:GridView ID="GvEmployee" runat="server" AllowPaging="True" 
                    BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" 
                    CellPadding="3" EnableModelValidation="True" ForeColor="Black" 
                    GridLines="Vertical" PageSize="5" 
                    onpageindexchanging="GvEmployee_PageIndexChanging">
                    <AlternatingRowStyle BackColor="#CCCCCC" />
                    <FooterStyle BackColor="#CCCCCC" />
                    <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                </asp:GridView>
                
            </ContentTemplate> 
        </asp:UpdatePanel>   
    </div>
    </form>
</body>
</html>

2. Code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                BindGridView(); 
            }
        }
        catch (Exception)
        { }
    }
    #region Methods
    /// <summary>
    /// Bind the Employee data in GridView
    /// </summary>
    private void BindGridView()
    {
        try
        {
            IList ILEmployeeData = Employee.GetEmployeeData();

            if (ILEmployeeData != null)
            {
                if (ILEmployeeData.Count > 0)
                {
                    GvEmployee.DataSource = ILEmployeeData;
                    GvEmployee.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion

    protected void GvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            GvEmployee.PageIndex = e.NewPageIndex;
            BindGridView(); 
        }
        catch (Exception)
        { }
    }
}

Download
Download Source Code