Посмотрим каким образом можно выполнять различные запросы SQL, такие как Select, Insert, Update, используя PowerShell. Это может быть полезно для интеграции PowerShell с базами данных SQL. Речь пойдет про Access, MS SQL, а так же любые другие СУБД.
Разбирать будем на примере моего скрипта из прошлого поста Чем открыть JSON из приложения проверка чеков? Например, Access! Скрипт можно скачать и ознакомиться с достаточно подробными комментариями.
Создаем подключение
Для начала нам необходимо создать объект подключения к нашей СУБД. Конечно это напрямую зависит от способа подключения к БД — SQLClient, OleDB, ODBC, OracleClient.
Давайте рассмотрим сначала самый универсальный способ — OleDB. Создадим подключение к файлу Access, путь к которому хранится в переменной $AccessFullFilePath.
|
1 2 3 |
#Создаем подключение к файлу базы данных $conn = New-Object System.Data.OleDb.OleDbConnection $conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0.7;Data Source=$AccessFullFilePath;Persist Security Info=False" |
Как видно из приведенного примера достаточно создать переменную и присвоить ей новый .Net объект OleDb.OleDbConnection. После чего указать параметр ConnectionString. Более подробно о строке подключения тут.
В общем-то OleDB можно использовать и для подключения к MS SQL серверу, однако в этом случае придется указать имя пользователя и пароль в открытом виде. Поэтому я решил воспользоваться объектом SqlClient.SqlConnection.
Давайте рассмотрим еще один способ — SQLClient. У меня уже есть переменная $credential типа System.Management.Automation.PSCredential. Из этой переменной я получил пароль и имя пользователя. Обратите внимание пароль установлен в режим readonly. После чего я получил объект SqlCredential, который и использовал совместно со строгой подключения
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#приглашение на ввод логина и пароля $Credential = Get-Credential #Получаем пароль из объекта System.Management.Automation.PSCredential и делаем его доступны для чтения $password = $Credential.Password $password.makereadonly() #создаем объект SQLCredential $sqlcreds = New-Object System.Data.SqlClient.SqlCredential($Credential.username,$password) #создаем подключение к базе данных и указывает разрешения $conn = New-Object System.Data.SqlClient.SqlConnection $conn.credential = $sqlcreds #указываем строку подключения $conn.ConnectionString = "server=$sqlserver;database=$database;" |
Обратите внимание строка подключения в одном параметре, объект Credential в другом.
Подключение к SQL или Access
Скорее вопрос даже не в том, что подключение к базе данных нужно открыть, а скорее о том, что в конце вашего скрипта его нужно закрыть.
|
1 2 3 4 5 6 7 8 9 |
#Открываем базу данных $conn.open() # #тут ваш длинный код по работе с базой данных # #закрываем подключение к БД $conn.close() |
Выполнение SQL запроса на получение данных
Если вам нужно получить, какие-либо данные вам необходимо
- Создать команду
- Вставить SQL запрос в команду
- Выполнить запрос, сохранив результат
- Можно построчно читать данные
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#создаем команду, которую потом будем несколько раз изменять $cmd=$conn.CreateCommand() #указываем SQL запрос в команде $cmd.CommandText="select recID,[user],totalsum,datet from receipt" #выполняем и сохраняем результат $result = $cmd.ExecuteReader() #выводим по очереди заголовки столбцов write-host $result.getname(0) $result.getname(1) $result.getname(2) $result.getname(3) #запускаем цикл чтения всех данных while ($result.Read()) { #для получения данных используем функцию getvalue(номер_столбца), указывая номер столбца write-host $result.Getvalue(0) $result.GetValue(1) $result.GetValue(2) $result.GetValue(3) } |
Обратите внимание, каждый раз вызывая Read() мы переходим к новой строчке в нашей таблице. Для чтения заголовков столбцов используем GetName() и указываем номер столбца. Для чтения значений используем GetValue() так же с указанием столбца. Вместо GetValue() можно использовать GetDateTime(), GetDouble(), GetGuid(), GetInt32(). Подробнее об использовании DataReader можно почитать на сайте Microsoft.
Выполняем SQL запрос для изменения данных
Если мы желаем использовать запросы, которые не возвращают нам таблицы: DDL, DCL, некоторые команды DML и т.п. В этом случае мы используем не ExecuteReader(), а ExecuteNonQuery() и конечно же нам уже не нужно так сильно анализировать результат.
|
1 2 3 4 5 |
#Формируем запрос на вставку чека $cmd.CommandText="INSERT INTO Receipt (cashTotalSum,dateT) values ($($Receipt.cashTotalSum/100),'$($Receipt.dateTime -replace '(....)-(..)-(..)T(........)','$3.$2.$1 $4')')" #выполняем запрос $cmd.ExecuteNonQuery() |
Т.е. тут особых действий применять не нужно. Вставили SQL запрос и выполнили команду. Если все успешно, то будет 1, если команда вышла с ошибками, то — 0.
Успехов вам в ваших скриптах.