據統計近六成以上的軟體系統需要資料庫,而當我們面對大型企業,設計與維護資料庫結構,或嘗試對資料庫內的物件如預存程序、函數進行單元測試 (Unit test),對資料庫做壓力測試時,大部分軟體開發生命週期的工具卻不重視這一塊,而微軟於 2007 年初,在 Team System 增加了 Visual Studio 2005 Team Edition For Database Professional之後,開始嘗試降低資料庫管理人員與開發人員間的隔閡。而今這個版本在 Visual Studio 2010中,已經由原先的 add-on功能變成了既有功能,所以本文也將介紹 Visual Studio 2010之後,與資料庫整合的相關內容吧。
環境準備
若要支援本篇文章所提供的功能,您必需準備除了 SQL Server之外的下列環境:
1.Visual Studio 2010 PREMIUM企業版或是 Visual Studio 2010 ULTIMATE企業旗艦版
2.Visual Studio Team Foundation Server 2010
其中, Visual Studio Team Foundation Server 2010可以先安裝並且做好設定,使得你進入 Visual Studio 2010,選擇連接到 Team Foundation Server時(也就是如下圖)

圖 1: Visual Studio 2010的進入畫面。
可以看到如圖2的畫面:

圖 2: 可以點選已有的 Team Foundation Server。
如果你只有安裝 Visual Studio 2010,但是沒有安裝 Team Foundation Server,你看到的圖2,將會是伺服器裡空空如也,沒有任何內容,這就不對了。
當你確定好安裝了這兩個軟體之後,才能開始我們下面的內容。
Visual Studio 2010所提供資料庫整合的功能
Team System 本身是個很大的系統,它希望能完整支援軟體生命週期內各面向的需求,讓開發團隊的成員得以協調溝通、分享資源、控管變異、測試與追蹤 bug...等。同時,它整合了多項既有的產品,如 Visual Studio 開發環境、Office、SQL Server、Reporting Services、Analysis Services、Windows SharePoint Services...等。不過因為 Team System 架構極大,所以我們的文章將著重在討論 Team System 與資料庫開發和管理的相關議題。
一般開發資料庫的應用程式,都希望應用程式開發人員能呼叫 SQL Server端已經撰寫好的預存程序或是檢視表、函數等等,而不希望應用程式直接下查詢指令查詢基礎資料表,這是因為安全以及效能的因素考量。
然而,資料庫預存程序的撰寫就一點問題也沒有嗎?事實上也不儘然,為何這麼説,因為當資料表結構有改的時後,你的預存程序也要更改,如果影響的預存程序相當多時,就必需一個個修改,相當麻煩。而如果你使用了 Visual Studio 2010來輔助 SQL Server資料庫的開發,將會得到異想不到的好處,即便你可能未必需要它的其他功能(有些功能個人覺得未必相當實用,例如產生大量虛擬資料這個功能,完全就產生看不懂的數據只是讓資料列增多罷了,感覺沒有那麼實用)
大體來説, Visual Studio 2010,與資料庫整合的部分提供資料庫管理者以下的功能:
● 讓資料庫管理師也可以透過 Visual Studio 整合環境加入團隊運作。
● 以 Visual Studio 專案的方式控管資料庫的 Schema。
● 靜態程式碼分析。
● 測試:
針對資料庫內的預存程序、函數做單元測試,並可將測試整合到以 Visual Studio 建立的其他測試專案。
建立資料表內的測試資料:新增符合條件約束 (Constraint) 如外部主鍵、Check 等之紀錄到資料表。
● 變動管理:
● 修改資料庫內的物件定義。
更動物件名稱:可輔助資料表或欄位的名稱重構 (Refactoring),修改後的結果會自動更新到使用該物件的其他物件,如重新定義資料表名稱後,自動修改有用到該資料表的預存程序、檢視等。
資料庫物件定義的版本控管。
● 比較兩個資料庫 Schema 與資料內容的異同:比較資料表、資料庫或伺服器間的 schema 定義與資料內容之異同。
除了提供工具輔助外,要整合資料庫的專家到軟體開發的生命週期中,並提供與流程整合的變動管理,仍需要針對整個團隊設計的權利義務規範。VSTS 產品中直接附贈了微軟為 Agile 和 CMMI 兩軟體開發方法論的流程範本,若你尚未購買或安裝該產品,也可以到直接到微軟網站下載流程範本。
不過本人對於軟體開發的方法論並沒有那麼感興趣,所以我們還是把重點放在與資料庫的處理吧!
整體來說,Visual Studio 2010所建構的資料庫系統開發與維護生命週期如圖 3 所示:

