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 Table.

function Set-SQL ([string]$Query,[string]$ConnectionString) {
  $Connection = New-Object System.Data.SQLClient.SQLConnection

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

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.Connection = $Connection
  $Command.CommandText = $Query

  return $Reader = $Command.ExecuteNonQuery()

  $Connection.Close()
}

Examples on Running the Function

PS > Set-SQL -Query "INSERT INTO Region (RegionID,RegionDescription) VALUES ('5','Even More North')" `
-ConnString "server=Server;database=Northwind;trusted_connection=true;"

PS > Set-SQL "DELETE * FROM Region WHERE RegionID = '5'" `
-ConnString "server=Server;database=Northwind;trusted_connection=true;"

Again, if you are working with user input or any input that you do not control 100% you should use parameterized queries instead.
Here’s an example on how you can secure your function.

function Set-SQL ([int]$Id, [string]$Description, [string]$ConnectionString) {
  $Connection = New-Object System.Data.SQLClient.SQLConnection

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

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.Connection = $Connection
  $Command.CommandText = "INSERT INTO Region (RegionID,RegionDescription) VALUES (@RegionID,@RegionDescription)"

  $SQLParameter1 = New-Object System.Data.SqlClient.SqlParameter("@RegionID", $Id)
  $SQLParameter2 = New-Object System.Data.SqlClient.SqlParameter("@RegionDescription", $Description)
  [void]$command.Parameters.Add($SQLParameter1)
  [void]$command.Parameters.Add($SQLParameter2)

  return $Reader = $Command.ExecuteNonQuery()

  $Connection.Close()
}