Excelに外部データベースを自動取り入れする方法

ここで使用する技は、次の使用状況を前提に考えた：

- 参照するキーはExcel上もともとある資料

- 外部データベース（今回はSQL Serverを例にする）には、すでに必要な資料を整理してある（つまり、DB内のTableのJOIN等は、Viewなどの形で、すでに整理してある）

使用する技の中身を明かすと、次のように分解する：

1. 必要なパラメータをそれぞれTableにする（Queryを掛けるときには必要）

2. それぞれのパラメータをクエリに変換。（後ほど詳しく説明）

3. 本当にデータをクエリし、必要のパラメータフィルタはひとまず「定数」で設定。

4. 上記定数のパラメータをそれぞれクエリに入れ替え。

それでは、ステップ分けて説明する。

パラメータをテーブルに設定

例として、MicrosoftのNorthwindデータベースをSqlServerのLocalDBに構築した前提で進む。目標としては[Order Details Extended]というViewの中で、OrderIDが同一するものを抽出する。

では、まずは[OrderID]をTableに設定する。A1のセルにOrderIDと入力し、例えの内容として、"10254"をA2に入力する。

A1のセルをクリックして、[挿入] - [テーブル]をクリックして、該当範囲をテーブルに変換する。ショートカットは[Ctrl-T]を押しても同じようなものが出る。注意すべきのは、「先頭行をテーブルの見出しとして使用する」にチェックを入れること。Excelの設計上、必ず一番上のマスにタイトルを入れるので、自分でタイトルを入れないと、Excelが勝手にタイトルを付けておくようになってしまう。

以上で、パラメータをテーブルに変換する手順が終わる。

パラメータをクエリに変換

次に、それぞれのパラメータテーブルをクエリに変換していく。

テーブルの一部をクリックして、[データ] - (取得と変換) - [テーブルから]をクリックする。とすると、Power Queryエディターが出てくる。

先ずはタイトルの部分を注目。ここでは「数値」としてマスの内容を認識しているはず。どっちにしよう、SQLクエリを掛けるのに、「文字」タイプが一番ベスト。ゆえに、ここは一度文字へ変換しておく。また、テーブルが複数欄になってしまった場合、ここで一度整理整頓を行う。一つのパラメータは一つの欄だけにすることが大事。

そして、1欄目のところに右クリックして、「ドリルダウン」をクリックする。とすると、ちょっと変な形になるが、使用上支障はないので、無視する。右側にプロパティが出るので、ここで名前をQueryOrderIDとする。

そのままの形で、ホーム - 閉じて次に読み込む　(閉じて読み込むの裏にある)をクリックする。読み込み先が聞かれてくるが、「接続の作成のみ」として読み込み。

上記の繰り返して、すべてのパラメータをクエリへ変換しておく。

一度本クエリを行う

データ⇒新しいクエリ⇒データベースから⇒SqlServerデータベースからをクリック、接続のダイアログを出す。サーバーは　(localdb)\MSSQLLocalDB で、データベースは Northwind となる。

実際に接続するときには、毎回接続情報を聞いてくるが、そこは各自に解決してください…

今回接続したいテーブル（実はView）は前述の「Order Details Extended」、そして下の「編集」をクリックする。

それぞれパラメータを掛けたいところに、タイトルの右にあるドリルダウンボタンをクリックし、フィルタを掛ける。今回の案では、10254とフィルタする。

終了したときは、同じホーム ⇒ 閉じて次に読み込む として、同じ表のA4セルに読み込む。

クエリのパラメータを入れ替える

鬼門が来た。前のステップで作成した本クエリのところをクリックして、編集を掛ける。編集を行いやすいように、表示 - レイアウト - 数式バー　にチェックを入れる。

とすると、数式バーに「[OrderID] = 10254」のような文字が書いてある。10254のところをOrderIDと書き換える。

同じように、掛けたいパラメータのところをそれぞれ対応するクエリ名に入れ替えて置く。

