Windows PowerShell: Windows PowerShell からデータベースにアクセスする

  • [アーティクル]

Windows PowerShell は、SQL Server データベースに格納されているデータを使用するように構成できます。このコラムでは、その方法を紹介します。

Don Jones

Windows PowerShell でデータベース (SQL Server でホストされているデータベースなど) を使用する方法についての問い合わせが頻繁にあります。実際、この質問は、あまりにも頻繁に寄せられるので、自主出版した『Windows PowerShell Scripting and Toolmaking』(lulu.com、2011 年) に、この方法に関する章を盛り込んだほどです。

私が考えた解決策は、2 つの関数を作成することでした。1 つはデータをクエリするためのもので、もう 1 つはデータを変更 (タスクを挿入、削除、および更新) するためのものです。このようにすると、自己完結型の機能を提供できるので、他のプロジェクトでも簡単に再利用できます。データベース関連の機能は Microsoft .NET Framework に組み込まれているため、これらの関数を機能させるために Windows PowerShell 以外のものをインストールする必要はありません。

データベース関数

これらの関数を使いやすくするために、モジュールとして保存します。たとえば、私は \MyDocuments\WindowsPowerShell\Modules\DataTools\DataTools.psm1 に関数を保存しました。

共有ドキュメントではなく、マイ ドキュメントに保存するようにしてください。この場所に保存すると、Windows PowerShell では、関数を適切に見つけられるようになります。次に、「Import-Module DataTools」というコマンドを実行して、ファイルを読み込みます (図 1 参照)。

図 1 この関数をモジュールとして保存して実行することで見つけやすくなる

function Get-DatabaseData {
	[CmdletBinding()]
	param (
		[string]$connectionString,
		[string]$query,
		[switch]$isSQLServer
	)
	if ($isSQLServer) {
		Write-Verbose 'in SQL Server mode'
		$connection = New-Object-TypeName System.Data.SqlClient.SqlConnection
	} else {
		Write-Verbose 'in OleDB mode'
		$connection = New-Object-TypeName System.Data.OleDb.OleDbConnection
	}
	$connection.ConnectionString = $connectionString
	$command = $connection.CreateCommand()
	$command.CommandText = $query
	if ($isSQLServer) {
		$adapter = New-Object-TypeName System.Data.SqlClient.SqlDataAdapter $command
	} else {
		$adapter = New-Object-TypeName System.Data.OleDb.OleDbDataAdapter $command
	}
	$dataset = New-Object-TypeName System.Data.DataSet
	$adapter.Fill($dataset)
	$dataset.Tables[0]
}
function Invoke-DatabaseQuery {
	[CmdletBinding()]
	param (
		[string]$connectionString,
		[string]$query,
		[switch]$isSQLServer
	)
	if ($isSQLServer) {
		Write-Verbose 'in SQL Server mode'
		$connection = New-Object-TypeName System.Data.SqlClient.SqlConnection
	} else {
		Write-Verbose 'in OleDB mode'
		$connection = New-Object-TypeName System.Data.OleDb.OleDbConnection
	}
	$connection.ConnectionString = $connectionString
	$command = $connection.CreateCommand()
	$command.CommandText = $query
	$connection.Open()
	$command.ExecuteNonQuery()
	$connection.close()
}

これらの関数を使用するには、Get-DatabaseData コマンドレットまたは Invoke-DatabaseQuery コマンドレットのいずれかを呼び出します。各コマンドレットには、SQL Server をクエリするときに必要な –isSQLServer パラメーターが用意されています。このパラメーターを指定しないと、Windows PowerShell では、SQL Server 以外の OleDB ソースを使用していると見なされます。また、–connectionString パラメーターと –query パラメーターも指定する必要があります。

たとえば、Server1 という名前の SQL Server コンピューターにある MyDatabase データベースの MyTable テーブルの全データを削除するには、SQL Server の既定のインスタンスではない SQLEXPRESS インスタンスを使用して、次のコマンドを実行します (ここでは統合ログインではなく、SQL Server のログインとパスワードを指定していることに注意してください)。

