へっぽこ実験ブログ

C#とPowerShellで色々なDBを操作してみる

投稿日: 投稿者: mima3
Table of Content

前書き

C#とPowerShellで色々なDBを操作してみます。
環境は以下の通りです。

クライアントの環境

  • PowerShell 5.1(32bit)
  • VisualStudio 2019 .NET Framework 4.7.2(32bit)

操作対象のDatabase

  • SQLite3
  • MDB
  • Ver 15.1 Distrib 5.5.60-MariaDB
  • PostgreSQL 9.2.24
  • Oracle12
  • SQLServer2017

操作対象のテーブル

CREATE TABLE test_tbl
(
  user_name varchar(50),
  age integer
)

PowerShellのusingについて

PowerShellでのusingを用いたリソースの解放処理が標準で存在していなかったので下記を利用する。

Dave Wyatt's Blog　Using-Object: PowerShell version of C#’s “using” statement.
https://davewyatt.wordpress.com/2014/04/11/using-object-powershell-version-of-cs-using-statement/

function Using-Object
{
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [AllowEmptyString()]
        [AllowEmptyCollection()]
        [AllowNull()]
        [Object]
        $InputObject,

        [Parameter(Mandatory = $true)]
        [scriptblock]
        $ScriptBlock
    )

    try
    {
        . $ScriptBlock
    }
    finally
    {
        if ($null -ne $InputObject -and $InputObject -is [System.IDisposable])
        {
            $InputObject.Dispose()
        }
    }
}

SQLite3

外部ライブラリのSystem.Data.SQLiteを利用してデータベースの操作を行います。

事前準備

NuGetで下記をインストールする

  • System.Data.SQLite.Core

C＃

using System;
using System.Data.SQLite;

namespace sqliteSample
{
    class Program
    {
        // https://www.ivankristianto.com/howto-make-user-defined-function-in-sqlite-ado-net-with-csharp/
        [SQLiteFunction(Name = "ToUpper", Arguments = 1, FuncType = FunctionType.Scalar)]
        public class ToUpper : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                return args[0].ToString().ToUpper();
            }
        }

        static void Main(string[] args)
        {
            if (System.IO.File.Exists("database.db"))
            {
                System.IO.File.Delete("database.db");
            }
            using (var conn = new SQLiteConnection("Data Source=database.db; Version = 3; New = True; Compress = True; "))
            {
                conn.Open();

                using (var cmd = new SQLiteCommand("CREATE TABLE test_tbl(user_name varchar(50), age integer)", conn))
                {
                    cmd.ExecuteNonQuery();
                }

                using (var cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)";
                    cmd.Parameters.Add(new SQLiteParameter("@user", "aa明日のジョー"));
                    cmd.Parameters.Add(new SQLiteParameter("@age", 17));
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = new SQLiteCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                // トランザクション
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（ロールバック）");
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = new SQLiteCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)";
                        cmd.Parameters.Add(new SQLiteParameter("@user", "bb丹下さくら"));
                        cmd.Parameters.Add(new SQLiteParameter("@age", 43));
                        cmd.ExecuteNonQuery();
                    }
                    tran.Rollback();

                }
                using (var cmd = new SQLiteCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（コミット）");
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = new SQLiteCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)";
                        cmd.Parameters.Add(new SQLiteParameter("@user", "bb丹下さくら"));
                        cmd.Parameters.Add(new SQLiteParameter("@age", 43));
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();

                }
                using (var cmd = new SQLiteCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }

                Console.WriteLine("=================================================");
                Console.WriteLine("ユーザ定義関数");
                using (var cmd = new SQLiteCommand("SELECT ToUpper(user_name) FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}", reader.GetString(0));
                    }
                }
                conn.Close();
            }
            Console.ReadLine();
        }
    }
}

PowerShell

C#と異なりユーザ定義関数の実行がうまく行きません。（コメント欄参考に実現しました）
また、今回は調査外としましたがPSSQLiteというライブラリがあります。
System.Data.SQLite.dllをラップして使い易くしているようです。

using namespace System.Data.SQLite
Add-Type -Path 'System.Data.SQLite.dll'

