kenger.SQLData.psm1
|
function New-SQLConn { <# .SYNOPSIS Create a new SQL Connection object .DESCRIPTION Returns a new SQL Connection object, than can hold the connection details of a SQL connection, that may be used several times in the same script .PARAMETER Server IP or hostname of the SQL database server you want to connect to. .PARAMETER Database Database name of the database you want to access. .PARAMETER sqlCredentials Username and password for the sql server .OUTPUTS ConnectionObject Object that contains connection the information given, so that they can be reused #> [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')] param ( [Parameter(Mandatory=$True, HelpMessage='What IP or hostname do you want to connect to?')] [string]$server, [Parameter(Mandatory=$true, HelpMessage='What database do you want to access?')] [string]$database, [Parameter( Mandatory=$false, ParameterSetName='Trusted')] [switch]$trustedconnection, [Parameter( Mandatory=$false, HelpMessage='Windows credentials for impersonating', ParameterSetName='Trusted')] [PSCredential]$credentials, [Parameter( Mandatory=$false, HelpMessage='SQL Username and Password', ParameterSetName='NonTrusted')] [PSCredential]$sqlCredentials ) begin { if ( $trustedconnection -eq $false -and $sqlCredentials.UserName -eq '' ) { throw "Missing some credentials parameter, sqlCredentials or use -TrustedConnection switch" } if ( $credentials.UserName -ne '') { # if credentials is present, we assum that they will use trusted connection $trustedconnection = [switch]::Present; } } process { $info = @{}; $info.Server = $server; $info.Database = $database; $info.sqlCredentials = $sqlCredentials; $info.TrustedConnection = $trustedconnection; $info.Credentials = $credentials; if ( $trustedconnection ) { $info.ConnectionString = "server='$($info.Server)';database='$($info.Database)';trusted_connection=true;" }else{ $info.ConnectionString = "server='$($info.Server)';database='$($info.Database)';User Id='$($info.sqlCredentials.Username)';Password='$($info.sqlCredentials.GetNetworkCredential().Password)'" } $conn = New-Object -TypeName PSObject -Prop $info; return $conn; } } function Read-SQLData { <# .SYNOPSIS Read data from SQL database .DESCRIPTION Returns a datatable that holds the selected data .EXAMPLE Read-SQLData -query "SELECT * FROM table" -Conn $conn .PARAMETER Query SQL query .PARAMETER Conn Connection Object #> [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')] param ( [Parameter(Mandatory=$true, HelpMessage='SQL query?')] [string]$query, [Parameter( Mandatory=$true, HelpMessage='Connection object (New-SQLConn)')] [PSObject]$conn ) begin { } Process { Try { $code = { $conn = $args[1] $Connection = New-Object System.Data.SQLClient.SQLConnection $Connection.ConnectionString = $conn.ConnectionString $Connection.Open() $Command = New-Object System.Data.SQLClient.SQLCommand $Command.Connection = $Connection $Command.CommandText = $args[2]; $result = $Command.ExecuteReader() $table = new-object "System.Data.DataTable" $table.Load($result) $Connection.Close() $table; } Write-Verbose "Query: $query"; Write-Verbose "ConnectionString: $($conn.ConnectionString)"; if ( $conn.Credentials -ne $null ) { Write-Verbose "Impersonating $($conn.Credentials.UserName)" $job = Start-Job -Credential $conn.Credentials -ArgumentList( ([environment]::CurrentDirectory=(Resolve-Path .\).Path), $conn, $query) -ScriptBlock $code } else { $job = Start-Job -ArgumentList(([environment]::CurrentDirectory=(Resolve-Path .\).Path), $conn, $query) -ScriptBlock $code } $result = $job | Wait-Job | Receive-Job | select * -ExcludeProperty RunspaceId $job | Remove-Job Write-Verbose "$($result.Rows.Count) records returned"; $result; } Catch { Write-Host -BackgroundColor Red "Error: $($_.Exception)" Break } } End { If ($?) { #Write-Host 'Completed Successfully.' #Write-Host ' ' } } } function Write-SQLData { <# .SYNOPSIS Run SQL query on a MS SQL database .DESCRIPTION Save or update data to a database, or run a SQL command. This command return the number of rows affected, if $false is returned, something when wrong, try -verbose .EXAMPLE Write-SQLData -query "INSERT * FROM table () VALUES ('aaa','bbb')" -Conn $conn .PARAMETER Query SQL query .PARAMETER Conn Connection Object #> [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')] param ( [Parameter(Mandatory=$true, HelpMessage='SQL query?')] [string[]]$query, [Parameter(Mandatory=$true, HelpMessage='Connection object')] [PSObject]$conn ) begin { } process { try { $code = { $conn = $args[1] $Connection = New-Object System.Data.SQLClient.SQLConnection $Connection.ConnectionString = $conn.ConnectionString $Connection.Open() $Command = New-Object System.Data.SQLClient.SQLCommand $Command.Connection = $Connection $Command.CommandText = $args[2]; $result = $Command.ExecuteNonQuery() $Connection.Close() $result } Write-Verbose "ConnectionString: $($conn.ConnectionString)"; Write-Verbose "Query: $query"; if ( $WhatIfPreference ) { Write-Host "Whatif, this query would have been executed: $query" -ForegroundColor Yellow; } $rowCount = 0; if ($WhatIfPreference -eq $false) { if ( $conn.Credentials -ne $null ) { Write-Verbose "Impersonating $($conn.Credentials.UserName)" $job = Start-Job -Credential $conn.Credentials -ArgumentList( ([environment]::CurrentDirectory=(Resolve-Path .\).Path), $conn, $query) -ScriptBlock $code } else { $job = Start-Job -ArgumentList(([environment]::CurrentDirectory=(Resolve-Path .\).Path), $conn, $query) -ScriptBlock $code } $rowCount = $job | Wait-Job | Receive-Job -Keep #$job | Remove-Job } Write-Verbose "Rows Updated: $rowCount" } catch { Write-Host -BackgroundColor Red "Error: $($_.Exception)" return $false; } } end { If ($?) { #return $rowCount; #Write-Host 'Completed Successfully.' #Write-Host ' ' } } } function Write-SQLObject { <# .SYNOPSIS Write / Update data to/in a SQL database .DESCRIPTION Save or update data a table, by providing a object with properties matching to the table columns, this command gives no SQL data as result, only succeded or not .EXAMPLE Write-SQLData -object $obj -Conn $conn .EXAMPLE Write-SQLData $obj -Conn $conn .PARAMETER Object Data Object (PSObject) .PARAMETER table Name of the data table to use .PARAMETER mode Default mode is insert, change if you need to update or delete data. Insert mode is used for inserting a new row into a table. Update mode is used for updating an existing row, remember to provide -objectkeys to tell what row that should be updated, ex. (-updatekeys ID) will tell that the ID columns is unique. Delete mode, is used to delete data from a table. The -objectkeys is also required to tell what row that should be deleted. InsertOrUpdate mode is a dynamic way to wrap the Insert and Update statetent in a TSQL IF statement, so it will check if the -updatekeys is found, and update the row, or if not found, insert as a new row. .PARAMETER objectKeys Used if you are using Update or InsertOrUpdate modes, lets the module know what rows you want to update (like a where clause) .PARAMETER Conn Connection Object .PARAMETER UpdateWithEmptyData Tells the module that you want to insert or update empty data such as '' into the db, ex. in case of empty properties on your data object. .PARAMETER IgnoreColumns List of columns to ignore and leave out from insert or updates, ex. auto incremental primary keys -IgnoreColumns id #> [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')] param ( [Parameter(Mandatory=$true, HelpMessage='Data Object?', ValueFromPipeline=$True)] [PSObject]$object, [Parameter(Mandatory=$true, HelpMessage='Table name?')] [string]$table, [Parameter(Mandatory=$false)] [ValidateSet('Insert', 'Update', 'Delete', 'InsertOrUpdate')] [string]$mode = 'Insert', [Parameter(Mandatory=$false)] [string[]]$objectKeys = $null, [Parameter(Mandatory=$true)] [PSObject]$conn, [Parameter(Mandatory=$false)] [switch]$UpdateWithEmptyData = $false, [Parameter(Mandatory=$false)] [string]$IgnoreColumns = '' ) begin { Write-Verbose "Mode: $mode"; if ( $mode -ne 'Insert' -and $objectKeys -eq $null) { throw "You need to define -objectKeys when using Update or InsertOrUpdate modes" } $rowCount = 0; } process { try { $queries = @(); $object | % { $obj = $_ #[System.Collections.Generic.List[System.Object]]($columns) $columns = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name); $values = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name | % { "'$($obj.$_ -Replace "'","''")'" }); # we need the original key/value sets because the columns and values are filtered, emptydata and ignored columns are removed.. # we still need to use these ignored keys in where clause for updating, ex. by id column... we could create then as a PSObject instead with a ignored flag? (TODO) $whereColumns = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name); $whereValues = [System.Collections.Generic.List[System.Object]]($obj | get-member -MemberType NoteProperty,Properties | select -exp Name | % { "'$($obj.$_ -Replace "'","''")'" }); # handle the UpdateWithEmptyData switch, if not set, dont insert or update with empty data '', remove the column and values from the lists. for( $i=0; $i -lt $columns.ToArray().count; $i++) { if ( ( $UpdateWithEmptyData -ne $true -and $values[$i] -eq "''" ) -or ( $IgnoreColumns -contains $columns[$i] ) ) { $columns.RemoveAt($i); $values.RemoveAt($i); } } # todo ^ handle int or String excape ($obj.ConnectionString.GetType() | select -exp name) == "String" # ^ really no need.. int can be handles as strings # Generate Update and Where string, for use in Update and InsertOrUpdate modes $list = @(); for( $i=0; $i -lt $columns.count; $i++) { $list += "[$($columns[$i])]=$($values[$i])" } $updateString = $list -join ", "; $whereString = ($objectKeys | % { "[$_]=$($whereValues[$whereColumns.IndexOf($_)])"}) -join " AND "; $columns = $columns | % { "[$_]" } switch ($mode) { 'Insert' { $query = "INSERT INTO $table ($( $columns -join ", ")) VALUES ($( $values -join ", "))"; } 'Update' { $query = "UPDATE $table SET $updateString WHERE ($whereString)" } 'InsertOrUpdate' { $query = @" IF EXISTS (SELECT * FROM $table WHERE $whereString) BEGIN UPDATE $table SET $updateString WHERE ($whereString) END ELSE BEGIN INSERT INTO $table ($( $columns -join ", ")) VALUES ($( $values -join ", ")) END "@ } 'Delete' { $query = "DELETE FROM $table WHERE ($whereString)" } } $queries += $query; #$rowCount += Write-SQLData -query $query -conn $conn; } $rowCount = Write-SQLData -query $queries -conn $conn; } catch { Write-Host -BackgroundColor Red "Error: $($_.Exception)" break; } } end { If ($?) { $rowCount #Write-Host 'Completed Successfully.' #Write-Host ' ' } #return $?; } } |