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.exe /web:http: // sp1dev:1600  /namespace:CopyListContent.WPShowCustomerInfo  /code:SPLinq.cs

 SPmetal的使用请参见MSDN: SPMetal 

  命令执行成功后，就在我们的项目目录下新建了一个名为SPLinq.cs的类，如下图

 

接下来我们需要做的就是回到我们VS2010的项目中，在此项目中添加已存在的项目，即我们上面创建的SPLinq.cs类。如下图:

 然后回到我们在本文的最开始创建的Visual Web Part,做相应的修改，如下图:

 

WPShowCustomerInfoUserControl.ascx代码如下 :

View Code

<% @ Assembly Name = " $SharePoint.Project.AssemblyFullName$ "   %>
<% @ 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;
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的绑定列。