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

Можно настроить Windows PowerShell на использование данных из SQL Server. Из этой статьи вы узнаете, как это делать.

Дон Джонс

Мне очень часто задавали вопрос, как заставить 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. Однако если модуль находится в указанной папке, его можно импортировать просто по имени, не указывая путь. Это дополнительно экономит время.

Дон Джонс носит звание MVP и является автором «Learn Windows PowerShell in a Month of Lunches» (Manning Publications Co., 2011), книги, призванной помочь каждому администратору освоить Windows PowerShell. Дон читает общедоступные и интерактивные курсы по Windows PowerShell. Связаться с ним можно через его веб-сайт ConcentratedTech.com или сайт bit.ly/AskDon.