

Rank: Advanced Member





Joined: 7/1/2018(UTC)

Posts: 63



Groups: RegisteredJoined: 7/1/2018(UTC)Posts: 63

Thanks: 1 times

Was thanked: 6 time(s) in 6 post(s)



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 Edited by user Monday, September 14, 2020 10:26:55 AM(UTC) | Reason: Not specified