【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
}