我從很久以前就對 Visual Studio 中新加入的資料庫專案範本感到很有興趣; 但是一來我並不熟資料庫, 二來工作上也用不到, 所以從來沒有真正較深入地研究它, 只有嘗試過建立幾個測試專案, 然後被搞得灰頭土臉, 被迫放棄。但是最近由於工作上突然有了一點變化, 讓我回頭來思考在原來的專案中加上一個資料庫專案的想法, 所以就重新把它又拿來研究一番...
我從很久以前就對 Visual Studio 中新加入的資料庫專案範本感到很有興趣; 但是一來我並不熟資料庫, 二來工作上也用不到, 所以從來沒有真正較深入地研究它, 只有嘗試過建立幾個測試專案, 然後被搞得灰頭土臉之後, 被迫放棄。但是最近由於工作上突然有了一點變化, 讓我回頭來思考在原來的方案中加入一個資料庫專案的想法, 所以就重新把它又拿來研究一番。
由於我對資料庫沒有什麼研究, 也沒有很大的興趣 (我是對這個專案範本和其應用有興趣), 所以我的實驗都僅僅基於我個人的需求與興趣, 並沒有從什麼學術研究的方向切入。所以若有其他專家在看過這篇之後, 覺得我根本是在胡說八道的話, 那麼恐怕要勞煩你公開在這裡提出指正或者指教, 不要在我的背後做人身攻擊。感恩!
首先, 我必需先聲明, 我的實驗都是在 VS2013 Preview (搭配 MVC 5 + Entity Framework 6) 上完成的。所幸, 似乎並沒有聽說 VS2013 的資料庫專案跟以前的版本有什麼太大的改變, 所以, 我想我在這裡寫的每一個重點應該都跟你用的是不是 VS2013 無關; 在較舊的 VS 版本 (特別是 VS2012) 上應該也是相同的做法。可惜的是, 經過幾次測試, 我發現在 VS2013 上建立資料庫專案時, 它會要求你去安裝最新的 SQL Data Tools, 但這會導致在 VS2012 建立資料庫專案時, 發生 Data Tools 版本不相容的情況。我想, 這個問題必須等到 VS2013 正式版之後才能獲得最終的解決。
要建立資料庫專案, 在 VS 的範本中請選擇 SQL Server 頁籤, 然後選擇 "SQL Server 資料庫專案" (這也是此處唯一的一個範本)。
不過, 在我們真正進行資料庫專案的設計前, 我要來講講這個專案範本可以應用在什麼地方。
當 VS 剛剛加入這個範本時, 我原本以為它只是一個 DBA 的工具, 與開發者沒有關係, 所以一點也引不起我的興趣。後來, 由於有些朋友提到它, 我還誤以為它是什麼 ORM 工具 (我記得那時候 LightSwitch、Entity Framework 和 NHibernate 等工具剛好都在起步階段, 所以我自以為是的以為它們是同類的東西)。經過短暫的研究, 我發現根本不是那回事。
然後, 我發現原來我可以拿這個專案來開發資料庫; 就像 MSDN 網站上把它歸類為「專案導向的離線資料庫開發」一樣。剛開始時覺得新鮮, 但是我旋即念頭一轉, 很直覺地認為「我直接在資料庫上面做不就好了嗎」? 所以, 我很快地把這東西抛諸腦後了。
一直到了不久之前, 我突然發現自己必須面對一個事實, 就是公司的網站和資料庫的部署, 恐怕沒有從前那麼單純了! 由於牽涉到機密和安全性考量, 開發人員不能直接進入機房, 也不能直接做資料庫檔案的 attach 動作, 而是必須交給專職的網管來做這個動作。這讓我不得不回頭來重新思考是否應該採用資料庫專案來設計資料庫。事實上, 我已經重複地在測試 Server 上把資料庫成功地刪掉又發行過好幾次了, 證實這種專案是可行又可靠的。
以下, 我要簡單地描述我截至目前為止所研究出來的 Best Practice。
首先, 把客戶需求研究清楚, 然後把資料表、欄位、對應性都拉出來 (我不知道別人怎麼做; 我個人習慣使用心智圖做規劃)。然後在資料庫專案中建立一個稱為 Tables 的資料夾, 把建立各個資料表的 SQL 指令放進去。
此外, 我發現我可以把 Constraint 同時寫進建立資料表的指令裡面, 如下例:
CREATE TABLE [dbo].[tblCommunication]
(
[CommunicationId] INT NOT NULL PRIMARY KEY IDENTITY,
[IssueId] INT NOT NULL,
[UserId] NVARCHAR(50) NOT NULL,
[Message] NVARCHAR(MAX) NOT NULL,
[DateCreated] DATETIME NOT NULL DEFAULT getdate()
)
GO
ALTER TABLE [dbo].[tblCommunication] ADD CONSTRAINT [FK_tblCommunication_tblIssue] FOREIGN KEY([IssueId])
REFERENCES [dbo].[tblIssue] ([IssueId])
ON DELETE CASCADE
GO不過我後來又發現, 其實我應該在專案中新增「外部索引鍵」這個項目 (在「資料表和檢視表」頁籤裡), 也就是把 "ADD CONSTRAINT [FK_tblCommunication_tblIssue] …" 這一段單獨抽出來。但是我現在覺得, 既然可以寫在資料表定義裡, 其實就寫在那裡也無妨。
接著, 我想, 既然我可以定義資料庫格式了, 那麼我可不可以順便插入測試資料呢?
我原本想著, 為何不和 Contraint 一樣直接放在資料表的 CREATE 指令後面呢? 但這是不行的。先別說這麼做可能會違反 Contraint 了, 在 CREATE 指令後面根本不允許 INSERT 指令。所以這個動作行不通。
我後來的做法是單獨寫一個 Stored Procedure, 然後把所有寫入測試資料的指令全部寫進去:
CREATE PROCEDURE [dbo].[spInsertSampleDataToTables]
AS
BEGIN
SET IDENTITY_INSERT [dbo].[tblCategory] ON
INSERT [dbo].[tblCategory] ([CategoryId], [Category], [IsSystem], [DateCreated]) VALUES (1, N'A管理', 1, CAST(0x0000A1F2012C38DE AS DateTime))
INSERT [dbo].[tblCategory] ([CategoryId], [Category], [IsSystem], [DateCreated]) VALUES (2, N'B管理', 1, CAST(0x0000A1F2012C5A39 AS DateTime))
INSERT [dbo].[tblCategory] ([CategoryId], [Category], [IsSystem], [DateCreated]) VALUES (3, N'C管理', 1, CAST(0x0000A1F2012C62C4 AS DateTime))
INSERT [dbo].[tblCategory] ([CategoryId], [Category], [IsSystem], [DateCreated]) VALUES (4, N'D管理', 1, CAST(0x0000A1F2012C6C6E AS DateTime))
SET IDENTITY_INSERT [dbo].[tblCategory] OFF
END
RETURN 0這麼做有兩個好處: 一來, 你可以控制不同資料表的資料的插入順序, 所以不會違反任何 Constraints。二來, 你可以在資料庫部署好之後再去執行它, 或者不去執行它 (例如在 Production Server 上)。
當你把資料庫設計好之後, 就可以把它發行出去了。方法是, 在專案名稱上按滑鼠右鍵, 然後選擇「發行」, 然後選擇目標資料庫連接 (很可惜, VS2013 並不會記錄連接資訊, 所以每次在測試發行時都要重打一次; 這是小小的不方便之處), 然後 SQL Server 上就會自動建立起這個資料庫了。接著, 你可以手動執行上面提到的那個 Stored Procedure, 把測試資料建立起來。
套用這種模式, 我們以後就可以方便地把整個方案打包起來, 而且包在裡面的資料庫並不是資料庫檔案而是資料庫專案。同時, 透過上述方法, 我們可以使用文字方式去產生並編輯你需要的測試資料, 甚至指定 Identity Key 的 ID 號碼 (雖然我想不出這樣做有什麼必要性)。總之, 它可以讓我們對資料庫的啟始調整方便一些。
或許你會問, 建立資料庫專案去部署資料庫, 和使用 SQL 指令去建立資料庫, 這有什麼兩樣? 我個人的看法是這樣的: 首先, 前者可以使用「發行」的方式進行部署, 後者則必須透過 Management Studio 或其它方式去執行 SQL 指令。其次, 前者比較能夠方便我們以專案的方式, 更具結構性地透過不同的 task 去做資料庫的設計; 每個 task 都有明確的區隔; 你的資料庫愈複雜, 它的好處就愈彰顯。而後者只是把指令全部寫在一起而已。當然啦, 對於熟練的 DBA 而言, 這兩種做法可能並沒有差異。但是如果團隊裡沒有 DBA, 我覺得前者仍然是一個可以考慮的做法。