$source = @"
using System.Data.SQLite;
[SQLiteFunction(Name = "ToUpper", Arguments = 1, FuncType = FunctionType.Scalar)]
public class ToUpper : SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        return args[0].ToString().ToUpper();
    }
}

"@

Add-Type -TypeDefinition $source -ReferencedAssemblies ("C:\dev\ps\database\System.Data.SQLite.dll")

if (Test-Path C:\dev\ps\database\database.db) {
    Remove-Item C:\dev\ps\database\database.db
}
Using-Object ($conn = New-Object SQLiteConnection('Data Source=C:\dev\ps\database\database.db; Version = 3; New = True; Compress = True; ')) {
    $conn.Open()
    Using-Object ($cmd = New-Object SQLiteCommand('CREATE TABLE test_tbl(user_name varchar(50), age integer)', $conn)) {
        $cmd.ExecuteNonQuery() | Out-Null
    }
    Using-Object ($cmd = New-Object SQLiteCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
        $param = New-Object SQLiteParameter("@user", "aa明日のジョー")
        $cmd.Parameters.Add( $param ) | Out-Null
        $param = New-Object SQLiteParameter("@age", 17)
        $cmd.Parameters.Add( $param ) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }
    Using-Object ($cmd = New-Object SQLiteCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（ロールバック）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object SQLiteCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
            $param = New-Object SQLiteParameter("@user", "bb丹下さくら")
            $cmd.Parameters.Add( $param ) | Out-Null
            $param = New-Object SQLiteParameter("@age", 43)
            $cmd.Parameters.Add( $param ) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Rollback()
    }
    Using-Object ($cmd = New-Object SQLiteCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（コミット）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object SQLiteCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
            $param = New-Object SQLiteParameter("@user", "bb丹下さくら")
            $cmd.Parameters.Add( $param ) | Out-Null
            $param = New-Object SQLiteParameter("@age", 43)
            $cmd.Parameters.Add( $param ) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Commit()
    }
    Using-Object ($cmd = New-Object SQLiteCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    [SQLiteFunction]::RegisterFunction([ToUpper]) 
    Write-Host "================================================="
    Write-Host "ユーザ定義"
    Using-Object ($cmd = New-Object SQLiteCommand("SELECT ToUpper(user_name) FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0) 
            }
        }
    }

    $conn.Close()
}

MDB

古いアクセスの拡張子であるMDBは実は32bitに限り、標準で操作可能です。
COMオブジェクトのMicrosoft ADO Ext(ADOX)を利用してデータベースの作成とテーブルの作成を行います。
.NETのSystem.Data.OleDbを利用してテーブルの操作を行います。

事前準備

C#の場合は、COMとして「Microsoft ADO Ext」を参照します。

C＃

System.Data.OleDbはバインド変数に名前を付けることができないようです。

What's wrong with these parameters?
https://stackoverflow.com/questions/1216271/whats-wrong-with-these-parameters

using System;
using System.Data.OleDb;
using System.Runtime.InteropServices;

