2atSql

1.0.263

2atSql.psm1

                                #Requires -Version 4.0 -Modules 2atGeneral

$PSDefaultParameterValues.Clear()

Set-StrictMode -Version 2.0

Import-Module 2atGeneral

Function execSql {

    [CmdletBinding()]

    param (

        [string]

        $ConnectionString,

        [Parameter(ValueFromPipeline)]

        [System.Data.SqlClient.SqlConnection]

        $SqlConnection,

        [Parameter(Position=0)]

        [ValidateNotNullorEmpty()]

        [string]

        $CommandText,

        [bool]

        $CommandIsQuery,

        [Parameter(Position=1)]

        [hashtable]

        $Parameters=@{},

        [scriptblock]

        $Script,

        [int]

        $Timeout

    )

    Write-Verbose "Executing SQL: $CommandText"

    if ($SqlConnection -and ![string]::IsNullOrEmpty($ConnectionString)) { Write-Warning "Both SqlConnection and ConnectionString set. Using SqlConnection and ignoring ConnectionString value." }

    if (!$SqlConnection)

    {

        if ([string]::IsNullOrEmpty($ConnectionString)) { throw "Either supply a SqlConnection object or a ConnectionString" }

        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

        $disposeConnection = $true

    }

    $SqlCmd = $SqlConnection.CreateCommand()

    $SqlCmd.CommandText = $CommandText

    if ($Timeout -ne -1) { $SqlCmd.CommandTimeout = $Timeout }

    if (!$CommandIsQuery) { $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure' }

    foreach($p in $parameters.Keys)

    {

        if ($parameters[$p] -ne $null) { $v = $Parameters[$p] } else { $v = [DBNull]::Value } 

        Write-Verbose "  $p = $v"

        [void]$SqlCmd.Parameters.AddWithValue("@$p",$v)

    }

    if ($SqlConnection.State -eq [System.Data.ConnectionState]::Closed) { $SqlConnection.Open() }

    $script.Invoke($SqlCmd)

    $SqlCmd.Dispose()

    if ($disposeConnection) { $SqlConnection.Dispose() }

}

Function Get-SqlData {

    [CmdletBinding()]

    param (

        [string]

        $ConnectionString,

        [Parameter(ValueFromPipeline)]

        [System.Data.SqlClient.SqlConnection]

        $SqlConnection,

        [Parameter(Position=0)]

        [ValidateNotNullorEmpty()]

        [string]

        $CommandText,

        [switch]

        $CommandIsQuery,

        [Parameter(Position=1)]

        [hashtable]

        $Parameters=@{},

        [int]

        $Timeout=-1

    )

    Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    execSql -ConnectionString $ConnectionString -SqlConnection $SqlConnection -CommandText $CommandText -CommandIsQuery $CommandIsQuery -Timeout $Timeout -Parameters $Parameters -Script {

        param ( [System.Data.SqlClient.SqlCommand]$SqlCmd )

        $dt=New-Object system.Data.DataTable

        [void](New-Object system.Data.SqlClient.SqlDataAdapter($SqlCmd)).fill($dt)

        $dt

    }

}

Function Set-SqlData {

    [CmdletBinding()]

    param (

        [string]

        $ConnectionString,

        [Parameter(ValueFromPipeline)]

        [System.Data.SqlClient.SqlConnection]

        $SqlConnection,

        [Parameter(Position=0)]

        [ValidateNotNullorEmpty()]

        [string]

        $CommandText,

        [switch]

        $CommandIsQuery,

        [Parameter(Position=1)]        

        [hashtable]

        $Parameters=@{},

        [int]

        $Timeout=-1

    )

    Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    execSql -ConnectionString $ConnectionString -SqlConnection $SqlConnection -CommandText $CommandText -CommandIsQuery $CommandIsQuery -Timeout $Timeout -Parameters $Parameters -Script {

        param ( [System.Data.SqlClient.SqlCommand]$SqlCmd )

        [void]$SqlCmd.ExecuteNonQuery()

    }

}

Export-ModuleMember -Function Get-*

Export-ModuleMember -Function Set-*

# SIG # Begin signature block

# MIIWbwYJKoZIhvcNAQcCoIIWYDCCFlwCAQExDzANBglghkgBZQMEAgEFADB5Bgor

# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG

# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCAQAHPEHsFva1X7

# Cf/iUGoZN0F5e3uk25XNDyWtlZEqOKCCCxswggUzMIIEG6ADAgECAhEAgNHe/U3D

# BzyckFGAgIDcJDANBgkqhkiG9w0BAQsFADB9MQswCQYDVQQGEwJHQjEbMBkGA1UE

# CBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQK

# ExFDT01PRE8gQ0EgTGltaXRlZDEjMCEGA1UEAxMaQ09NT0RPIFJTQSBDb2RlIFNp

# Z25pbmcgQ0EwHhcNMTcwMTEzMDAwMDAwWhcNMjAwMTEzMjM1OTU5WjCBgDELMAkG

# A1UEBhMCTkwxEDAOBgNVBBEMBzM1NDIgRFoxEDAOBgNVBAgMB1V0cmVjaHQxEDAO

# BgNVBAcMB1V0cmVjaHQxFTATBgNVBAkMDEVuZXJnaWV3ZWcgMTERMA8GA1UECgwI

# MkFUIEIuVi4xETAPBgNVBAMMCDJBVCBCLlYuMIIBIjANBgkqhkiG9w0BAQEFAAOC

# AQ8AMIIBCgKCAQEAzB3KZ2CBenaD2WDwOsy0cHE6mLIeIYqWP718FuWeUZ5eejvw

# 8BozajbtBWgISZ2IMsTYZ1I7KFBzHgXXkNglmyboa6++x7j2Ws+T0hmHCUZ64AFb

# OkXjqYsOBCPhi3yuKIRLwc4snA3F3DCH24mBpDYymrU22+0vMIlDqpzRXBNEeIhG

# ss3jehu86l85fWVS54F5KGeDYQ2BT0Tc0UO6hMlcpCEVKIbthLm36q1/oSchRYjH

# B4JCT1KqACRhD0hJcQmTcJZvhpgOrglUVlj1ClS5xfWgHq3ySShOOZMecl0VNMtY

# xNi5TF1Ae+sie4044ioyGB6dGItGXwhObIk/9wIDAQABo4IBqDCCAaQwHwYDVR0j

# BBgwFoAUKZFg/4pN+uv5pmq4z/nmS71JzhIwHQYDVR0OBBYEFDHc2o80OMg8zNfF

# WMH8QB57E7rnMA4GA1UdDwEB/wQEAwIHgDAMBgNVHRMBAf8EAjAAMBMGA1UdJQQM

# MAoGCCsGAQUFBwMDMBEGCWCGSAGG+EIBAQQEAwIEEDBGBgNVHSAEPzA9MDsGDCsG

# AQQBsjEBAgEDAjArMCkGCCsGAQUFBwIBFh1odHRwczovL3NlY3VyZS5jb21vZG8u

# bmV0L0NQUzBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3JsLmNvbW9kb2NhLmNv

# bS9DT01PRE9SU0FDb2RlU2lnbmluZ0NBLmNybDB0BggrBgEFBQcBAQRoMGYwPgYI

# KwYBBQUHMAKGMmh0dHA6Ly9jcnQuY29tb2RvY2EuY29tL0NPTU9ET1JTQUNvZGVT

# aWduaW5nQ0EuY3J0MCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5jb21vZG9jYS5j

# b20wGQYDVR0RBBIwEIEOc3VwcG9ydEAyYXQubmwwDQYJKoZIhvcNAQELBQADggEB

# AHGDJyOKLJwzdt4Y8ow7H4ZKZXs9Hopf0GhizzhcPWyWL7GI6QHhKHzFWYGsFhh2

# vesuY7p89jthK5YqSn1u2KUQuLWzQZQj3cZCK2BwSz6FpgmmjqIo49qCfKIB5IrE

# DcZAQPC9wxaXPI+R3B32JmTllBpkFQNTIJVcB7jR/Ft991iV17tMMq0GssMAHnVd

# /yvTWlUaE7XNtgtNYQ5v/8HxxNtdBXsIbdjiv/A8GjUmyPN8Dum9CW82hUqOE7U9

# AXHZIBWy9yrooSieo26GA1OzrBvnDc+L42JZnjvwdhBqSnbQrSS7L6VjVHU+Ct84

# Fnb5u23Jypdmj9123Hw9qJwwggXgMIIDyKADAgECAhAufIfMDpNKUv6U/Ry3zTSv

# MA0GCSqGSIb3DQEBDAUAMIGFMQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRl

# ciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQKExFDT01PRE8g

# Q0EgTGltaXRlZDErMCkGA1UEAxMiQ09NT0RPIFJTQSBDZXJ0aWZpY2F0aW9uIEF1

# dGhvcml0eTAeFw0xMzA1MDkwMDAwMDBaFw0yODA1MDgyMzU5NTlaMH0xCzAJBgNV

# BAYTAkdCMRswGQYDVQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIxEDAOBgNVBAcTB1Nh

# bGZvcmQxGjAYBgNVBAoTEUNPTU9ETyBDQSBMaW1pdGVkMSMwIQYDVQQDExpDT01P

# RE8gUlNBIENvZGUgU2lnbmluZyBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC

# AQoCggEBAKaYkGN3kTR/itHd6WcxEevMHv0xHbO5Ylc/k7xb458eJDIRJ2u8UZGn

# z56eJbNfgagYDx0eIDAO+2F7hgmz4/2iaJ0cLJ2/cuPkdaDlNSOOyYruGgxkx9hC

# oXu1UgNLOrCOI0tLY+AilDd71XmQChQYUSzm/sES8Bw/YWEKjKLc9sMwqs0oGHVI

# wXlaCM27jFWM99R2kDozRlBzmFz0hUprD4DdXta9/akvwCX1+XjXjV8QwkRVPJA8

# MUbLcK4HqQrjr8EBb5AaI+JfONvGCF1Hs4NB8C4ANxS5Eqp5klLNhw972GIppH4w

# vRu1jHK0SPLj6CH5XkxieYsCBp9/1QsCAwEAAaOCAVEwggFNMB8GA1UdIwQYMBaA

# FLuvfgI9+qbxPISOre44mOzZMjLUMB0GA1UdDgQWBBQpkWD/ik366/mmarjP+eZL

# vUnOEjAOBgNVHQ8BAf8EBAMCAYYwEgYDVR0TAQH/BAgwBgEB/wIBADATBgNVHSUE

# DDAKBggrBgEFBQcDAzARBgNVHSAECjAIMAYGBFUdIAAwTAYDVR0fBEUwQzBBoD+g

# PYY7aHR0cDovL2NybC5jb21vZG9jYS5jb20vQ09NT0RPUlNBQ2VydGlmaWNhdGlv

# bkF1dGhvcml0eS5jcmwwcQYIKwYBBQUHAQEEZTBjMDsGCCsGAQUFBzAChi9odHRw

# Oi8vY3J0LmNvbW9kb2NhLmNvbS9DT01PRE9SU0FBZGRUcnVzdENBLmNydDAkBggr

# BgEFBQcwAYYYaHR0cDovL29jc3AuY29tb2RvY2EuY29tMA0GCSqGSIb3DQEBDAUA

# A4ICAQACPwI5w+74yjuJ3gxtTbHxTpJPr8I4LATMxWMRqwljr6ui1wI/zG8Zwz3W

# GgiU/yXYqYinKxAa4JuxByIaURw61OHpCb/mJHSvHnsWMW4j71RRLVIC4nUIBUzx

# t1HhUQDGh/Zs7hBEdldq8d9YayGqSdR8N069/7Z1VEAYNldnEc1PAuT+89r8dRfb

# 7Lf3ZQkjSR9DV4PqfiB3YchN8rtlTaj3hUUHr3ppJ2WQKUCL33s6UTmMqB9wea1t

# QiCizwxsA4xMzXMHlOdajjoEuqKhfB/LYzoVp9QVG6dSRzKp9L9kR9GqH1NOMjBz

# wm+3eIKdXP9Gu2siHYgL+BuqNKb8jPXdf2WMjDFXMdA27Eehz8uLqO8cGFjFBnfK

# S5tRr0wISnqP4qNS4o6OzCbkstjlOMKo7caBnDVrqVhhSgqXtEtCtlWdvpnncG1Z

# +G0qDH8ZYF8MmohsMKxSCZAWG/8rndvQIMqJ6ih+Mo4Z33tIMx7XZfiuyfiDFJN2

# fWTQjs6+NX3/cjFNn569HmwvqI8MBlD7jCezdsn05tfDNOKMhyGGYf6/VXThIXcD

# Cmhsu+TJqebPWSXrfOxFDnlmaOgizbjvmIVNlhE8CYrQf7woKBP7aspUjZJczcJl

# mAaezkhb1LU3k0ZBfAfdz/pD77pnYf99SeC7MH1cgOPmFjlLpzGCCqowggqmAgEB

# MIGSMH0xCzAJBgNVBAYTAkdCMRswGQYDVQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIx

# EDAOBgNVBAcTB1NhbGZvcmQxGjAYBgNVBAoTEUNPTU9ETyBDQSBMaW1pdGVkMSMw

# IQYDVQQDExpDT01PRE8gUlNBIENvZGUgU2lnbmluZyBDQQIRAIDR3v1Nwwc8nJBR

# gICA3CQwDQYJYIZIAWUDBAIBBQCgfDAQBgorBgEEAYI3AgEMMQIwADAZBgkqhkiG

# 9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgorBgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIB

# FTAvBgkqhkiG9w0BCQQxIgQg6bBf8fGjSMY9St+lY+A0/Mnj/BW4EWLjnqmnurEg

# bwYwDQYJKoZIhvcNAQEBBQAEggEADtlGBmwSGFAQJnIuacYAhaV9gau7ShWOI4uU

# OXURK0zEQ5c7HlCyK4s+2BEwD0q665l2xSWvumQSU5GBx6i3l7QtpnEsMsQU7GwS

# dBCENsiMHrWUAaNRSIOTBJC1/dokdiDW65nfywTFifq27piEAgGckHGjL93zYlU6

# 2TsFA1ae5yaPjb6FiJtOpDGOPeAt9oV09aVmTfGev5d+zttbyplP/kOfVMq4Y1PW

# ZbBC0mQKd8UkN4Avcb4v2MxLtF5sGsOWAWa+MZ13cwfHIYl9kT/N4Svs8PCUtzTq

# Z3Mf1DZJ+Q+D0E+qzO/TgCOXWWS1eTVyByml+pyLqr1mdE4smaGCCGowgghmBgor

# BgEEAYI3AwMBMYIIVjCCCFIGCSqGSIb3DQEHAqCCCEMwggg/AgEDMQ8wDQYJYIZI

# AWUDBAIBBQAwggEOBgsqhkiG9w0BCRABBKCB/gSB+zCB+AIBAQYKKwYBBAGyMQIB

# ATAxMA0GCWCGSAFlAwQCAQUABCAYgRgK2SRzFO3iqdJc16qw5AK+7SE6YIF0IJfx

# my51rwIUE/xXEi8b3P5aRV+ITIRykvsOuOoYDzIwMTgwMzE2MDgyODEwWqCBjKSB

# iTCBhjELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQ

# MA4GA1UEBxMHU2FsZm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxLDAq

# BgNVBAMTI0NPTU9ETyBTSEEtMjU2IFRpbWUgU3RhbXBpbmcgU2lnbmVyoIIEoDCC

# BJwwggOEoAMCAQICEE6wh4/MJDU2stjJ9785VXcwDQYJKoZIhvcNAQELBQAwgZUx

# CzAJBgNVBAYTAlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENp

# dHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0

# cDovL3d3dy51c2VydHJ1c3QuY29tMR0wGwYDVQQDExRVVE4tVVNFUkZpcnN0LU9i

# amVjdDAeFw0xNTEyMzEwMDAwMDBaFw0xOTA3MDkxODQwMzZaMIGGMQswCQYDVQQG

# EwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxm

# b3JkMRowGAYDVQQKExFDT01PRE8gQ0EgTGltaXRlZDEsMCoGA1UEAxMjQ09NT0RP

# IFNIQS0yNTYgVGltZSBTdGFtcGluZyBTaWduZXIwggEiMA0GCSqGSIb3DQEBAQUA

# A4IBDwAwggEKAoIBAQDOvHS3cIBPXvM/mKouy9QSASM1aQsivOb9CWwo5BMSrLu6

# LeXV3SLuc7Ys+NKkcedJJXirJbeQEKCbi3cm3UDqQaP9iM1ypok7UFcceiUkIgJR

# QDVnijFpDeU5c0k5m5UBhVLyKxSJmk4EpLxArjmm3UAC4Dp1/j19VZRb8U4kfMi4

# WBnKwNq+WBOa5hzn0cE78F2PSQghntDzvtbUZk9ccjZ7w4LTmAiUr6tETxjHFNoW

# sR4yDhI4wLU8dux1UAAgBBEZ7cb/307+CIEnMU9xdG4DDHAngVVqmkOSpH/b/T/F

# Fx5Bu87op3+Mlfn9f/hhiIkAPv8LAdv91bWk5JERAgMBAAGjgfQwgfEwHwYDVR0j

# BBgwFoAU2u1kdBScFDyr3ZmpvVsoTYs8ydgwHQYDVR0OBBYEFH2/kdenbFpHZkR7

# kNSOkHJBjxfCMA4GA1UdDwEB/wQEAwIGwDAMBgNVHRMBAf8EAjAAMBYGA1UdJQEB

# /wQMMAoGCCsGAQUFBwMIMEIGA1UdHwQ7MDkwN6A1oDOGMWh0dHA6Ly9jcmwudXNl

# cnRydXN0LmNvbS9VVE4tVVNFUkZpcnN0LU9iamVjdC5jcmwwNQYIKwYBBQUHAQEE

# KTAnMCUGCCsGAQUFBzABhhlodHRwOi8vb2NzcC51c2VydHJ1c3QuY29tMA0GCSqG

# SIb3DQEBCwUAA4IBAQBQsPXfX60z3MNTWFi8whN1eyAdVMq6P1A/uor0awljwFtd

# i9Z1GnO9i/9H8RXcURYjGTLmbpJN0cYuWh6IQhTJcuXXCFCKavVkQFauJONhlxVC

# 8CxIroPmNTyLW8KPro7MNFI04Pv+yv2xJGjRpBEjEAb9ssIkJ8fX6Uocjz8+z+3r

# dXlsjl/3IbZQ5iWhzWaUEmy/27Ouh9hoA3IgAsJ+2pTzcgc8V+hVJOcFoB3EgQGC

# Sx8/D50zm/BPzJ3WhYHPy+f9SumSuPcNcnMt6Xf5b48oej4evQiG3I0eEV/3W7uH

# dsaeTFRh0Gfbk4TaMYcDkuef4+nPWlbIaOBSSZRcMYICcTCCAm0CAQEwgaowgZUx

# CzAJBgNVBAYTAlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENp

# dHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0

# cDovL3d3dy51c2VydHJ1c3QuY29tMR0wGwYDVQQDExRVVE4tVVNFUkZpcnN0LU9i

# amVjdAIQTrCHj8wkNTay2Mn3vzlVdzANBglghkgBZQMEAgEFAKCBmDAaBgkqhkiG

# 9w0BCQMxDQYLKoZIhvcNAQkQAQQwHAYJKoZIhvcNAQkFMQ8XDTE4MDMxNjA4Mjgx

# MFowKwYLKoZIhvcNAQkQAgwxHDAaMBgwFgQUNlJ9T6JqaPnrRZbx2Zq7LA6nbfow

# LwYJKoZIhvcNAQkEMSIEILX+YZL6Z1WkkBOXnrp4JaObwIzGgMLxHrbhy+zzpS54

# MA0GCSqGSIb3DQEBAQUABIIBAHAYVCMb05CDRVltWjk1HSXnTED/bBFjvKfqMY7u

# /t0o8fCxF8ky9QZ9iV6OF9zVk/6YzYTJcDKVO8YOp4SmNzKGzNX4MR5/o9h+zGH4

# i/IVS3v5Wy/fOayD9py1WefnCok75DzZrtCrHDq+qLYgHtPl73FFvYiqEPkodyWR

# mtFhuEXsBqXY1R8yLq8No/SVj0WNNkaejMi97qBbjODa4PlDxEBe8MWzTJKfBRyU

# PM5csDLvoC6ndkdBEp4nC67nSLG4Dx0fL3jXdnkTkVObyn9pPeheHOBaqG20x5NM

# W9pkIwJsfV7o4hbS0yRqYVNLkDyyC1jcHUsRHAW63dY0ksw=

# SIG # End signature block