NpgsqlでPostgreSQLに接続する

環境

使用アプリケーション

• Windows XP SP-2
• MDAC 2.8 SP-1
• .NET Framework 1.1 SP-1
• PostgreSQL 8.0.4
• Npgsql 0.7.1
• Visual Studio.NET 2003 （C#）

DB環境

• ローカルPC上のPostgreSQLサーバー

参照設定

テストコード

テストコードのプロジェクト

using System.Data;
using Npgsql;

private void btnTest_Click(object sender, System.EventArgs e) {
//	 txtConnectString.Text = "Server=127.0.0.1;Port=5432;Database=test1;Encoding=UNICODE;User Id=postgres;Password=;";
	string connectString = txtConnectString.Text;		
	txtResult.Clear(); 

	//接続を開く
	NpgsqlConnection conn = new NpgsqlConnection(connectString);
	try {
		conn.Open();
	} catch (Exception ex) {
		txtResult.Text += ex.Message + "\r\n";
		return;
	}
		
	//テストテーブル作成
	NpgsqlCommand command;
	NpgsqlDataAdapter da;
	DataTable dt;
	string sql = "";
	int result = 0;
	sql = "CREATE TABLE t_pgodbcテスト (";
	sql += "id int PRIMARY KEY,";
	sql += "メモ text,";
	sql += "はいいいえ bool NOT NULL DEFAULT false,";
	sql += "日時 timestamp NOT NULL DEFAULT LOCALTIMESTAMP);";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";
	
	//テストデータ作成
	txtResult.Text += "\r\n";
	sql = "INSERT INTO t_pgodbcテスト (id, メモ) VALUES(1, 'あいうえお');";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";
	sql = "INSERT INTO t_pgodbcテスト (id, メモ) VALUES(2, 'かきくけこ');";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";
	//失敗するINSERT文
	try {
		sql = "INSERT INTO t_pgodbcテスト (id, メモ) VALUES(2, 'さしすせそ');";
		txtResult.Text += "(sql) " + sql + "\r\n";
		command = new NpgsqlCommand(sql, conn);
		result = command.ExecuteNonQuery(); 
		txtResult.Text += " result: " + result.ToString()+ "\r\n";
	} catch (Exception ex){
		txtResult.Text += ex.Message + "\r\n"; 
	}
	sql = "INSERT INTO t_pgodbcテスト (id, メモ, はいいいえ) VALUES(3, 'さしすせそ', TRUE);";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";

	//テストデータ読み込みType1
	txtResult.Text += "\r\n";
	txtResult.Text += "=====SELECT=====\r\n";
	sql = "SELECT * FROM t_pgodbcテスト;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\t" + dt.Columns[2].ColumnName + "\t" + dt.Columns[3].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      //Field_Indexで参照
			txtResult.Text += row[0].ToString() + "\t" + row[1].ToString() + "\t" + row[2].ToString() + "\t" + row[3].ToString() + "\r\n";
		}
	}
	
	//テストデータ更新
	txtResult.Text += "\r\n";
	txtResult.Text += "=====UPDATE=====\r\n";
	sql = "UPDATE t_pgodbcテスト SET メモ='かかききくくけこ' WHERE id=2;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";
	//更新確認
	sql = "SELECT count(id) AS 件数 FROM t_pgodbcテスト WHERE メモ='かかききくくけこ';";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn); 
	object result_o = command.ExecuteScalar(); 
	if (result_o != null) {
		txtResult.Text += "  更新確認件数 " + result_o.ToString() + "\r\n"; 
	}
	//テストデータ読み込みType2
	sql = "SELECT * FROM t_pgodbcテスト ORDER BY id;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\t" + dt.Columns[2].ColumnName + "\t" + dt.Columns[3].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      //Field_Nameで参照
			txtResult.Text += row["id"].ToString() + "\t" + row["メモ"].ToString() + "\t" + row["はいいいえ"].ToString() + "\t" + row["日時"].ToString() + "\r\n";
		}
	}
		
	//テストデータ削除
	txtResult.Text += "\r\n";
	txtResult.Text += "=====DELETE=====\r\n";
	sql = "DELETE FROM t_pgodbcテスト WHERE id=2;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";
	//テストデータ読み込みType2
	sql = "SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      
			txtResult.Text += row["id"].ToString() + "\t" + row["メモ"].ToString() + "\r\n";
		}
	}
	
	//DataAdapterで挿入・更新・削除
	//DataAdapter準備
	txtResult.Text += "\r\n";
	txtResult.Text += "=====DataAdapter=====\r\n";
	sql = "SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;";
	da = new NpgsqlDataAdapter(sql, conn); 
	sql = "INSERT INTO t_pgodbcテスト (id, メモ) VALUES(:id, :memo);";
	NpgsqlCommand insertCommand = new NpgsqlCommand(sql, conn); 
	insertCommand.Parameters.Add(new NpgsqlParameter("id", NpgsqlDbType.Integer , 0, "id"));
	insertCommand.Parameters.Add(new NpgsqlParameter("memo", NpgsqlDbType.Text , 0, "メモ"));
	da.InsertCommand = insertCommand;
	sql = "UPDATE t_pgodbcテスト SET メモ=:memo WHERE id=:id;";
	NpgsqlCommand updateCommand = new NpgsqlCommand(sql, conn); 
	updateCommand.Parameters.Add(new NpgsqlParameter("id", NpgsqlDbType.Integer , 0, "id"));
	updateCommand.Parameters.Add(new NpgsqlParameter("memo", NpgsqlDbType.Text , 0, "メモ"));
	da.UpdateCommand = updateCommand;
	sql = "DELETE FROM t_pgodbcテスト WHERE id=:id;";
	NpgsqlCommand deleteCommand = new NpgsqlCommand(sql, conn); 
	deleteCommand.Parameters.Add(new NpgsqlParameter("id", NpgsqlDbType.Integer , 0, "id"));
	da.DeleteCommand = deleteCommand;
	//DataAdapterで更新
	dt = new DataTable(); 
	da.Fill(dt); 
	//挿入
	DataRow tempRow = dt.NewRow();
	tempRow.BeginEdit();
	tempRow["id"] = 18;
	tempRow["メモ"] = "DataAdapter Insert";
	tempRow.EndEdit();
	dt.Rows.Add(tempRow); 
	tempRow = dt.NewRow();
	tempRow.BeginEdit();
	tempRow["id"] = 19;
	tempRow["メモ"] = "DataAdapter Insert 2";
	tempRow.EndEdit();
	dt.Rows.Add(tempRow); 
	da.Update(dt); 
	//更新
	DataRow[] tempRows = dt.Select("id=18"); 
	tempRows[0].BeginEdit();
	tempRows[0]["メモ"] = "DataAdapter Insert 1";
	tempRows[0].EndEdit();
	da.Update(tempRows); 
	//削除
	tempRows = dt.Select("id=19");
	tempRows[0].Delete(); 
	da.Update(dt);		
	//結果表示
	dt = new DataTable(); 
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      
			txtResult.Text += row[0].ToString() + "\t" + row[1].ToString() + "\r\n";
		}
	}			


	//TRANSACTIONテスト
	txtResult.Text += "\r\n";
	txtResult.Text += "=====TRANSACTION=====\r\n";
	NpgsqlTransaction trn = conn.BeginTransaction(); 
	txtResult.Text += "BEGIN\r\n";
	sql = "INSERT INTO t_pgodbcテスト (id, メモ) VALUES(10, 'トランザクション内挿入');";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn, trn);
	result = command.ExecuteNonQuery(); 
	sql = "UPDATE t_pgodbcテスト SET メモ='トランザクション内更新' WHERE id=1;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn, trn);
	result = command.ExecuteNonQuery(); 
	sql = "DELETE FROM t_pgodbcテスト WHERE id=3;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn, trn);
	result = command.ExecuteNonQuery(); 
	//テストデータ読み込みType2
	sql = "SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      
			txtResult.Text += row["id"].ToString() + "\t" + row["メモ"].ToString() + "\r\n";
		}
	}
	//ROLLBACK
	trn.Rollback(); 
	txtResult.Text += "ROLLBACK\r\n";
	//テストデータ読み込みType2
	sql = "SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      
			txtResult.Text += row["id"].ToString() + "\t" + row["メモ"].ToString() + "\r\n";
		}
	}
	
		
	//nextval()テスト
	txtResult.Text += "\r\n";
	txtResult.Text += "=====SERIAL=====\r\n";
	//SERIALテストテーブル作成
	sql = "CREATE TABLE t_pgodbcテスト2 (";
	sql += "id serial PRIMARY KEY,";
	sql += "メモ text)";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	txtResult.Text += " result: " + result.ToString()+ "\r\n";

	//既定値で挿入
	sql = "INSERT INTO t_pgodbcテスト2 (メモ) VALUES('既定値で挿入');";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	//nextVal取得
	int nextVal =0;
	sql = "SELECT nextval('t_pgodbcテスト2_id_seq');";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		nextVal = Convert.ToInt32(dt.Rows[0][0]); 
		txtResult.Text += "  nextVal: " + nextVal.ToString() + "\r\n";
	}
	//nextValで挿入
	if (nextVal > 0) {
		sql = "INSERT INTO t_pgodbcテスト2 VALUES(" + nextVal.ToString() + ", 'nextValで挿入');";
		txtResult.Text += "(sql) " + sql + "\r\n";
		command = new NpgsqlCommand(sql, conn);
		result = command.ExecuteNonQuery(); 
	}
	//確認
	sql = "SELECT id, メモ FROM t_pgodbcテスト2;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\t" + dt.Columns[1].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      
			txtResult.Text += row["id"].ToString() + "\t" + row["メモ"].ToString() + "\r\n";
		}
	}
		
		
	//PLpgSql FUNCTION
	txtResult.Text += "\r\n";
	txtResult.Text += "=====PLpgSql=====\r\n";
	sql = "CREATE OR REPLACE FUNCTION getmemo(key integer) RETURNS text AS $$ ";
	sql += "DECLARE memo text;";
	sql += "BEGIN";
	sql += " SELECT INTO memo メモ FROM t_pgodbcテスト WHERE id=key;";
	sql += " RETURN memo;";
	sql += "END; $$ LANGUAGE plpgsql;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	//FUNCTIOIN 実行
	sql = "SELECT getmemo(3);";
	txtResult.Text += "(sql) " + sql + "\r\n";
	da = new NpgsqlDataAdapter(sql, conn);
	dt = new DataTable();
	da.Fill(dt);
	if (dt.Rows.Count >0) { 
		txtResult.Text += dt.Columns[0].ColumnName + "\r\n";
		txtResult.Text += "------------------\r\n";
		foreach (DataRow row in dt.Rows) {      
			txtResult.Text += row[0].ToString() + "\r\n";
		}
	}
		
		
	//-----後始末
	txtResult.Text += "\r\n";
	txtResult.Text += "=====後始末=====\r\n";
	//FUNCTION削除
	sql = "DROP FUNCTION getMemo(integer);";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	
	//テストテーブル削除
	sql = "DROP TABLE t_pgodbcテスト;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	
	sql = "DROP TABLE t_pgodbcテスト2;";
	txtResult.Text += "(sql) " + sql + "\r\n";
	command = new NpgsqlCommand(sql, conn);
	result = command.ExecuteNonQuery(); 
	
	conn.Close(); 
}

