如何從SharePoint Content DB中查詢List資料

sharepoint用來維護基礎資料非常友善，隻需要建立自定義清單，然後使用infopath自定義一下維護界面，就可以實作線上的增删改查，開發效率很高。如果維護的資料需要進行審批，還可以加入工作流功能。使用sharepoint designer可以快速開發出簡單的工作流，如果是很複雜的工作流，那麼就需要使用vs進行開發了。現在資料已經維護進了sharepoint list，那麼怎麼從資料庫中将維護的資料查詢出來呢？

sharepoint 的清單資料都存儲在content db中，其中最最重要的表就是[dbo].[alluserdata]，這個表中的一行資料就對應sharepoint list中的一條資料。下面介紹下如何從content db中查詢出list資料。

假設我們現在有一個country清單，記錄了全球200多個國家和地區的中文名，英文名，建國日期，面積，人口等資訊，整個清單隻有字元串、日期、數字等簡單類型，沒有user，lookup等資料類型，則整個list的資料都可以從[dbo].[alluserdata]查詢獲得，具體查詢步驟：

1. 查詢[dbo].[alllists]找到listid。

由于整個網站集都是共用一個content db資料庫，是以可能會出現在多個網站中都建立了country這個list的情況，那麼就會返還多條結果，這個情況下，就需要關聯allwebs表，根據網站的url來判斷到底哪個listid才是我們需要的。

在找到了listid後，接下來所有查詢都會用到這個id。

2. 查詢[dbo].[alluserdata]，找到需要查詢的列，并命名為别名。

假設第一步我們查詢出來的listid是'f20e316b-ea24-4164-9437-bbb17c182691'，那麼我們查詢country這個清單的所有資料的sql就是：

這個表的列非常多，在sharepoint 2013的環境中會更多，但是存儲資料的列都是用“資料類型+數字”來命名的。是以如果要找建國日期這個字段對應的列，那麼就去看datetime1 datetime2等，如果要找面積，人口等數值類型的列，那就去看float1 float2等；如果要找中文名，英文名之類的字元串列，那就要看nvarchar1 nvarchar3等列。這裡基本上都是靠眼睛來看的，根據查詢的結果推斷哪些字段存儲了哪些資料。在得知每個字段的對應後，即可修改查詢，将别名加上。

3. 過濾掉已删除的資料。

如果資料經過删除，然後又重新錄入，那麼我們就會發現，第2步的查詢結果會把删除的和重新錄入的資料都查詢出來。sharepoint采用的删除方法都是軟删除，通過設定一個标志位來表示一條資料已經被删除，是以我們隻需要将删除辨別tp_deletetransactionid=0添加到where條件中，即可将未删除的資料傳回。

4. 過濾掉曆史版本的資料。

如果這個清單開啟了版本控制，那麼我們查詢的結果可能包含多個版本的資料，而我們隻需要最新版本的資料，不希望曆史版本資料出現在查詢中。alluserdata表中，使用tp_iscurrentversion字段來辨別這條資料是最新的目前版本還是曆史版本。

于是，查詢最新版本的sql改為：

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更新為：

若要将關系資料維護到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，這個整型主鍵值就是用作外鍵關聯的。

2. 查詢主表。

對于我們的主表user表，查詢方法跟case1的步驟一樣，找到listid，找到需要的字段，同時需要找到外鍵的關聯字段，也就是int1 int2這種字段。

3. join兩個查詢結果。

現在看來，前兩步的查詢就像是兩個獨立的表一樣了，那麼接下來我們隻需要把主表和外鍵表進行關聯查詢，即可。比如我們需要查詢使用者的姓名，生日，出生地國家中文名，那麼對應的sql就是：

當然，我們可以對這個查詢進行簡化，把不需要查詢傳回的字段都删掉。

前面隻是說的一對多情況的關系，但是要在關系資料庫中實作多對多關系，那麼就需要用到中間表。同樣的道理，在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的表隻存在一個多對多關系，那麼我們可以寫成：

如果主表和lookup表存在多個多對多關系，那麼我們還需要知道這裡要查詢的多對多是哪個字段的多對多。關于fieldid，并不能很簡單的通過界面看到，我隻找到通過代碼或者spcamlqueryhelper這樣的第三方工具才能檢視。在得知了fieldid後，我們的查詢便可改為：

4. 聯合查詢主表、lookup表和中間表。

前面三個步驟我們已經得到了三個獨立的表查詢，接下來我們就可以按照普通的三個表進行join查詢的方式，将結果查詢出來。

【注意：這裡使用的都是inner join，那是因為我們認為多選的lookup是必選的，最少需要選一個，如果是允許不選的，那麼就需要改寫為left join。】

在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字段。

3. 聯合查詢使用者表和userinfo表，将編輯者的登入名添加到查詢結果中。

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的登入名。

3. 将步驟1、2的查詢結果進行join，便可得到使用者的審批人。

以上介紹了關于基本類型，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資料庫中查詢維護的清單。