Accessing Data in a SharePoint 2010 Web Part by using LINQ

Task yo complete: Ojective for this exercise is to 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.

To create the Visual Web Part

1.     Start Visual Studio 2010, click File, point to New, and then click Project.

2.     Navigate to the Visual C# node in the Installed Templates section, click SharePoint, and then click 2010.

3.     Select the Visual Web Part project template, provide a name (such as AccessSPDatawithLINQ) and a location for your project, and then click OK.

4.     In the What local site do you want to use for debugging drop-down list, select the site to use (such as http://localhost/sites/MySampleWebSite). Also select theDeploy 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 

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

1.     In Solution Explorer, right-click AccessSPDatawithLINQ and then click Open Folder in Windows Explorer.

2.     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.

3.     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

4.     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:http://localhost/sites/MySampleWebSite /namespace:AccessSPDatawithLINQ.VisualWebPart1 /code:SPLinq.cs

This command creates the SPLinq.cs file in the AccessSPDatawithLINQ project folder.

5.     Close the command window.

6.     Now add the file to the project. Switch back to the Visual Studio project screen.

7.     In Solution Explorer, right-click AccessSPDatawithLINQ, point to Add, and then click Existing Item.

8.     Select SPLinq.cs from the Add Existing Item dialog window and then click Add.

9.     In Solution Explorer, right-click References and then click Add Reference.

10. Click the Browse tab and type C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI in the File Name box.

11. 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

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

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

HTML

<%@ 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.

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

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

using Microsoft.SharePoint.Linq;

using Microsoft.SharePoint;

using System.Linq;

5.     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

1.     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.

2.     Open Internet Explorer and browse to the SharePoint site.

3.     Click the Edit icon at the top of the Home page to start to edit the page.

4.     On the ribbon, on the Editing Tools tab, click Insert, and then click Web Part.

5.     In the Categories section, click Custom, and in the Web Parts section, click SPLinqDemoTitle, and then click Add as shown in Figure 3.

On the ribbon, click Stop Editing to save the page and stop editing.

6.     Close Internet Explorer.