SQLQueryBuilder.psm1
|
function Format-SQLSelectRowByKeyQuery { <# .SYNOPSIS Returns a MS SQL query to select a row by primary/secondary key formatted as a string. .DESCRIPTION This is intended to be used as a help to build queries for Invoke-SQLCommand, which is part of the SQLServer PowerShell module. .PARAMETER TableName Name of SQL table. .PARAMETER PrimaryKeyName SQL table primary key column name. This does not need to be the actual primary key, just the name of the parameter on which you would like to select. .PARAMETER PrimaryKeyValue Primary key value to match. .PARAMETER SecondaryKeyName SQL table secondary key column name. This does not need to be the actual secondary key, just the name of the secondary parameter on which you would like to select. .PARAMETER SecondaryKeyValue Secondary key value to match. .EXAMPLE This example will return a formatted SQL query to select rows from MYDBTable where MyColumn1 = MyValue1. Format-SQLSelectRowByKeyQuery -TableName 'MYDBTable' -PrimaryKeyName 'MyColumn1' -PrimaryKeyValue 'MyValue1' .EXAMPLE This example will return a formatted SQL query to select rows from MYDBTable where MyColumn1 = MyValue1 and MyColumn2 = MyValue2. Format-SQLSelectRowByKeyQuery -TableName 'MYDBTable' -PrimaryKeyName 'MyColumn1' -PrimaryKeyValue 'MyValue1' -SecondaryKeyName 'MyColumn2' -SecondaryKeyValue 'MyValue2' #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TableName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$PrimaryKeyName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$PrimaryKeyValue, [Parameter(Mandatory=$false)] [string]$SecondaryKeyName, [Parameter(Mandatory=$false)] [string]$SecondaryKeyValue ) Begin { Write-Verbose "Formatting SQL select row by key query..." if ($SecondaryKeyName -and !($SecondaryKeyValue)) { Write-Error "Secondary key name was specified but no secondary key value was provided!" break } } Process { if ($SecondaryKeyName) { Write-Verbose "Secondary key specified." $SelectRowByKeyQuery = "SELECT * FROM $TableName WHERE $PrimaryKeyName = '$PrimaryKeyValue' AND $SecondaryKeyName = '$SecondaryKeyValue'" } else { $SelectRowByKeyQuery = "SELECT * FROM $TableName WHERE $PrimaryKeyName = '$PrimaryKeyValue'" } } End { Write-Verbose "Finished formatting query." Write-Output $SelectRowByKeyQuery } } function Format-SQLInsertRowQuery { <# .SYNOPSIS Returns a MS SQL query to insert a row formatted as a string. .DESCRIPTION This is intended to be used as a help to build queries for Invoke-SQLCommand, which is part of the SQLServer PowerShell module. .PARAMETER TableName Name of SQL table. .PARAMETER DataRow System.Data.DataRow for which to format SQL insert query. .EXAMPLE This example will return a formatted SQL query to insert $row into MYDBTable. Format-SQLInsertRowQuery -TableName 'MYDBTable' -DataRow $row #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TableName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [System.Data.DataRow]$DataRow ) Begin { Write-Verbose "Formatting SQL Insert Row Query..." } Process { [System.Collections.ArrayList]$Columns = @() [System.Collections.ArrayList]$Values = @() foreach ($Property in ($DataRow | Get-Member -MemberType Property | Select-Object Name).Name) { $Columns.Add($(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores)) | Out-Null if ($DataRow.Item($Property).GetType().Name -eq 'DateTime') { $Values.Add($DataRow.Item($Property).GetDateTimeFormats('G')[-3]) | Out-Null } elseif ($DataRow.Item($Property).GetType().Name -eq 'String') { $Values.Add($DataRow.Item($Property).Replace("'","''")) | Out-Null } else { $Values.Add($DataRow.Item($Property)) | Out-Null } } $ColumnString = "($($Columns -join ', '))" $ValueString = $($Values -join "', '") $InsertRowQuery = "INSERT INTO $TableName $ColumnString VALUES ('$ValueString')" } End { Write-Verbose "Finished formatting query." Write-Output $InsertRowQuery } } function Format-SQLUpdateRowByKeyQuery { <# .SYNOPSIS Returns a MS SQL query to update a row by primary/secondary key formatted as a string. .DESCRIPTION This is intended to be used as a help to build queries for Invoke-SQLCommand, which is part of the SQLServer PowerShell module. .PARAMETER TableName Name of SQL table. .PARAMETER PrimaryKeyName SQL table primary key column name. This does not need to be the actual primary key, just the name of the parameter on which you would like to select. .PARAMETER DataRow System.Data.DataRow for which to format SQL insert query. .PARAMETER SecondaryKeyName SQL table secondary key column name. This does not need to be the actual secondary key, just the name of the secondary parameter on which you would like to select. .EXAMPLE This example will return a formatted SQL query to update rows from MYDBTable where MyColumn1 = MyValue1 with the values stored in $row. Format-SQLUpdateRowByKeyQuery -TableName 'MYDBTable' -PrimaryKeyName 'MyColumn1' -DataRow $row .EXAMPLE This example will return a formatted SQL query to update rows from MYDBTable where MyColumn1 = MyValue1 and MyColumn2 = MyValue2 with the values stored in $row. Format-SQLUpdateRowByKeyQuery -TableName 'MYDBTable' -PrimaryKeyName 'MyColumn1' -SecondaryKeyName 'MyColumn2' -DataRow $row #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TableName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$PrimaryKeyName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [System.Data.DataRow]$DataRow, [Parameter(Mandatory=$false)] [string]$SecondaryKeyName ) Begin { Write-Verbose "Formatting SQL update row by key query..." } Process { $ColumnValueString = "" if ($SecondaryKeyName) { Write-Verbose "Secondary key specified." foreach ($Property in ($DataRow | Get-Member -MemberType Property | Select-Object Name).Name) { if ($Property -eq $PrimaryKeyName) { $PrimaryKeyValue = $DataRow.Item($Property) Write-Verbose "Primary key value: $PrimaryKeyValue" } elseif ($Property -eq $SecondaryKeyName) { $SecondaryKeyValue = $DataRow.Item($Property) Write-Verbose "Secondary key value: $SecondaryKeyValue" } else { if ($DataRow.Item($Property).GetType().Name -eq 'DateTime') { $ColumnValueString += " $(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores) = '$($DataRow.Item($Property).GetDateTimeFormats('G')[-3])'," } elseif ($DataRow.Item($Property).GetType().Name -eq 'String') { $ColumnValueString += " $(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores) = '$($DataRow.Item($Property).Replace("'","''"))'," } else { $ColumnValueString += " $(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores) = '$($DataRow.Item($Property))'," } } } } else { foreach ($Property in ($DataRow | Get-Member -MemberType Property | Select-Object Name).Name) { if ($Property -eq $PrimaryKeyName) { $PrimaryKeyValue = $DataRow.Item($Property) Write-Verbose "Primary key value: $PrimaryKeyValue" } else { if ($DataRow.Item($Property).GetType().Name -eq 'DateTime') { $ColumnValueString += " $(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores) = '$($DataRow.Item($Property).GetDateTimeFormats('G')[-3])'," } elseif ($DataRow.Item($Property).GetType().Name -eq 'String') { $ColumnValueString += " $(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores) = '$($DataRow.Item($Property).Replace("'","''"))'," } else { $ColumnValueString += " $(Remove-SpecialCharactersFromString -InputString $Property -ReplaceSpacesWithUnderscores) = '$($DataRow.Item($Property))'," } } } } $SetString = "$($ColumnValueString.TrimEnd(',').TrimStart(' '))" if ($SecondaryKeyName) { $UpdateRowByKeyQuery = "UPDATE $TableName SET $SetString WHERE $PrimaryKeyName = '$PrimaryKeyValue' AND $SecondaryKeyName = '$SecondaryKeyValue'" } else { $UpdateRowByKeyQuery = "UPDATE $TableName SET $SetString WHERE $PrimaryKeyName = '$PrimaryKeyValue'" } } End { Write-Verbose "Finished formatting query." Write-Output $UpdateRowByKeyQuery } } function Format-SQLCreateTableQuery { <# .SYNOPSIS Returns a MS SQL query to create a new table formatted as a string. .DESCRIPTION This is intended to be used as a help to build queries for Invoke-SQLCommand, which is part of the SQLServer PowerShell module. .PARAMETER TableName Name of SQL table to create. .PARAMETER DataTable System.Data.DataTable to use for base table schema. .PARAMETER PrimaryKeyName SQL table primary key column name. .EXAMPLE This example will return a formatted SQL query to create a SQL table names MyNewTable with the same schema as $datatable. Format-SQLCreateTableQuery -TableName "MyNewTable" -DataTable $DataTable .EXAMPLE This example will return a formatted SQL query to create a SQL table names MyNewTable with the same schema as $datatable with a primary key called document_guid. Format-SQLCreateTableQuery -TableName "MyNewTable" -DataTable $DataTable -PrimaryKeyName 'document_guid' #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TableName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [System.Data.DataTable]$DataTable, [Parameter(Mandatory=$false)] [string]$PrimaryKeyName ) Begin { Write-Verbose "Formatting SQL create table query..." } Process { $ColumnsString = "" foreach ($Column in $DataTable.Columns) { if ($Column.MaxLength -eq -1) { $SQLMaxLength = 'Max' } else { $SQLMaxLength = $Column.MaxLength } switch ($Column.DataType.Name) { 'Boolean' {$SQLDataType = 'Bit' } 'Byte[]' {$SQLDataType = 'VarBinary'} 'Byte' {$SQLDataType = 'VarBinary'} 'Datetime' {$SQLDataType = 'DateTime'} 'Decimal' {$SQLDataType = 'Decimal'} 'Double' {$SQLDataType = 'Float'} 'Guid' {$SQLDataType = 'UniqueIdentifier'} 'Int16' {$SQLDataType = 'SmallInt'} 'Int32' {$SQLDataType = 'Int'} #'Int32' #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Numeric} 'Int64' {$SQLDataType = 'BigInt'} #'Int64' #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Numeric} 'UInt16' {$SQLDataType = 'SmallInt'} 'UInt32' {$SQLDataType = 'Int'} 'UInt64' {$SQLDataType = 'BigInt'} 'Single' {$SQLDataType = 'Decimal'} default {$SQLDataType = "VarChar($SQLMaxLength)"} } $ColumnsString += " $(Remove-SpecialCharactersFromString -InputString $column.ColumnName -ReplaceSpacesWithUnderscores) $SQLDataType," } if ($PrimaryKeyName) { Write-Verbose "Primary key specified." $CreateTableQuery = "CREATE TABLE $TableName ($($ColumnsString.TrimStart(' ')) PRIMARY KEY ($PrimaryKeyName))" } else { $CreateTableQuery = "CREATE TABLE $TableName ($($ColumnsString.TrimEnd(',').TrimStart(' ')))" } } End { Write-Verbose "Finished formatting query." Write-Output $CreateTableQuery } } function Format-SQLSelectTableInformationQuery { <# .SYNOPSIS Returns a MS SQL query to get table information formatted as a string. .DESCRIPTION This is intended to be used as a help to build queries for Invoke-SQLCommand, which is part of the SQLServer PowerShell module. .PARAMETER TableName Name of SQL table to search for. .PARAMETER SchemaName Schema to search in for SQL table. .EXAMPLE This example will search for a table named MyTable. Format-SQLSelectTableInformationQuery -TableName "MyTable" .EXAMPLE This example will search for a table named MyTable in the schema dbo. Format-SQLSelectTableInformationQuery -TableName "MyTable" -SchemaName 'dbo' #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TableName, [Parameter(Mandatory=$false)] [string]$SchemaName ) Begin { Write-Verbose "Formatting SQL select table information query..." } Process { if ($SchemaName) { $SelectTableInformationQUery = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$TableName' AND TABLE_SCHEMA = '$SchemaName'" } else { $SelectTableInformationQUery = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$TableName'" } } End { Write-Verbose "Finished formatting query." Write-Output $SelectTableInformationQUery } } function Remove-SpecialCharactersFromString { <# .SYNOPSIS Removes all characters from string that are not a-z or 0-9. .DESCRIPTION Strips all special characters from an input string, returning a clean string. .PARAMETER InputString Value of string to clean. .PARAMETER ReplaceSpacesWithUnderscores If activated, spaces in the input string will be replaced with underscores. Otherwise spaces will be removed. .EXAMPLE This example will remove special characters and spaces from the input string. Remove-SpecialCharactersFromString -InputString "Cool Column Name!?!#@$%^&*()_+\|}{○<>??/€$¥£¢\^$.|?*+()[{0123456789" Output: CoolColumnName_0123456789 .EXAMPLE This example will remove special characters from the input string and replace spaces with underscores. Remove-SpecialCharactersFromString -InputString "Cool Column Name!?!#@$%^&*()_+\|}{○<>??/€$¥£¢\^$.|?*+()[{0123456789" -ReplaceSpacesWithUnderscores Output: Cool_Column_Name_0123456789 #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$InputString, [Parameter(Mandatory=$false)] [switch]$ReplaceSpacesWithUnderscores ) Begin { Write-Verbose "Removing special characters..." } Process { if ($ReplaceSpacesWithUnderscores) { $CleanString = $InputString.replace(' ','_') -replace '[\W]', '' } else { $CleanString = $InputString -replace '[\W]', '' } } End { Write-Verbose "Finished removing special characters." Write-Output $CleanString } } |