PowerShell 连接 SQL SERVER/MySQL 数据库进行操作

 

#配置信息
$Database	= 'DemoDB'
$Server		= '"WIN-AHAU9NO5R6U\DOG"'
$UserName	= 'kk'
$Password	= '123456'

#创建连接对象
$SqlConn = New-Object System.Data.SqlClient.SqlConnection

#使用账号连接MSSQL
$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"

#或者以 windows 认证连接 MSSQL
#$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security=SSPI;"

#打开数据库连接
$SqlConn.open()

#执行语句方法一
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.connection = $SqlConn
$SqlCmd.commandtext = 'delete top(1) from dbo.B'
$SqlCmd.executenonquery()

#执行语句方法二
$SqlCmd = $SqlConn.CreateCommand()
$SqlCmd.commandtext = 'delete top(1) from dbo.B'
$SqlCmd.ExecuteScalar()

#方法三，查询显示
$SqlCmd.commandtext = 'select name,recovery_model_desc,log_reuse_wait_desc from sys.databases'
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$set = New-Object data.dataset
$SqlAdapter.Fill($set)
$set.Tables[0] | Format-Table -Auto 

#关闭数据库连接
$SqlConn.close()

 

Function GetSqlConnection{
	Param(
		[Parameter(position = 0 , Mandatory = $true)][string]$SQLIP,
		[Parameter(position = 1 , Mandatory = $true)][string]$SQLPort,
		[Parameter(position = 2 , Mandatory = $true)][string]$SQUser,
		[Parameter(position = 3 , Mandatory = $true)][string]$SQLPwd,
		[Parameter(position = 4 , Mandatory = $true)][string]$Database
	)
	$ConnString = "Data Source=$($SQLIP),$($SQLPort);Initial Catalog=$($Database);User ID=$($SQUser);Password=$($SQLPwd)"
	Try{
		$SqlConn = New-Object System.Data.SqlClient.SqlConnection $ConnString
		$SqlConn.Open()
		Return $SqlConn
	}
	Catch{
		Write-Warning $_
		Return $Null
	}
}

Function ExecuteSQL{
    Param(
        [Parameter(position = 0 , Mandatory = $true)]$SqlConn,
        [Parameter(position = 1 , Mandatory = $true)][string]$SqlString
    )
    Try{
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlString
        $SqlCmd.Connection = $SqlConn
        $Reader= $SqlCmd.ExecuteReader()
        $DataTable = New-Object System.Data.DataTable
        $DataTable.Load($Reader)
    }
    Catch {
        Write-Warning $_
		Return $Null
    }
    Return $DataTable
}

Function CloseSqlConnection{
    Param(
        [Parameter(position = 0 , Mandatory = $true)]$SqlConn
    )
	If ($sqlConn.State -eq 'Open'){
		$SqlConn.Close()
	}
}


#示例
$SqlString = "select top 5 name from sys.databases "
$SqlString2 = "select top 5 name from sys.objects "

$SqlConn = GetSqlConnection $SQLIP $SQLPort $SQUser $SQLPwd "master"
$Data = ExecuteSQL $SqlConn $SqlString
$Data2 = ExecuteSQL $SqlConn $SqlString2
CloseSqlConnection $SqlConn

Write-Host ($Data).ItemArray
Write-Host ($Data2).ItemArray

 

MySQL 连接操作：

[string]$S_MyLHost = '192.168.1.110'
[string]$S_MyDB    = 'dbname'
[string]$S_MyUser  = 'myuser'
[string]$S_MyPWD   = 'mypwd'

[string]$T_MyLHost = '192.168.1.111'
[string]$T_MyDB    = 'dbname_new'
[string]$T_MyUser  = 'myuser'
[string]$T_MyPWD   = 'mypwd'

[string]$S_ConnStr = "server=$($S_MyLHost);port=3306;uid=$($S_MyUser);pwd=$($S_MyPWD);database=$($S_MyDB);SslMode=None;"
[string]$T_ConnStr = "server=$($T_MyLHost);port=3306;uid=$($T_MyUser);pwd=$($T_MyPWD);database=$($T_MyDB);SslMode=None;"
#[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
#[void][system.reflection.Assembly]::LoadFrom("C:\Windows\System32\MySql.Data.dll")

Function GetSqlConnection(){
  param(
    [string]$ConnectionString=$null
  )
  try{
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection($ConnectionString)
    $Connection.Open()
  }
  catch{
    Write-Warning $_
    Write-Warning $Error[0].Exception.InnerException
    return $null
  }
  return $Connection
}

Function ExecuteQuery(){
  param(
    [string]$ConnectionString=$null, 
    [string]$SqlString=$null
  )
  $Connection = GetSqlConnection($ConnectionString)
  if($Connection) {
    try{
      $Command = New-Object MySql.Data.MySqlClient.MySqlCommand
      $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
      $DataSet = New-Object System.Data.DataSet
      $Command.Connection=$Connection
      $Command.CommandText=$SqlString
      $DataAdapter.SelectCommand=$Command
      $NumberOfDataSets=$DataAdapter.Fill($DataSet, "data")
    }
    catch {
      Write-Warning $_
      return $null
    }
    Finally {
      $Connection.close()
    } 
  }
  return $DataSet
}

#未使用
Function ExecuteNonQuery(){
  param(
    [string]$ConnectionString=$null, 
    [string]$SqlString=$null
  )
  $Connection = GetSqlConnection($ConnectionString)
  if($Connection) {
    try{
      $Command = New-Object MySql.Data.MySqlClient.MySqlCommand
      $Command.Connection = $Connection
      $Command.CommandText = $SqlString
      $RowsAffected=$Command.ExecuteNonQuery()
      write-host $RowsAffected
    }
    catch {
      Write-Warning $_
      return $false
    }
    Finally {
      $Connection.close()
    }
  }
  return $true
}


Function ExecuteNonQuery_change_info(){
  param(
    [string]$S_ConnStr=$null,
    [string]$T_ConnStr=$null
  )

  [string]$S_Sql="SELECT `id`, `name` FROM tab;"
  [string]$T_Sql="INSERT INTO tab_test(`id`, `name`)VALUES(@id, @name)"
  
  $Connection = GetSqlConnection($T_ConnStr)
  $DataSet = ExecuteQuery $S_ConnStr $S_Sql

  if($Connection) {
    try{
      foreach($Row in $DataSet.tables[0]){
        $Command = New-Object MySql.Data.MySqlClient.MySqlCommand
        $Command.Connection = $Connection
        $Command.CommandText = $T_Sql
        $Command.Parameters.AddWithValue("@id",     $Row.id)     | Out-Null
        $Command.Parameters.AddWithValue("@name",   $Row.name)   | Out-Null
        $RowsAffected=$Command.ExecuteNonQuery()
      }
    }
    catch {
      Write-Warning $_
      return $false
    }
    Finally {
      $Connection.close()
    }
  }
  return $true
}

ExecuteNonQuery_change_info $S_ConnStr $T_ConnStr

 

 

 

参考： Windows PowerShell：使用 PowerShell 处理数据库