圖 3: Visual Studio 2011 所建構的資料庫系統開發與維護生命週期
圖 3 所描述的主要流程如下:
第 1 部分:建立資料庫專案。
● 評估資料庫開發工作的成本。
● 更新本機專案的環境。
● 載入 (Import) 既有的資料庫定義。
● 設定建置和部署的相關定義。
● 修改自動產生用在部署前置 (Pre) 和收尾 (Post) 之腳本 (Script)。
● 確認資料庫專案。
第 2 部分:整合 Team System 的團隊合作架構與功能。
● 將資料庫專案加入到原始碼版本控管。
● 連結工作項目 (Work Item)。
● 登入原則。
● 進度追蹤和報表。
第 3 部分:在本機的沙箱伺服器建置\部署。
● 為資料庫專案撰寫 T-SQL 程式碼。
建置 \ 部署...。
● 靜態程式碼分析。
● 重構 (Refactor) 資料庫程式碼。
建置 \ 部署 \ 執行測試...。
● 重複各種物件的開發和測試。
第 4 部分:測試。
● 產生測試資料。
● 執行資料庫專案的單元與壓力測試。
● 重新檢視資料庫程式碼。
建置 \ 部署 \ 執行測試...。
● 整合資料庫程式碼所做的變更。
建置 \ 部署 \ 執行測試...。
第 5 部分:部署資料庫。
● 同步資料庫專案。
● 確認建置。
● 執行資料庫單元測試。
● 分析變更。
● 產生建置 Script。
● 備份目標資料庫。
● 部署資料庫到中繼伺服器 (選項)。
接下來,以實作的方式說明各項功能,首先,我們用簡單的範例來建立資料庫專案,並做資料庫定義的版本控管。
建立資料庫專案,並控管定義物件的 SQL Script
其實這篇文章最主要的目的,只有探討資料庫物件 script與結構的維護部分(誰叫 Visual Studio 2010功能那麼多呢?)如果你想很快的了解如何控制,可以立刻使用 Visual Studio 2010建立一個新專案。以下動作將示範把已存在的資料庫定義納入到 Team Foundation Server做版本控管,而且我們使用的是 SQL Server的老牌範例資料庫 Northwind。
建立專案
開啟Visual Studio 2010後,在主選單選擇「檔案」à「新增」à「專案」選項,並在「新增專案」對話窗中,於左方的「已安裝的範本」樹狀結構選擇「資料庫」->「SQL Server」節點,中間的視窗中選擇「SQL Server 2008 精靈」選項,如圖4所示:

圖 4:Visual Studio 2010提供的資料庫專案型態
我比較習慣利用精靈設定專案,如果你熟悉之後,可以開啟空的「SQL Server XXX」專案,自行設定。
透過精靈設定專案的各項屬性,例如專案內分門別類管理資料庫物件的方式、資料庫物件預設的結構描述(schema)、是否啟動全文檢索、SQLCLR、資料庫連接的各種 SET 設定以及 collation、從哪個既有的資料庫載入各種物件之定義,以及建置與部屬資料庫的相關設定,其精靈畫面如圖5、圖6所示:

圖 5:透過精靈建立 Visual Studio 2010的資料庫專案(1)。

圖 6:透過精靈建立 Visual Studio 2010的資料庫專案(2)。
接下來,設定對伺服器和資料庫的連接、是否匯入擴充屬性…等,以及組建部署的方式,如圖7、圖8所示:

圖 7:透過精靈建立 Visual Studio 2010的資料庫連線 (1)。