namespace mdbSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = @"C:\dev\ps\database\test.mdb";
            string cnnStr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path;
            if (System.IO.File.Exists(path))
            {
                System.IO.File.Delete(path);
            }
            // 32bitで動かす必要がある
            // https://www.c-sharpcorner.com/uploadfile/mahesh/using-adox-with-ado-net/
            // Microsoft ADO Ext 6.0
            var ct = new ADOX.Catalog();
            var createdObj = ct.Create(cnnStr);
            ADOX.Table tbl = new ADOX.Table();
            tbl.Name = "test_tbl";
            tbl.Columns.Append("user_name", ADOX.DataTypeEnum.adVarWChar, 50);
            tbl.Columns.Append("age", ADOX.DataTypeEnum.adInteger);
            ct.Tables.Append(tbl);
            createdObj.Close();
            Marshal.ReleaseComObject(createdObj);
            Marshal.ReleaseComObject(tbl);
            Marshal.ReleaseComObject(ct);

            using (var conn = new OleDbConnection(cnnStr))
            {
                conn.Open();
                using (var cmd = new OleDbCommand("INSERT INTO test_tbl (user_name, age) VALUES (?, ?)", conn))
                {
                    cmd.Parameters.AddWithValue("@user", "明日のジョー");
                    cmd.Parameters.AddWithValue("@age", 17);
                    cmd.ExecuteNonQuery();

                }
                using (var cmd = new OleDbCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }

                // トランザクション
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（ロールバック）");
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = new OleDbCommand("INSERT INTO test_tbl (user_name, age) VALUES (?, ?)", conn, tran))
                    {
                        cmd.Parameters.AddWithValue("@user", "丹下さくら");
                        cmd.Parameters.AddWithValue("@age", 43);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Rollback();
                }
                using (var cmd = new OleDbCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（コミット）");
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = new OleDbCommand("INSERT INTO test_tbl (user_name, age) VALUES (?, ?)", conn, tran))
                    {
                        cmd.Parameters.AddWithValue("@user", "丹下さくら");
                        cmd.Parameters.AddWithValue("@age", 43);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                using (var cmd = new OleDbCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

PowerShell

    using namespace System.Data.OleDb
    using namespace System.Runtime.InteropServices

    $path = "C:\dev\ps\database\test.mdb"
    if (Test-Path $path) {
        Remove-Item $path
    }
    $cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$path"
    $ct = New-Object -ComObject "ADOX.Catalog"
    $createdObj = $ct.Create($cnnStr)
    $tbl = New-Object -ComObject "ADOX.Table"
    $tbl.Name = "test_tbl"
    $tbl.Columns.Append("user_name", 202, 50)
    $tbl.Columns.Append("age", 3)
    $ct.Tables.Append($tbl)
    $createdObj.Close()
    [Marshal]::ReleaseComObject($createdObj) | Out-Null
    [Marshal]::ReleaseComObject($tbl) | Out-Null
    [Marshal]::ReleaseComObject($ct) | Out-Null

    Using-Object ($conn = New-Object OleDbConnection($cnnStr)) {
        $conn.Open()

        Using-Object ($cmd = New-Object OleDbCommand('INSERT INTO test_tbl (user_name, age) VALUES (?, ?)', $conn)) {
            $cmd.Parameters.AddWithValue("@user", "明日のジョー") | Out-Null
            $cmd.Parameters.AddWithValue("@age", 17) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }

        Using-Object ($cmd = New-Object OleDbCommand("SELECT user_name, age FROM test_tbl", $conn)) {
            Using-Object ($reader = $cmd.ExecuteReader()){
                while ($reader.Read())
                {
                    Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
                }
            }
        }
        Write-Host "================================================="
        Write-Host "トランザクション（ロールバック）"
        Using-Object ($tran = $conn.BeginTransaction()) {
            Using-Object ($cmd = New-Object OleDbCommand("INSERT INTO test_tbl (user_name, age) VALUES (?, ?)", $conn, $tran)) {
                $cmd.Parameters.AddWithValue("@user", "丹下さくら") | Out-Null
                $cmd.Parameters.AddWithValue("@age", 43) | Out-Null
                $cmd.ExecuteNonQuery() | Out-Null
            }
            $tran.Rollback()
        }
        Using-Object ($cmd = New-Object OleDbCommand("SELECT user_name, age FROM test_tbl", $conn)) {
            Using-Object ($reader = $cmd.ExecuteReader()){
                while ($reader.Read())
                {
                    Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
                }
            }
        }

        Write-Host "================================================="
        Write-Host "トランザクション（コミット）"
        Using-Object ($tran = $conn.BeginTransaction()) {
            Using-Object ($cmd = New-Object OleDbCommand("INSERT INTO test_tbl (user_name, age) VALUES (?, ?)", $conn, $tran)) {
                $cmd.Parameters.AddWithValue("@user", "丹下さくら") | Out-Null
                $cmd.Parameters.AddWithValue("@age", 43) | Out-Null
                $cmd.ExecuteNonQuery() | Out-Null
            }
            $tran.Commit()
        }
        Using-Object ($cmd = New-Object OleDbCommand("SELECT user_name, age FROM test_tbl", $conn)) {
            Using-Object ($reader = $cmd.ExecuteReader()){
                while ($reader.Read())
                {
                    Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
                }
            }
        }
    }

MariaDB/MySQL

MySQLのインストーラについてくるMySql.Data.dllを利用して操作を行います。
今回はMariaDBを対象としましたがMySQLも同様に動作すると思います。

事前準備

下記からMySQLのインストーラを手にいれてMySql.Data.dllを手に入れます。
https://dev.mysql.com/doc/connector-net/en/connector-net-installation-binary-mysql-installer.html

既定では以下にインストールされるので、参照してください。
C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll

また下記のストアドプロシージャを作成しておきます。

CREATE PROCEDURE test_sp(IN from_age integer, IN to_age integer)
BEGIN
  SELECT test_tbl.user_name,test_tbl.age FROM test_tbl
                            WHERE test_tbl.age BETWEEN from_age AND to_age;

END

C＃

// 以下を参照
//C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll
using MySql.Data.MySqlClient;
using System;

namespace mysqlSample
{
    class Program
    {
        static void Main(string[] args)
        {
            // https://dev.mysql.com/doc/connector-net/en/connector-net-installation-binary-mysql-installer.html
            // https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-connection.html
            string connStr = "server=192.168.80.131;user=root;database=test;port=3306;password=root";
            using (var conn = new MySqlConnection(connStr))
            {
                try
                {
                    Console.WriteLine("Connecting to MySQL...");
                    conn.Open();

                    using (var cmd = new MySqlCommand("truncate table test_tbl", conn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    using (var cmd = new MySqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", conn))
                    {
                        cmd.Parameters.AddWithValue("@user", "明日のジョー");
                        cmd.Parameters.AddWithValue("@age", 17);
                        cmd.ExecuteNonQuery();

                    }
                    using (var cmd = new MySqlCommand("SELECT user_name, age FROM test_tbl", conn))
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                        }
                    }

                    // トランザクション
                    Console.WriteLine("=================================================");
                    Console.WriteLine("トランザクション（ロールバック）");
                    using (var tran = conn.BeginTransaction())
                    {
                        // Perform database operations
                        using (var cmd = new MySqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", conn))
                        {
                            cmd.Parameters.AddWithValue("@user", "丹下さくら");
                            cmd.Parameters.AddWithValue("@age", 43);
                            cmd.ExecuteNonQuery();
                        }
                        tran.Rollback();
                    }
                    using (var cmd = new MySqlCommand("SELECT user_name, age FROM test_tbl", conn))
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                        }
                    }
                    Console.WriteLine("=================================================");
                    Console.WriteLine("トランザクション（コミット）");
                    using (var tran = conn.BeginTransaction())
                    {
                        // Perform database operations
                        using (var cmd = new MySqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", conn))
                        {
                            cmd.Parameters.AddWithValue("@user", "丹下さくら");
                            cmd.Parameters.AddWithValue("@age", 43);
                            cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                    }
                    using (var cmd = new MySqlCommand("SELECT user_name, age FROM test_tbl", conn))
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                        }
                    }

                    Console.WriteLine("=================================================");
                    Console.WriteLine("ストアド");
                    using (var cmd = new MySqlCommand("call test_sp(@from, @to)", conn))
                    {
                        cmd.Parameters.AddWithValue("@from", 10);
                        cmd.Parameters.AddWithValue("@to", 19);
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                            }
                        }
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
            Console.WriteLine("Done.");
            Console.ReadLine();
        }
    }
}

PowerShell

using namespace MySql.Data.MySqlClient
Add-Type -Path 'MySql.Data.dll'

Using-Object ($conn = New-Object MySqlConnection('server=192.168.80.131;user=root;database=test;port=3306;password=root')) {
    $conn.Open()
    Using-Object ($cmd = New-Object MySqlCommand('truncate table test_tbl', $conn)) {
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object MySqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
        $cmd.Parameters.AddWithValue("@user", "明日のジョー") | Out-Null
        $cmd.Parameters.AddWithValue("@age", 17) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object MySqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（ロールバック）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object MySqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
            $cmd.Parameters.AddWithValue("@user", "丹下さくら") | Out-Null
            $cmd.Parameters.AddWithValue("@age", 43) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Rollback()
    }
    Using-Object ($cmd = New-Object MySqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }
    Write-Host "================================================="
    Write-Host "トランザクション（コミット）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object MySqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
            $cmd.Parameters.AddWithValue("@user", "丹下さくら") | Out-Null
            $cmd.Parameters.AddWithValue("@age", 43) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Commit()
    }
    Using-Object ($cmd = New-Object MySqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "ストアド"
    Using-Object ($cmd = New-Object MySqlCommand('call test_sp(@from, @to)', $conn)) {
        $cmd.Parameters.AddWithValue("@from", 10) | Out-Null
        $cmd.Parameters.AddWithValue("@to", 19) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }
    Using-Object ($cmd = New-Object MySqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }
    $conn.Close()
}

PostgreSQL

外部ライブラリのNpgsqlを利用して操作を行います。

事前準備

NugetでNpgsqlをインストールします。
下記のストアドプロシージャを作成します。

CREATE OR REPLACE FUNCTION test_sp(IN from_age integer, IN to_age integer)
  RETURNS TABLE(user_name varchar(50), age integer) AS
$$
DECLARE
BEGIN
    RETURN QUERY SELECT test_tbl.user_name,test_tbl.age FROM test_tbl
            WHERE test_tbl.age BETWEEN from_age AND to_age;
END;
$$ LANGUAGE plpgsql;

C＃


using Npgsql;
using System;

namespace DbSample
{
    class Program
    {
        static void Main(string[] args)
        {
            var connString = "Host=192.168.80.131;Username=postgres;Password=postgres;Database=test";

            using (var conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                //
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "truncate table test_tbl";
                    cmd.ExecuteNonQuery();
                }

                // Insert some data
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)";
                    cmd.Parameters.AddWithValue("user", "明日のジョー");
                    cmd.Parameters.AddWithValue("age", 17);
                    cmd.ExecuteNonQuery();
                }

                // Retrieve all rows
                using (var cmd = new NpgsqlCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                // トランザクション
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（ロールバック）");
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)";
                        cmd.Parameters.AddWithValue("user", "丹下サクラ");
                        cmd.Parameters.AddWithValue("age", 43);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Rollback();
                }
                using (var cmd = new NpgsqlCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                // 
                // トランザクション
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（コミット）");
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)";
                        cmd.Parameters.AddWithValue("user", "丹下サクラ");
                        cmd.Parameters.AddWithValue("age", 43);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                using (var cmd = new NpgsqlCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                // ストアドの試験
                Console.WriteLine("=================================================");
                Console.WriteLine("ストアドファンクション");
                using (var cmd = new NpgsqlCommand("SELECT user_name, age FROM test_sp(@fromage,@toage)", conn))
                {
                    cmd.Parameters.AddWithValue("fromage", 10);
                    cmd.Parameters.AddWithValue("toage", 19);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                        }
                    }
                }
            }
        }
    }
}

