bug in invoke-sql?

Sep 2, 2010 at 2:00 PM
Shouldn't 
	$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$connection)
be
	$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)
	$cmd.CommandTimeout=$timeout
	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()
	
}
Developer
Sep 2, 2010 at 2:50 PM

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

Sep 2, 2010 at 2: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.

Coordinator
Sep 3, 2010 at 2: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.