PowerShell 連接SQLServer資料庫執行DML，DDL，DCL操作

 本位出處：http://www.cnblogs.com/wy123/p/6426261.html 

正如ADO.NET連接至數據執行增刪查改操作一樣，PowerShell 也可以連接SQLServer資料庫，執行性增刪查改功能，除此之外，PowerShell還可以執行DDL和DCL操作，另外就是支持DDL+DML+DCL的事務性操作，也就說PowerShell執行T-SQL的時候，可以將DDL+DML+DCL作為一個事物處理

PowerShell執行DML操作

新增操作

執行完之後來資料庫中確認

修改

刪除

      查詢操作，首先在資料庫中插入兩條記錄

在PowerShell中遍歷讀取表中的記錄，讀取數據的時候，不支持GO，加上GO提交符會報錯? 不太熟悉PowerShell，熟悉的朋友可以指點一下，謝謝。

PowerShell執行DDL和DML操作

 與ADO.NET不同的是PowerShell可以執行DDL和DCL操作，這裡以創建一個表並且授權給Test用戶為示例

cls
$serverInstance="localhost\sqlserver2014A" #SQL Server實例名

$strSql = "
            USE TestDB
            GO
            CREATE TABLE TestDMLByPS
            (
                Id int,
                Name varchar(100)
            )
            GO
            
            GRANT SELECT ON TestDMLByPS TO Test
            GO
           "

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null  #載入程式集Microsoft.SqlServer.ConnectionInfo
$ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance
try
{
    $ServerConnection.Connect()
    $ServerConnection.ExecuteNonQuery($strSql) 
}
Catch
{
    throw
}

　　創建了TestDMLByPS表

　　同時執行的DCL語句也生效了

PowerShell事務性操作

　　將一個DML操作和一個DDL操作放在一個事物中執行，如果DDL創建的表不存在，則兩個SQL可以成功執行

　　將一個DML操作和一個DDL操作放在一個事物中執行，如果DDL創建的已經存在，那麼DDL執行失敗，那麼DML執行的刪除操作也失敗，這一次刪除操作換一個存在的Id，提示異常，事物回滾

　　可以發現，DML執行的刪除也一併回滾，並沒有刪除掉Id=2的記錄

　　與ADO.NET不同的是，PowerShell執行的事物可以包括DML和DDL或者DCL等操作

　　最後附上操作腳本　

cls
$serverInstance="localhost\sqlserver2014A" #SQL Server實例名

$strSql1 = "USE TestDB
           GO
           CREATE TABLE T1(ID INT,VAL VARCHAR(50))
           GO"
$strSql2 = "USE TestDB
           GO
           INSERT INTO T2 VALUES (1,'TestPowerShell')
           GO"
$strSql3 = "USE TestDB
           GO
           UPDATE T2 SET Name = 'Test Update by PowerShell' WHERE ID = 1
           GO"
$strSql4 = "USE TestDB
           
           SELECT * FROM T2
           "

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null  #載入程式集Microsoft.SqlServer.ConnectionInfo
$ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance

try
{
    $ServerConnection.Connect()
    #打開事務
    $ServerConnection.BeginTransaction()
    
    #執行DDL語句
    $ServerConnection.ExecuteNonQuery($strSql1)
    #執行DML語句
    $ServerConnection.ExecuteNonQuery($strSql2)
    #執行DML語句
    $ServerConnection.ExecuteNonQuery($strSql3)
    #執行DML語句，遍歷查詢結果

    $sqlReader = $ServerConnection.ExecuteReader($strSql4)
    $Datatable = New-Object System.Data.DataTable
    $DataTable.Load($SqlReader)
    for ($i=0;$i -le $DataTable.Rows.Count;$i++)
    {
        Write-Host 'value is : ' + $i + ' ' + $DataTable.Rows[$i][0]
        Write-Host 'value is : ' + $i + ' ' + $DataTable.Rows[$i][1]
    }
    
    #提交事務
    $ServerConnection.CommitTransaction()
}
Catch
{
    if ($ServerConnection.TransactionDepth -gt 0)
    {
        $ServerConnection.RollbackTransaction()
    }
    throw
}