PowerShell

using namespace Npgsql
try {
    Add-Type -Path 'System.Runtime.CompilerServices.Unsafe.dll'
    Add-Type -Path 'System.Threading.Tasks.Extensions.dll'
    Add-Type -Path 'System.Memory.dll'
    Add-Type -Path 'Npgsql.dll'
} catch [System.Reflection.ReflectionTypeLoadException] {
    $_.Exception.LoaderExceptions
}

Using-Object ($conn = New-Object NpgsqlConnection('Host=192.168.80.131;Username=postgres;Password=postgres;Database=test')) {
    $conn.Open()
    Using-Object ($cmd = New-Object NpgsqlCommand('truncate table test_tbl', $conn)) {
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object NpgsqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
        $cmd.Parameters.AddWithValue("user", "明日のジョー") | Out-Null
        $cmd.Parameters.AddWithValue("age", 17) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object NpgsqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（ロールバック）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object NpgsqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
            $cmd.Parameters.AddWithValue("user", "丹下さくら") | Out-Null
            $cmd.Parameters.AddWithValue("age", 43) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Rollback()
    }
    Using-Object ($cmd = New-Object NpgsqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }
    Write-Host "================================================="
    Write-Host "トランザクション（コミット）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object NpgsqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
            $cmd.Parameters.AddWithValue("user", "丹下さくら") | Out-Null
            $cmd.Parameters.AddWithValue("age", 43) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Commit()
    }
    Using-Object ($cmd = New-Object NpgsqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "ストアド"
    Using-Object ($cmd = New-Object NpgsqlCommand('SELECT user_name, age FROM test_sp(@fromage,@toage)', $conn)) {
        $cmd.Parameters.AddWithValue("@fromage", 10) | Out-Null
        $cmd.Parameters.AddWithValue("@toage", 19) | Out-Null
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)
            }
        }
    }
    $conn.Close()
}

