function Get-DatabaseObject
{
    <#
     .SYNOPSIS
    Get-DatabaseObject returns an PowerShell Custom Object that works with a SQL Server database.    

     .DESCRIPTION
    The Get-DatabaseObject function returns a PowerShell Custom Object that works with a SQL Server database.
    The object has the following methods: OpenConnection, CloseConnection,  ExecuteNonQuery,
    ExecuteQuery.  The only input to the function is the connection string used to communicate 
    with the database.

     .PARAMETER ConnectionString
    This is the ConnectionString for the System.Data.SqlClient.SqlConnection connection.

     .EXAMPLE
    Get-DatabaseObject "Data Source=localhost\DHB;Initial Catalog=MyDatabase;Integrated Security=true;" 
    #>
    param (
        [Parameter(Mandatory=$True)]
        [string]$ConnectionString
    )
    # Create the object
    $DatabaseObj = [PSCustomObject]@{    
        ConnectionString = $connectionString
        Connection = $Null
        ConnectionOpenTime = $Null
        ConnectionCloseTime = $Null
        ConnectionDuration = $Null
        ConnectionCommandCount = 0
        Exception = $Null
        SqlCommand = $Null
        DateCreated = Get-Date
    }
    # Add the OpenConnection Method
    $DatabaseObj | Add-Member -Name "OpenConnection" -MemberType ScriptMethod -Value {
        try {
            $This.Connection = New-Object System.Data.SqlClient.SqlConnection
            $This.Connection.ConnectionString = $This.ConnectionString
            $This.Connection.Open()
            $This.ConnectionOpenTime = Get-Date
        } catch {
            Write-Host $_.Exception.Message -ForegroundColor Red
            $This.Exception = $_.Exception.Message
        }
    }
    # Add the CloseConnection Method
    $DatabaseObj | Add-Member -Name "CloseConnection" -MemberType ScriptMethod -Value {
        try {
            $This.Connection.Close()
            $This.ConnectionCloseTime = Get-Date
            $This.ConnectionDuration = $This.ConnectionCloseTime - $This.ConnectionOpenTime
        } catch {
            Write-Host $_.Exception.Message -ForegroundColor Red
            $This.Exception = $_.Exception.Message
        }
    }
    # Add the ExcecuteNonQuery Method
    $DatabaseObj | Add-Member -Name "ExecuteNonQuery" -MemberType ScriptMethod -Value {
        param (
            [Parameter(Mandatory=$True)]
            [PSCustomObject]$sqlCmdObj
        )
        # Create the command
        $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
        $SqlCommand.Connection = $This.Connection
        $SqlCommand.CommandText = $sqlCmdObj.CommandText
        # Bump the command count
        $This.ConnectionCommandCount = $This.ConnectionCommandCount + 1
        try {
            # Add the cmd parameters if they are there
            if ($sqlCmdObj.CommandParameterHash -ne $Null) {
                foreach ($item in $sqlCmdObj.CommandParameterHash.GetEnumerator()) {
                    [void]$SqlCommand.Parameters.Add($item.Key, $item.Value)
                }
            }
        } catch {
            Write-Host $_.Exception.Message -ForegroundColor Red
        }
        # Execute the cmd
        try {
            $returnCode = $SqlCommand.ExecuteNonQuery()
            $sqlCmdObj.ReturnCode = $returnCode
        } catch {
            $sqlCmdObj.ReturnCode = -1000
            $sqlCmdObj.Exception = $_.Exception.Message
        }        
        return $sqlCmdObj
    }
    # Add the ExecuteQuery Method
    $DatabaseObj | Add-Member -Name "ExecuteQuery" -MemberType ScriptMethod -Value {
        param (
            [Parameter(Mandatory=$True)]
            [PSCustomObject]$sqlCmdObj
        )
        # Create the command
        $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
        $SqlCommand.Connection = $This.Connection
        $SqlCommand.CommandText = $sqlCmdObj.CommandText
        # Bump the command count
        $This.ConnectionCommandCount = $This.ConnectionCommandCount + 1
        try {
            # Add the cmd parameters if they are there
            if ($sqlCmdObj.CommandParameterHash -ne $Null) {
                foreach ($item in $sqlCmdObj.CommandParameterHash.GetEnumerator()) {
                    [void]$SqlCommand.Parameters.Add($item.Key, $item.Value)
                }
            }
        } catch {
            Write-Host $_.Exception.Message -ForegroundColor Red
        }
        # Execute the Query
        try {
            $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCommand
            $dt = New-Object System.Data.Datatable
            $returnCode = $adapter.Fill($dt)
            $sqlCmdObj.ReturnCode = $returnCode
            $sqlCmdObj.Results = $dt
            $sqlCmdObj.ResultsCount = $dt.Rows.Count
        } catch {
            $sqlCmdObj.ReturnCode = -1001
            $sqlCmdObj.Exception = $_.Exception.Message
        }
        # Return the object
        return $sqlCmdObj
    }
    # Return the DB object
    return $DatabaseObj
} 