テスト結果

(sql) CREATE TABLE t_pgodbcテスト (id int PRIMARY KEY,メモ text,はいいいえ bool NOT NULL DEFAULT false,日時 timestamp NOT NULL DEFAULT LOCALTIMESTAMP);
 result: -1

(sql) INSERT INTO t_pgodbcテスト (id, メモ) VALUES(1, 'あいうえお');
 result: 1
(sql) INSERT INTO t_pgodbcテスト (id, メモ) VALUES(2, 'かきくけこ');
 result: 1
(sql) INSERT INTO t_pgodbcテスト (id, メモ) VALUES(2, 'さしすせそ');
ERROR: 23505: duplicate key violates unique constraint "t_pgodbcテスト_pkey"
(sql) INSERT INTO t_pgodbcテスト (id, メモ, はいいいえ) VALUES(3, 'さしすせそ', TRUE);
 result: 1

=====SELECT=====
(sql) SELECT * FROM t_pgodbcテスト;
id	メモ	はいいいえ	日時
------------------
1	あいうえお	False	2005/12/28 10:19:20
2	かきくけこ	False	2005/12/28 10:19:20
3	さしすせそ	True	2005/12/28 10:19:21

=====UPDATE=====
(sql) UPDATE t_pgodbcテスト SET メモ='かかききくくけこ' WHERE id=2;
 result: 1