Oracle12

外部ライブラリのOracle.ManagedDataAccessを利用して操作をします。

事前準備

NugetでOracle.ManagedDataAccessをインストールします。

C＃

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;

namespace OracleSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string cnnStr = "user id=system;password=oracle;data source=" +
                             "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
                             "(HOST=192.168.99.102)(PORT=1521))(CONNECT_DATA=" +
                             "(SERVICE_NAME=orcl)))";
            using (var conn = new OracleConnection(cnnStr))
            {
                conn.Open();
                using (var cmd = new OracleCommand("truncate table test_tbl", conn))
                {
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = new OracleCommand("INSERT INTO test_tbl (user_name, age) VALUES (:userName, :age)", conn))
                {
                    cmd.Parameters.Add( new OracleParameter("userName", "明日のジョー"));
                    cmd.Parameters.Add( new OracleParameter("age", 17));
                    cmd.ExecuteNonQuery();

                }
                using (var cmd = new OracleCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }

                // トランザクション
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（ロールバック）");
                using (var tran = conn.BeginTransaction())
                {
                    // Perform database operations
                    using (var cmd = new OracleCommand("INSERT INTO test_tbl (user_name, age) VALUES (:userName, :age)", conn))
                    {
                        cmd.Parameters.Add(new OracleParameter("userName", "丹下さくら"));
                        cmd.Parameters.Add(new OracleParameter("age", 43));
                        cmd.ExecuteNonQuery();
                    }
                    tran.Rollback();
                }
                using (var cmd = new OracleCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }

                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（コミット）");
                using (var tran = conn.BeginTransaction())
                {
                    // Perform database operations
                    using (var cmd = new OracleCommand("INSERT INTO test_tbl (user_name, age) VALUES (:userName, :age)", conn))
                    {
                        cmd.Parameters.Add(new OracleParameter("userName", "丹下さくら"));
                        cmd.Parameters.Add(new OracleParameter("age", 43));
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                using (var cmd = new OracleCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                conn.Close();
            }
            Console.ReadLine();
        }
    }
}

