PowerShell 连接SQL Server 数据库

PowerShell 连接SQL Server 数据库 2

2014年11月27日  在 Powershell小技巧  标签 SQLServer 来自 Mooser Lee

---

 

PowerShell 通过ADO.NET连接SQL Server数据库，并执行SQL脚本。工作中整理的一小段脚本，后来没有用上，先记录在这里：

1. 建立数据库连接
2. 查询返回一个DataTatble对象
3. 执行一条SQL语句
4. 通过事物执行多条SQL语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93

# # 建立数据库连接. # function New-SqlConnection([string]$connectionStr) {     $SqlConnection = New-Object System.Data.SqlClient.SqlConnection     $SqlConnection.ConnectionString = $connectionStr     try{         $SqlConnection.Open()         Write-Host 'Connected to sql server.'         return $SqlConnection     }     catch [exception] {         Write-Warning ('Connect to database failed with error message:{0}' -f ,$_)         $SqlConnection.Dispose()         return $null     } }   # # 查询返回一个DataTable对象 # function Get-SqlDataTable {     param     (     [System.Data.SqlClient.SqlConnection]$SqlConnection,     [string]$query     )     $dataSet = new-object "System.Data.DataSet" "WrestlersDataset"     $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query,$SqlConnection)     $dataAdapter.Fill($dataSet) | Out-Null     return $dataSet.Tables | select -First 1 }   # # 执行一条SQL命令 # function Execute-SqlCommandNonQuery {     param     (     [System.Data.SqlClient.SqlConnection]$SqlConnection,     [string]$Command     )     $cmd = $SqlConnection.CreateCommand()     try     {         $cmd.CommandText = $Command         $cmd.ExecuteNonQuery() | Out-Null         return $true     }     catch [Exception] {          Write-Warning ('Execute Sql command failed with error message:{0}' -f $_)          return $false     }     finally{         $SqlConnection.Close()     } }   # # 通过事物处理执行多条SQL命令 # function Execute-SqlCommandsNonQuery {     param     (     [System.Data.SqlClient.SqlConnection]$SqlConnection,     [string[]]$Commands     )     $transaction = $SqlConnection.BeginTransaction()     $command = $SqlConnection.CreateCommand()     $command.Transaction = $transaction     try     {         foreach($cmd in $Commands) {             #Write-Host  $cmd -ForegroundColor Blue             $command.CommandText = $cmd             $command.ExecuteNonQuery()         }         $transaction.Commit()         return $true     }     catch [Exception] {          $transaction.Rollback()          Write-Warning ('Execute Sql commands failed with error message:{0}' -f $_)          return $false     }     finally{         $SqlConnection.Close()     } }