Sharepoint学习笔记---SPList--使用Linq to Sharepoint间接查询External List(3.使用Linq to Sharepoint查询List内容)
通过前面两步打下的基础 ,
1、Sharepoint学习笔记---SPList--使用Linq to Sharepoint间接查询External List(1.通过BCS创建External List)
2、Sharepoint学习笔记---SPList--使用Linq to Sharepoint间接查询External List(2.复制External List内容)
这里我们将使用Linq to Sharepoint来查询我们前面创建的Customer List: ACustomer.因为此List的内容是从External List：NWCustomer复制过来的，所以通过以它为中介来间接实现对NWCustomer的间接查询效果。下面进入操作步骤：
此处，我们要基于前面的Sharepoint 项目，新创建一个Visual Webpart (命名为WPShowCustomerInfo)， 如下图:
我们要在此Webpart上添加两个控件，一个是Dropdownlist控件，它的值绑定到ACustomer的Country值上，由用户选择某个Country值，然后基于此值对ACustomer的Item值进行过滤，把过滤结果显示到另一个控件SPGridview上。效果如下图
在添加了前面的Visual Webpart后，我们先不管它的内容，我们先要在项目中引用我们的Linq to Sharepoint需要用到的DLL,它的位置在
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\ 目录下。
名字是: Microsoft.Sharepoint.Linq.dll。如下图所示：
引用添加好之后 ，我们就需要使用SPMetal来创建我们测试网站的Entity Classes了，操作步骤如下:
首先在我们的项目上点击右键，在弹出菜单中选择打开项目在目录(Open Folder in Windows Exploer)
这样就打开了我们项目所在目录，我们需要按住Shift键不放，在Windows Exploer的任何空白位置点击鼠标右键，在弹出的菜单中选择Open Command Windows Here，从而直接进入Windows的命令行窗口。效果如下图:
在Windows命令行窗口中输入SPMetal如下命令:
SPmetal的使用请参见MSDN: SPMetal
命令执行成功后，就在我们的项目目录下新建了一个名为SPLinq.cs的类，如下图
接下来我们需要做的就是回到我们VS2010的项目中，在此项目中添加已存在的项目，即我们上面创建的SPLinq.cs类。如下图:
然后回到我们在本文的最开始创建的Visual Web Part,做相应的修改，如下图:
WPShowCustomerInfoUserControl.ascx代码如下 :
View Code 
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"
Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="WPShowCustomerInfoUserControl.ascx.cs"
Inherits="CopyListContent.WPShowCustomerInfo.WPShowCustomerInfoUserControl" %>
<div>
<table>
<tr>
<td>
Select Country Filter
</td>
<td>
<asp:DropDownList ID="drplstCountry" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="drplstCountry_SelectedIndexChanged" Height="20px"
Width="145px">
</asp:DropDownList>
</td>
</tr>
</table>
<SharePoint:SPGridView ID="spGridView" runat="server" AutoGenerateColumns="false">
<HeaderStyle HorizontalAlign="Left" ForeColor="Navy" Font-Bold="true" />
<Columns>
<SharePoint:SPBoundField DataField="BCSFindCompanyName"
HeaderText="CompanyName">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindContactName"
HeaderText="ContactName">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindContactTitle"
HeaderText="ContactTitle">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindCity" HeaderText="City">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindAddress" HeaderText="Address">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindRegion" HeaderText="Region">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindCountry" HeaderText="Country">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindPhone" HeaderText="Phone">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindFax" HeaderText="Fax">
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField="BCSFindPostalCode" HeaderText="PostalCode">
</SharePoint:SPBoundField>
</Columns>
</SharePoint:SPGridView>
</td>
</div>
WPShowCustomerInfoUserControl.ascx.cs代码如下 :
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint.Linq;
using Microsoft.SharePoint;
using System.Linq;
namespace CopyListContent.WPShowCustomerInfo
{
public partial class WPShowCustomerInfoUserControl : UserControl
{
EntityList<ACustomerItem> MyCustomers;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetDistinctCustomerCountries();
}
}
protected void GetDistinctCustomerCountries()
{
var dc = new SPLinqDataContext(SPContext.Current.Web.Url);
MyCustomers = dc.GetList<ACustomerItem>("ACustomer");
var q = (from c in MyCustomers select c.BCSFindCountry).Distinct();
this.drplstCountry.DataSource = q;
this.drplstCountry.DataBind();
}
protected void drplstCountry_SelectedIndexChanged(object sender, EventArgs e)
{
string CountryStr = this.drplstCountry.SelectedValue.ToString().Trim();
GetLookUpDataByCountry(CountryStr);
}
protected void GetLookUpDataByCountry(string CountryStr)
{
var dc = new SPLinqDataContext(SPContext.Current.Web.Url);
var Customers = dc.GetList<ACustomerItem>("ACustomer");
var customerQuery = from customer in Customers
where customer.BCSFindCountry == CountryStr
select new
{
customer.BCSFindCompanyName,
customer.BCSFindContactName,
customer.BCSFindContactTitle,
customer.BCSFindCity,
customer.BCSFindAddress,
customer.BCSFindRegion,
customer.BCSFindCountry,
customer.BCSFindPhone,
customer.BCSFindFax,
customer.BCSFindPostalCode
};
spGridView.DataSource = customerQuery;
spGridView.DataBind();
}
}
}
从上面代码中我们可以看到，我们在两个地方用到了Linq to Sharepoint，一处是创建Dropdownlist的数据源时，使用Linq来从ACustomer中获取Country的Distinct数据值列，然后和Dropdownlist进行绑定，用于向用户提供可供过滤的条件值。一处是在用户获取到Country的过滤条件后，使用此条件来过滤ACostomer List的Items，并把过滤结果在SPGridview中显示出来，在SPGridview控件中，我们设置了针对ACustomer的绑定列。
