[Powershell]在Powershell中使用T-SQL或呼叫預存程序(Stored Procedure)

2016-11-12

2908
0
Power Shell
2016-11-12

大致的作法是利用.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