PowerShell

using namespace Oracle.ManagedDataAccess.Client
Add-Type -Path 'Oracle.ManagedDataAccess.dll'

Using-Object ($conn = New-Object OracleConnection('user id=system;password=oracle;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.102)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))')) {
    $conn.Open()
    Using-Object ($cmd = New-Object OracleCommand('truncate table test_tbl', $conn)) {
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object OracleCommand('INSERT INTO test_tbl (user_name, age) VALUES (:userName, :age)', $conn)) {
        $param = New-Object OracleParameter("userName", "明日のジョー")
        $cmd.Parameters.Add( $param ) | Out-Null
        $param = New-Object OracleParameter("age", 17)
        $cmd.Parameters.Add( $param ) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object OracleCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（ロールバック）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object OracleCommand('INSERT INTO test_tbl (user_name, age) VALUES (:userName, :age)', $conn)) {
            $param = New-Object OracleParameter("userName", "丹下さくら")
            $cmd.Parameters.Add( $param ) | Out-Null
            $param = New-Object OracleParameter("age", 43)
            $cmd.Parameters.Add( $param ) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Rollback()
    }
    Using-Object ($cmd = New-Object OracleCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }
    Write-Host "================================================="
    Write-Host "トランザクション（コミット）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object OracleCommand('INSERT INTO test_tbl (user_name, age) VALUES (:userName, :age)', $conn)) {
            $param = New-Object OracleParameter("userName", "丹下さくら")
            $cmd.Parameters.Add( $param ) | Out-Null
            $param = New-Object OracleParameter("age", 43)
            $cmd.Parameters.Add( $param ) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Commit()
    }
    Using-Object ($cmd = New-Object OracleCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }
    $conn.Close()
}

SQLServer

標準についているSystem.Data.SqlClientを使用します。

事前準備

事前に下記のストアドプロシージャを作成します。