圖 8:透過精靈建立 Visual Studio 2010的資料庫連線 (2)。
設定完成後,精靈便開始載入資料庫內的物件定義,這會花費一點時間,請耐心等待,尤其你的資料庫結構很多時。
精靈完成後,如果當初在建立專案時沒有選擇「加入至原始檔控制」,則可以事後加入,選擇專案右鍵,即可看到如圖9的畫面:

圖 9:選擇將專案加入至原始檔控制。
由於我們有裝 Team Foundation Server,所以可以直接點選 Team專案集合,見圖10:

圖 10:選擇 Team Foundation Server。
加入之後方案總管就會出現如圖11的畫面:

圖 11:加入原始檔控制的方案總管。
你把方案總管的tables中的 sql檔案打開,如圖12:

圖 12:方案總管中的 sql指令碼。
就會出現如圖13的 sql指令:

圖 13:資料表物件的 sql指令。
以檔案總管檢視,可以發現在檔案目錄下有對應的結構與檔案,如圖14所示:

圖 14:資料庫專案在檔案系統上所建立的各式目錄與檔案。
從圖14中可以看到該專案是以不同型式的檔名來區別用途,最終以 .sql 為副檔名,放在不同的專案子目錄中,代表不同主要類型的資料庫物件。
如果你想看資料庫架構的感覺,可以在主選單的「檢視」à「資料庫結構描述檢視」,如圖15,則會顯示圖16的畫面,如此就可以很容易看到資料庫的結構描述資料。

圖 15:顯示資料庫結構描述檢視。

圖 16:很像 SQL Server Management Studio的樣子顯示資料庫結構描述檢視。
使用原始碼控管
如果你有加入原始碼控管,畫面應該是像圖17一樣,每個指令前都有一個鎖頭:

圖 17:每個物件前都有一個鎖頭。
若要直接對裡面的 SQL指令直接修改,修改完後要記得按下檔案名稱à右鍵à簽入讓原始碼控制程式能控制。而且也千萬別以為,你直接對 SQL指令修改,真正伺服器上的資料庫結構就會被跟著改變,因為請注意圖18的畫面:

圖 18:修改裡面 SQL指令。
圖18的左上角寫的是「未連接」,所以你所修改的只是前端應用程式的 sql檔,而真正在伺服上的資料庫結構並沒有跟隨著被修改。我們可以事後再用資料結構同步的方式與伺服器資料庫同步。
開發資料庫物件
接下來我們可以開始新增資料庫物件。不過這裡必需要下 SQL指令,幸好 Visual Studio 2010會產生該物件的 SQL範本,所以除非你完全不會 SQL指令,不然要建立一個物件或是修改它並非難事。
我們以建立一個新資料表為例,你可以在方案總管上,選擇 Tables,然後按右鍵加入資料表,或是在結構描述檢視的資料表中按下右鍵同樣也可以加入資料表,這時會出現如圖19的畫面:

圖 19:建立一個新的資料表視窗。
給這個資料表一個名稱,待會你將會產生一個將同名稱的sql檔案。
由於Visual Studio 2010並不會那麼佛心來著幫你產生一個圖形界面幫助你建立資料表,它只會產生一個 sql指令的樣版,如圖20:

圖 20:sql指令的樣版視窗。
在此建立一個非常簡單的資料表如下指令:
create table mytable
(
pk int not null identity (1,1),
tableName2 nvarchar(10) null
)
存起來後,你可以選擇簽入這個指令,並且選擇建立一個主索引鍵或是 check條件約束如圖21:

圖 21:建立一個主索引鍵。
同樣的,也是要下指令,指令如下:
alter table myTable
add constraint [PrimaryKeyConstraint1]
primary key (pk)
設定好之後同樣記得簽入,這時你的前端已經擁有一個資料表上面有一個主索引鍵。
但是這時伺服器上的 SQL Server並沒有這個資料表,不信可以利用 SQL Server Management Studio看一下即知。
如果你想建立物件時,伺服器上的 SQL Server也順便把物件建立起來,這時記得要選擇「連接」,連接到伺服器上的 SQL Server,如圖 22:

