PowerShell 数据库操作辅助脚本

ncqingchuan1976

已于 2022-09-08 23:41:00 修改

阅读量418

点赞数 1

文章标签： etl工程师

于 2022-07-05 21:38:04 首次发布

本文链接：https://blog.csdn.net/ncqingchuan1976/article/details/125628531

版权

该脚本为通用数据库操作,如果加载其他ADO.NET驱动(MYSQL,ORACLE等) 则可运行相关数据库的操作

主脚本

using namespace System.Reflection
using namespace System.Data.Common
using namespace System.Data
function Get-DbConnection {
    param (
        [string]$providerFile,
        [string]$providerName = ("System.Data.SqlClient"),
        [string]$connectionString
    )
    try {
        if (-not [string]::IsNullOrWhiteSpace($ProviderFile)) {
            [Assembly]$assembly = [Assembly]::LoadFrom($ProviderFile)
            [type]$type = $assembly.GetExportedTypes() | Where-Object { [DbProviderFactory].IsAssignableFrom($_) } | Select-Object -First 1
            if (!$type) {
                return @{RC = -1; MSG = "not implement DbProviderFactory class"; DATA = $null }
            }
            [FieldInfo]$fieldInfo = $type.GetField("Instance", [BindingFlags]::Public -bor [BindingFlags]::Static)
            if (!$fieldInfo) {
                return @{RC = -1; MSG = "this class is not contain 'Instance' static field."; DATA = $null }
            }
            [DbProviderFactory]$Factory = $fieldInfo.GetValue($null)
        }
        else {
            [DbProviderFactory]$Factory = [DbProviderFactories]::GetFactory($providerName)  
        }        
        $con = $Factory.CreateConnection()
        $con.ConnectionString = $ConnectionString
        return @{RC = 0; MSG = "OK"; DATA = $con } 
    }
    catch {
        return @{RC = -1; MSG = "Get-Connection:$($_Exception.Message)"; DATA = $con }
    }
}

function Get-DbCommand {
    param (
        [System.Data.Common.DbConnection]$conenction,
        [string]$commandText,
        [System.Data.CommandType]$commandType = ([System.Data.CommandType]::Text),
        [psobject[]]$parameters = $null,
        [int]$commandTimeout = 30,
        [System.Data.Common.DbTransaction]$transaction = $null
    )

    $cmd = $Conenction.CreateCommand()
    $cmd.CommandText = $commandText
    $cmd.CommandTimeout = $commandTimeout
    $cmd.CommandType = $commandType
    if ($null -ne $parameters) {
        foreach ($item in $parameters) {
            $param = $cmd.CreateParameter()
            $param.ParameterName = $item.parameterName
            $param.Direction = $item.direction
            $param.IsNullable = $item.isNullable
            $param.Value = $item.value
            $param.Size = $item.size
            $param.Precision = $item.precision
            $param.Scale = $item.scale
            $param.DbType = $item.dbType
            [void]$cmd.Parameters.Add($param)
        }    
    }
    
    $cmd.Transaction = $transaction
    return $cmd
}

