The Get-SQL function retrieved data from SQL and presented it in a HashTable Array.

Let’s create a similar function that we can use when updating, inserting or deleting data from an SQL server.

function Set-SQL ([string]$Query,[string]$ConnString) {

if ($ConnString) {

if($ConnString -match ‘”*”‘) {
$ConnString = $ConnString.TrimStart(‘”‘)
$ConnString = $ConnString.TrimEnd(‘”‘)
}

} else {

# Default ConnectionString
$ConnString =
“server=SQL;database=master;trusted_connection=true;”

}

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = $ConnString
$Connection.Open()

$Command = [...]

In a previous post I described a little about PowerShell and SQL, how to connect and how to handle different queries through PowerShell.

Now let’s put it all togheter and create a more handy function.

function Get-SQL ([string]$Query,[string]$ConnString) {

if ($ConnString) {

if($ConnString -match ‘”*”‘) {
$ConnString = $ConnString.TrimStart(‘”‘)
$ConnString = $ConnString.TrimEnd(‘”‘)
}

} else {

# Default Connection String
$ConnString =
“server=ServerName;database=DbName;trusted_connection=true;”

}

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString [...]

Working with Databases through PowerShell can be a little tricky since PowerShell doesn’t contain any SQL CmdLets. Let’s start connecting to our SQL database using the SQLConnection object:

PS > $Connection = New-Object System.Data.SQLClient.SQLConnection
PS > $Connection

StatisticsEnabled : False
ConnectionString [...]