Accessing Data in a SharePoint 2010 Web Part by using LINQ

Article
07/24/2014

SharePoint QuickStart Banner

Getting Started with Data Access in SharePoint 2010: Learn how to create a SharePoint 2010 Web Part that retrieves data from lists by using LINQ.

Applies to: Office 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | Visual Studio | Visual Studio 2010

In this article
Create a Visual Web Part Project
Generate LINQ to SharePoint Proxy Code
Add Code to Read Data from a SharePoint List
Deploy and Add the Web Part to a Web Part Page
Next Steps

Published: May 2010

Provided by: Frank Rice, Microsoft Corporation

Watch the video: Access data in SharePoint 2010 web parts by using LINQ

In this exercise, you develop and deploy a visual Web Part that reads data from two lists by using a LINQ query and displays it in a DataGrid control. To complete this task, you must do the following:

Create a Visual Web Part Project
Generate LINQ to SharePoint Proxy Code
Add Code to Read Data from a SharePoint List
Deploy and Add the Web Part to a Web Part Page

Create a Visual Web Part Project

In this task, you create a Visual Web Part project in Microsoft Visual Studio 2010.

Note

The following tasks assume that there are lists titled Employees and Projects on the default SharePoint website. Find instructions about how to create these two lists in Create Linked Lists in SharePoint 2010.

To create the Visual Web Part

Start Visual Studio 2010, click File, point to New, and then click Project.
Navigate to the Visual C# node in the Installed Templates section, click SharePoint, and then click 2010.
Select the Visual Web Part project template, provide a name (such as AccessSPDatawithLINQ) and a location for your project, and then click OK.
In the What local site do you want to use for debugging drop-down list, select the site to use (such as https://localhost/sites/MySampleWebSite). Also select the Deploy as a farm solution option and then click Finish.

Note that after the project is created, Solution Explorer contains the visual Web Part named VisualWebPart1 (see Figure 1).

Figure 1. AccessSPDatawithLINQ project in Solution Explorer

Generate LINQ to SharePoint Proxy Code

In this task, you use the spmetal.exe code-generation utility to generate the LINQ to SharePoint proxy code. The tool generates entity class code that Visual Studio 2010 uses as an interface to obtain IntelliSense and enable LINQ-based queries to be performed on SharePoint Server 2010 lists.

To generate the LINQ proxy code

In Solution Explorer, right-click AccessSPDatawithLINQ and then click Open Folder in Windows Explorer.
Press and hold the SHIFT key, right-click the Explorer window, and then click Open command window here to open the command window in the current project directory.
In the command window, type the following command and press the ENTER key to set the path of the SharePoint Server 2010 folder.

set path=%path%;c:\program files\common files\microsoft shared\web server extensions\14\bin
In the command window, type the following command and press the ENTER key to generate the LINQ to SharePoint proxy code. Replace the SharePoint site URL with your own site's URL.

spmetal.exe /web:https://localhost/sites/MySampleWebSite /namespace:AccessSPDatawithLINQ.VisualWebPart1 /code:SPLinq.cs

This command creates the SPLinq.cs file in the AccessSPDatawithLINQ project folder.
Close the command window.
Now add the file to the project. Switch back to the Visual Studio project screen.
In Solution Explorer, right-click AccessSPDatawithLINQ, point to Add, and then click Existing Item.
Select SPLinq.cs from the Add Existing Item dialog window and then click Add.
In Solution Explorer, right-click References and then click Add Reference.
Click the Browse tab and type C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI in the File Name box.
Select Microsoft.SharePoint.Linq.dll and then click OK.

Add Code to Read Data from a SharePoint List

In this task, you add code to your solution that enables the visual Web Part to retrieve data from the SharePoint Server 2010 lists.

To add code to the project

In Solution Explorer, expand VisualWebPart1 and then double-click VisualWebPart1UserControl.ascx. Visual Studio displays the HTML for the visual Web Part user control.

Add the following code at the bottom of the existing code to construct the grid view.

<%@ Import Namespace="Microsoft.SharePoint.WebControls" %> 
<SharePoint:SPGridView id="spGridView" runat="server" AutoGenerateColumns="false"> 
  <HeaderStyle HorizontalAlign="Left" ForeColor="Navy" Font-Bold="true" /> 
  <Columns> 
    <SharePoint:SPBoundField  DataField="Title" HeaderText="Title"></SharePoint:SPBoundField> 
    <SharePoint:SPBoundField DataField="JobTitle" HeaderText="JobTitle"></SharePoint:SPBoundField> 
    <SharePoint:SPBoundField DataField="ProjectTitle" HeaderText="ProjectTitle"></SharePoint:SPBoundField> 
    <SharePoint:SPBoundField DataField="DueDate" HeaderText="DueDate"></SharePoint:SPBoundField> 
  </Columns> 
</SharePoint:SPGridView>

The HTML for the visual Web Part user control should resemble Figure 2 after you add the code.

Figure 2. Complete code in the visual Web Part user control

Code in visual Web Part user control

In Solution Explorer, right-click VisualWebPart1UserControl.ascx and then click View Code.

Add the following statements to the code at the top of the code screen.

using Microsoft.SharePoint.Linq; 
using Microsoft.SharePoint; 
using System.Linq;

Insert the following code inside the Page_Load method.

SPLinqDataContext dc = new SPLinqDataContext(SPContext.Current.Web.Url);  
EntityList<EmployeesItem> Employees = dc.GetList<EmployeesItem>("Employees");  
var empQuery = from emp in Employees 
               where emp.Project.DueDate < DateTime.Now.AddMonths(6) 
               select new 
               { 
                   emp.Title, 
                   emp.JobTitle, 
                   ProjectTitle = emp.Project.Title, 
                   DueDate = emp.Project.DueDate.Value.ToShortDateString() 
               };  
spGridView.DataSource = empQuery; 
spGridView.DataBind();

Deploy and Add the Web Part to a Web Part Page

In this task, you deploy the Web Part and add it to the Home page on the SharePoint Server 2010 website.

To deploy and test the visual Web Part

In Solution Explorer, right-click AccessSPDatawithLINQ and then click Deploy. This builds and deploys the visual Web Part to the SharePoint site that you specified earlier.
Open Internet Explorer and browse to the SharePoint site.
Click the Edit icon at the top of the Home page to start to edit the page.
On the ribbon, on the Editing Tools tab, click Insert, and then click Web Part.
In the Categories section, click Custom, and in the Web Parts section, click SPLinqDemoTitle, and then click Add as shown in Figure 3.

Figure 3. Select the SPlinqDemoTitle Web Part

This adds the SPLinqDemoTitle Web Part to the Home page as shown in Figure 4.

Figure 4. SPLinqDemoTitle Web Part added to the Home page
On the ribbon, click Stop Editing to save the page and stop editing.
Close Internet Explorer.