如何从SharePoint Content DB中查询List数据

枫叶飞翔

　　SharePoint用来维护基础数据非常方便，只需要建立自定义列表，然后使用InfoPath自定义一下维护界面，就可以实现在线的增删改查，开发效率很高。如果维护的数据需要进行审批，还可以加入工作流功能。使用SharePoint Designer可以快速开发出简单的工作流，如果是很复杂的工作流，那么就需要使用VS进行开发了。现在数据已经维护进了SharePoint List，那么怎么从数据库中将维护的数据查询出来呢？
　　SharePoint 的列表数据都存储在Content DB中，其中最最重要的表就是[dbo].[AllUserData]，这个表中的一行数据就对应SharePoint List中的一条数据。下面介绍下如何从Content DB中查询出List数据。
  Case 1简单数据类型的自定义列表查询
　　假设我们现在有一个Country列表，记录了全球200多个国家和地区的中文名，英文名，建国日期，面积，人口等信息，整个列表只有字符串、日期、数字等简单类型，没有User，Lookup等数据类型，则整个List的数据都可以从[dbo].[AllUserData]查询获得，具体查询步骤：
　　1. 查询[dbo].[AllLists]找到ListId。
select *
from [dbo].[AllLists]
where tp_Title = 'Country'
　　由于整个网站集都是共用一个Content DB数据库，所以可能会出现在多个网站中都创建了Country这个List的情况，那么就会返还多条结果，这个情况下，就需要关联AllWebs表，根据网站的Url来判断到底哪个ListId才是我们需要的。

select w.FullUrl,l.tp_ID
from [dbo].[AllLists] l
inner join [dbo].[AllWebs] w
on l.tp_WebId=w.Id
where l.tp_Title = 'Country'
　　在找到了ListId后，接下来所有查询都会用到这个Id。
　　2. 查询[dbo].[AllUserData]，找到需要查询的列，并命名为别名。
　　假设第一步我们查询出来的ListId是'F20E316B-EA24-4164-9437-BBB17C182691'，那么我们查询Country这个列表的所有数据的SQL就是：

SELECT *
FROM AllUserData
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691'
　　这个表的列非常多，在SharePoint 2013的环境中会更多，但是存储数据的列都是用“数据类型+数字”来命名的。所以如果要找建国日期这个字段对应的列，那么就去看datetime1 datetime2等，如果要找面积，人口等数值类型的列，那就去看float1 float2等；如果要找中文名，英文名之类的字符串列，那就要看nvarchar1 nvarchar3等列。这里基本上都是靠眼睛来看的，根据查询的结果推断哪些字段存储了哪些数据。在得知每个字段的对应后，即可修改查询，将别名加上。

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691'
　　【注意：SharePoint并没有在数据库中以很方便的结构展现哪些列表字段对应哪个数据库字段，在AllLists表中，虽然有个字段tp_Fields，但是在SharePoint2010及之后，该字段是压缩的二进制，使用SQL是无法读取的。所以根本不可能通过查询数据库得知哪个字段的别名是什么。】参考：http://stackoverflow.com/questions/8988098/how-could-i-find-the-fields-of-a-sharepoint-list-from-database-in-sharepoint-201
　　3. 过滤掉已删除的数据。
　　如果数据经过删除，然后又重新录入，那么我们就会发现，第2步的查询结果会把删除的和重新录入的数据都查询出来。SharePoint采用的删除方法都是软删除，通过设置一个标志位来表示一条数据已经被删除，所以我们只需要将删除标识tp_DeleteTransactionId=0添加到where条件中，即可将未删除的数据返回。

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0
　　4. 过滤掉历史版本的数据。
　　如果这个列表开启了版本控制，那么我们查询的结果可能包含多个版本的数据，而我们只需要最新版本的数据，不希望历史版本数据出现在查询中。AllUserData表中，使用tp_IsCurrentVersion字段来标识这条数据是最新的当前版本还是历史版本。
　　于是，查询最新版本的SQL改为：

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1
　　5. 过滤掉内容审批未通过的数据。
　　如果这个列表开启了内容审批，那么就会出现多个最新版本的情况，一个是已经被审批通过的版本，另一个是修改后还没有进行审批的版本。一般来说，我们是希望只有审批通过的才查询出来，用户进行修改后，只要审批状态不是Approve，那么就不应该出现在查询结果中。在AllUserData表中，使用tp_ModerationStatus字段来标识这行数据是否已经被审批通过。这是一个枚举类型，其值为：
　　0 The list item is approved.
1 The list item has been denied approval.
2 The list item is pending approval.
3 The list item is in the draft or checked out state.
4 The list item is scheduled for automatic approval at a future date.
　　这里，我们只要审批通过的数据，所以我们的SQL更新为：

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
Case 2引用（Lookup）其他列表的自定义列表查询
　　若要将关系数据维护到SharePoint中，那么Lookup数据类型是非常常用的实现方法。使用Lookup可以很容易实现一对多和多对多关系，关于多对多关系，我们在Case3中再进行讲解，先看看一对多关系的维护与查询。
　　紧接着Case1，现在我们需要创建一个用户表，里面记录了用户的姓名，生日，出生国等信息，出生国字段对应的就是Lookup Country这个List，用户出生国不能乱填，必须从现有Country中进行选择，而且只能选择一个，这就是典型的一对多关系。
　　做过数据库设计的都应该知道，对于一对多关系，需要使用一个表添加另一个表的主键来作为外键，如果是数据库表的话，那么User表中必然有个BirthCountryId列。那么在SharePoint中，所有列表都存储在AllUserData表中，那这种关系怎么表示呢？
　　AllUserData表中有很多int1 int2之类的整型字段，但是这些字段并不用于存储数值类型（数值类型使用float1 float2等存储），而是用于存储Lookup表的外键。查询具有Lookup字段的自定义列表的操作如下：
　　1. 查询外键表。
　　这里Country表是User表的外键表，所以先将Country表查出来，查的字段必须包含tp_ID，这个整型主键值就是用作外键关联的。