CREATE PROCEDURE test_sp(@from int, @to int)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT user_name, age FROM test_tbl
        WHERE age BETWEEN @from  AND  @to;
END
GO

C＃

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace mssqlSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string cnnString = @"Data Source=.\SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=sa";
            using (var conn = new SqlConnection(cnnString))
            {
                conn.Open();
                using (var cmd = new SqlCommand("truncate table test_tbl", conn))
                {
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = new SqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", conn))
                {
                    cmd.Parameters.AddWithValue("@user", "明日のジョー");
                    cmd.Parameters.AddWithValue("@age", 17);
                    cmd.ExecuteNonQuery();

                }
                using (var cmd = new SqlCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }

                // トランザクション
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（ロールバック）");
                using (var tran = conn.BeginTransaction())
                {
                    // Perform database operations
                    using (var cmd = new SqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", conn, tran))
                    {
                        cmd.Parameters.AddWithValue("@user", "丹下さくら");
                        cmd.Parameters.AddWithValue("@age", 43);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Rollback();
                }
                using (var cmd = new SqlCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }
                Console.WriteLine("=================================================");
                Console.WriteLine("トランザクション（コミット）");
                using (var tran = conn.BeginTransaction())
                {
                    // Perform database operations
                    using (var cmd = new SqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", conn, tran))
                    {
                        cmd.Parameters.AddWithValue("@user", "丹下さくら");
                        cmd.Parameters.AddWithValue("@age", 43);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                using (var cmd = new SqlCommand("SELECT user_name, age FROM test_tbl", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                    }
                }

                Console.WriteLine("=================================================");
                Console.WriteLine("ストアド");
                using (var cmd = new SqlCommand("EXEC test_sp @from, @to", conn))
                {
                    cmd.Parameters.AddWithValue("@from", 10);
                    cmd.Parameters.AddWithValue("@to", 19);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetInt32(1).ToString());
                        }
                    }

                }
                conn.Close();
            }
            Console.ReadLine();
        }
    }
}

PowerShell

using namespace System.Data.SqlClient

Using-Object ($conn = New-Object SqlConnection('Data Source=.\SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=sa')) {
    $conn.Open()
    Using-Object ($cmd = New-Object SqlCommand('truncate table test_tbl', $conn)) {
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object SqlCommand('INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)', $conn)) {
        $cmd.Parameters.AddWithValue("@user", "明日のジョー") | Out-Null
        $cmd.Parameters.AddWithValue("@age", 17) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object SqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（ロールバック）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object SqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", $conn, $tran)) {
            $cmd.Parameters.AddWithValue("@user", "丹下さくら") | Out-Null
            $cmd.Parameters.AddWithValue("@age", 43) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Rollback()
    }
    Using-Object ($cmd = New-Object SqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "トランザクション（コミット）"
    Using-Object ($tran = $conn.BeginTransaction()) {
        Using-Object ($cmd = New-Object SqlCommand("INSERT INTO test_tbl (user_name, age) VALUES (@user, @age)", $conn, $tran)) {
            $cmd.Parameters.AddWithValue("@user", "丹下さくら") | Out-Null
            $cmd.Parameters.AddWithValue("@age", 43) | Out-Null
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $tran.Commit()
    }
    Using-Object ($cmd = New-Object SqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }

    Write-Host "================================================="
    Write-Host "ストアド"
    Using-Object ($cmd = New-Object SqlCommand('EXEC test_sp @from, @to', $conn)) {
        $cmd.Parameters.AddWithValue("@from", 10) | Out-Null
        $cmd.Parameters.AddWithValue("@to", 19) | Out-Null
        $cmd.ExecuteNonQuery() | Out-Null
    }

    Using-Object ($cmd = New-Object SqlCommand("SELECT user_name, age FROM test_tbl", $conn)) {
        Using-Object ($reader = $cmd.ExecuteReader()){
            while ($reader.Read())
            {
                Write-Host $reader.GetString(0)  $reader.GetInt32(1).ToString()
            }
        }
    }
    $conn.Close()
}

まとめ

今回はC#やPowerShellでの各種データベースの取り扱いをまとめました。
基本的な操作は全てのDBで同じようなインターフェイスで行われていることがわかります。