DbData

2.2.2

Exit-DbTransaction.ps1

<#
 
.SYNOPSIS
Exit a SQL transaction.
 
.DESCRIPTION
Commit or rollback a SQL transaction.
 
.PARAMETER InputObject
A SqlCommand with a SqlConnection and SqlTransaction. This can be extracted from a DataTable or a DataSet, but not a DataRow.
 
.PARAMETER Commit
Commit the transaction.
 
.PARAMETER Rollback
Rollback the transaction.
 
.PARAMETER PassThru
Pass the transaction on in the pipeline for further operations.
 
.INPUTS
Pipe in SqlCommand or a DataSet. You cannot pipe in a DataTable because it will be enumerated into DataRows.
 
.OUTPUTS
(Optionally) Whatever was piped in.
 
.EXAMPLE
$serverInstance = ".\SQL2016"
$dbData = New-DbConnection $serverInstance | New-DbCommand "Select @@Trancount"
$dbData | Get-DbData -As Scalar
$dbData | Enter-DbTransaction "ABC"
$dbData | Get-DbData -As Scalar
$dbData | Exit-DbTransaction -Commit
$dbData | Get-DbData -As Scalar
 
Results:
0
1
0
 
Show the transaction count, begin a transaction and show the transaction count increased. Then rollback and show the transaction count decreased.
 
#>

function Exit-DbTransaction {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("SqlTransaction")]
        [Alias("SqlCommand")]
        $InputObject,

        [switch] $Commit,
        [switch] $Rollback,

        [switch] $PassThru
    )

    begin {
    }

    process {
        if ($InputObject -is [System.Data.SqlClient.SqlCommand]) {
            $sqlCommand = $InputObject
        } elseif ($InputObject -is [System.Data.DataTable]) {
            $sqlCommand = $InputObject.SqlDataAdapter.SelectCommand
        } elseif ($InputObject -is [System.Data.DataSet]) {
            $sqlCommand = $InputObject.Tables[0].SqlDataAdapter.SelectCommand
        } else {
            Write-Error "InputObject must be an SqlCommand with an SqlConnection, a DataTable, or a DataSet."
        }

        if (-not $sqlCommand.Connection) {
            Write-Error "SqlCommand requires a valid associated SqlConnection before a transaction can be started."
        }

        if ($SqlCommand.Transaction) {
            if ($Rollback) {
                $sqlCommand.Transaction.Rollback()
            } else {
                $sqlCommand.Transaction.Commit()
            }
        } elseif ($Commit) {
            Write-Error "Cannot commit a transaction as a transaction isn't open in this SqlCommand."
        } # If a Rollback was requested and it has already been closed we ignore it


        if ($passThru) {
            $InputObject
        }
    }

    end {
    }
}