Windows PowerShell: Доступ к базам данных из PowerShell

Мне очень часто задавали вопрос, как заставить Windows PowerShell использовать данные из базы данных, например в SQL Server. Это стало происходить так часто, что я решил посвятить этому главу в своей электронной книге «Windows PowerShell Scripting and Toolmaking» (lulu.com, 2011).

Мое решение заключается в создании двух функций: одна запрашивает данные, а другая меняет их (то есть выполняет вставку, удаление и обновление). В этому случае функциональность оказывается самодостаточной и ее можно повторно использовать во многих проектах. Так как возможности доступа к базам данных встроены в Microsoft .NET Framework, не нужно ничего дополнительно устанавливать, кроме Windows PowerShell.

Функции для работы с базами данных

Чтобы было легче работать с этими функциями, сохраните их в модуле. Например, я свои поместил в файл \MyDocuments\WindowsPowerShell\Modules\DataTools\DataTools.psm1. Вот код модуля:

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()
}

Ничего не перепутайте и сохраните свой модуль в ветке со своими пользовательскими документами (My Documents), а не с общими документами (Shared Documents). В этом случае Windows PowerShell сможет корректно их найти. Затем для загрузки файла просто выполните команду Import-Module DataTools.

Чтобы воспользоваться этим, можно вызвать команду Get-DatabaseData или Invoke-DatabaseQuery. У этих команд есть параметр –isSQLServer, который надо использовать при запросе SQL Server, иначе Windows PowerShell посчитает, что вы обращаетесь к OleDB-источнику, который не является SQL Server. Нужно также предоставить параметры –connectionString и –query.

Например, чтобы удалить все данные в таблице по имени MyTable в базе данных MyDatabase на машине с SQL Server по имени Server1, на которой установлен именованный экземпляр 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. В частности, она возвращает DataTable, которая представляет собой коллекцию объектов, каждый из которых является строкой результатов запроса. Свойства каждого объекта соответствуют столбцам результата запроса.

Тестирование и еще раз тестирование

Давайте выполним полный цикл тестирования. Воспользуйтесь бесплатной редакцией SQL Server Express Edition, которая устанавливается как именованный экземпляр SQLEXPRESS. Я загрузил и установил версию, в которую входит средство Express Management Studio. Все это я выполнил на своем компьютере. В этом примере я буду полагаться на проверку подлинности Windows, а не SQL Server.

Я открыл консоль SQL Server Management Studio и подключился к экземпляру localhost\SQLEXPRESS (я вошел в систему как Administrator и просто использовал эти учетные данные для доступа к серверу баз данных). Я открыл новое окно запроса и выполнил следующий запрос создания примера базы данных:

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

Затем я удалил предыдущий запрос из окна и выполнил следующий запрос:

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

Этот запрос добавляет в таблицу три строки данных. Затем я переключился в Windows PowerShell. После импорта своего модуля DataTools я выполнил следующие команды:

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"

Это подтверждает, что я сначала добавил три записи, после чего добавил еще одну.

Базы данных без проблем

Использование этих двух функций экономит мне много времени. Каждый раз, когда мне нужно что-то загрузить или записать в базу данных, я просто импортирую свой модуль и все.

Не обязательно хранить в указанной мной папке. Его можно размещать где угодно, если только вы готовы каждый раз указывать полный путь к psm1-файлу при выполнении команды Import-Module. Однако если модуль находится в указанной папке, его можно импортировать просто по имени, не указывая путь. Это дополнительно экономит время.