【DB】Npgsqlを利用してPostgreSQLを操作する方法（検索、削除、更新）

【DB】Npgsqlを利用してPostgreSQLを操作する方法（検索、削除、更新）

---

VB.NET

---

Imports Npgsql
Imports System.Data

Dim connString = "Server=localhost;Port=5432;User Id=postgres;Password=password#;Database=postgres"

' DataReaderを利用したSELECT

Using conn = New NpgsqlConnection(connString)

conn.Open()

Dim command = New NpgsqlCommand("select * from table_name", conn)

Dim dataReader = command.ExecuteReader()

While dataReader.Read()

Console.WriteLine("value : {0},", dataReader("column_name"))

End While

End Using

' DataAdapterを利用したSELECT

Using conn = New NpgsqlConnection(connString)

conn.Open()

Dim dataAdapter = New NpgsqlDataAdapter("select * from table_name", conn)

Dim dataSet = New DataSet()

dataAdapter.Fill(dataSet)

Console.WriteLine(dataSet.Tables(0).Rows(0)("column_name"))

End Using

Learning Visual Basic .NET【電子書籍】[ Jesse Liberty…

---

C#

---

using Npgsql;
using System.Data;

var connString = @"Server=localhost;Port=5432;User Id=postgres;Password=password#;Database=postgres";

// DataReaderを利用したSELECT
using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();

  var command = new NpgsqlCommand(@"select * from table_name", conn);

  var dataReader = command.ExecuteReader();
  while (dataReader.Read())
  {
    Console.WriteLine("value : {0},", dataReader["column_name"]);
  }
}

// DataAdapterを利用したSELECT
using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();

  var dataAdapter = new NpgsqlDataAdapter(@"select * from table_name", conn);

  var dataSet = new DataSet();
  dataAdapter.Fill(dataSet);

  Console.WriteLine(dataSet.Tables[0].Rows[0]["column_name"]);
}


■トランザクションを活用する方法
var connString = @"Server=localhost;Port=5432;UserId=postgres;Password=password;Database=postgres";

// Commandを利用したUPDATE
using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();
  using (var transaction = conn.BeginTransaction())
  {
    var command = new NpgsqlCommand(@"update table set column = 'new value' where id = :p", conn);
    command.Parameters.Add(new NpgsqlParameter("p", DbType.Int32) { Value = 123 });

    try
    {
      command.ExecuteNonQuery();
      transaction.Commit();
    }
    catch (NpgsqlException)
    {
      transaction.Rollback();
      throw;
    }
  }
}

// DataAdapterを利用したUPDATE
using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();
  using (var transaction = conn.BeginTransaction())
  {
    var dataAdapter = new NpgsqlDataAdapter(@"select * from table", conn);
    dataAdapter.UpdateCommand = new NpgsqlCommand(@"update table set column = :p_value where id = :p", conn, transaction);
    dataAdapter.UpdateCommand.Parameters.Add(new NpgsqlParameter("p", DbType.Int32) { Value = 123 });
    dataAdapter.UpdateCommand.Parameters.Add(new NpgsqlParameter("p_value", DbType.String, 10, "column"));   ……1

    var dataSet = new DataSet();
    dataAdapter.Fill(dataSet);

    var rows = dataSet.Tables[0].Rows;
    rows[0].BeginEdit();
    rows[0]["column"] = "new value";
    rows[0].EndEdit();

    try
    {
      dataAdapter.Update(dataSet);
      transaction.Commit();
    }
    catch (NpgsqlException)
    {
      transaction.Rollback();
      throw;
    }
  }
}