この記事では、PowerShellからSQL Serverのテーブルデータを取得する方法についてご説明します。
【動画】PowerShellからSQL Serverのテーブルデータを取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
接続文字列を設定し、PowerShellからSQL Serverのデータベースに接続します。
接続したら、取得先のサーバ・テーブルに対してSELECT文を実行してテーブルデータを取得しています。
今回はコンソール上に取得したデータを表示させています。
コードの流れ
system.Dataアセンブリをロードすることで、データベースにアクセスするために必要なクラスやメソッドを使用することができるようになります。
今回はSQL Server接続で接続するので、接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
SQL Serverのデータベースとテーブルの例
今回は下のSQL Serverのデータベースとテーブルを用意しました。
SQL Serverのデータベースには「tbl_test」のテーブルが存在しており、10件のデータが存在しています。
今回はこの10件のデータをPowerShellが取得してコンソール上に表示させます。
表示させた結果は下のとおりです。
PowerShellのコード(例)
# 必要なアセンブリをロードする
Add-Type -AssemblyName System.Data
# SQL Serverの接続情報を設定する
$serverName = "(LocalDB)\MSSQLLocalDB" # 接続するサーバ名
$databaseName = "CS_01" # 接続するデータベース名
$userId = "test_user" # ログインユーザ名
$password = "pass_str1" # ログインユーザのパスワード
# 接続文字列を設定する(SQL Server認証の場合)
$connectionString = "Server=$serverName;Database=$databaseName;User Id=$userId;Password=$password;"
# SqlConnectionオブジェクトを生成する
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# SELECT文を設定する
$query = "SELECT * FROM tbl_test"
# SqlCommandオブジェクトを生成する
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
# データベースに接続する
$connection.Open()
# SELECT文を実行する
$reader = $command.ExecuteReader()
# 空の配列を用意する
$table = @()
# テーブルのフィールド名をすべて取得する
$columnNames = $reader.GetSchemaTable() | Select -Expand ColumnName
# 取得したデータの最終行まで処理を繰り返すwhileループ
while ($reader.Read()) {
# 1行分のデータを格納するPSObjectインスタンスを生成し、rowに格納する
$row = New-Object PSObject
# フィールドの数だけ処理を繰り返すforeachループ
foreach ($columnName in $columnNames) {
# rowに行のデータを格納する
$row | Add-Member -MemberType NoteProperty -Name $columnName -Value $reader[$columnName]
}
# tableにrowの値を追加で格納する
$table += $row
}
# 表形式でtableの値を出力する
$table | Format-Table -AutoSize
# データベースとの接続を閉じる
$connection.Close()
注目すべきコード①
最初に見て頂きたいのは2行目です。
# 必要なアセンブリをロードする Add-Type -AssemblyName System.Data
コードの説明
以上のコードは、必要なアセンブリをロードするコードです。
今回はSQL Serverのデータベースファイルに接続するのでsystem.Dataアセンブリをロードします。
system.Dataアセンブリをロードすることで、データベースにアクセスするために必要なクラスやメソッドを使用することができるようになります。
注目すべきコード②
次に見て頂きたいのは5行目から8行目です。
# SQL Serverの接続情報を設定する $serverName = "(LocalDB)\MSSQLLocalDB" # 接続するサーバ名 $databaseName = "CS_01" # 接続するデータベース名 $userId = "test_user" # ログインユーザ名 $password = "pass_str1" # ログインユーザのパスワード
コードの説明
以上のコードは、SQL Serverの接続情報を設定するコードです。
今回はSQL Server接続で接続するので、接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
注目すべきコード③
次に見て頂きたいのは11行目から14行目です。
# 接続文字列を設定する(SQL Server認証の場合) $connectionString = "Server=$serverName;Database=$databaseName;User Id=$userId;Password=$password;" # SqlConnectionオブジェクトを生成する $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
コードの説明
以上のコードは、接続文字列を作成してSqlConnectionオブジェクトを生成するコードです。
SQL Serverのデータベースに接続するための接続情報をconnectionオブジェクトに格納します。
このコードにより、どのSQL Serverのデータベースに接続するのか、PowerShellが認識できるようになります。
注目すべきコード④
次に見て頂きたいのは17行目から20行目です。
# SELECT文を設定する $query = "SELECT * FROM tbl_test" # SqlCommandオブジェクトを生成する $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
コードの説明
以上のコードは、SELECT文を取得し、そのSELECT文をSqlCommandメソッドに渡してSqlCommandオブジェクトを生成するコードです。
SqlCommandオブジェクトを生成することで、SELECT文を実行することができるようになります。
注目すべきコード⑤
次に見て頂きたいのは23行目です。
# データベースに接続する $connection.Open()
コードの説明
以上のコードは、PowerShellがデータベースに接続する処理のコードです。
注目すべきコード⑥
次に見て頂きたいのは26行目です。
# SELECT文を実行する $reader = $command.ExecuteReader()
コードの説明
以上のコードは、SELECT文を実行する処理のコードです。
実行したSELECT文が抽出したデータはreaderに格納されます。
注目すべきコード⑦
次に見て頂きたいのは29行目です。
# 空の配列を用意する $table = @()
コードの説明
以上のコードは、実行したSELECT文が抽出したデータを格納する配列を用意するコードです。
コンソールに出力する情報をすべて格納しておく「箱」をここで用意しておきます。
注目すべきコード⑧
次に見て頂きたいのは32行目です。
# テーブルのフィールド名をすべて取得する $columnNames = $reader.GetSchemaTable() | Select -Expand ColumnName
コードの説明
以上のコードは、テーブルのフィールド名をすべて取得するコードです。
今回は「tbl_test」のテーブルに対してSELECT文を実行しているので、「tbl_test」テーブルにあるフィールド名をすべて取得し、columnNamesに格納しています。
「tbl_test」のフィールド名は「infoNo」「person」「score」の3つのフィールドがあるので、この3つ全てのフィールド名がcolumnNamesに格納されます。
注目すべきコード⑨
次に見て頂きたいのは35行目です。
# 取得したデータの最終行まで処理を繰り返すwhileループ
while ($reader.Read()) {
コードの説明
以上のコードは、取得したデータの最終行まで処理を繰り返すwhile文です。
今回は10件あるテーブルデータを取得しているので10件目まで処理を繰り返します。
注目すべきコード⑩
次に見て頂きたいのは38行目から48行目です。
# 1行分のデータを格納するPSObjectインスタンスを生成し、rowに格納する
$row = New-Object PSObject
# フィールドの数だけ処理を繰り返すforeachループ
foreach ($columnName in $columnNames) {
# rowに行のデータを格納する
$row | Add-Member -MemberType NoteProperty -Name $columnName -Value $reader[$columnName]
}
# tableにrowの値を追加で格納する
$table += $row
コードの説明
以上のコードは、取得したテーブルデータをtableに格納する処理のコードです。
取得したテーブルデータを1行ずつ取得し、tableに格納していきます。
コードの詳細
38行目のコードは、新しいPowerShellオブジェクトのPSObjectを生成し、rowという変数に格納します。
41行目のコードは、フィールドの数だけ処理を繰り返すforeachです。
「tbl_test」のフィールドは「infoNo」「person」「score」の3つなので、3回処理を繰り返します。
44行目のコードでは、rowに行のデータを格納しています。
rowへの追加はAdd-Memberコマンドレットを実行します。
Add-Memberコマンドレットに「-MemberType NoteProperty」パラメータを指定することで、追加するメンバー(プロパティやメソッドなど)の種類を指定します。
「-MemberType NoteProperty」パラメータは値を格納するために指定します。
「-Name」パラメータにcolumnNameを指定し、「-Value」パラメータに$reader[$columnName]を指定します。
columnNameには現在のフィールド名が、$reader[$columnName]には現在の列の値が格納されています。
各行のデータが一つのPSObject(PowerShellオブジェクト)としてまとまり、そのプロパティとして各列のデータが格納されます。
48行目のコードは、tableにrowの値を追加で格納します。
注目すべきコード⑪
次に見て頂きたいのは52行目です。
# 表形式でtableの値を出力する $table | Format-Table -AutoSize
コードの説明
以上のコードは、表形式でtableの値を出力するコードです。
注目すべきコード⑫
次に見て頂きたいのは55行目です。
# データベースとの接続を閉じる $connection.Close()
コードの説明
以上のコードは、データベースとの接続を閉じる処理のコードです。
動作確認
「SQL Serverのデータベースとテーブルの例」をご覧ください。
最後に
この記事では、PowerShellからSQL Serverのテーブルデータを取得する方法についてご説明しました。
PowerShellからSQL Serverのテーブルデータを取得したい場合は本記事を参考にして頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