Invoke-DatabaseQuery –query "DELETE FROM MyTable" –isSQLServer –connectionString "Data Source=SERVER1\SQLEXPRESS;Initial Catalog=MyDatabase;User Id=myLogin;Password=P@ssw0rd"

ここでヒントを紹介しましょう。私は接続文字列の構文を覚えることができませんし、覚えようとも思いません。connectionstrings.com (英語) にアクセスして、必要な例を探しています。

Invoke-DatabaseQuery コマンドレットでは、出力は生成されませんが、Get-DatabaseData コマンドレットでは、出力が生成されます。具体的には、Get-DatabaseData コマンドレットでは、DataTable が返されます。DataTable は、オブジェクトのコレクションで、各オブジェクトはクエリ結果の行を表しています。各オブジェクトのプロパティは、クエリ結果の列に対応しています。

テストにまつわる話

全体的なテストを実行しましょう。ここでは、無償の SQL Server Express Edition を使用します。SQL Server Express Edition は、SQLEXPRESS という名前付きインスタンスとしてインストールされます。私は Management Studio Express ツールが同梱されているバージョンをダウンロードしてインストールしました。これは、すべてローカル コンピューターで行いました。また、この例では、SQL Server 認証ではなく、Windows 認証を使用します。

Management Studio を起動し、localhost\SQLEXPRESS インスタンスに接続します (管理者としてログオンしていたので、この資格情報を使用してデータベース サーバーにアクセスしました)。新しいクエリ ウィンドウを開き、図 2 のクエリを実行してサンプル データベースを作成します。

図 2. サンプル データベースを作成するクエリ

CREATE DATABASE [Inventory] ON  PRIMARY 
( NAME = N'Inventory', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Inventory.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'Inventory_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Inventory_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE [Inventory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Computers](
[computer] [nvarchar](50) NULL,
[osversion] [nvarchar](100) NULL,
[biosserial] [nvarchar](100) NULL,
[osarchitecture] [nvarchar](5) NULL,
[procarchitecture] [nvarchar](5) NULL
) ON [PRIMARY]

GO

次に、クエリ ウィンドウから図 2 のクエリを削除して、次のクエリを実行します。

Use [Inventory]
Go
INSERT INTO Computers (computer) VALUES ('localhost')
INSERT INTO Computers (computer) VALUES ('localhost')
INSERT INTO Computers (computer) VALUES ('not-online')

これで、3 つのデータ行がテーブルに追加されます。その後、Windows PowerShell に切り替えます。DataTools モジュールを Windows PowerShell にインポートしたら、次のコマンドを実行します。

Get-DatabaseData -verbose -connectionString 'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' -isSQLServer-query "SELECT * FROM Computers"
Invoke-DatabaseQuery -verbose -connectionString 'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' -isSQLServer-query "INSERT INTO Computers (computer) VALUES('win7')"
Get-DatabaseData -verbose -connectionString 'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' -isSQLServer-query "SELECT * FROM Computers"

このコマンドでは、最初にテーブルに 3 つのコンピューターのデータが設定されていたことと、別のコンピューターを適切に追加できたことを確認します。

データベース処理が簡単に

これらの 2 つの関数を作成したことで、私は多くの時間を節約することができました。データベースに対して読み取り/書き込み処理を行う必要がある場合、モジュールをインポートすれば準備完了です。

このコラムで紹介したフォルダーに、モジュールを格納する必要はありません。Import-Module コマンドレットを実行するときに .psm1 ファイルのフル パスを指定することを厭わなければ、どこに保存してもかまいません。ただし、このフォルダーを使用すると、パスを指定しなくても、名前を指定するだけでモジュールをインポートできます。これで、さらに時間を節約できます。

Don Jones

Don Jones は、Microsoft MVP の受賞者で、『Learn Windows PowerShell in a Month of Lunches』(Manning Publications、2011 年) の著者でもあります。この書籍は、管理者が Windows PowerShell を効率的に使用できるようにすることを目的としています。また、一般ユーザーを対象にオンサイトの Windows PowerShell トレーニングも開催しています。Don に対するお問い合わせについては、彼の Web サイト (ConcentratedTech.com、英語)、または bit.ly/AskDon (英語) を参照してください。

関連コンテンツ