##################################################################################
#
#
#  Script name: Set-SQL.ps1
#  Author:      goude@powershell.nu
#  Homepage:    www.powershell.nu
#
#
##################################################################################

param ( [string]$Query, 
	[string]$Connection = ("server=ServerName;database=DbName;trusted_connection=true;"),
	[switch]$help
)

function GetHelp() {


$HelpText = @"

DESCRIPTION:
NAME: Set-SQL.ps1
Updates, Insert or Delete information in a SQL Database
Change the $Connection Variable on Row 12 in the script
to your Default Database.

PARAMETERS: 
-Query          SELECT query (Required)
-Connection     Connection to Databse (Optional)
-help           Prints the HelpFile (Optional)

SYNTAX:
./Set-SQL.ps1 -Query "INSERT INTO Region (RegionID,RegionDescription) VALUES ('5','Even More North')"

Inserts RegionID and RegionDescription into Region table

./Set-SQL.ps1 -Query "DELETE * FROM Region WHERE RegionID = '5'" -Connection "server=Server;database=Northwind;trusted_connection=true;"

Deletes the Row where Region equals 5 in the
specified Database

Get-Inventory.ps1 -help

Displays the help topic for the script

"@
$HelpText

}

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

	# Prepare the ConnectionString

	$ConnString = $ConnString.TrimStart('"')
	$ConnString = $ConnString.TrimEnd('"')

	# Connect to The SQL Server

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

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

	# Execute the Wuery

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

	return $Reader = $Command.ExecuteNonQuery()

	$Connection.Close()
}

if ($help) { GetHelp }

if ($Query -AND $Connection) { Get-SQL $Query $Connection }