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.
