sharepoint 里面 查询列表操作

sharepoint里面通过SPQuery类来完成对列表的查询

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Assembly Name="Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Assembly Name="CollaDec.FQuery, Version=1.0.0.0, Culture=neutral, PublicKeyToken=de03247b5d1213e9" %>   //下载的dll FriendlyQuery http://www.codeplex.com/
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Import Namespace="Microsoft.Office.Server.UserProfiles" %>

 

先介绍SPQuery类的几个属性

SPWeb web = SPContext.Current.Web;
        SPList list = web.Lists["Tasks"];
        SPListItemCollection items = list.Items;
        for (int i = 0; i < items[0].Fields.Count; i++)
        {
            //字段名 和 InternalName:sharepoint里面处理需要的名字
            Response.Write(items[0].Fields[i].Title + ":" + items[0].Fields[i].InternalName + "<br />");
        }
        
        SPQuery spq = new SPQuery();
        //查询结果要显示的字段[select title,status,duedate from tbl],但是这里的name后面是等于字段的InternalName
        //有些Fields[i].Title == Fields[i].InternalName,但有些不相等! 如DueDae
        spq.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='Status' /><FieldRef Name='DueDate' />";
        //查询条件 [分组 排序 where条件]
        //<Geq></Geq> >=
        //<EQ><EQ>  =
        //<Gt></Gt>  >
        //<Lt></Lt> <  | <Neq></Neq> != | <Contains></Contains> 包含
        // where title like 'my'
        spq.Query = "<Where><Contains><FieldRef Name='Title' /><Value Type='Text'>my</Value></Contains></Where>";
        spq.RowLimit = 5;//返回结果条数
        SPListItemCollection myItems = list.GetItems(spq);
        Response.Write("个数："+myItems.Count+"<br />");
        foreach (SPListItem item in myItems)
        {
            Response.Write(item.Title+"<br />");
            Response.Write(item["Due Date"] + "<br />");   
        }

 

他的查询条件设置需要满足CAML的语法标准！  这个写法真的有点影响效率！[是书写的效率哈!不是执行的效率哦]

其他几个查询：:)

1.SPQuery qpq = new SPQuery();
//需要显示的字段
StringBuilder sb = new StringBuilder("<FieldRef Name='Title' />");
sb.Append("<FieldRef Name='" + list.Fields["Created"].InternalName + "' />");
sb.Append("<FieldRef Name='" + list.Fields["Created By"].InternalName + "' />");
qpq.ViewFields = sb.ToString();
//最新动态为最近一个月
qpq.Query = String.Format("<Where><Gt><FieldRef Name='" + list.Fields["Created"].InternalName + "'/><Value Type='DateTime' StorageTZ='TRUE'>{0}</Value></Gt></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.ToLocalTime().AddMonths(-1)));
//显示10条
//qpq.RowLimit = 10;

2.

 

最近浏览帖子,可以用我们常规的T-sql来写查询语句！ 真是爽呆了！

 

 

SPWeb web = SPContext.Current.Web;
        //Response.Write(web.Name+"??");
        string selectStr = "SELECT * FROM Tasks WHERE [Title] CONTAINS 'my'"; //注意my后面不要多一个空格,否则要报错     Is|Contains|BeginsWith|=|！=|>|>=|<|<=

       selectStr =  SELECT * FROM Tasks WHERE [Title] BeginsWith 'my'";

       selectStr = "SELECT * FROM Tasks WHERE [Due Date] is Null";

       selectStr = "SELECT * FROM Tasks WHERE [Assigned To] BeginsWith 'hai' order by ID desc";

       selectStr = "SELECT * FROM Tasks WHERE [Assigned To] BeginsWith 'hai' or [Due Date] is Null order by ID desc"

        //以上几条查询语句 都成功！ 千万注意最后不要 有 一个 空格 ！  如 Id desc "; desc后面只有有空格就报 Index was outside the bounds of the array.   at CollaDec.Tools.FriendlyQuery.AnalyseQuery()

        //当你条件判断  不再 Is|Contains|BeginsWith|=|！=|>|>=|<|<= 这几个之内 将报 Is|Contains|BeginsWith|Eq|Neq|Lt|Leq|Gt|Geq was expected. at CollaDec.Tools.FriendlyQuery.AnalyseQuery()错误 “
        CollaDec.Tools.FriendlyQuery fq = new CollaDec.Tools.FriendlyQuery(web, selectStr);
        fq.RowLimit = 5;
        fq.Scope = CollaDec.Tools.FriendlyQuery.QueryScope.AllItemsAndFolders;
        SPListItemCollection items = fq.GetItems();
        //SPList list = fq.QueryList;
        //Response.Write(list.Title);
        foreach (SPListItem item in items)
        {
            Response.Write(item.Title + "<br />");
            Response.Write(item["Due Date"] + "<br />");
        }

 

效果和上面一样! 这样我们是不是更能接受呢？