public/Invoke-Query.ps1
|
<# .SYNOPSIS Execute a sql statement, returning the results of the query. .DESCRIPTION This function executes a sql statement, using the parameters provided (both input and output) and returns the results of the query. You may optionally provide a connection or sufficient information to create a connection, as well as input and output 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 outparameters A hashtable of input parameters to be supplied with the query. Entries in the hashtable should have names that match the parameter names, and string values that are the type of the parameters. See example 3. .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 This is an example of a query that returns a single result. PS C:\> $c=New-Connection '.\sqlexpress' PS C:\> $res=invoke-query 'select * from master.dbo.sysdatabases' -conn $c PS C:\> $res .EXAMPLE This is an example of a query that returns 2 distinct result sets. PS C:\> $c=New-Connection '.\sqlexpress' PS C:\> $res=invoke-query 'select * from master.dbo.sysdatabases; select * from master.dbo.sysservers' -conn $c PS C:\> $res.Tables[1] .EXAMPLE This is an example of a query that returns a single result and uses a parameter. It also generates its own (ad hoc) connection. PS C:\> invoke-query 'select * from master.dbo.sysdatabases where name=@dbname' -param @{dbname='master'} -server '.\sqlexpress' -database 'master' .INPUTS None. You cannot pipe objects to invoke-query .OUTPUTS Several possibilities (depending on the structure of the query and the presence of output variables) 1. A list of rows 2. A dataset (for multi-result set queries) 3. An object that contains a dictionary of ouptut parameters and their values and either 1 or 2 (for queries that contain output parameters) #> function Invoke-Query{ param( [Parameter(Mandatory=$true)][string]$sql, [Parameter(ParameterSetName="SuppliedConnection")][System.Data.SqlClient.SqlConnection]$connection, [hashtable]$parameters=@{}, [hashtable]$outparameters=@{}, [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, [ValidateSet("DataSet", "DataTable", "DataRow", "Dynamic")] [string]$AsResult="Dynamic" ) $connectionparameters=copy-hashtable $PSBoundParameters -exclude AsResult $cmd=new-sqlcommand @connectionparameters $ds=New-Object system.Data.DataSet $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) $da.fill($ds) | Out-Null #if it was an ad hoc connection, close it if ($server){ $cmd.connection.close() } get-outputparameters $cmd $outparameters switch ($AsResult) { 'DataSet' { $result = $ds } 'DataTable' { $result = $ds.Tables } 'DataRow' { $result = $ds.Tables[0] } 'Dynamic' { $result = get-commandresults $ds $outparameters } } return $result } |