PowerShellでSqlServerの操作クラスを作成

PowerShell勉強用。

#環境
Windows10 Home
PSVersion 5.1

#操作クラス

SqlManager.ps1

using namespace System.Data;
using namespace System.Data.SqlClient;

# =============================================================
# Class    : SqlManager
# -------------------------------------------------------------
# Sunmmary : SqlServerの管理クラス
# =============================================================
class SqlManager
{
    # -------------------------------
    # Member
    # -------------------------------
    [SqlConnection]  $clsSqlConnect;
    [SqlTransaction] $clsSqlTran;

    # =============================================================
    # constructor : SqlManager
    # -------------------------------------------------------------
    # Summary     : コンストラクタ
    # Param1      : サーバー
    # Param2      : データベース
    # Param3      : ユーザー
    # Param4      : パスワード
    # =============================================================
    SqlManager([string]$strServer, [string]$strDatabase, [string]$strUserId, [string]$strPassword)
    {
        # 接続文字列の設定
        [string]$strConnectionString = "Data Source=$strServer;Initial Catalog=$strDatabase;User ID=$strUserId;Password=$strPassword;MultipleActiveResultSets=True";

        # DB接続
        $this.clsSqlConnect = [SqlConnection]::New($strConnectionString);
        $this.clsSqlConnect.Open();
    }

    # =============================================================
    # Method   : BeginTran
    # -------------------------------------------------------------
    # Summary  : トランザクション
    # =============================================================
    BeginTran()
    {
        $this.clsSqlTran = $this.clsSqlConnect.BeginTransaction();
    }

    # =============================================================
    # Method   : Commit
    # -------------------------------------------------------------
    # Summary  : コミット
    # =============================================================
    Commit()
    {
        if ($this.clsSqlTran.Connection -ne $null)
        {
            $this.clsSqlTran.Commit();
            $this.clsSqlTran.Dispose();
            $this.clsSqlTran = $null;
        }
    }

    # =============================================================
    # Method   : RollBack
    # -------------------------------------------------------------
    # Summary  : ロールバック
    # =============================================================
    RollBack()
    {
        if ($this.clsSqlTran.Connection -ne $null)
        {
            $this.clsSqlTran.Rollback();
            $this.clsSqlTran.Dispose();
            $this.clsSqlTran = $null;
        }
    }

    # =============================================================
    # Method   : Dispose
    # -------------------------------------------------------------
    # Summary  : 解放処理
    # =============================================================
    Dispose()
    {
        if ($this.clsSqlTran -ne $null)
        {
            $this.clsSqlTran.Dispose();
            $this.clsSqlTran = $null;
        }
        if ($this.clsSqlConnect -ne $null)
        {
            $this.clsSqlConnect.Dispose();
            $this.clsSqlConnect = $null;
        }
    }
    
    # =============================================================
    # Method   : Fill
    # -------------------------------------------------------------
    # Summary  : クエリの実行結果をデータテーブルで返す
    # Param1   : サーバー
    # Return   : 実行結果
    # =============================================================
    [DataTableCollection] Fill([string]$strQuery)
    {
        [SqlCommand]          $clsSqlCmd  = $null;
        [SqlDataAdapter]      $clsAdapter = $null;
        [DataSet]             $clsDataSet = $null;

        try
        {
            $clsSqlCmd             = $this.clsSqlConnect.CreateCommand();
            $clsSqlCmd.Connection  = $this.clsSqlConnect;
            $clsSqlCmd.Transaction = $this.clsSqlTran;
            $clsSqlCmd.CommandText = $strQuery;
            $clsAdapter            = [SqlDataAdapter]::New($clsSqlCmd);
            $clsDataSet            = [DataSet]::New();

            $clsAdapter.Fill($clsDataSet)   
        }
        finally
        {
            if($clsSqlCmd -ne $null)
            {
                $clsSqlCmd.Dispose();
                $clsSqlCmd = $null;
            }
            if($clsAdapter -ne $null)
            {
                $clsAdapter.Dispose();
                $clsAdapter = $null;
            }
        }
        return $clsDataSet.Tables;
    }

    # =============================================================
    # Method   : ExecuteQuery
    # -------------------------------------------------------------
    # Summary  : クエリの実行し、結果を返す
    # Param1   : 適用するクエリ
    # Return   : 実行結果
    # =============================================================
    [Boolean]ExecuteQuery([string]$strQuery)
    {
        [Boolean]        $bResult    = $false;
        [SqlCommand]     $clsSqlCmd  = $null;
        [SqlDataAdapter] $clsAdapter = $null;
        [DataSet]        $clsDataSet = $null;

        try
        {
            $clsSqlCmd             = $this.clsSqlConnect.CreateCommand();
            $clsSqlCmd.Connection  = $this.clsSqlConnect;
            $clsSqlCmd.Transaction = $this.clsSqlTran;
            $clsSqlCmd.CommandText = $strQuery;

            $clsSqlCmd.ExecuteNonQuery();
            $bResult = $true;
        }
        finally
        {
            if($clsSqlCmd -ne $null)
            {
                $clsSqlCmd.Dispose();
                $clsSqlCmd = $null;
            }
            if($clsAdapter -ne $null)
            {
                $clsAdapter.Dispose();
                $clsAdapter = $null;
            }
            if($clsDataSet -ne $null)
            {
                $clsDataSet.Dispose();
                $clsDataSet = $null;
            }
        }
        return $bResult;
    }
}

#使用方法

SqlMngTest.ps1

# -------------------------------
# 初期処理
# -------------------------------

# クラスの読み込み
# ⇒ 同一ディレクトリに存在する、SqlManager.ps1を実行させる
. (Join-Path $MyInvocation.MyCommand.Path ../SqlManager.ps1);

# SqlServer接続用の情報を設定
[string]$strServer   = 'localhost';
[string]$strDatabase = 'TempDB';
[string]$strUserId   = 'sa';
[string]$strPassword = 'sa';

# -------------------------------
# INSERT
# -------------------------------
try
{
    $clsSqlMng = [SqlManager]::New($strServer,$strDatabase,$strUserId,$strPassword);

    $clsSqlMng.BeginTran();

    [string] $strInsert = "insert into Hoge values ('000','TEST', 999)";
    
    $clsSqlMng.ExecuteQuery($strInsert);

    $clsSqlMng.Commit();
}
catch
{
    $clsSqlMng.RollBack();
}
finally
{
    $clsSqlMng.Dispose();
}

# -------------------------------
# SELECT
# -------------------------------
try
{
    $clsSqlMng = [SqlManager]::New($strServer,$strDatabase,$strUserId,$strPassword);

    [String]$strSelect = "SELECT * FROM Hoge";
    [Int32]$iCount = 1;
    
    foreach($item in $clsSqlMng.Fill($strSelect))
    {
        # 画面表示
        $item | format-table;

        # CSV保存
        $item | export-csv ("Result" + $iCount + ".csv") -Encoding default -NoTypeInformation;
        
        $iCount++;
    }
}
finally
{
    $clsSqlMng.Dispose();
}