窗外

Inside SPSiteDataQuery

一段关于Cross List Query的代码:

// Define the lists that we want to query.
string lists = ""
    + ""
    + ""
    + "";

// Define query conditions.
string whereString = ""
    + ""
    + ""
    + "" + condition + "" 
    + ""
    + "";

// Define fields that we want to see in the result. 
string viewFields = ""
    //+ ""
    + ""
    + ""
    + ""
    //+ ""
    //+ ""
    + "";

// Perform cross list query.
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = lists;
query.Query = whereString;
query.ViewFields = viewFields;

DataTable dtResult = web.GetSiteData(query);

通常在使用SPSiteDataQuery时,最常见的情况是,针对同样的List Template或者BaseType来做Query,即指定SPSiteDataQuery.Lists为一个List Template或一个BaseType。但我们也可以对几个并非基于同一List Template或BaseType的List做query,像上面的代码那样,只需指定SPSiteDataQuery.Lists为我们要query的list的ID即可。

当然,我们所要query的这些list还是需要有一些共同的field来作为query的基础,我们需要使用在<Where>中使用这些field。以上面的代码为例,我们需要对blog site的Posts和Comments两个list做query。虽然在<Where>中我们可以使用逻辑运算,例如<Or>,理论上我们可以通过<Or>来query Posts的Title和Comments的Post Title,但实际上这种方式并不可行。在<Where>中使用的field只有同时存在于Posts和Comments时才会有结果返回。因此,在上面的代码中我们使用了一个自定义的site column,Post Modified Date。

ViewFields到是没有这样的限制,如果某个Field只存在于一个List中,只要指定该Field的Nullable属性为true即可,这样,没有该Field的List在该Field处值为Null。但是Nullable属性不支持Lookup或People/Group类型的Field,因此上面的代码中,被注释掉的PostTitle和PostID两行实际不能工作,因为在Comments中这两个Field都是Lookup类型。

如果使用SQL Profiler来观察SharePoint实际上所作的Query的话,最终的Query是对AllUserData和AllDocs两个Table的Inner Join,类似下面这段T-SQL Query:

SELECT UserData.[tp_ID] AS QryCol0,
''7D092AC5-10CB-42AE-820D-457CC0D6D214'' AS QryCol1,
''78709AB4-6FA6-4BC8-A15D-5C3037020773'' AS QryCol2,
UserData.[tp_Created] AS QryCol3,
t1.[Type] AS QryCol4,
CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName 
WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName 
ELSE t1.DirName + N''/'' + t1.LeafName END  AS QryCol5,
UserData.[tp_ModerationStatus] AS QryCol6,
UserData.[tp_Level] AS QryCol7,
UserData.[ntext1] AS QryCol8,
UserData.[nvarchar1] AS QryCol9,
UserData.[ntext2] AS QryCol10,
UserData.[tp_ContentType] AS QryCol11,
NULL AS QryCol12,
UserData.[datetime2] AS QryCol13 
FROM UserData  
INNER JOIN Docs AS t1 WITH(NOLOCK) 
ON ( 1 = 1  AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName  AND t1.LeafName = UserData.tp_LeafName  AND t1.Level = UserData.tp_Level  AND  (UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL OR  (UserData.tp_DraftOwnerId @IU AND t1.ScopeId NOT IN (@L3)))  OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR t1.ScopeId IN (@L3))) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId  @IU )) AND (1 = 1) AND UserData.tp_ListId = @L4) WHERE (UserData.[datetime2] > @L5DTP) UNION ALL SELECT UserData.[tp_ID] AS QryCol0,'2012-04-30 09:43:52'7D092AC5-10CB-42AE-820D-457CC0D6D214'' AS QryCol1,''7C3A7D65-A153-4FD4-900A-8F7D52B25E20'' AS QryCol2,UserData.[tp_Created] AS QryCol3,t1.[Type] AS QryCol4,CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N''/'' + t1.LeafName END  AS QryCol5,UserData.[tp_ModerationStatus] AS QryCol6,UserData.[tp_Level] AS QryCol7,UserData.[ntext1] AS QryCol8,UserData.[nvarchar1] AS QryCol9,UserData.[ntext2] AS QryCol10,UserData.[tp_ContentType] AS QryCol11,UserData.[nvarchar3] AS QryCol12,UserData.[datetime1] AS QryCol13 FROM UserData  INNER JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1  AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName  AND t1.LeafName = UserData.tp_LeafName  AND t1.Level = UserData.tp_Level  AND t1.IsCurrentVersion = 1 AND (1 = 1) AND UserData.tp_ListId = @L6) WHERE (UserData.[datetime1] > @L5DTP)',N'@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5DTP datetime,@L6 uniqueidentifier',@L0='00000000-0000-0000-0000-000000000000',@L2='3E33731F-7CFA-4E45-AB6C-CA864E9E0A0B',@IU=2,@L3='6F64B594-5EDB-498F-AE17-1D5038D7DE15',@L4='78709AB4-6FA6-4BC8-A15D-5C3037020773',@L5DTP='2009-01-19 15:59:59',@L6='7C3A7D65-A153-4FD4-900A-8F7D52B25E20'

 

Tags

Leave a Reply

Your email address will not be published. Required fields are marked *