PowerShell ile Microsoft SQL Server (MSSQL) Veritabanını Sorgulama

Bu makalede, bir Microsoft SQL Server'a bağlanmanın ve PowerShell'den SQL sorguları çalıştırmanın tüm etkili yollarını tartışacağız. PowerShell kullanarak SQL Server ile çalışmanın birçok yolu vardır ve Web'de birçok makaleyi incelediğinizde kafanız kolayca karışabilir, çünkü hepsi farklı yöntemleri açıklar ve deneyimli bir yöneticinin bile soruları olabilir.

System.Data.OleDb Kullanarak PowerShell'de T-SQL Sorguları

PowerShell, .NET Framework sınıflarına erişebildiğinden, T-SQL sorgularını yürütmek için System.Data.OleDb'deki sınıfları kullanabilirsiniz.

Burada, System.Data.OleDb sınıfını kullanarak SQL Server'a bağlanmak için örnek bir PowerShell betiği verilmiştir. MS SQL veritabanındaki bir tabloya karşı bir SELECT sorgusu çalıştıralım:

$dataSource = “lon-sql01\testdb”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

MSSQL veritabanına karşı bir INSERT/UPDATE/DELETE sorgusu yürütmek için bir PowerShell betiği örneği aşağıda verilmiştir:

$dataSource = “lon-sql01\testdb”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

Bu

$rowsAffected

değişken eklenen veya değiştirilen satır sayısını içerir. Bir güncelleme veya silme sorgusu çalıştırmak için SQL sorgusunun satırını değiştirmeniz yeterlidir.

$sql

değişken.

System.Data.SqlClient Sınıfını Kullanarak PowerShell'de SQL Sorgusu Çalıştırma

MS SQL Server'a PowerShell'den erişmek için başka bir yerleşik .NET sınıfını kullanabilirsiniz - System.Data.SqlClient. İşte SqlClient ile bir PowerShell betiğinde bir SELECT sorgusu örneği:

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

INSERT/DELETE/UPDATE sorgusuna bir örnek:

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()

Not. SqlClient sınıflarını içeren kod, OleDB'deki koda çok benzer. Bu sınıflar benzer şekilde çalışır:

  1. Bir MSSQL sunucusu bağlantı nesnesi oluşturulur;

  2. SQL sorgusu olan bir nesne oluşturulur ve ona bağlantı nesnesi atanır;

  3. Daha sonra bir SELECT sorgusu çalıştırılması durumunda, bir adaptör nesnesi oluşturulur ve sorgu bu nesne bağlamında yürütülür;

  4. INSERT/UPDATE/DELETE sorgusu çalıştırılması durumunda, sorguya sahip nesne (bağlantı nesnesini içeren) şu komutu yürütür:

    ExecuteNonQuery()

    yöntem.

SQL Server Management Studio Modülünü Kullanarak PowerShell'de SQL Sorgusu

Microsoft.SqlServer.Smo (SMO) sınıflarını kullanmak için bilgisayarınızda SQL Server Management Studio kurulu olmalıdır.

SMO modülünü yükleyin, yeni bir sunucu nesnesi oluşturun ve ardından bir SELECT sorgusu çalıştırın:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "lon-sql01\testdb"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

Bir ekleme/güncelleme/silme sorgusu için şunu çalıştırın:

ExecuteNonQuery
$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('123456')")

Not. SMO kitaplıklarını NuGet Paket Yöneticisi aracılığıyla da yükleyebilirsiniz:

  1. nuget.exe'yi indirin https://www.nuget.org/downloads;

  2. PowerShell'i yönetici olarak çalıştırın ve nuget.exe'yi içeren dizine gidin;

  3. Koşmak:

    .\nuget.exe Install Microsoft.SqlServer.SqlManagementObjects

    .

  4. Geçerli dizinde tüm DLL'leri içeren Microsoft.SqlServer.SqlManagementObjects klasörü görünecektir;

  5. SMO kitaplığını bir DLL dosyasından PowerShell oturumunuza yükleyin. Komut dosyanıza ekleyin:

add-type -Path "C:\Users\username\Downloads\Microsoft.SqlServer.SqlManagementObjects.150.18208.0\lib\net45\Microsoft.SqlServer.Smo.dll"

Daha sonra SMO sınıfları kullanıma açılacaktır.

SQLServer PowerShell Modülünden Invoke-Sqlcmd Cmdlet'i

Invoke-Sqlcmd cmdlet'ini kullanmak için SqlServer for PowerShell modülü'nü kurun. PowerShell'i yönetici ayrıcalıklarıyla çalıştırın ve şu komutu yürütün:

Install-Module -Name SqlServer

(Yükleyici bildirimlerini kabul etmek için Y'ye ve ardından ENTER'a basın.)

Kurulumdan sonra, şu komutu çalıştırarak modülün doğru kurulduğundan emin olabilirsiniz:

Get-Module SqlServer -ListAvailable

Invoke-Sqlcmd cmdlet'i, PowerShell'den bir Microsoft SQL Sunucusuna bağlanmanın diğer yollarından daha kolay ve sezgiseldir. Invoke-Sqlcmd, SELECT ve INSERT/UPDATE/DELETE sorguları için aynı sözdizimini kullanır.

Aşağıda bir Select sorgusu örneği verilmiştir:

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Query "sp_who"

Bu bir INSERT sorgusu örneğidir:

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Database "test1" -Query "insert into test_table (test_col) Values (‘123321’)"

Diğer yöntemlerden farklı olarak,

Invoke-Sqlcmd

her zaman ayarlıdır

-Query

parametre.

Hangi SQL bağlantı seçeneğini kullanmalısınız?

oledb/smo/sqlclient/invoke-sqlcmd arasındaki seçim, bir PowerShell betiğini çalıştıracağınız göreve ve ortama bağlıdır.

Bir betiği birden çok sunucuya dağıtmak istiyorsanız (örneğin, betiğiniz izleme verilerini yerel olarak topluyor), SMO veya SqlServer PowerShell modülünü (Invoke-SQLcmd) kullanmak makul değildir çünkü uzak ana bilgisayarlara fazladan paketler yüklemeniz gerekecektir. betiği çalıştırın ve çok sayıda sunucu varsa bundan kaçınmak daha iyidir.

Buna karşılık, PowerShell için SqlServer modülü, SQL Server'ınızla çalışmak için başka birçok cmdlet sunar (buradan daha fazla bilgi edinebilirsiniz: https://docs.microsoft.com/en-us/powershell/module/sqlserver). Modül, SQL Server'ın kendisini yönetmek için daha fazla komut içerir.

Komut dosyanız yönetimle ilgili olmayan görevleri gerçekleştirecekse (örneğin, iş mantığının bir bölümünden sorumluysa), daha uygun geliştirme araçları sağladıkları için System.Data.SqlClient/SMO'yu kullanmaya değer. OleDB'nin bir avantajı, yalnızca bir SQL Server ile değil, aynı zamanda Access/Oracle/Firebird/Interbase ile de çalışabilmesidir.

İlgili makaleler