public/Invoke-SQL.ps1
|
<# .SYNOPSIS Execute a sql statement, ignoring the result set. Returns the number of rows modified by the statement (or -1 if it was not a DML staement) .DESCRIPTION This function executes a sql statement, using the parameters provided and returns the number of rows modified by the statement. You may optionally provide a connection or sufficient information to create a connection, as well as input parameters, command timeout value, and a transaction to join. .PARAMETER sql The SQL Statement .PARAMETER connection An existing connection to perform the sql statement with. .PARAMETER parameters A hashtable of input parameters to be supplied with the query. See example 2. .PARAMETER timeout The commandtimeout value (in seconds). The command will fail and be rolled back if it does not complete before the timeout occurs. .PARAMETER Server The server to connect to. If both Server and Connection are specified, Server is ignored. .PARAMETER Database The initial database for the connection. If both Database and Connection are specified, Database is ignored. .PARAMETER User The sql user to use for the connection. If both User and Connection are specified, User is ignored. .PARAMETER Password The password for the sql user named by the User parameter. .PARAMETER Transaction A transaction to execute the sql statement in. .EXAMPLE PS C:\> invoke-sql "ALTER DATABASE AdventureWorks Modify Name = Northwind" -server MyServer .EXAMPLE PS C:\> $con=New-Connection MyServer PS C:\> invoke-sql "Update Table1 set Col1=null where TableID=@ID" -parameters @{ID=5} .INPUTS None. You cannot pipe objects to invoke-sql .OUTPUTS Integer #> function Invoke-Sql{ param([Parameter(Mandatory=$true,Position=0)][string]$sql, [Parameter(ParameterSetName="SuppliedConnection")][System.Data.SqlClient.SQLConnection]$connection, [hashtable]$parameters=@{}, [hashtable]$outparameters=@{}, [Parameter(Position=4, Mandatory=$false)][int]$timeout=30, [Parameter(ParameterSetName="AdHocConnection")][string]$server, [Parameter(ParameterSetName="AdHocConnection")][string]$database, [Parameter(ParameterSetName="AdHocConnection")][string]$user, [Parameter(ParameterSetName="AdHocConnection")][string]$password, [System.Data.SqlClient.SqlTransaction]$transaction=$null) $cmd=new-sqlcommand @PSBoundParameters #if it was an ad hoc connection, close it if ($server){ $cmd.connection.close() } return $cmd.ExecuteNonQuery() } |