五餅二魚工作室

疫情期間也不能去哪裡，剛好看到去年有片 SQL Saturday 的影片，該片作者好幾年都有分享這個題目，因此想說來看一下，有沒有甚麼密技可以挖一下。

首先因為主題是介紹連接 SQL Server 取資料，因此開始用一段很容易的 PowerShell ，但因為作者沒有提供腳本檔案，因此我參考他的影片也做了一個類似的範例

Import-Module SQLPS

$Query = @"
SELECT top 3 * FROM 
[Person].[Person] 
"@

$Result = Invoke-Sqlcmd -ServerInstance ".\SQL2019" -Database "AdventureWorks2019" -Query $Query;

$Result | Format-Table *

上述的一個很通用的範例，透過載入 SQLPS 的模組之後，就可以透過 Invoke-SqlCmd 的指令來取得資料。然後在這個範例當中，作者也用這樣的範例，展示當我們執行 PowerShell 的腳本時候(如下圖 1 )，正常執行完畢之後，腳本內的變數我們就沒有辦法取得了。但是如果當我們在執行的時候，前方多指定一個點(如下圖 2 )，那麼就會讓腳本在目前運行的環境下去執行，這樣當腳本執行結束之後，我們就可以取得該腳本裡面相關變數的值出來用了。

而透過上述的範例，作者同樣又帶出如果我們不使用 PowerShell 的 Invoke-Sqlcmd 的命令的話，雖然 SQL Server 有提供一個 Sqlcmd 的工具程式，允許我們傳入指令去執行，如下同的範例我們使用 sqlcmd 去執行，雖然一樣是可以執行命令，但執行完畢所有的資料會變成一個字串的物件，造成雖然跟前面的腳本類似，都可以正常執行

$Server = ".\SQL2019"
$Database = "AdventureWorks2019"
$Query = @"
SELECT top 3 * FROM 
[Person].[Person] 
"@

$cmd = sqlcmd -S $Server -d $Database -Q $Query

但從下面的結果中我們可以看到，回傳結果會放在一個字串內，就很不方便拿出來處理了。

這樣看起來似乎用 Invoke-Sqlcmd 是比較好的選擇，但這也要看您的運用環境，假設像是下面的範例

Import-Module SQLPS

# Invoke-Sqlcmd not support hierarchyid data type
# OrganizationNode hierarchyid
$Query = @"
SELECT 
    [BusinessEntityID], [NationalIDNumber], [LoginID], [OrganizationNode], [OrganizationLevel], [JobTitle], [BirthDate]
FROM [HumanResources].[Employee]
"@

$Result = Invoke-Sqlcmd -ServerInstance ".\SQL2019" -Database "AdventureWorks2019" -Query $Query;

$Result | Format-Table *

跟原本一開始的範例很類似，只是抓取的資料表和欄位不同，但是很不幸的這樣的狀況下，利用 PowerShell 去執行反而會出錯，而且從訊息中也很難猜得出來是甚麼樣的錯誤，因為 Invoke-Sqlcmd 把相關處理給封裝起來，因此像是一些新的資料型態，是目前 Invoke-Sqlcmd 沒有支援的，那就會出錯了。這一點倒是我之前在練習的時候沒有注意到的狀況，也花了點時間來查到底為什麼失敗。

當然除了使用上述 PowerShell 所封裝好的 cmdlet，或者是使用 SQL Server 的公用程式，都可以去連接資料庫，但 PowerShell 還有個特殊功能，就是可以整合 dot net 的物件，因此如果可以用 dot net 的物件，那我們就可以使用 ado.net 的方式來存取資料。而在作者的範例當中，就整理出一個採用 dot net 的方式來搭配 PowerShell 的處理方式。

在下面的範例當中，我們可以看到利用 new-object 去建立 dot net 的物件來做使用，而這個是採用 DataReader 來取得資料，從效能上來說，利用 DataReader 去取得資料是比較快的方式，但因為 Data Reader 在取資料會用 Array Index 的方式，因此如果欄位較多的時候，或者相關 SQL 欄位順序有變化的時候，利用 Data Reader 比較不好維護。

$Server = ".\SQL2019"
$Database = "AdventureWorks2019"
$Query = @"
SELECT top 3 * FROM 
[Person].[Person] 
"@

function QueryDB1 ($Server = "localhost", $DBName = "master", $Query )
{
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$DBName")
    $cmd = new-object System.Data.SqlClient.SqlCommand($Query, $connection);

    $connection.Open();
    $reader = $cmd.ExecuteReader();

    $result = @();
    while ($reader.Read()) {
        $row = @{ }
        for ($i = 0; $i -lt $reader.FieldCount; $i++)
        {
            $row[$reader.GetName($i)]=$reader.GetValue($i)
        }
        $result += new-object psobject -property $row
    }
    $connection.Close();
    $result
}

$QueryResult = QueryDB1 $Server $Database $Query

$QueryResult | Format-Table *

因為這樣的原因，因此在上述的腳本中，我們利用迴圈將資料先塞入到 row 的物件 , 然後再組合成為一個 row array 的物件，因此透過這樣的處理之後，我們就可以比較容易的去取得和使用資料了。

另外一個比較好的方式，就是利用 Dataset 的物件來做處理，用這個就相對會比 Data Reader 來的方便，也不用自己去將欄位的資料封裝到物件裡面

$Server = ".\SQL2019"
$Database = "AdventureWorks2019"
$Query = @"
SELECT top 3 * FROM 
[Person].[Person] 
"@

