Npgsql

如果你常常在微軟的世界裏寫程式，你一定知道什麼是 .Net Data Provider，它就是 .NET 資料庫程式設計用的資料庫驅動程式，而 PostgreSQL 專屬的官方 .Net Data Provider 就是 Npgsql。Npgsql 目前是 PostgreSQL 的應用程式界面中，維護的速度前三名的；另兩個是 ODBC driver 和 JDBC driver。半個月前 Npgsql 釋出 2.0.7 版。這只是 2.0 版的第七個安全性更新。已經釋出一年的 2.0.x 版，實作了給 .Net 2.0 和 3.5 甚至 3.5sp1 的 .Net Data Provider 絕大部份的功能。也就是說，如果你是在用微軟的 Visual Studio 2008 寫程式，不管你是用 C#、VB 還是 C++，就是用這個 Data Provider 就對了，就算你用的是不用錢的 Visual Studio Express 也能。很可惜的是，Npgsql 的文件實在太稀有了，只有一篇整理得不太好，而且也有不少錯誤的 User's Manual。這裏我用三個小程式來展示如何使用 C# 來開發 Windows 上的應用程式。這三個程式，你可以把它們當成我在測試這個 provider 是不是實作了社群所宣稱的功能。
要開始前，請先到 Npgsql 官方網站下載最新版本的 Npgsql，目前(2009/12/2)最新的版本，如上述的，是 2.0.7 版。但是你會發現下載網頁中，光是 Npgsql2.0.7 就有好幾個檔案可以下載：

我來介紹一下，那兩個 apidocs 就是 API 的 HELP 檔，有兩種格式，一種是 Windows 專屬的 CHM 檔，另一個是 HTML 格式。接著四個 bin 就是 provider，mono2 的兩個，是給 mono 用的，mono 是 Novell/SuSE 支持的一個計劃，也就是把 .NET port 到其它系統的計劃，一般在 Linux 上都很容易可以找到 mono。過一陣子我再來介紹它，現在先知道有這回事就好。接下來的兩個檔就很重要，ms 就是給 .NET 2.0 用的，ms.net3.5sp1 則是給 .NET 3.5 SP1 用的。如果你還在用 .NET 1.1，那抱歉，請你下載 1.0.1 版，而這個版本似乎沒人在維護了。不過，我目前所認識的開發者，不是已經追到 VS 2008，保守一點的至少也在用 VS 2005 了。當然也有骨灰級的還在 VB6 下撐著，這就不在本文的服務範圍內了。所以就看你用的是哪套 VS，下載你需要的版本。最後那兩個是原始碼，用 C# 寫的。所以有志幫忙維護的(或是你不小心發現一個 bug，又沒時間等人家官方的維護版本出來)，或是好奇人家怎麼寫出 .NET data provider 的可以下載原始碼下來參考。這就是 open source 的好處。
下載下來解開後，放到任何你想放的位置。當然你也可以到到系統的 GAC 中，但是我覺得沒必要，只需要開發時多做一個動作就行了，不然每次有新的維護版本出來時，要把舊版的移除還要動一堆手腳，實在太累。我的建議是建立一個新的目錄專門放 Npgsql，而且越明顯的位置越好。除非你中了木馬，否則放到明顯的地方，當你把舊版更新成新版時，會輕鬆不少。先來看看解開的情形：

有兩個目錄，一個是 bin，一個是 docs。docs 裏放的就是 HTML 版本的 API DOCs。我們要的 provider 放在 bin 這個目錄中：

注意看上圖，先不用管上面那堆放 L10N resource 檔的目錄，因為裏面沒有我們家的 zh-TW。這裏有三個 DLL，理論上我們開發桯式只需要用到 Npgsql.dll；至少我等一下要展示的三個測試程式只需用到它。記得它的位置，因為等一下我們要用到它。
我的測試環境是 Windiws 7 RC，Visual C# 2008 Express (就是 Visual Studio Express 的 C# 部份)，全部是免費的。沒錯，用免費的開發工具就很好用了，我一直認為，除非你不想用 NUnit，否則用這個免錢的開發工具已經夠用了，連簡單的「重構」功能都有了，欠缺的只有可以直接用 UML 的 class diagram 來設計程式這個功能要用標準版以上才有。但是如果你不習慣用這種工具來幫你把早期架構組合起來，那麼錢乾脆省下來比較實在。
第一個程式，測試連結
第一個程式，先來一個極簡單的程式，來測測資料庫連結，因為資料庫連結是一切資料庫應用的基礎，如果連結沒辦法建立，什麼事都不用做了。所以我們就來寫一個什麼事都沒做，只拿來測試連結可不可以建立的程式。
Visual Studio Express 其實一點都不陽春，它已經可以幫你建立相當 fancy 的 GUI 骨架，你只需把骨架上加上該有的程式碼就夠了，和早期的 VB 沒什麼兩樣。它和我年輕的時候的 Delphi 一樣，當你在設計使用者界面時，IDE 其實在後面一直辛苦的幫你建立一些骨架程式，讓你省下一堆要在紙上畫圖、想像的時間。
所以我們就先在「設計工具檢視」上，拉出一個 form，然後在 Form 上擺一個 button，然後在這兩個 control 上打幾個字(不一定要和我一樣打這幾個字)，就像下圖一樣：

