2atSql

1.0.278

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

# MIIWcAYJKoZIhvcNAQcCoIIWYTCCFl0CAQExDzANBglghkgBZQMEAgEFADB5Bgor

# 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/pD77pnYf99SeC7MH1cgOPmFjlLpzGCCqswggqnAgEB

# 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+pyLqr1mdE4smaGCCGswgghnBgor

# BgEEAYI3AwMBMYIIVzCCCFMGCSqGSIb3DQEHAqCCCEQwgghAAgEDMQ8wDQYJYIZI

# AWUDBAIBBQAwggEPBgsqhkiG9w0BCRABBKCB/wSB/DCB+QIBAQYKKwYBBAGyMQIB

# ATAxMA0GCWCGSAFlAwQCAQUABCAYgRgK2SRzFO3iqdJc16qw5AK+7SE6YIF0IJfx

# my51rwIVAJ2+Nj4G0t9q+k3r4kwFGJ8CCvgXGA8yMDE4MDMyMjA4MDU0OFqggYyk

# gYkwgYYxCzAJBgNVBAYTAkdCMRswGQYDVQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIx

# EDAOBgNVBAcTB1NhbGZvcmQxGjAYBgNVBAoTEUNPTU9ETyBDQSBMaW1pdGVkMSww

# KgYDVQQDEyNDT01PRE8gU0hBLTI1NiBUaW1lIFN0YW1waW5nIFNpZ25lcqCCBKAw

# ggScMIIDhKADAgECAhBOsIePzCQ1NrLYyfe/OVV3MA0GCSqGSIb3DQEBCwUAMIGV

# MQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBD

# aXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0

# dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMUVVROLVVTRVJGaXJzdC1P

# YmplY3QwHhcNMTUxMjMxMDAwMDAwWhcNMTkwNzA5MTg0MDM2WjCBhjELMAkGA1UE

# BhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2Fs

# Zm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxLDAqBgNVBAMTI0NPTU9E

# TyBTSEEtMjU2IFRpbWUgU3RhbXBpbmcgU2lnbmVyMIIBIjANBgkqhkiG9w0BAQEF

# AAOCAQ8AMIIBCgKCAQEAzrx0t3CAT17zP5iqLsvUEgEjNWkLIrzm/QlsKOQTEqy7

# ui3l1d0i7nO2LPjSpHHnSSV4qyW3kBCgm4t3Jt1A6kGj/YjNcqaJO1BXHHolJCIC

# UUA1Z4oxaQ3lOXNJOZuVAYVS8isUiZpOBKS8QK45pt1AAuA6df49fVWUW/FOJHzI

# uFgZysDavlgTmuYc59HBO/Bdj0kIIZ7Q877W1GZPXHI2e8OC05gIlK+rRE8YxxTa

# FrEeMg4SOMC1PHbsdVAAIAQRGe3G/99O/giBJzFPcXRuAwxwJ4FVappDkqR/2/0/

# xRceQbvO6Kd/jJX5/X/4YYiJAD7/CwHb/dW1pOSREQIDAQABo4H0MIHxMB8GA1Ud

# IwQYMBaAFNrtZHQUnBQ8q92Zqb1bKE2LPMnYMB0GA1UdDgQWBBR9v5HXp2xaR2ZE

# e5DUjpByQY8XwjAOBgNVHQ8BAf8EBAMCBsAwDAYDVR0TAQH/BAIwADAWBgNVHSUB

# Af8EDDAKBggrBgEFBQcDCDBCBgNVHR8EOzA5MDegNaAzhjFodHRwOi8vY3JsLnVz

# ZXJ0cnVzdC5jb20vVVROLVVTRVJGaXJzdC1PYmplY3QuY3JsMDUGCCsGAQUFBwEB

# BCkwJzAlBggrBgEFBQcwAYYZaHR0cDovL29jc3AudXNlcnRydXN0LmNvbTANBgkq

# hkiG9w0BAQsFAAOCAQEAULD131+tM9zDU1hYvMITdXsgHVTKuj9QP7qK9GsJY8Bb

# XYvWdRpzvYv/R/EV3FEWIxky5m6STdHGLloeiEIUyXLl1whQimr1ZEBWriTjYZcV

# QvAsSK6D5jU8i1vCj66OzDRSNOD7/sr9sSRo0aQRIxAG/bLCJCfH1+lKHI8/Ps/t

# 63V5bI5f9yG2UOYloc1mlBJsv9uzrofYaANyIALCftqU83IHPFfoVSTnBaAdxIEB

# gksfPw+dM5vwT8yd1oWBz8vn/Urpkrj3DXJzLel3+W+PKHo+Hr0IhtyNHhFf91u7

# h3bGnkxUYdBn25OE2jGHA5Lnn+Ppz1pWyGjgUkmUXDGCAnEwggJtAgEBMIGqMIGV

# MQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBD

# aXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0

# dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMUVVROLVVTRVJGaXJzdC1P

# YmplY3QCEE6wh4/MJDU2stjJ9785VXcwDQYJYIZIAWUDBAIBBQCggZgwGgYJKoZI

# hvcNAQkDMQ0GCyqGSIb3DQEJEAEEMBwGCSqGSIb3DQEJBTEPFw0xODAzMjIwODA1

# NDhaMCsGCyqGSIb3DQEJEAIMMRwwGjAYMBYEFDZSfU+iamj560WW8dmauywOp236

# MC8GCSqGSIb3DQEJBDEiBCDdjbfaLRCS+ulDJSl06yxyZ6iC23R/lJuYlR9d1b6p

# pzANBgkqhkiG9w0BAQEFAASCAQBJQZnQU8JK3fz+kg1zSf/5ejSvJwCf6rqYx+Im

# 7O6dDm6fGpyFO56HvRl4/dAZXLjoiYfV69XB6lRaQcrVQpA7DHavVse+Nb2EG6dw

# TuPk1+D1W4fz6Gjwl0Ze4hBIQAZsVEytFs4yuVkCjPRaNSBUKPcpeGyiL+9A5o3/

# N6rW80NvCJ+dK0R3FCTRxCR9/A9K1/HTrKMnZiNmfiVBW10X3gzMkFkQMF/85ozy

# uUnr/722l8L0tfgoRbINFh6Z6Pa8PRslRpnfUESqDPS++A1HHYpgyiG6bmPqruKG

# TPdq+3iLZtkaxqoPxEp4RyW5ixTXwpoUr9h8Rt1nwQpXmYpZ

# SIG # End signature block