大致的作法是利用.NET反射(Reflection)載入.NET System.Data組件,建立SqlClient實作,然後就是你我熟悉的SqlCommand。
測試兩種作法:
- 1.執行T-SQL
- 2.執行預存程序
執行T-SQL
假設我們想備份AdventureWorks資料庫中的產品資料表production.product
Try
{
[System.reflection.assembly]::loadwithpartialname('System.Data')
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source = STANLEY14\SQL2014; Initial Catalog = AdventureWorks2014; User ID = 帳號; Password = 密碼"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$conn.open()
$cmd.commandtext = " select * into temp_Powersell from production.product"
$obj = $cmd.ExecuteNonQuery()
$obj
}
Finally
{
$conn.Close()
}
打開Powershell ISE
執行結果:
載入組件成功!執行結果為504筆!
從SSMS也可以查到備份後的資料表Temp_powershell
呼叫預存程序
1.首先在資料庫中建立一支預存程序
CREATE PROCEDURE [dbo].[TestPS1]
@return nvarchar(max) output
AS
BEGIN
SELECT @return = 'Hello World!'
RETURN
END
GO
2.用powershell執行預存程序
Try
{
[System.reflection.assembly]::loadwithpartialname('System.Data')
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source = STANLEY14\SQL2014; Initial Catalog = AdventureWorks2014; User ID = 帳號; Password = 密"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure';
$cmd.CommandText = "TestPS1"
$sqlParameter = new-object System.Data.SqlClient.SqlParameter;
$sqlParameter.ParameterName = "@return";
$sqlParameter.Direction = [System.Data.ParameterDirection]'Output';
$sqlParameter.DbType = [System.Data.DbType]'String';
$sqlParameter.Size = 100;
$cmd.Parameters.Add($sqlParameter) >> $null
$conn.Open();
$result = $cmd.ExecuteNonQuery();
$r = $cmd.Parameters["@return"].Value;
$r;
}
Finally
{
$conn.Close()
}
貼到powershell ISE或者串成.ps1
執行結果: Hello World!
小結:
- Powershell can do EVERYTHING!
- 不過用的人相較少,是一種罕見。
參考
Windows PowerShell: Doing Databases with PowerShell