接著第二步，把剛才下載的 Npgsql 加到參考中。方法如下，請打開「 專案(P)」/「加入參考(R)...」選單。

這時候會打開「加入參考」對話方塊，先切到「瀏覽」頁籤，然後把 Npgsql.dll 加入。

按下確定後，看看右邊的「方案總管」中的「參考」節點中是不是出現了 Npgsql？

有的話，恭喜你，可以進入第三步，打入以下的簡單程式。注意，部份的程式碼 Visual C# Express 已經幫你打好了，所以其實你需要輸入的字實在不多。

這裏有一行要改一下，就是連線字串那行。你的連線字串不可能和我的完全一樣，所以請改一下。

這四個參數我在這裏請明一下。Server 是伺服器的位址，你可以使用 IP 或 Domain Name，至於 GNC 我不確定可不可以，理論上是不行。本機你可以用 localhost 或 127.0.0.1 都可以。我是用 Virtual Machine，所以用 IP 比較簡單。你可以會問，用 Virtual Machine 不是每次重開 IP 都會改嗎？我至少知道 VMware 不會，每次你的 VM 都會用同一個虛擬 IP，所以可以把它寫死也無所謂。User ID 是你的 PostgreSQL 登入帳號，建議不要用 postgres 帳號，太危險了，請另外建立一個權限小一點的帳號，專門給應用使用。Password 是密碼，如果你是用 trust 連線方式，那這個可以略過，不過我建議你還是用 MD5 比較安全。Database 是連線資料庫，一樣，建議你另建一個給應用程式使用的資料庫，然後給予連線用帳號合適的權限即可。
如果你有用 SSL 替你的連線加密，那麼，還有兩個參數你必須加入，一個是 SSL，一個是 Sslmode。SSL 預設值是 False，你必須先把它指定成 True。Sslmode 有四種，就是你用 PgAdmin III 遠端連線時的那四種模式：需要、偏好、允許、停用。在這裏，它們分別是竐定成：Require、Prefer、Allow、Disable，預設值是 Disable。
好了，接著請在應該加入程式碼的地方加入程式碼。千萬不要忘了前面的 using Npgsql; ；還有，請注意大小寫。包括這行，應該有六行要加入。定義連接字串那行後面的註解就不用跟著打了。
打好後，請按下 F5 或上方工具列上的「開始偵錯」的那個綠色三角形，執行程式。

好，如果你確定伺服器已經執行，而且防火牆有打開 5432 這個 port，就按下那個 button，也就是我的程式 UI 的「開始測試」按紐。如果沒有意外的話，下面的訊息方塊這時應該會如預期的出現：

真是很簡單，而且沒用的程式。這個程式是一切 Npgsql 的程式的基礎，一定要是 Assembly 參考建立正確，然後資料庫的遠端連線設定完全正確，也就是允許你這個 VM 所在的網段可以連上伺服器，才能出現這個訊息方塊。
如果沒出現，那麼我建議你先檢查資料庫的設定是否正確。你可以先用 pgAdim III 連線看看，如果連 pgAdmin III 都連不上，那麼肯定伺服器端一定有哪裏設定錯誤。如果沒問題，那再來檢查哪裏出錯。當然要先確定你程式沒打錯。
這個無聊的測試程式如果沒問題，那麼接著我們該來試著寫個比較有用點的程式，先寫個從伺服器擷取一些資料的程式吧。
第二個程式，接收資料
第二個程式要寫之前，請先到我之前所寫的文章「利用網路上的範例資料庫來做 SQL 練習」最後面，下載 tbl_pgsql.sql 和 pg_sample.sql 這兩個資料庫範例。這兩個範例好像在 psql 執行時會有一點問題，應該是一個 psql 的 bug，但是你若用 pgAdmin III 的「查詢工具」；也就是工具列上有一張紙上面有一隻筆跟一個 SQL 字樣的那個圖示；用它執行我確定沒問題。這個範例很極端，因為我用了中文來當資料表格名稱，也有中文來建立表格的欄位。因為 BIG5 在 PostgreSQL 的支援仍然有問題，所以這裏要建立時，請確定你目前給程式連線使用的資料庫使用的編碼是 UNICODE。在 UNICODE 下，中文是完全支援的，即使你的 Windows 的系統編碼是 MS950 連線一樣不會有任何問題。
把範例資料匯入資料庫後，接著建立一個新的專案。首先，記得如上面那個例子一樣，把 pgsql.dll 加到參考裏。其次，如下圖一樣，建立一個上面有一個 dataGridView 和一個 button 的 form。要注意 dataGridView 不要拉得太小，拉大一點。