圖 22:選擇「連接」登入到伺服器 SQL Server。
當 SQL指令寫好後,除了存檔之外,也不忘按下「執行 SQL」,如圖23:

圖 23:執行 SQL指令。
這樣伺服器的 SQL Server就會對物件新增或是修改,端看你所使用的 SQL語法。
比較資料庫物件結構
如果你在前端建立了一個或多個資料庫物件,希望能與伺服器端的 SQL Server進行結構的同步時,可以選擇選單上的「資料」à「結構描述比較」à「新增結構描述比較」,如圖24:

圖 24:新增結構描述比較。
選擇來源與目標資料庫之後,就可以進行資料結構的比對,如圖 25:

圖 25:選擇來源與目標結構描述。
這裡的設定是把來源設定成本專案的資料庫,目標結構描述設定在伺服器端資料庫,希望如果伺服器端的資料庫沒有這個結構或是結構有異動,程式可以建議加上去或是修改,一切都是以來源結構為主,並不是所謂的merge(合併),所以如果目標結構描述也有結構的話,此程式並不會修改到來源結構描述。
程式的建議如圖26:

圖 26:程式建議的資料結構描述改變。
圖26可以發現,目標資料會把 Table_1建議卸除,因為來源資料庫並沒有 Table_1的定義而目標資料庫有,所以此種結構描述的改變並不會變成雙方結構的合併同步,這是需要注意的,不然會造成結構描述的錯誤。
按下「寫入更新」,或是「匯出至編輯器」(如圖27),即可更改資料庫結構。

圖 27:可按下寫入更新或匯出至編輯器進行結構更改。
有時因為結構刪除而導致某些物件之間的關聯造成影響,也就是所謂的「孤兒物件」,這時會出現如圖28的警告訊息,此時就要留意不要出錯。

圖 28:可能出現孤兒物件的警告訊息。
重構資料庫物件名稱
在撰寫程式時,最擔心的問題就是,如果物件名稱有所改變時,呼叫你的那些預存程序是否也會自動的修改?在以往的程式撰寫經驗,當你想改變物件名稱時,多半都要先參考該物件的「相依性」,以決定受影響的物件有哪些,如圖29以及圖30:

圖 29:物件按右鍵選擇「View Dependencies」顯示相依性。

圖 30:顯示我與哪個物件有相依,與哪個物件與我有相依。
圖29與圖30顯示一個物件的「相依性」,例如兩個資料表間有設定外部鍵(Foreign Key),它們之間就有相依,或是A預存程序裡有呼叫 B資料表,而 B資料表若是更改了名稱,則A預存程序是否知情?如果A預存程序不知情,則它會呼叫舊的資料表而導致程式發生錯誤。
所以可以利用 Visual Studio 2010的功能「重構」,來解決這樣的問題。
首先,先利用 SELECT INTO產生一個自訂的資料表 OrderDetails:
SELECT * INTO OrderDetails from [order details]
並且產生一個預存程序呼叫這個資料表:
CREATE PROC usp_testproc
as
select * from OrderDetails
現在我們將 OrderDetails資料表改成 OrderDetailsTest,不過要利用重構,也就是點選 OrderDetails資料表(使用結構描述檢視),按右鍵選擇重構à重新命名,如圖31:

圖 31:選擇「重構」,再選重新命名。
這時請選擇重新命名的名稱,如圖32:

圖 32:設定一個新名稱。
當按下「確定」時,會隨即出現如圖33的畫面:

圖 33:重新命名會影響到的物件顯示。
按下「套用」後,不但連資料表名稱改變之外,連預存程序的名稱也跟著改變,再利用「資料」à「結構描述比較」à「新增結構描述比較」,更改伺服器端資料庫的結構描述。
結語
本期所説明的,是 Visual Studio 2010與 SQL Server資料庫的整合,但其實 Visual Studio 2010與資料庫方面的整合相當多,不是只有本期文章的內容,如果對這方面功能有興趣的,可以參考由悅知出版社所出版的「軟體測試實戰 Visual Studio & Team Foundation Server」一書。