【转】PowerShell 连接SQL Server 数据库 - ADO.NET

转至：http://www.pstips.net/connect-sql-database.html

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()      } }

转载于:https://www.cnblogs.com/keepSmile/p/5818554.html