SELECT d.tp_ID,
d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
　　2. 查询主表。
　　对于我们的主表User表，查询方法跟Case1的步骤一样，找到ListId，找到需要的字段，同时需要找到外键的关联字段，也就是int1 int2这种字段。

SELECT
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
d.int1 as CountryId
FROM AllUserData d
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
　　3. Join两个查询结果。
　　现在看来，前两步的查询就像是两个独立的表一样了，那么接下来我们只需要把主表和外键表进行关联查询，即可。比如我们需要查询用户的姓名，生日，出生地国家中文名，那么对应的SQL就是：

SELECT
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
c.ChineseName as BirthCountryChineseName
FROM AllUserData d
inner join
(
SELECT d.tp_ID,
d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
) c
on d.int1=c.tp_ID
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
　　当然，我们可以对这个查询进行简化，把不需要查询返回的字段都删掉。

Case 3引用其他列表并可多选的自定义列表查询
　　前面只是说的一对多情况的关系，但是要在关系数据库中实现多对多关系，那么就需要用到中间表。同样的道理，在SharePoint中，Lookup字段可以设置成多选，那么就变成了多对多关系，对应数据库中使用[dbo].[AllUserDataJunctions]这个表来实现多对多关联。
　　我们还是用Case2中的用户表和国家表举例，如果我们为用户表添加国籍字段，由于有些国家允许双重国籍，所以用户和国家就变成了多对多关系。与一对多中使用tp_ID进行关联不一样，在多对多关联中，使用的是主表的Doc_Id和Lookup表的tp_ID进行关联。查询步骤如下：
　　1. 查询被Lookup的表。同Case2，不再累述。
　　2. 查询主表，需要tp_DocId字段。查询雷同Case2，只是需要多添加一个tp_DocId字段。
　　3. 查询中间表，主要是tp_DocId和tp_Id两个字段。
　　中间表的联合主键字段较多，where条件比较复杂，如果主表和Lookup的表只存在一个多对多关系，那么我们可以写成：

select tp_DocId,tp_Id
from [dbo].[AllUserDataJunctions]
where tp_SourceListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and tp_DeleteTransactionId=0 and tp_IsCurrentVersion=1
　　如果主表和Lookup表存在多个多对多关系，那么我们还需要知道这里要查询的多对多是哪个字段的多对多。关于FieldId，并不能很简单的通过界面看到，我只找到通过代码或者SPCAMLQueryHelper这样的第三方工具才能查看。在得知了FieldId后，我们的查询便可改为：

select tp_DocId,tp_Id
from [dbo].[AllUserDataJunctions]
where tp_SourceListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and tp_DeleteTransactionId=0 and tp_IsCurrentVersion=1 and tp_FieldId='48DCB501-EBFD-4727-85AE-9CC9A51E73CF'
　　4. 联合查询主表、Lookup表和中间表。
　　前面三个步骤我们已经得到了三个独立的表查询，接下来我们就可以按照普通的三个表进行Join查询的方式，将结果查询出来。

select main.*,lkup.ChineseName as Nationality
from
(
SELECT d.tp_DocId,
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate
FROM AllUserData d
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
)main
inner join
(
select tp_DocId,tp_Id
from [dbo].[AllUserDataJunctions]
where tp_SourceListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and tp_DeleteTransactionId=0 and tp_IsCurrentVersion=1 and tp_FieldId='48DCB501-EBFD-4727-85AE-9CC9A51E73CF'
)m2m
on main.tp_DocId=m2m.tp_DocId
inner join
(
SELECT d.tp_ID,
d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
)lkup
on m2m.tp_Id=lkup.tp_ID
　　【注意：这里使用的都是Inner Join，那是因为我们认为多选的Lookup是必选的，最少需要选一个，如果是允许不选的，那么就需要改写为Left Join。】

