bug in invoke-sql?

Sep 2, 2010 at 1:00 PM
	$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$connection)
	$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
Since the $conn is created just above from the supplied parameters.

function invoke-sql{
param([Parameter(Position=0, Mandatory=$true)][string]$sql,
      [Parameter(Position=1, Mandatory=$false)][System.Data.SqlClient.SQLConnection]$connection,
      [Parameter(Position=2, Mandatory=$false)][hashtable]$parameters=@{},
      [Parameter(Position=3, Mandatory=$false)][int]$timeout=30,
      [Parameter(Position=4, Mandatory=$false)][string]$server,
      [Parameter(Position=5, Mandatory=$false)][string]$database,
      [Parameter(Position=6, Mandatory=$false)][string]$user,
      [Parameter(Position=7, Mandatory=$false)][string]$password,
      [Parameter(Position=8, Mandatory=$false)][System.Data.SqlClient.SqlTransaction]$transaction=$nothing)
	$conn=get-connection -conn $connection -server $server -database $database -user $user -password $password 
	$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$connection)
	foreach($p in $parameters.Keys){
				[Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
    if ($transaction -is [System.Data.SqlClient.SqlTransaction]){
       write-verbose 'Setting transaction'
       $cmd.Transaction = $transaction
	return $cmd.ExecuteNonQuery()
Sep 2, 2010 at 1:50 PM

Agree, invoke-sql (module adolib) only works when supplied a valid connection in $connection. 

Sep 2, 2010 at 1:57 PM

invoke-query has the same problem.  Interestingly enough though, invoke-storedprocedure does it different and might be the best pattern.  It depends on the expectation of the output parameter of $connection.

Sep 3, 2010 at 1:57 PM

Looking at the code, the following (correct) line shows up in invoke-query and invoke-storedprocedure:


	$connection=get-connection -conn $connection -server $server -database $database -user $user -password $password 

The corresponding line in invoke-sql looks like this:

	$conn=get-connection -conn $connection -server $server -database $database -user $user -password $password 

All 3 functions use this line to create the command object:

	$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$connection)

Obviously, the $conn should have been $connection in invoke-sql. I'll get that updated in the source as soon as I can.