Walkthrough: Populating a Grid Control with Data from a LINQ Query

Artigo
10/20/2016

Applies to: SharePoint Foundation 2010

This walkthrough illustrates how to use a simple LINQ to SharePoint query to obtain selected fields of data from two different lists. The data is presented in a Web Part with a grid control. For an overview of querying with LINQ to SharePoint, see How to: Query Using LINQ to SharePoint.

Prerequisites

Ensure that the path to the SPMetal tool is part of your %PATH% environment variable. For more information, see How to: Add Tool Locations to the PATH Environment Variable.
Create a Microsoft SharePoint Foundation website named Contoso. The remainder of this walkthrough, including the code and markup assumes that this name is being used.
Create two lists on the website, named Employees and Projects. A default Title column is automatically present in each list. Add columns to the lists as shown in the following tables.

Employees List:

Column Name

Column Type

Remarks

Job Title

Single line of text

Team

Single line of text

Project

Lookup

Lookup to the Title column of the Projects list.

Projects List:

Column Name

Column Type

Remarks

Description

Single line of text

Due Date

Date and Time

Primary Contact

Lookup

Lookup to the Title column of the Employees list.
Populate both lists with some sample list items. Use the Title field of the Employees list to hold an employee name. Use the Title field of the Projects list to hold the name of the project. Some project due dates should be less than six months from now and others more than six months from now.

Column Name	Column Type	Remarks
Job Title	Single line of text
Team	Single line of text
Project	Lookup	Lookup to the Title column of the Projects list.

Column Name	Column Type	Remarks
Description	Single line of text
Due Date	Date and Time
Primary Contact	Lookup	Lookup to the Title column of the Employees list.

To set up the project

In Microsoft Visual Studio, start a Web Part project by selecting Visual Web Part on the SharePoint | 2010 section of the Installed Templates area on the New Project dialog box. Name the project ProjectsWithContacts, and click OK. The SharePoint Customization Wizard opens.
Specify the Contoso site as the debugging site, and specify that you want to deploy a farm solution, not a sandboxed solution. Click Finish.
In Solution Explorer, right-click the References node, and then click Add Reference. On the .NET tab in the Add Reference dialog box, select Microsoft.SharePoint.Linq. (It may help to alphabetize the component list by clicking the Component Name bar.) Click OK.
Right-click the project name in Solution Explorer, and select Properties.
On the Application tab of the Properties dialog box, enter Contoso.SharePoint.WebPartPages.ProjectsByContact as the Assembly name and Contoso.SharePoint.WebPartPages as the Default namespace. Leave the Target Framework set to .NET Framework 3.5.

Important

For projects based on any template in the SharePoint | 2010 section of the Installed Templates, the default Target framework is .NET Framework 3.5. Do not change this. The default value for the Solution Platforms box on the Visual Studio Standard Menu is "Any CPU". You have the option of changing this to "x64" with the Platform Target drop-down list on the Build tab of the Properties dialog box. For information about making the choice, see How to: Set the Correct Target Framework and CPU.
Click the Save all files button on the toolbar.
To automate the use of the SPMetal tool, add a text file to the project with the name Prebuild.bat.

Open Prebuild.bat if it is not already open. Add the following lines.

Echo Off
SET SPLANGEXT=cs

Echo Backing up previous version of generated code ... 
IF NOT EXIST .\PreviousVersionGeneratedCode MkDir .\PreviousVersionGeneratedCode
IF EXIST Contoso.%SPLANGEXT% xcopy /Y/V Contoso.%SPLANGEXT% .\PreviousVersionGeneratedCode

Echo Generating code ...
SPMetal /web:http://MyServer/Contoso /code:Contoso.%SPLANGEXT%

Make the following changes to the code, as needed:

Replace "MyServer/Contoso" with the full path to the Contoso website.

This batch file saves the previous version of the generated code so that you can compare it with the new version when you need to for debugging purposes.

Save the file, but do not save the file by clicking the Save or Save All Files button on the toolbar or by selecting Save on the File menu. The default encoding for text files in Visual Studio is not compatible with system batch files. Instead, on the File menu, select Save Prebuild.bat as. In the Save File As dialog box, click the down arrow on the Save button. Select Save with encoding. In the Advanced Save Options dialog box, in the Encoding drop-down list, select Unicode (UTF-8 without signature) – codepage 65001, and then click OK. Be sure to select the "without signature" version of UTF-8.