完成したら、閉じて読み込む。

以上で、完成。

おわりに

SQLクエリを変更可能なパラメータで行うには上記のやり方で可能だが、実際に操作してみてはやはり面倒くさいし、いろいろセキュリティ上の問題で、ネットでこれらの方法を展開していないのは理由が分かる気がする。自分でもやってみて「VBAにした方が楽だなぁ」と思ったりする。なにかいい方法があれば教えてください。

WPFでEFオブジェクトにData Bindingする場合の4t自動設定

WPFコントロールに自動的にBindingする場合、
デフォルトの4Tフレームワークで生成するファイルに少しだけの変更を入れると、あとはいつもの通りに任せきりにします。

内容はMSDNの公式文書からの抜粋で、私なりに必要な部分だけ取り出しました。

まず、適用する環境は次の通り：

• Visual Studio Express 2017 for Windows Desktop
• SQL Server Express 2017(大事でないけど、一応記録しておく。)
• EF 6.0
• WPF 3.0以上

そして、使用する手法はDatabase Firstで、ある程度データベースに保存するデータの形はDBですでに定義している前提で進みます。

ちなみに、MSDN公式は次の通り

https://docs.microsoft.com/ja-jp/ef/ef6/fundamentals/databinding/wpf#bind-object-to-controls

MSDN公式を閲覧した時点では内容めちゃくちゃなので、蛇足ですが私なりに訳しておきました。

https://hackmd.io/@rokashou/rkSHqVM2H

カギになる設定の部分を抜粋：

• ソリューションエクスプローラーを開き、edmxファイルの下にあるttファイル探します。編集としてファイルを開けます。
• 二箇所にある「ICollection」を「ObservableCollection」へ置き換えます。それぞれは約296行目と484行目あたりにあります。
• 最初に出た「HashSet」を見つけて「ObservableCollection」に置き換えます。 それは約50行目にあります。コードの後半にある2番目のHashSetを置き換えないでください。
• 一回だけある「System.Collections.Generic」を検索し、「System.Collections.ObjectModel」に置き換えます。 それは約424行目にあります。
• .ttファイルを保存します。 これにより、エンティティのコードが再生成されます。 コードが自動的に再生成されない場合は、.ttファイルを右クリックして、「カスタムツールの実行」を選択します。

以上。

あとは普通にプログラムを組んでください。

ちょっと前のやつにはいろいろ不具合があるようで、見つけた次第にエラーでないように更新していく…

Codd's 12 rules 科德十二定律

Codd's 12 Rules/科德十二定律/コッドの12の規則

---

Related to Wikipedia 2016/8/2 data
純屬資料收集，順便做一下語言學習

---

全關係系統十二準則

全關係系統應該完全支持關係模型的所有特徵。關係模型的奠基人埃德加·科德具體地給出了全關係系統應遵循的基本準則。

準則0 The Foundation rule

For any system that is advertised as, or claimed to be, a relational data base management system, that system must be able to manage data bases entirely through its relational capabilities.
一個關係形的關係資料庫系統必須能完全通過它的關係能力來管理資料庫。

準則1 The information rule 信息準則

All information in a relational data base is represented explicitly at the logical level and in exactly one way — by values in tables.
關係資料庫系統的所有信息都應該在邏輯一級上用表中的值這一種方法顯式的表示。

準則2 The guaranteed access rule 保證訪問準則