function Get-SqlCommand
{
    <#    
     .SYNOPSIS
    Get-SqlCommand returns an PowerShell Custom Object that is passed to methods of a PowerShell Custom Object 
    retrieved from the Get-DatabaseObject function.
  
     .DESCRIPTION
    Get-SqlCommand returns an PowerShell Custom Object that is passed to methods of a PowerShell Custom Object 
    retrieved from the Get-DatabaseObject function.  The object can be used to execute query and non-query
    commands against a SqlServer database.  If a Parameterized SQL command is being used, a hashtable must be
    passed to the function.
    
     .PARAMETER CommandText
    This is the text of the SQL Command that will be executed.
    
     .PARAMETER CommandText
    This is a hashtable of field names and values if a parameterized SQL Command is being used.

     .EXAMPLE
    $ParamHash = @{}
    $ParamHash.Add("Name", "Dustin Higgins")
    Get-SqlCommand "SELECT * FROM TestTable WHERE Name=@Name" 
    #>
    param (
        [Parameter(Mandatory=$True)]
        [string]$CommandText,
        [Parameter(Mandatory=$false)]
        [System.Collections.Hashtable]$ParamHash = $Null
    )
    # Create the object
    $sqlCmdObj = [PSCustomObject]@{    
        CommandText = $commandText
        CommandParameterHash = $paramHash
        ReturnCode = $Null
        Exception = $Null
        Results = $Null
        ResultsCount = 0
        DateCreated = Get-Date
    }
    return $sqlCmdObj
}
 

# Set the Connection
$connString = "Data Source=localhost\DHB;Initial Catalog=MyDatabase;Integrated Security=true;" 

# Get the Database Object
$DatabaseObj = Get-DatabaseObject $connString 

# Open the connection
$DatabaseObj.OpenConnection()

# Get the SQL Command Object to create the table
$SqlCreateCommandObj = Get-SqlCommand -CommandText “CREATE TABLE TestTable (Name nvarchar(50), DateCreated datetime)”

# Run the SQL NonQuery command
$SqlCreateCommandObj = $DatabaseObj.ExecuteNonQuery($SqlCreateCommandObj)

# Get the SQL Command Object to update the table
$DateTime = Get-Date
$ParamHash = @{}
$ParamHash.Add("Name","Dustin Higgins")
$ParamHash.Add("DateCreated",$DateTime)
$SqlInsertCommandObj = Get-SqlCommand -CommandText “INSERT INTO TestTable (Name, DateCreated) VALUES (@Name,@DateCreated)” -ParamHash $ParamHash

# Run the SQL NonQuery command
$SqlInsertCommandObj = $DatabaseObj.ExecuteNonQuery($SqlInsertCommandObj)

# Get the SQL Command Object to read the table
$SqlQueryCommandObj = Get-SqlCommand -CommandText “SELECT * FROM TestTable”

# Run the SQL Query command
$SqlQueryCommandObj = $DatabaseObj.ExecuteQuery($SqlQueryCommandObj)
$SqlQueryCommandObj

# Loop through the results
write-host ("Results Count: " + $SqlQueryCommandObj.ResultsCount.ToString())
foreach ($i in $SqlQueryCommandObj.Results.Rows) {    
    write-host ("Name: " + $i.Name + " DateCreated: " + $i.DateCreated)
}