最後在那個 button 上 double-click，打開程式碼模式，加入如下圖的程式碼。一樣別忘了 using Npgsql; 這一行，還有把連線字串改一改，改成自己的。

dataGridView1 就是我們拉的那個 dataGridView 的 name，變數名稱。就算加上那四個大括弧，也只有新增 12 行文字，如果不算那四個大括孤，則只需八行！打完後，按下 F5 或是工具列上的「開始偵錯」的那個綠色三角形，執行程式。

接著用滑鼠按下右邊那個 button，我的 button 上的文字是「載入資料」，你可以用不一樣的文字(text)。

哈！帥吧，即使內碼是 Unicode，所有中文字呈現一點問題也沒有。注意，連爛位名稱都是中文的。再注意一下這行程式碼：
         using (NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from 商品清單;", conn))
連資料表名稱都可以完全用中文的，而且和 Unicode 完全相容。
這個程式終於勉強可以算是一個「資料庫應用」了，但是仍然太陽春了，所以我們再來展示更加強大的資料庫應用，讓我們下一個程式可以新增、修改、刪除資料，而且在送往資料庫前，你還可以取消。
第三個程式，更新資料
和前兩個程式相比，接下來的這個程式長了一些，不過和真正線上使用的程式相比，依舊算是相當短的程式。
這次我們用一個隨時可以丟掉的資料表格來測試；我們姑且叫它 t3 好了。這個 t3 有兩個欄位，分別是 col1 和 col2。col1 的資料型態是 serial，也就是可以遞增的整數；col2 的資料型態是長度 5 的 varchar 或是更正式的 character varying；然後建立一個 Primary key，擁有 col1 這個欄位。

CREATE TABLE t3
(
  col1 serial NOT NULL,
  col2 character varying(5),
  CONSTRAINT t3_pkey PRIMARY KEY (col1)
);

接著可以用 pgAdmin III，輸入七個字串，分別是：一一一一一、二二二二二、三三三三三、四四四四四、五五五五五、六六六六六以及七七七七七。很無聊的資料吧？但是鑑別力超高的！XD 好，就利用這個測試資料表格和這七個很無聊的資料，我們來進行第三個測試程式。
首先，我們一樣建立一個新專案，在專案中設計一個表單，在表單中放上三個 button 和一個 dataGridView。這次這三個 button 各有用處，所以我們分在它們的 text property 中輸入「載入資料」、「取消變更」和「儲存資料」。大致上這個 form 的 control 擺放的 layout 如下圖。

這個程式比較複雜，所以一些共用的性質，比如連接字串，我們可以儘量擺在 form 一開始的程式碼中，就像下圖。除了要記得程式碼一開始要記得把 using Npgsql; 加上以外，在 Form1 的一開始，可以先把連接字串定義好。同時把共用的三個 ADO.NET 的物件先定義好。我們會用到一個 DataTable，就叫它 dt 好了；會用到一個 DataAdapter，在 Npgsql，DataAdapter 叫做 NpgsqlDataAdapter，我們給我們的 NpgsqlDataAdapter 取名為 da；還需要一個 Connection 物件，Npgsql 的 Connection 物件叫做 NpgsqlConnection，我們的 NpgsqlConnection 就取名為 conn。
這樣還不夠，我們要替我們的 ADO.NET 在 Form load 進來的時候就設定好。所以我們在我們的 form，Form1 初始化時(建構子中)，把 dt、da 以及 conn 設定好，等待程式啟動時呼叫 Form1_Load 時可以馬上使用。
除此之外，在這裏，我們先建立一個 private 方法 LoadData()，這個方法的目的是把資料表格 t3 的資料載入，和剛才第二個程式的主要功用是完全一樣。不包括那兩個大括孤的四行程式碼很簡單，就是建立一個本地的 Command 物件，把它指定給 DataAdapter 的 SelectCommand，接著把 DataTable 填滿，最後把這個 DataTable 指派為我們那個 dataGridView 的 DataSource。這樣資料就會自動填入 DataGridView 中。
把這部份功能特別「取出來」，是因為程式中有兩個地方要用到，所做的重構(Extract method)，再往下看就可以看到呼叫它的場合。

接著要進入主題中的重點了。程式用到四個 event，分別是 Form1 的載入 event：Form1_Load，以及三個 button 的 Click event。先注意到 Form1_Load。Form1_Load 很簡單，只呼叫 LoadData()，把 t3 資料表格載入。這樣才不會在把資料庫入前，你就按下「儲存資料」，而發生程式例外。
再來第一個，也就是「載入資料」的那個 button 的程式碼。這個部份只比 Form1_Load 多了一行，就是 dt.Clear();，它的用意就是先把 DataTable 清乾淨，不然你一直連續按這個 button，DataGridView 裏重複的資料會一直複加上去，如果發生這種狀況，接著當你結束編輯資料，按下「儲存資料」，而就在資料回存時一定會造成錯誤。因為我們的 t3 有一個 Primary Key，重複的 col1 值會發生錯誤。
第二個 button 就是「取消變更」，只要你還沒按下「儲存資料」，你按下這個 button 所有的更新都會回復。這部份的程式碼非常短，只有一行：dt.RejectChanges();，這一行會把你在 DataGridView(其實是後面的 DataTable) 上所做的變更全部放棄，恢復原來未更動前的資料。

第三個 button 是最重要的「儲存資料」，這一段程式是本程式的重點，主要的程式包含在一個 try...catch 區塊中。重點在於我用了 DataAdapter 的附屬物件 CommandBuilder。CommandBuilder 可以在你回存資料前，先把你這一次期程所編輯、更改、刪除的所有動作，建立成你的資料庫所能了解的 SQL 指令。而建立完成後，你只需把產生出來的 Command 指派給相關的 Command 物件就可以了。指派好了以後，呼叫 update 就會更新資料庫的資料。最後再呼叫 AcceptChanges，完成此次的修改。其實這一段可以再把「載入資料」button 的那兩行程式加入，會讓程式更合理；只是這個程式只是測試，不用把程式重構到如此完美。

接著按下 F5 來執行看看。程式一執行，資料馬上就被載入了。

我們先試試第二個 button，「取消變更」的功能。要試就多試一點，我們來改兩筆資料看看。我們把第三筆的資料從「三三三三三」改成「三三三三八」，同時也把第五筆「五五五五五」改成「五五五五九」。

接著按下「取消變更」，你所改的所有資料都會恢復。只要你還沒按下「儲存資料」之前，所有變更都會恢復。

再來試試「儲存資料」。這次我們修改第四筆資料，把它從「四四四四四」改成「四四四四九」；接著新增一筆「八八八八八」的資料。

按下「儲存資料」，畫面會有點亂，再按下「載入資料」，諾，資料更新了。而且顯示的順序是更新的順序。這是因為 PostgreSQL 的 MVCC，顯示的是儲存的資料的版本順序。

你可以不相信，那麼我們叫出 pgAdmin III 出來。在左邊找出 t3，按下滑鼠右鍵，找出「檢視資料」/「檢視所有資料」。

對吧，資料更新了。

好像還沒試刪除資料的功能，接著把剛才新增的第八筆資料刪除，按下「儲存資料」看看。

不相信刪掉了？沒關係，叫 pgAdmin III 出來做證。看吧！第八筆砍掉了。

看吧，連 CommandBuilder 的功能都完備了，不想繼續被微軟綁死的朋友們，要不要來試試 PostgreSQL 和 Npgsql 呀？
繼續往前走
Npgsql 被微軟的信徒抱怨最多的大概是它沒有提供一堆給 Visual Studio 用的 Data Control，所以他們認為開發速度會減慢。但是，我的看法不是這樣的。其實在 Visual Studio 2005 之後，很多控制項都有 DataSource 這項 property。所以你還是可以很快速，很方便的利用這個性質，和你的 DataTable、DataSet、DataAdapter...... 綁在一起，開發的效率不會降低。
另一個微軟的使用者比較擔心的是，一般他們設計採用的是 MS SQL Server，所以所有物件清單很容易在 MSDN 中，甚至在坊間的 ADO.NET 的書籍中查得到。但是 Npgsql 的相關物件的清單，卻不知道要如何對照？這個問題的答案更簡單。記得本文一開始，在下載那部份，在下載的檔案清單中，是不是有兩個 apidocs 的 ZIP 檔？這就是清單。你只需把這裏的東西，和 MSDN 中 Sql...... 開頭的做對照(Npgsql......)，就可以完全轉換過來。換句話說，幾乎只要出現 Sql 的地方，全改成 Npgsql 就可以了。
最後要參考的是一個重要而且很多人關心的網頁。Npgsql 有哪些東西還沒做到(而不是哪些功能完成)？有清單嗎？有，這個負面表列在這裏可以找到：http://npgsql.projects.postgresql.org/roadmap.html
看到我測試的這三個程式，我相信你應該躍躍欲試了，那就開始動手吧。把越來越貴的 MS SQL Server 丟到垃圾桶，換成 PostgreSQL，把週週為了更新，都要停機一段時間的 Windows Server 換成 FreeBSD 或 Linux 吧(連 Solaris 也行喔，改天我來教教大家如何安裝)。