【PowerShell】OledbHelper

<#	
    .NOTES
    ===========================================================================
     Created on:   	
     Created by:   	
     Organization: 	
     Filename:     	OledbHelper
    ===========================================================================
    .DESCRIPTION
        A toolkit for OleDb.
#>

function New-OleDbConnection
{
    <#
    .SYNOPSIS
        This functions helps you create an OleDbConnection.

    .PARAMETER $ConnectionString
        Database ConnecionString
    #>
    [OutputType([System.Data.OleDb.OleDbConnection])]
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString
    )
    
    $OleDbConnection = New-Object -TypeName System.Data.OleDb.OleDbConnection($ConnectionString)
    try
    {
        $OleDbConnection.Open()
        Write-Host 'Connected to oledb.'
        return $OleDbConnection
    }
    catch [exception]
    {
        Write-Warning ('Connect to database failed with error message:{0}' -f ,$_)
        $OleDbConnection.Dispose()
        return $null
    }
}

function Get-OleDbScalar
{
    <#
    .SYNOPSIS
        This functions returns the execution result of an SQL statement.

    .PARAMETER $ConnectionString
        A ConnecionString of Database
    
    .PARAMETER $Command
        An SQL statement
    #>
    [OutputType([System.Object])]
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$Command
    )
    
    [System.Data.OleDb.OleDbConnection]$OleDbConnection = New-OleDbConnection -ConnectionString $ConnectionString
    if ($null -ne $OleDbConnection)
    {
        try
        {
            $cmd = $OleDbConnection.CreateCommand()
            $cmd.CommandText = $Command
            return $cmd.ExecuteScalar()
        }
        catch [Exception]
        {
            Write-Warning ('Execute Sql command failed with error message:{0}' -f $_)
            return $null
        }
        finally
        {
            $OleDbConnection.Close()
        }
    }
    return $null
}

function Get-OleDbDataTable
{
    <#
    .SYNOPSIS
        This functions returns a DataTable of an SQL statement.

    .PARAMETER $ConnectionString
        A ConnecionString of Database
    
    .PARAMETER $Command
        An SQL statement
    #>
    [OutputType([System.Data.DataTable])]
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$Command
    )
    
    [System.Data.OleDb.OleDbConnection]$OleDbConnection = New-OleDbConnection -ConnectionString $ConnectionString
    if ($null -ne $OleDbConnection)
    {
        $dataSet = New-Object -TypeName System.Data.DataSet
        $dataAdapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter($Command, $OleDbConnection)
        $dataAdapter.Fill($dataSet) | Out-Null
        return $dataSet.Tables | Select-Object -First 1
    }
    return $null
}

function Get-Entities
{
    <#
    .SYNOPSIS
        This functions returns a set of Object of an SQL statement.

    .PARAMETER $ConnectionString
        A ConnecionString of Database
    
    .PARAMETER $Command
        An SQL statement
    
    .PARAMETER $Type
        System.Type
    #>
    [OutputType([System.Collections.ArrayList])]
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$Command,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [type]$Type
    )
    
    [System.Data.DataTable]$dt = Get-OleDbDataTable -ConnectionString $ConnectionString -Command $Command
    if (($null -ne $dt) -and ($dt.Rows.Count -gt 0))
    {
        $list = New-Object -TypeName System.Collections.Generic.List[$Type]
        $props = $Type.GetProperties([System.Reflection.BindingFlags]::Public + [System.Reflection.BindingFlags]::Instance)
        foreach ($row in $dt.Rows)
        {
            $t = [System.Activator]::CreateInstance($Type);
            foreach ($item in $props)
            {
                if (($null -ne $row[$item.Name]) -and ([System.DBNull]::Value -ne $row[$item.Name]))
                {
                    $item.SetValue($t, $row[$item.Name], $null)
                }
            }
            $list.Add($t)
        }
        return $list
    }
    return $null
}

function Get-OleDbDataSet
{
    <#
    .SYNOPSIS
        This functions returns a DataSet of a set of SQL statements.

    .PARAMETER $ConnectionString
        A ConnecionString of Database
    
    .PARAMETER $Commands
        A set of SQL statements
    #>
    [OutputType([System.Data.DataSet])]
    Param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string[]]$Commands
    )
    
    [System.Data.OleDb.OleDbConnection]$OleDbConnection = New-OleDbConnection -ConnectionString $ConnectionString
    if ($null -ne $OleDbConnection)
    {
        $dataSet = New-Object -TypeName System.Data.DataSet
        $cmd = [System.String]::Join(";", $Commands)
        $dataAdapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter($cmd, $OleDbConnection)
        $dataAdapter.Fill($dataSet) | Out-Null
        return $dataSet
    }
    return $null
}

function Invoke-OleDbCommandNonQuery
{
    <#
    .SYNOPSIS
        This functions returns the boolean of execution result of an SQL statement.

    .PARAMETER $ConnectionString
        A ConnecionString of Database
    
    .PARAMETER $Command
        An SQL statement
    #>
    [OutputType([System.Boolean])]
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$Command
    )
    
    [System.Data.OleDb.OleDbConnection]$OleDbConnection = New-OleDbConnection -ConnectionString $ConnectionString
    if ($null -ne $OleDbConnection)
    {
        try
        {
            $cmd = $OleDbConnection.CreateCommand()
            $cmd.CommandText = $Command
            $cmd.ExecuteNonQuery() | Out-Null
            return $true
        }
        catch [Exception]
        {
            Write-Warning ('Execute Sql command failed with error message:{0}' -f $_)
            return $false
        }
        finally
        {
            $OleDbConnection.Close()
        }
    }
    return $false
}

function Invoke-OleDbCommandsNonQuery
{
    <#
    .SYNOPSIS
        This functions returns the boolean of execution result of a set of SQL statements.

    .PARAMETER $ConnectionString
        A ConnecionString of Database
    
    .PARAMETER $Commands
        A set of SQL statements
    #>
    [OutputType([System.Boolean])]
    param (
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,
        [ValidateNotNull()]
        [Parameter(Mandatory = $true)]
        [string[]]$Commands
    )
    
    [System.Data.OleDb.OleDbConnection]$OleDbConnection = New-OleDbConnection -ConnectionString $ConnectionString
    if ($null -ne $OleDbConnection)
    {
        $transaction = $OleDbConnection.BeginTransaction()
        try
        {
            $cmd = $OleDbConnection.CreateCommand()
            $cmd.Transaction = $transaction
            foreach ($item in $Commands)
            {
                $cmd.CommandText = $item
                $cmd.ExecuteNonQuery()
            }
            $transaction.Commit()
            return $true
        }
        catch [Exception]
        {
            $transaction.Rollback()
            Write-Warning ('Execute Sql commands failed with error message:{0}' -f $_)
            return $false
        }
        finally
        {
            $OleDbConnection.Close()
        }
    }
    return $false
}