internal/functions/get-sqlstring.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

<#
    .SYNOPSIS
        Get an executable string from a SqlCommand object
         
    .DESCRIPTION
        Get an formatted and valid string from a SqlCommand object that contains all variables
         
    .PARAMETER SqlCommand
        The SqlCommand object that you want to retrieve the string from
         
    .EXAMPLE
        PS C:\> $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        PS C:\> $SqlCmd.CommandText = "SELECT * FROM Table WHERE Column = @Parm1"
        PS C:\> $SqlCmd.Parameters.AddWithValue("@Parm1", "1234")
        PS C:\> Get-SqlString -SqlCommand $SqlCmd
         
    .NOTES
        Author: Mötz Jensen (@Splaxi)
         
#>

function Get-SqlString {
    [CmdletBinding()]
    [OutputType('System.String')]
    param (
        [System.Data.SqlClient.SqlCommand] $SqlCommand
    )

    $sbDeclare = [System.Text.StringBuilder]::new()
    $sbAssignment = [System.Text.StringBuilder]::new()
    $sbRes = [System.Text.StringBuilder]::new()

    if ($SqlCommand.CommandType -eq [System.Data.CommandType]::Text) {
        foreach ($parameter in $SqlCommand.Parameters) {
            if ($parameter.Direction -eq [System.Data.ParameterDirection]::Input) {
                $null = $sbDeclare.Append("DECLARE ").Append($parameter.ParameterName).Append("\t")
                $null = $sbDeclare.Append($parameter.SqlDbType.ToString().ToUpper())
                $null = $sbDeclare.AppendLine((Get-SqlParameterSize -SqlParameter $parameter))

                $null = $sbAssignment.Append("SET ").Append($parameter.ParameterName).Append(" = ").AppendLine((Get-SqlParameterValue -SqlParameter $parameter))
            }
        }
        
        $null = $sbRes.AppendLine($sbDeclare.ToString())
        $null = $sbRes.AppendLine($sbAssignment.ToString())
        $null = $sbRes.AppendLine($SqlCommand.CommandText)
    }

    $sbRes.ToString()
}