PowerShell alapok 3 adatbázis kezelés

cd d:\prg\kl_ps\mssql\ps_ora
. .\ImportExportExcel.ps1
. .\GodotHelper.ps1

##[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
[Reflection.Assembly]::LoadFrom(".\System.Data.OracleClient.dll")
##[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")



$sql = " SELECT * from dual "

function Run_sql([string]$sql) {
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=dbcldbpr.t-mobile.intra)(Port=1526)))(CONNECT_DATA=(SID=cldbpr)));User ID=kecskemetil;Password=Ildiko_07"
$conn = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$conn.Open()
$cmd = new-Object System.Data.OracleClient.OracleCommand($sql, $conn)
$dataAdapter = New-Object System.Data.OracleClient.OracleDataAdapter($cmd);
$dataSet = New-Object System.Data.DataSet                               
$dataAdapter.Fill($dataSet)                                               

$DataSet.Tables[0] | Export-Csv "Eredmeny22.csv" -notype -force -encoding "unicode"
$DataS = $DataSet.Tables[0] | select -First $DataSet.Tables[0].Rows.Count



$Results = [Godot.Results] $DataSet.Tables[0]
Create-Workbook
Add-Worksheet $results "lalal"
Save-Workbook( ".\lajos.xls")


## $DataSet.Tables[0] | ConvertTo-HTML -As Table -head $a –body "<H2>iApp RDC Usage Stats as of $date</H2> " | Out-File ".\kl.html"


# $DataS = $DataSet.Tables[0] | ft -HideTableHeaders

Export-Excel -path .\kl.xls -InputObject $DataS -BoldHeader -force


  #for($i=0;$i -le $ds.Tables[1].Rows.Count;$i++) {
  #  write-host "value is : $i $($ds.Tables[1].Rows[$i][0])"
  #}

  ###Export-Csv -Path users.csv -Delimiter ";" -NoTypeInformation -Encoding UTF8
  # Fájl kódolásának átalakítása UTF-8-ról ISO-8859-2-re
##Convert-Encoding users.csv users.csv UTF-8 ISO-8859-2

$cmd.Dispose()
$conn.Close()

}

##$sql = Get-content .\pi_select.txt
$sql = " SELECT * from szerv_her where szint1 ='BBU' "

$rekordszam = Run_sql($sql)











#$conn= New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
#$conn.Open()
#### Create a datareader for a SQL statement

#$cmd = New-Object Oracle.DataAccess.Client.OracleCommand( $sql, $conn)
#$reader=$cmd.ExecuteReader()





import-module oracleclient
$oc = new-Oracle_connection  -tns orcl -user system -password abc123
$empdata = invoke-oracle_query -sql "Select * from scott.emp" -connection $oc
$Excel = New-Object -com Excel.application
$Excel.Visible=$true
$Workbook= $Excel.Workbooks.add()
$mcells = $Workbook.Worksheets.Item(1).Cells
# kiszedem az eredmény poperty -jeit egy gproperty változóba, ami egy datarow típus
$gproperty = $empdata | get-member -MemberType Property
#valodi ket dimenzios tömböt definiálunk
$valodi_ketdtomb = New-Object "object[,]" $empdata.Count, $gproperty.Count

for($i=0; $i -lt $empdata.count; $i++){
for($j=0; $j -lt $gproperty.count; $j++){
# gproperty datarow első elem és annak a neve
$propname = $gproperty.Get($j).Name
$valodi_ketdtomb[$i,$j] = $empdata.Get($i).$propname
}
}

#$sajattomb = @((3,4,5))
# a range -nek értékul adjuk a valodi ket dimenzios tömbot
$mcells.Range("A1:G14").Value2 = $valodi_ketdtomb