Ad Space here

Direct Link: 3/14/2019Object Oriented Approach to PowerShell and SQL Server


A long time friend and coworker once told me: "One of the beauties of programming is that there are many different ways to get to the answer.”  How very true.  I’m going to demonstrate sort of a hybrid solution that I authored.  It is modeled after and very similar to compiled code that I have written and am using.  It is an Object Oriented approach to using PowerShell for accessing a SQL Server / SQL Server Express database.

The feature that I like the most is the ease of being able to point to different databases, and execute SQL commands with parameters that preserve data types. 

It also allows me to focus more on the SQL commands instead of just how to run them in PowerShell.​​​​​​​​​​​​​​

​​​​​​​

There are just two functions. 

1.  Get-DatabaseObject accepts the database connection string as a parameter and returns a PowerShell Custom Object that has methods to do the following:

  • Open the Connection
  • Execute a NonQuery statement
  • Execute a Query statement
  • Close the Connection

Code:

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
} 

2.  Get-SqlCommand accepts the query command and a optional parameter hash as input.  It returns a PowerShell Custom Object that can be passed to the ExecuteQuery or ExecuteNonQuery methods of the object that Get-DatabaseObject returns.

Code:
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
}

 

Below is an example using the above functions to open a connection, create a table, insert something into it, read it back, and close the connection:

The connection string in the example is pointing to a local install of SQL Server Express,  DHB is the instance.  I have connected to other SQL Server databases (IE: SCCM) just by simply changing the connection string to the correct value.

Code:

# 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)
}
​​​​​​​

Please feel free to critique/comment.

Follow Dustin Higgins on Twitter

Thanks!

Dustin

First two comments:


Dustin Higgins

Here is a function that works with SQLite:

Code:
######################################################
function Get-DatabaseObject
{
    <#
    .SYNOPSIS
    Function to get an object for a SQLite database
    .DESCRIPTION
    Function to get an object for a SQLite database
    #>
    param (
        [Parameter(Mandatory=$true)]
        [string]$connectionString
    )
    # Create the object
    $dbObj = [PSCustomObject]@{    
        ConnectionString = $connectionString
        Connection = $null
        ConnectionOpenTime = $null
        ConnectionCloseTime = $null
        ConnectionDuration = $null
        ConnectionCommandCount = 0
        Exception = $null
        SqlCommand = $null
        ObjTimestamp = Get-Date
    }
    # Add the OpenConnection Method
    $dbObj | Add-Member -Name "OpenConnection" -MemberType ScriptMethod -Value {
        try {
            $this.Connection = New-Object -TypeName System.Data.SQLite.SQLiteConnection
            $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
    $dbObj | 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
    $dbObj | 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
            $this.Exception = $_.Exception.Message
        }
        # Execute the cmd
        try {
            $returnCode = $sqlCommand.ExecuteNonQuery()
            $sqlCmdObj.ReturnCode = $returnCode
        } catch {
            $sqlCmdObj.ReturnCode = -1000
            $sqlCmdObj.Exception = $_.Exception.Message
        }        
        return $sqlCmdObj
    }
    # Add the ExecuteQuery Method
    $dbObj | Add-Member -Name "ExecuteQuery" -MemberType ScriptMethod -Value {
        param (
            [Parameter(Mandatory=$true)]
            [PSCustomObject]$sqlCmdObj
        )
        # Create the command
        $sqlCommand = $this.Connection.CreateCommand()
        #$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
            $this.Exception = $_.Exception.Message
        }
        # Execute the Query
        try {
            $adapter = New-Object System.Data.SQLite.SQLiteDataAdapter $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 = -1000
            $sqlCmdObj.Exception = $_.Exception.Message
        }
        # Return the object
        return $sqlCmdObj
    }
    # Return the DB object
    return $dbObj
}

# Of course you have to Add-Type the System.Data.SQLite.dll file:

$ScriptDir = Split-Path $script:MyInvocation.MyCommand.Path

# Check for the SQL Lite Dll
$SQLLiteDllFile = "$($ScriptDir)\SQLite\System.Data.SQLite.dll"

if ((Test-Path $SQLLiteDllFile) -eq $false) {
    write-host ""
    Write-Host "$($SQLLiteDllFile) not found, exiting ..." -ForegroundColor Red
    Write-Host ""
}
# Add the DLL
Add-Type -Path $SQLLiteDllFile




Available Blog posts:






If you like this site, help us out.
Spread the word and share it with others!