(sql) SELECT count(id) AS 件数 FROM t_pgodbcテスト WHERE メモ='かかききくくけこ';
  更新確認件数 1
(sql) SELECT * FROM t_pgodbcテスト ORDER BY id;
id	メモ	はいいいえ	日時
------------------
1	あいうえお	False	2005/12/28 10:19:20
2	かかききくくけこ	False	2005/12/28 10:19:20
3	さしすせそ	True	2005/12/28 10:19:21

=====DELETE=====
(sql) DELETE FROM t_pgodbcテスト WHERE id=2;
 result: 1
(sql) SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;
id	メモ
------------------
1	あいうえお
3	さしすせそ

=====DataAdapter=====
id	メモ
------------------
1	あいうえお
3	さしすせそ
18	DataAdapter Insert 1

=====TRANSACTION=====
BEGIN
(sql) INSERT INTO t_pgodbcテスト (id, メモ) VALUES(10, 'トランザクション内挿入');
(sql) UPDATE t_pgodbcテスト SET メモ='トランザクション内更新' WHERE id=1;
(sql) DELETE FROM t_pgodbcテスト WHERE id=3;
(sql) SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;
id	メモ
------------------
1	トランザクション内更新
10	トランザクション内挿入
18	DataAdapter Insert 1
ROLLBACK
(sql) SELECT id, メモ FROM t_pgodbcテスト ORDER BY id;
id	メモ
------------------
1	あいうえお
3	さしすせそ
18	DataAdapter Insert 1

=====SERIAL=====
(sql) CREATE TABLE t_pgodbcテスト2 (id serial PRIMARY KEY,メモ text)
 result: -1
(sql) INSERT INTO t_pgodbcテスト2 (メモ) VALUES('既定値で挿入');
(sql) SELECT nextval('t_pgodbcテスト2_id_seq');
  nextVal: 2
(sql) INSERT INTO t_pgodbcテスト2 VALUES(2, 'nextValで挿入');
(sql) SELECT id, メモ FROM t_pgodbcテスト2;
id	メモ
------------------
1	既定値で挿入
2	nextValで挿入

=====PLpgSql=====
(sql) CREATE OR REPLACE FUNCTION getmemo(key integer) RETURNS text AS $$ DECLARE memo text;BEGIN SELECT INTO memo メモ FROM t_pgodbcテスト WHERE id=key; RETURN memo;END; $$ LANGUAGE plpgsql;
(sql) SELECT getmemo(3);
getmemo
------------------
さしすせそ

=====後始末=====
(sql) DROP FUNCTION getMemo(integer);
(sql) DROP TABLE t_pgodbcテスト;
(sql) DROP TABLE t_pgodbcテスト2;

ミドルウエア一覧へ戻る