Tip

If the file is accidentally saved the wrong way, reopen it and make a small change, such as adding a space character, and resave it the correct way. It will not actually be resaved if you do not make a change.
Right-click the project name in Solution Explorer. Select Properties, and open the Build Events tab.
In the Pre-build event command line box (not Post-build), enter the following two lines.
```
cd $(ProjectDir)
Prebuild.bat
```
Click the Save All button.
On the Build menu, select Build Solution. This will invoke your batch file and, thus, SPMetal. SPMetal creates a Contoso.cs file in the project directory in Windows, but not in Solution Explorer. Add it to the project as an Existing Item.

The Contoso.cs is regenerated each time you rebuild the project. Although regenerating the file is not necessary for this walkthrough, it is a good practice to do this when you are using SPMetal, because it ensures that the Contoso.cs file reflects changes made to the lists, the addition of new lists that are added to the website, and the deletion of lists from the website.

To create the code and markup

In Solution Explorer, expand the VisualWebPart1 node, and open the VisualWebPart1.webpart file.
Set the value of the Title property to "Projects Listed by Primary Contact".
Set the value of the Description property to "Projects due in next 6 months, listed by primary contact".
Click the Save button on the toolbar.

Open the file VisualWebPart1UserControl.ascx, and add the following markup below the existing directives.

<%@ 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="Employee">
      <headerstyle HorizontalAlign="Left" />
    </SharePoint:SPBoundField>
    <SharePoint:SPBoundField DataField="JobTitle" HeaderText="Job Title">
      <headerstyle HorizontalAlign="Left" />
    </SharePoint:SPBoundField>
    <SharePoint:SPBoundField DataField="ProjectTitle" HeaderText="Project Title">
      <headerstyle HorizontalAlign="Left" />
    </SharePoint:SPBoundField>
    <SharePoint:SPBoundField DataField="DueDate" HeaderText="Due Date">
      <headerstyle HorizontalAlign="Left" />
    </SharePoint:SPBoundField>
  </Columns>
</SharePoint:SPGridView>

Click the Save button on the toolbar.
In Solution Explorer, expand the VisualWebPart1UserControl.ascx node, and open the VisualWebPart1UserControl.ascx.cs code-behind file.

Add the following using statements.

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

Replace the Page_Load method with the following. Notice that the expressions emp.Project.DueDate and emp.Project.Title create implicit joins between the Employees and Projects lists. Notice, also, that the select clause specifies only the fields that will actually be presented in the Web Part. The Description field of each project item does not need to be sent over the network from the content database to the front-end web server.

protected void Page_Load(object sender, EventArgs e)
{
    using (ContosoDataContext dc = new ContosoDataContext(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();
    }
}

Click the Save button on the toolbar.

To deploy and test the Web Part

On the Build menu, select Deploy Solution. The deploy process does the following:
- Runs your batch file (and, thus, SPMetal)
- Builds the project
- Deploys the ProjectsWithContacts.wsp package file to the farm’s Solution Gallery
- Deploys the Contoso.SharePoint.WebPartPages.ProjectsByContact.dll assembly to the global assembly cache
- Adds the ProjectsWithContacts Feature1 feature to the site collection’s Feature gallery
- Activates the feature
- Recycles Internet Information Services (IIS)
Selecting Deploy Solution again after you make changes to the project automatically retracts the previous version of the solution and replaces it with the newest version.
Navigate to any Web Parts page on the website, and click the Page tab of the ribbon.
Click Edit Page, and then click Add a Web Part in any Web Part zone.
In the Categories box, click the Custom folder, click Projects Listed by Primary Contact in the Web Parts box, and then click Add.
Click Stop Editing. Verify that the Web Part appears on the page and is populated with data from the two lists. Projects with due dates more than six months from now should not appear.

Compartilhar via

Walkthrough: Populating a Grid Control with Data from a LINQ Query

Prerequisites

To set up the project

To create the code and markup

To deploy and test the Web Part

See Also

Tasks

Concepts

Other Resources

Recursos adicionais