function QueryDB2 ($Server = "localhost", $DBName = "master", $Query )
{
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$DBName")
    $cmd = new-object System.Data.SqlClient.SqlCommand($Query, $connection);

    $connection.Open();

    $adapter = new-object System.Data.SqlClient.SqlDataAdapter $cmd
    $dataset = new-object System.Data.Dataset    
    $adapter.Fill($dataset) | Out-Null
    $connection.Close();
    $dataset.Tables[0]
}

$QueryResult = QueryDB2 $Server $Database $Query

$QueryResult | Format-Table *

而透過這樣的方式，從下圖的結果可以看到我們可以很容易地取得 DataRow 的一個陣列，因此要做相關的資料處理就會來的比較容易和方便，雖然效能上來說並沒有辦法像 DataReader 那樣來的快，但相對也比較好維護和使用，因此這也是作者比較推薦的一種方式。

而介紹完取得資料之後，作者又說明當資料有 NULL 狀況的時候要如何特別處理，為了方便展示後面的腳本，因此我在 AdventureWorks 的資料庫下，先用以下的語法建立一個範例資料表。

SELECT [BusinessEntityID]
      ,[PersonType]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailPromotion]
      ,[AdditionalContactInfo]
  INTO [Person].[PersonMock] 	
  FROM [Person].[Person] 
  WHERE 0=1

當有了上面語法所產生的 Person.PersonMock 的資料表之後，下面我們可以透過下方的 PowerShell 腳本，來介紹資料處理的時候對於資料有 NULL 的情況時候，很可能我們在組合 SQL 命令的時候會因此異常或者是變成空字串，因此如果要特別處理 NULL 的時候，要稍微注意一下相關的處理，也要特別注意一下有關於 DBNULL 和 NULL 的不同。

Import-Module SQLPS

$Server = ".\SQL2019"
$Database = "AdventureWorks2019"
$Query = @"
SELECT top 10 * FROM 
[Person].[Person] 
"@

$DBNULL = [System.DBNULL]::Value ;
$connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$Database")
$cmd = new-object System.Data.SqlClient.SqlCommand($Query, $connection);
$connection.Open();
$reader = $cmd.ExecuteReader();
$title = "";
while ($reader.Read()) {
    IF ( ( $reader.GetValue(3) -eq $DBNULL ) -OR ( $reader.GetValue(3) -eq $NULL )){
        $title = "NULL"
    } else {
        $title = "'$($reader.GetValue(3))'"  
    }
 
    $InsertQuery = "insert into [Person].[PersonMock] values('$($reader.GetValue(0))','$($reader.GetValue(1))',"+
        "'$($reader.GetValue(2))',$title,'$($reader.GetValue(4))','$($reader.GetValue(5))',"+
        "'$($reader.GetValue(6))','$($reader.GetValue(7))','$($reader.GetValue(8))','$($reader.GetValue(9))');"
    
    #$InsertQuery | Out-File "D:\Temp\ErrorLog.txt" -Append
    Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $InsertQuery        
}
$connection.Close();

當然在上述的範例中，作者也介紹一個透過字串配合管線( Pipeline ) 的方式，可以很方便地將要除厝的資訊轉出到文字檔內，這算是一個大家較常使用的一種方式，把資料透過 INSERT 指令塞入到資料表內。然而上述這樣一筆一筆的資料透過 Invoke-Sqlcmd 去寫入資料，並不算是一個快速的方式，如果您的資料有非常大量的時候，因為每次執行一個 cmdlet 就算是一個交易，因此整體處理起來就顯得比較耗時。因此作者也提供一個大家比較少去使用的一種方式，利用 ADO.Net 裡面的 BulkCopy 的物件，可以做到類似 SQL Server 的 BCP 工具程式的大量資料匯入，我實際用以下這個範例在我的電腦上，在將近 20000 筆的資料整個處理花不到一秒的時間，因此如果利用 PowerShell 需要將大量的資料來寫入到 SQL Server 的資料庫時候，不妨來試試看以下的範例，將可以大幅縮短資料處理的時間。

Import-Module SQLPS

$Server = ".\SQL2019"
$Database = "AdventureWorks2019"
$Query = @"
SELECT [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
FROM 
[Person].[Person] 
"@

$TargetTable = "[Person].[PersonMock]"

$connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$Database")
$cmd = new-object System.Data.SqlClient.SqlCommand($Query, $connection);
$connection.Open();
$reader = $cmd.ExecuteReader();

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query "TRUNCATE TABLE $TargetTable"

try {
    $RedoConnectionString = "Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$Database"
    $BulkCopy = new-object System.Data.SqlClient.SqlBulkCopy($RedoConnectionString,[System.Data.SqlClient.SqlBulkCopyOptions]::Keepidentity);
    $BulkCopy.DestinationTableName = "[Person].[PersonMock]"
    $BulkCopy.WriteToServer($reader);
}
catch [System.Exception]{
    $ex = $_.Exception;
    Write-Host $ex.Message
}
finally {
    $connection.Close();    
}

上述整段影片差不多一個小時，其中也介紹到一些處理 PowerShell 的小技巧，因此算是一個不錯的入門影片，或許以後有機會多整理一些相關的範例，看起來也有機會當成一個可以分享的題目了。

如果想參考我的範例，可以直接到 github 上面取得相關的腳本 5L2FStudio/PowerShell (github.com)