PowerShell访问Oracle、SQL Server数据库,导出到CSV文件
function Oracle-Query {
param ($conn_info, [String] $SQL, [HashTable] $parameters, [Bool] $scalar)
$USER = $conn_info.USER
$PASSWD = $conn_info.PASSWD
$SERVER = $conn_info.SERVER
$DB = $conn_info.DB
$CONNECTION_STR = "User Id=$USER;Password=$PASSWD;Data Source=$SERVER/$DB"
$connection = New-Object System.Data.OracleClient.OracleConnection($CONNECTION_STR)
$connection.Open()
$ora_cmd = $connection.CreateCommand()
$ora_cmd.CommandText = $SQL
ForEach($key in $parameters.Keys) {
$param = New-Object System.Data.OracleClient.OracleParameter($key, '')
$param.Value = $parameters[$key]
$ora_cmd.Parameters.Add($param) | Out-Null
}
If($scalar) {
$val = $ora_cmd.ExecuteScalar()
$connection.Close()
Return $val
} Else {
$reader = $ora_cmd.ExecuteReader()
$columns = @()
For($i = 0; $i -lt $reader.FieldCount; $i++) {
$columns += $reader.GetName($i)
}
$data = @()
While($reader.Read()) {
$row = [ordered]@{}
$columns | % { $index = 0 } {
If($reader.IsDBNull($index)) {
$row.Add($_, '')
} Else {
$row.Add($_, $reader.GetValue($index))
}
$index += 1
}
$data += $row
}
$connection.Close()
Return $data
}
}
function SQLServer-Query {
param ($conn_info, [String] $SQL, [HashTable] $parameters=$null, [Bool] $scalar=$False)
$USER = $conn_info.USER
$PASSWD = $conn_info.PASSWD
$SERVER = $conn_info.SERVER
$DB = $conn_info.DB
$conn_str = "User Id=$USER;Password=$PASSWD;Database=$DB;Data Source=$SERVER"
$sql_conn = New-Object System.Data.SqlClient.SqlConnection($conn_str)
$sql_conn.Open()
$command = $sql_conn.CreateCommand()
$command.CommandText = $SQL
ForEach($key in $parameters.Keys) {
$parameter = New-Object System.Data.SqlClient.SqlParameter($key, '')
$parameter.Value = $parameters[$key]
$command.Parameters.Add($parameter) | Out-Null
}
If($scalar) {
$val = $command.ExecuteScalar()
$sql_conn.Close()
Return $val
} Else {
$reader = $command.ExecuteReader()
$columns = @()
For($i = 0; $i -lt $reader.FieldCount; $i++) {
$columns += $reader.GetName($i)
}
$data = @()
While($reader.Read()) {
$row = [ordered]@{}
$columns | % { $index = 0 } {
If($reader.IsDBNull($index)) {
$row.Add($_, '')
} Else {
$row.Add($_, $reader.GetValue($index))
}
$index += 1
}
$data += $row
}
$sql_conn.Close()
Return $data
}
}
function HashTable-ToCsv {
param([Array] $data, [String] $filename, [String] $date_format_str='dd/MM/yyyy')
if ($data.Count -le 0) {
Exit
}
if(FileExist "$filename") {
Clear-Content -Path "$filename"
}
$first_row = $data[0]
$first_row.Keys | % {
$header += """$_"""
$header += ','
}
$header = $header -replace ',$', ''
Add-Content -Value "$header" -Path "$filename"
$data | % {
$values = ''
$_.Values | %{
$val = $_
if (($val.GetType()) -eq [System.DateTime]) {
$val = $val.ToString($date_format_str)
}
$values += """$val"""
$values += ','
}
$values = $values -replace ',$', ''
$values = $values -replace '\n', ' '
$values = $values -replace '\r', ' '
$values = $values -replace '\r\n', ' '
Add-Content -Value "$values" -Path "$filename"
}
}