PowerShell.nu

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 = 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;"