Each and every data (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.
依靠表名、主碼和列名的組合，保證能以邏輯方式訪問關係資料庫中的每個數據項。

準則3 Systematic treatment of null values 空值的系統化處理

Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.
全關係的關係資料庫系統支持空值的概念，並用系統化的方法處理空值。

準則4 Dynamic online catalog based on the relational model 基於關係模型的動態的聯機數據字典

The data base description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.
資料庫的描述在邏輯級上和普通數據採用同樣的表述方式。

準則5 The comprehensive data sublanguage rule 統一的數據子語言

A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all of the following items:

1. Data definition
2. View definition
3. Data manipulation (interactive and by program).
4. Integrity constraints
5. Authorization
6. Transaction boundaries (begin, commit and rollback)

一個關係資料庫系統可以具有幾種語言和多種終端訪問方式，但必須有一種語言，它的語句可以表示為嚴格語法規定的字符串，並能全面的支持各種規則。

準則6 The view updating rule 視圖更新準則

All views that are theoretically updatable are also updatable by the system.
所有理論上可更新的視圖也應該允許由系統更新。

準則7 High-level insert, update, and delete 高階的插入、修改和刪除操作

The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.
系統應該對各種操作進行查詢優化。

準則8 Physical data independence 數據的物理獨立性

Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.
無論資料庫的數據在存儲表示或存取方法上作任何變化，應用程式和終端活動都保持邏輯上的不變性。

準則9 Logical data independence 數據邏輯獨立性

Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
當對基本關係進行理論上信息不受損害的任何改變時，應用程式和終端活動都保持邏輯上的不變性。

準則10 Integrity independence 數據完整的獨立性

Integrity constraints specific to a particular relational data base must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
關係資料庫的完整性約束條件必須是用資料庫語言定義並存儲在數據字典中的。

準則11 Distribution independence 分布獨立性

A relational DBMS has distribution independence.
關係資料庫系統在引入分布數據或數據重新分布時保持邏輯不變。

準則12 The nonsubversion rule 無破壞準則

If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher level relational language (multiple-records-at-a-time).
如果一個關係資料庫系統具有一個低級語言，那麼這個低級語言不能違背或繞過完整性準則。

Ref:
http://computing.derby.ac.uk/c/codds-twelve-rules/
http://www.itworld.com/nl/db_mgr/09022002

https://ja.wikipedia.org/wiki/ゴッドの12の規則

Use SQLite in Java(Netbeans)

It's my study note.
I made it by reference to Jens-André Koch's webpage, and updated it into a netbeans 8.0 with SQLite 3.8 version.

---

Step 1. Download the SQLite JDBC Library

To use SQLite in Netbeans, we must download the library at first.
We can get the library here:
https://bitbucket.org/xerial/sqlite-jdbc

Step 2. Add the JDBC Driver into your project

Add the downloaded JAR into our project.
It can be copied into the project's lib dir, too.

At this moment, it was sqlite-jdbc-3.8.7.jar

Step 3. use it in your code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.sqlite.JDBC;

public class JavaSQLiteTest {

    public static void main(String[] args) throws ClassNotFoundException {
        // load the sqlite-JDBC driver using the current class loader
        //Class.forName("org.sqlite.JDBC"); // you can use this if you don't use the import.
        
        Connection connection = null;
        try{
            connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
            Statement stm = connection.createStatement();
            
            stm.setQueryTimeout(30); // set timeout to 30 sec.
            stm.executeUpdate("drop table if exists person");
            stm.executeUpdate("create table person (id integer, name string)");
            stm.executeUpdate("insert into person values(1,'leo')");
            stm.executeUpdate("insert into person values(2,'yui')");
            
            ResultSet rs = stm.executeQuery("select * from person");
            while(rs.next()){
                // read the result set
                System.out.println("name = " + rs.getString("name"));
                System.out.println("id = " +rs.getInt("id"));
            }
        }catch(SQLException e){
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        }finally{
            try{
                if(connection != null)
                    connection.close();
                
            }catch(SQLException e){
                // connection close failed.
                System.err.println(e);
            }
        }
    }
}

Step 4. to manage your database in netbeans

1. Add the JDBC driver in Service->Databases->Drivers
there should be "org.sqlite.JDBC" showed in "Driver Class", and the Name field is "SQLite".
2. Add a JDBC link by SQLite JDBC Driver
right-click on the SQLite Driver, and select "Connecting Using...". Then give the JDBC URL as you like.
To connect with the file named "sample.db", the JDBC URL should be jdbc:sqlite:sample.db 3. Done.

十個PostgreSQL比SQL Server好的理由

Ten Reasons PostgreSQL is better than SQL Server

原文出處: Ten Reasons PostgreSQL is Better Than SQL Server
原文作者: Jeremiah Peschka
原文發布時間: Dec. 06, 2011

---

為什麼會有人想要用PostgreSQL取代SQL Server？當你在選擇如何儲存你的資料之時會有很多因素需要考慮。有時候你需要看一些比標準選擇更深層一些的部分，並考慮一些新的事情。如果你是要開始一個新的專案，你應該將你的資料儲存在哪裡？以下列出十個理由，告訴你為什麼也許應該要考慮用PostgreSQL而不是SQL Server。

每年版本發布 Release Every Year

讓我們面對問題吧，等某個產品的三到五年的更新週期去加入新的功能是件痛苦的事。我不想不斷地學習新功能，但是另一方面，我又不想為了解決業務上的重大問題而自己寫黑客解決方案，因為我知道某些東西終究會沿著管線下來，但是在我自己實踐他之前我又不能等上個好幾年。迅捷發布週期保證PostgreSQL的開發團隊可以快速地將用戶需求的功能交出來，並且持續的改進。

從9.0版開始，PostgreSQL的發布週期已經切換為年度週期。在那之前，PostgreSQL總是在新功能完成時發布釋出。縱觀維基百科上的主要版本的發布時間，我們可以看到每18個月會推出新的主要版本。對軟體產品來說，18個月的發布週期並不差，比起一個資料庫之類的重大任務來說已經是短得多了。

真．序列化 True Serialization

快照隔離(snapshot isolation)保證同一次的交易處理中的所有的讀取動作都會對應到一致的資料快照內容。另外，一次的交易處理應當只在當次的快照之後的資料變更沒有起衝突的時候進行提交。但不幸的是，快照允許異常狀況存在。那可能會發生一種狀況，兩筆有效的交易發生，讓資料庫留在一個不一致的狀態上 — 資料庫不會符合它自己的資料完整性規則。

序列化快照隔離(serializable snapshot isolation)在9.1版中被加進了PostgreSQL中。SSI模擬了嚴格的序列化執行動作 - 交易行為會以一個接著一個的方式被執行。如果中間有衝突發生，甚至是一個潛在的衝突，資料庫引擎會將一個錯誤丟回給呼叫者(呼叫者將留下如何處理下一步的指示)。

序列化快照隔離聽起來很痛。但厲害的是它提供讓資料庫去擁有更高等級的一致性控制方案。應用程式可以在假設資料修改會失敗的前提下進行開發，隨後重試失敗的交易。真正的好處是寫得好的城市可以預防資料不一致，並且維持所有的操作都應該是符合期望的形象。

理智的預設值，荒謬的調校 Sane Defaults, Ridiculous Tuning

好吧，讓我們保持公平，PostgreSQL附帶了一些荒謬的保守性共享記憶體設定。其他大部分的PostgreSQL設定都是保守的，但是一般來說足以滿足大多數普通的工作負載。大多數人在佈署PostgreSQL的時候將不需對PostgreSQL做太多變更(也許只是在開始時將shared_buffers增加到整體記憶體的25%)。

一旦PostgreSQL安裝完成開始運行，有一些設定還是可以變更。最好的部分是，雖然，是在伺服器，資料庫，用戶，或甚至是單獨查詢級別中可以對其大部分的設定進行變更。擁有混合工作負載的伺服器是十分常見的 - 大部分在該伺服器上的活動都是在基本的CRUD上執行，但是活動中的百分之幾的小部份，用來做報告的那些，需要調整為高一些的優先度。與其將獨立報告移到分離的區域(不管是分離的伺服器，資料庫，還是相同資料庫中的分離資源池)去執行，我們可以藉由使用適當的參數簡單地調校小部分的查詢，包括分配要用來做排序(sort)與聯結(join)的記憶體分配。

無紀錄資料表 Unlogged Tables

你是否對試著取得批量插入作業的少量記錄感到不快？我也是。與其嘗試各種不同的方法去讓某些資料表的紀錄少量化，PostgreSQL讓我們選擇可以創建一個無紀錄資料表 - 只要在創建資料表create table語句中簡單地直接加上UNLOGGED字句，一切就都準備好了。

無紀錄資料表會繞過寫入起始紀錄；他們沒有崩潰防護，但是他們就是可以執行的飛快。無紀錄資料表中的資料將會在伺服器崩潰或是不明關機後被截斷，不然他們就是一直在那裏。他們也會被排除在備用伺服器的備份以外。這讓無紀錄資料表成為ETL或是其他資料操作程序的理想選項，那會讓他們易於重複使用原資料。

地理位置的KNN...以及更多 KNN for Geospatial… and More

對，我有聽過這消息，關於SQL Server將會很快地擁有這個功能，但是PostgreSQL已經擁有了。如果K最近鄰居(K Nearest Neighbor)搜尋對你的業務來說非常重要，你已經走過一些痛苦的過去，為了要讓他在你的RDBMS裡面起作用。或者你已經放棄並且在其他地方實作解決方案。我不會怪你 - 地理位置查詢是好，但是沒有KNN功能會讓人想死。

PostgreSQL的KNN查詢在特定索引(index)類型上動作(在PostgreSQL中有許多種索引類型)。你不只可以使用KNN查詢去找到前五個離你最近的Dairy Queen(*連鎖冰品店)，你還可以使用KNN搜尋去找其他的資料類型。你完全有可能藉由執行KNN搜尋去找到10個最接近"冰淇淋"的語句。

KNN搜尋能力使得PostgreSQL在任何尋找地理位置查詢功能的人眼中是個有力的競爭者。而追加的柔軟性讓PostgreSQL在其他同種搜尋導向的應用程式中處於領導地位。

交易管理同步複製 Transcation-Controlled Synchronous Replication

要保持你的資料庫的另一個備份的最簡單的方法之一，就是用某種資料庫複製(database replication)。SQL Server DBA將主要使用交易紀錄性的複製(transactional replication) - 一個專門的代理程序會讀取SQL Server的紀錄，蒐集重要的指令，然後將其傳送給應用他們的訂戶。

PostgreSQL的內建複製，比起SQL Server的複製來說，更接近SQL Server的鏡像(PostgreSQL的複製是個可讀性的待機)。紀錄活動將會固定在主要機上，然後被傳輸給二號機。那個動作可以是同步也可以是非同步的。直到PostgreSQL 9.1為止，複製是一個全友或是全無的動作 - 每一筆交易不是同步就是非同步的。開發者可以藉由單次交易的synchronous_replication設定值去設定一個特定的交易。這很重要，因為它讓我們在除錯時可以寫入大量的複製資料進紀錄資料表而不會受到同步提交在寫入紀錄資料表的效能影響。

每當我們在開發應用程式時有更多的選項時，我都很快樂。

可寫的CTE Writeable CTEs

CTE對讀取來說很好，但是如果我需要用他們來做一些更複雜的動作，那會發生一些其他的問題。一個例子會讓他更簡單一些。比方說我想刪除過時的資料，但是我想要將它儲存在一個封存資料表中。要在SQL Server上這麼做，最簡單的方法(從開發的觀點上)就是提升我的隔離級別(isolation level)到至少快照的等級，如果不是序列化(serializable)的，並且使用隔離等級去保證資料不會被變更。我也可以讀取將要被刪除的註解的PK值，將它放在一個暫存資料表中，並多次參照它。

兩種方法都可行，但是兩種方法都有問題。第一種方法需要程式碼在特定隔離等級上執行。這讓目前的特定設定會失效。程式碼也可以從特定程序中被複製出來，並在SSMS中運行，會導致潛在的異常，會有少數的幾行被刪除且不存在於封存之中。對垃圾留言來說不是個大問題，但是在其他的狀況中這可能會是大問題。第二種方法並不是十分差勁，而且他也沒有錯，只是它涉及額外的程式碼噪音。暫存資料表對解決我們的問題來說不是必要的，而且它是在處理不同隔離級別時產生的副產品。

PostgreSQL有不同的方法來解決這個問題：可寫式的CTE。CTE是用T-SQL中被打造的同樣方式來打造。不同的是，當我們使用PostgreSQL，資料可以在CTE之中被更改。然後輸出資料可以就像其他CTE輸出一樣被再度利用：

CREATE TABLE old_text_data (text_data text); 

WITH deleted_comments AS ( 
  DELETE FROM comments 
  WHERE comment_text LIKE '%spam%' 
  RETURNING comment_id, email_address, created_at, comment_text 
) 
INSERT INTO spam_comments 
SELECT * 
FROM deleted_comments 

這可以結合預設值(default value)，觸發器(trigger)，或是任何其他資料修改方式去打造非常豐富的ETL鏈。在表面之下它可能就像我們在SQL Server上做的事一樣，但是好處是它簡潔。

擴充 Extensions

有曾想過要在SQL Server上增加一些功能嗎？要如何保持這些功能是最新版的？這也許對DBA來說是個大問題。當你在橫跨生產環境中執行管理用腳本時要跳過一台伺服器是十分簡單的事。更進一步，你怎麼知道你已經安裝的是哪個版本？

PostgreSQL擴充功能網路(PostgreSQL Extension Network, PGXN)是一個外部功能的集中發放點。它是開源PostgreSQL書庫的一個可信賴的來源 - 不會包含任何可疑的二進位程式。再加上，在PGXN上的任何東西都有版本編號。當更新時，PGXN會有效地提供，擴充功能將會為你關注更新途徑 - 它知道如何確認自己是最新的。

有一些關於排名的擴充功能像是 K均值叢類(K-Means clustering)、甲骨文兼容功能(Oracle compatibility functions)、Amazon S3遠端查詢(remote queries to Amazon S3)。

將這些功能推出並放進擴充功能中，讓開發者與DBA易於打造客製化封包，讓它看起來且動作就像是PostgreSQL的核心功能，但卻不需試著在PostgreSQL發布程序中取得這些包裝。這些包裝可以之後再獨立開發，進而提升他們自己的效率，並提供那些無法符合PostgreSQL核心團隊的發布計畫的複雜功能。簡單地說，這有一個環繞PostgreSQL的健全生態系統。

豐富的時間資料類別Rich Temporal Data Types

我最喜歡的PostgreSQL的功能之一就是它的時間資料類別的豐富支援。當然，SQL Server 2008最終為SQL Server帶來了一些成熟的時間資料支援，但是那還是塊十分貧脊的土地。對時間資料的強力支援在很多工業中是關鍵議題，而且不幸地是，在SQL Server中還有許多工作在繼續進行，為了解決要SQL Server對時間資料支援上的限制。

PostgreSQL引進了時區智能處理。加上了對ISO 8601標準(1999-01-08 04:05:06 -8:00)的支援，PostgreSQL支持用縮寫(PST)或是指定位置識別子(America/Tijuana)來識別時區。縮寫會對應到從UTC的固定偏移量，而位置識別子會加上夏令時間的規則支援。

在時區柔軟性之上，PostgreSQL有個interval資料類別。interval資料類別能夠在14位精度上儲存最大178,000,000年的間隔。interval可以量測時間，以一年的間隔內測出比微秒還要小的精細度。

排除約束 Exclustion Constraints

你是否有嘗試過用SQL Server寫任何一種排程功能？如果你有，你將會知道當你有業務上的需求像是"兩個人不能在同一時間占用同一間會議室"，你就會知道這很難執行程式碼，而且通常會需要資料庫中的額外動作。有很多方法可以完全透過在應用程式層別程式碼來實現這個功能，但沒有一個會讓用戶與開發者開心。

PostgreSQL 9.0引進了排除約束在欄位之中。換句話說，我們定義資料表並加上追加的約束，那包括一些檢查(check)，且至少要有一項檢查是假(false)。排除約束藉由索引的罩門下受支援，所以這些動作可以像是在我們的磁碟以及我們所設計的索引一樣快。你可以在時空間結合資料上使用排除約束，確定在同一個時間點不同的人不能預約同一間會議室或是區塊不會重疊。

在2010 PGCon大會中有一份發表資料是關於排除約束的細節部分。雖然沒有視頻，但是幻燈片已經足以讓他們展示足夠的案例與解釋幫助你開始。

加分功能 - 原價 Bonus Feature - Cost

它是免費的。所有的功能都是。PostgreSQL沒有版本分類(edition) - 功能總是存在資料庫中。一些公司會提供商業支援，他們之中有些甚至會提供追加的閉源功能，但是核心的PostgreSQL資料庫一直都是可用的，一直都是免費的，一直都擁有相同的功能。

讓我們開始吧 Getting Started

想要開始用PostgreSQL嗎？跳到下載頁面並下載你選擇的平台的副本。如果你想要更多細節，文件都已經準備好了而且良好撰寫，或是你可以查看維基上的教程。

[C#]Force DataGridView Save Current Row's Data to Backyard.

Usually we edit the table data with DataGridView.

I do it, too. and I get problem when I try to get the changed DataTable.

It happened if you try to save data just after editing it.


There is the situation:

I have a 3x3 DataTable dt, and I link it to a DataGridView dgv

dgv.DataSource = dt;

then I edited the content with row 3 column 3.
I can move cursor to column 1 or column 2, but before i move cursor to the other row, the changing of row 3 will not be set to DataTable.

It's not good. I want it be saved after click SAVE or Ctrl-S.

So, there is a solution: DataGridView.EndEdit().
but, the row's data didn't change with only DataGridView.EndEdit().
The CurrentRow must lose focus to save the data.
Hmm... Here is a member called CurrentCell
and Someone set CurrentCell to null, then the data is stored.

I tried it, and it works good!
so, if you want to save the current row's data.
run the below 2 statement:

dgv.EndEdit();
dgv.CurrentCell = null;

then the current row's change will be saved in to DataTable, too.

[C#]Get DataTable from Database, Update DataTable to Database

just take a note.

When we want to update database with a list, as you know, there will be 3 kinds of operation: insert(for new rows), update(for changed rows), delete(for deleted rows)

for a single record, it will be easy. But for a list of data, it will be a nightmare.

use the .NET DataAdapter, we can make it easier.
for base, we have sqlConnectString for default connection, and we try to access all columns of demoTable. There is a global DataTable named dt.

to make the example for common case, I will type it for SQL server.

1. to get DataTable from Database

string sql = @"SELECT * FROM demoTable;";
using(var conn = new SQLConnection(sqlConnectString))
{
    conn.Open();
    using(var cmd = new SQLCommand(conn))
    {
        cmd.CommandText = sql;
        var reader = cmd.ExecuteReader();
        dt.Load(reader);
    }
}

2. to update DataTable's data to Database

string sql = @"SELECT * FROM demoTable;";
using(var conn = new SQLConnection(sqlConnectString))
{
    conn.Open();
    var adp = new SQLDataAdapter(sql, conn);
    var cmb = new SQLCommandBuilder(adp);
    adp.Update(dt);
}

to speed up the database access, can add transaction commands. Begin the transaction before update, and commit the transaction after update.

#DELETED in Access

In Access, if you have a record in form/subform is deleted after you read the data into form, the values will be showen "#DELETED".

to fix it, just force the form to requery.

with VBA, it will be like below:
Me.[SomeControlNameIfThereIsSubForm.Form].Requery


アクセス上のことです。
操作している間に、データがフォームに読み込まれた後に、実際のデータが消された場合、フォーム上で、#DELETEDという文字が出てきます。

解決方法も簡単です。フォームを再クエリしたらいいです。荒々しいが仕方ありません。

Six Principles of Database Design 資料庫設計的六個原則

節錄自Oreilly的Access 2010 Missing Manual

1. Choose Good Field Names 為欄位選擇一個好名字
• Keep it short and simple. 越短越簡單越好。但是不要短到像暗號一樣。
• CapitalizeLikeThis. 適當的作出大小寫區分
• Avoid spaces. 避免使用空白
• Be consistent. 保持一貫性
• Don’t repeat the table name. 不要重複表格名
• Don’t use the field name “Name.” 不要把欄位命名作Name
2. Break Down Your Information 切割資訊
3. Include All the Details in One Place 把所有的細節放在一起
4. Avoid Duplicating Information 避免重複資訊
5. Avoid Redundant Information 避免無謂的資訊
6. Include an ID Field 留一個ID欄

Connect to Databases in C#

今回はC#でSQLiteやMySQLに繋ぎ方法を記録する。

一言で言うと、SQL Serverとの繋ぎはほぼ似ている。
ただし使用するメソッド／オブジェクトをSqlXxxからSQLiteXxxあるいはMySqlXxxに変わるだけ。

さて、本題に入る。先ずは基本のSQL serverに行こう。
することは全部同じ。

1. データベースに繋ぎ（連結する）。
2. CarというTableを作成。
3. テーブルCarにデータを入れる。
4. データを出力し、連結をクローズする。

using System;
using System.Data.SqlClient;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "user id=testuser;" +
            "password=testpwd;server=localhost;" +
            "Trusted_Connection=yes;" +
            "database=dbTest; " +
            "connection timeout=30";

        // Connection to Database
        // with the new "using" garbage collection 
        using (SqlConnection con = new SqlConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(@Id, @Name, @Price)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Audi");
                cmd.Parameters.AddWithValue("@Price", 52642);
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@Id", 2);
                cmd.Parameters.AddWithValue("@Name", "Mercedes");
                cmd.Parameters.AddWithValue("@Price", 57127);
                cmd.ExecuteNonQuery();

                // do with Standard SQL command
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (SqlCommand cmd = new SqlCommand(stm,con))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }
    }
}

続き、似ている内容で、SQLiteでしましょう。
注意してほしい点は

• 事前にSystem.Data.SQLiteのDLLファイルをプロジェクトのフォルダ下のExternalsにコピーしておくこと。また、プロジェクト設定でそれに参照するように設定して下さい。
• DLLファイルを持っていない場合、SQLite.NET公式サイトあるいはSQLite公式サイトより探して下さい。無償で手に入れるはず。

using System;
using System.Data.SQLite;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "URI=file:test.db";

        // Connection to Database
        // with the new "using" garbage collection 
        using (SQLiteConnection con = new SQLiteConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(@Id, @Name, @Price)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Audi");
                cmd.Parameters.AddWithValue("@Price", 52642);
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@Id", 2);
                cmd.Parameters.AddWithValue("@Name", "Mercedes");
                cmd.Parameters.AddWithValue("@Price", 57127);
                cmd.ExecuteNonQuery();

                // do with Standard SQL command
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
            {
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }

        Console.WriteLine("Press ENTER to continue...");
        Console.ReadLine();
    }
}

最後、MySQL（今回はローカルを例にする）でしましょう。
SQLiteの時と同じ、System.Data.MySQLのDLLを取得して下さい。SQLiteの時と同じ、MySQLの公式サイトより無償で手に入れるはず。

using System;
using MySql.Data.MySqlClient;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "user id=testuser;" +
            "password=testpwd;server=localhost;" +
            "database=testDB; " +
            "connection timeout=30";

        // Connection to Database
        // with the new "using" garbage collection 
        using (MySqlConnection con = new MySqlConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(1, 'Audi', 52642)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(2, 'Mercedes', 57127)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (MySqlCommand cmd = new MySqlCommand(stm, con))
            {
                using (MySqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }
    }
}