Case 4包含用户或用户组类型的自定义列表用户查询
　　在SharePoint自定义列表的数据类型中，除了前面Case提到的基本数据类型和Lookup类型外，还有比较常用的就是Person or Group类型。SharePoint Content DB的User数据，存储在[dbo].[UserInfo]表中，在AllUserData中，只需要存储User的ID（int类型）即可。UserId和Lookup表一样，是占用的int4 int5这样的int类型的列，但具体是int几那需要根据实际情况用肉眼去看。另外在AllUserData中有两个常用的UserId，那就是tp_Author和tp_Editor，表示该行数据的创建人和修改人。
　　关于UserInfo表需要注意的是，这个表的主键是[tp_SiteID],[tp_ID]，也就是说只通过UserId去Join的话，可能会找到多条数据，在不同的Site中，UserId是可能重复的。
　　还是以Case2的用户为例，我们希望知道每条用户数据最后是谁编辑的，将编辑者的登录名显示出来。那么我们需要进行如下操作：
　　1. 查询用户表，并包含tp_Editor列。与Case2相似，这个不再累述。
　　2. 查询UserInfo表，将该用户表所在的Site作为过滤条件。
　　关于SiteId，我们可以在AllUserData中找到，就是tp_SiteID字段。

select tp_ID,tp_Login
from [dbo].[UserInfo] u
where u.tp_SiteID='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'
　　3. 联合查询用户表和UserInfo表，将编辑者的登录名添加到查询结果中。

SELECT
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
d.int1 as CountryId,
users.tp_Login as EditorLoginName
FROM AllUserData d
inner join
(
select tp_ID,tp_Login
from [dbo].[UserInfo] u
where u.tp_SiteID='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'
) users
on d.tp_Editor=users.tp_ID
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
Case 5包含用户或用户组类型的自定义列表用户组查询
　　Case4这里只是查询了用户，如果我们添加的是一个用户组的数据，那么就不能再从UserInfo表中进行查询，而是要从[dbo].[Groups]中进行查询。
　　Groups表的主键和UserInfo很相似，也是[SiteId],[ID]。而且Groups表和UserInfo表的ID使用的是同一套Sequence。也就是说，如果对于某一个SiteId，我们在UserInfo表中有1,2,3这三个ID的用户，那么我们肯定在Groups中找不到同样ID的Group，GroupID和UserID是不会重复的，这样就避免了在关联AllUserData时Confused。
　　AllUserData表中并没有一个标识说关联的到底是UserInfo表还是Groups表，所以我们在查询时，可能需要将Groups的数据和UserInfo的数据Union起来然后在和AllUserData进行Join查询。
　　更普遍的情况是，我们其实并不关心Group的信息，我们更希望得到的是AllUserData和GroupMember的列表，这个时候我们就还需要Join [dbo].[GroupMembership]表。
　　以用户数据表为例，假设我们添加了一个用户或用户组的列“审批人”用以表示该用户的数据由哪些人审批。该列填入的数据都是SharePoint中建立的用户组。那么我们要查询出用户的审批人列表，那么操作如下：
　　1. 查询用户数据。这里需要关注的是int类型的列，审批人这个字段就存储在int列中。
　　2. 查询Groups表和GroupMembership表，获得用户组的信息。当然，这里也需要Join到UserInfo表，找到真正的Membership的登录名。

select g.ID,u.tp_Login
from [dbo].[Groups] g
inner join [dbo].[GroupMembership] gm
on g.ID=gm.GroupId and g.SiteId=gm.SiteId
inner join [dbo].[UserInfo] u
on u.tp_ID=gm.MemberId and u.tp_SiteID=gm.SiteId
where g.SiteId='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'
　　3. 将步骤1、2的查询结果进行Join，便可得到用户的审批人。

SELECT
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
d.int1 as CountryId,
users.tp_Login as Approvers
FROM AllUserData d
left join
(
select g.ID,u.tp_Login
from [dbo].[Groups] g
inner join [dbo].[GroupMembership] gm
on g.ID=gm.GroupId and g.SiteId=gm.SiteId
inner join [dbo].[UserInfo] u
on u.tp_ID=gm.MemberId and u.tp_SiteID=gm.SiteId
where g.SiteId='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'
) users
on d.int4=users.ID
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0
　　以上介绍了关于基本类型，Lookup类型，用户和组类型的SharePoint数据库查询方法，对于其他的数据类型都可以当做基本类型来对待：

• Multiple Text:ntext类型
  
• Choose：nvarchar类型
  
• Currency：float类型
  
• Yes/No：bit类型
  
• Hyperlink or Picture：nvarchar类型
  
• Calculated：看具体选择的结果类型
  
• External Data：nvarchar ntext都会用到
  
• Managed Metadata：未研究
  

　　以上几个Case的查询已经包含了大部分数据查询的情况，对于不同的数据列表，只需要稍作修改即可从SharePoint数据库中查询维护的列表。