function Get-NewParameter {
    <#
    .SYNOPSIS
    创建SQL参数
    
    .DESCRIPTION
    Long description
    
    .PARAMETER parameterName
    参数名称
    
    .PARAMETER direction
    参数方向
    
    .PARAMETER value
    参数值
    
    .PARAMETER dbType
    参数类型
    
    .PARAMETER size
    参数大小
    
    .PARAMETER scale
    参数标量
    
    .PARAMETER precision
    参数精度
    
    .PARAMETER isNullable
    参数是否为空
    
    .EXAMPLE
    Get-NewParameter -parameterName "@p1" -direction Output -dbType DateTime
    
    .NOTES
    General notes
    #>
    param (
        [Parameter(Mandatory = $true)]
        [ValidateNotNull()]
        [string]$parameterName,
        [System.Data.ParameterDirection]$direction = ([System.Data.ParameterDirection]::Input),
        [System.Object]$value = $null,
        [System.Data.DbType]$dbType = ([System.Data.DbType]::Object),
        [int]$size = 50,
        [int]$scale = 0,
        [int]$precision = 0,
        [bool]$isNullable = $true
        
    )
    return New-Object psobject -Property ([Ordered]@{ parameterName = $parameterName; direction = $direction; value = $value; size = $size; `
                scale = $scale; precision = $precision; dbType = $dbType ; isNullable = $isNullable 
        })
}


function Get-ExecuteNonQuery {
    param (
        [System.Data.Common.DbConnection]$connection,
        [string]$commandText,
        [System.Data.CommandType]$CommandType = ([System.Data.CommandType]::Text),
        [psobject[]]$parameters = $null,
        [int]$commandTimeout = 30,
        [System.Data.Common.DbTransaction]$transaction = $null,
        [switch]$close
    ) 
    try {
        [System.Data.Common.DbCommand]$cmd = Get-DbCommand -conenction $Connection -commandText $commandText -commandType $commandType `
            -parameters $parameters -commandTimeout $commandTimeout -transaction $transaction
        if ($connection.State -ne [System.Data.ConnectionState]::Open) { $connection.Open() }
        $effect = $cmd.ExecuteNonQuery()
        return @{RC = 0; MSG = "OK"; DATA = $effect }
    }
    catch {
        return @{RC = -1; MSG = $_.Exception.Message; Data = $nu }
    }
    finally {
        foreach ($p in $parameters ) {
            if ($p.direction -in @([System.Data.ParameterDirection]::Output, [System.Data.ParameterDirection]::InputOutput)) {
                $p.Value = $cmd.Parameters[$p.ParameterName].Value
            }            
        }        
        if ($null -ne $connection -and $close) { $connection.Close() }
    }    
}


function Get-ExecuteScalar {
    param (
        [System.Data.Common.DbConnection]$connection,
        [string]$commandText,
        [System.Data.CommandType]$CommandType = ([System.Data.CommandType]::Text),
        [psobject[]]$parameters = $null,
        [int]$commandTimeout = 30,
        [System.Data.Common.DbTransaction]$transaction = $null,
        [switch]$close
    ) 
    try {
        [System.Data.Common.DbCommand]$cmd = Get-DbCommand -conenction $Connection -commandText $commandText -commandType $commandType `
            -parameters $parameters -commandTimeout $commandTimeout -transaction $transaction
        if ($connection.State -ne [System.Data.ConnectionState]::Open) { $connection.Open() }
        $obj = $cmd.ExecuteScalar()
        return @{RC = 0; MSG = "OK"; DATA = $obj }
    }
    catch {
        return @{RC = -1; MSG = $_.Exception.Message; Data = $null }
    }
    finally {
        foreach ($p in $parameters ) {
            if ($p.direction -in @([System.Data.ParameterDirection]::Output, [System.Data.ParameterDirection]::InputOutput)) {
                $p.value = $cmd.Parameters[$p.ParameterName].Value
            }            
        }     
        if ($null -ne $connection -and $close) { $connection.Close() }
    }    
}

function Get-ExecuteReader {
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [System.Data.Common.DbConnection]$connection,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$commandText,
        [System.Data.CommandType]$commandType = ([System.Data.CommandType]::Text),
        [psobject[]]$parameters = $null,
        [int]$commandTimeout = 30,
        [System.Data.Common.DbTransaction]$transaction = $null,
        [switch]$close
    )
    $resultList = [Ordered]@{}; $j = 0
    try {
        [System.Data.Common.DbCommand]$cmd = Get-DbCommand -conenction $Connection -commandText $commandText -commandType $commandType `
            -parameters $parameters -commandTimeout $commandTimeout -transaction $transaction
        if ($connection.State -ne [System.Data.ConnectionState]::Open) { $connection.Open() }
        $reader = $cmd.ExecuteReader()
        do {            
            $tempList = @()      
            while ($reader.Read()) {
                $property = [Ordered]@{}
                for ($i = 0; $i -lt $reader.FieldCount; $i++) { $property.($reader.GetName($i)) = $reader.GetValue($i) }
                $tempList += New-Object psobject -Property $property
            }
            $resultList.("Table$($j)") = $tempList; $j++           
        } while ($reader.NextResult())
   
        return @{RC = 0; MSG = "OK"; DATA = $resultList }
    }
    catch {
        return @{RC = -1; MSG = "Get-Reader:$($_.Exception.Message)"; DATA = $null }
    }
    finally {
        if ($null -ne $reader) {
            $reader.Close()
            foreach ($p in $parameters ) {
                if ($p.direction -in @([System.Data.ParameterDirection]::Output, [System.Data.ParameterDirection]::InputOutput)) {
                    $p.Value = $cmd.Parameters[$p.ParameterName].Value
                }                
            }    
        }
        if ($null -ne $Connection -and $close) { $Connection.Close() }        
    }    
}

function Get-Schema {
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [System.Data.Common.DbConnection]$connection,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$commandText,
        [System.Data.CommandType]$commandType = ([System.Data.CommandType]::Text),
        [psobject[]]$parameters = $null,
        [int]$commandTimeout = 30,
        [System.Data.Common.DbTransaction]$transaction = $null,
        [switch]$close
    )
    $resultList = [Ordered]@{}; $j = 0
    try {
        [System.Data.Common.DbCommand]$cmd = Get-DbCommand -conenction $Connection -commandText $commandText -commandType $commandType `
            -parameters $parameters -commandTimeout $commandTimeout -transaction $transaction
        if ($connection.State -ne [System.Data.ConnectionState]::Open) { $connection.Open() }
        $reader = $cmd.ExecuteReader([System.Data.CommandBehavior]::SchemaOnly)
        do {
            $table = $reader.GetSchemaTable()
            $tempList = @()      
            foreach ($row in $table.Rows) {
                $properties = [ordered]@{}
                for ($i = 0; $i -lt $table.Columns.Count; $i++) {
                    $properties.($table.Columns[$i]) = $row[$i]
                }
                $tempList += New-Object psobject -Property $properties
            }
            $resultList.("Schema$($j)") = $tempList; $j++           
        } while ($reader.NextResult())
   
        return @{RC = 0; MSG = "OK"; DATA = $resultList }
    }
    catch {
        return @{RC = -1; MSG = "Get-Reader:$($_.Exception.Message)"; DATA = $null }
    }
    finally {
        if ($null -ne $reader) {
            $reader.Close()
            foreach ($p in $parameters ) {
                if ($p.direction -in @([System.Data.ParameterDirection]::Output, [System.Data.ParameterDirection]::InputOutput)) {
                    $p.Value = $cmd.Parameters[$p.ParameterName].Value
                }                
            }    
        }
        if ($null -ne $Connection -and $close) { $Connection.Close() }        
    }    
}

客户端调用

using namespace System
Import-Module .\DbHelper.psm1 -Force

$con = Get-DbConnection -connectionString "Data Source=localhost;Initial Catalog=highwavedw;User id=sa;password=password"
$p1 = Get-NewParameter -parameterName "@p1" -direction Output -dbType DateTime
$p2 = Get-NewParameter -parameterName "@p2" -value "U" -dbType String
$reault = Get-ExecuteReader -connection $con.DATA -commandText "Select * from sys.objects Where Type=@p2;select * from sys.columns;select @p1=getdate()" -parameters @($p1, $p2)
$reault.DATA.Table1 | Format-Table

调用结果