public/New-SQLCommand.ps1

 <#
      .SYNOPSIS
          Create a sql command object
  
      .DESCRIPTION
          This function uses the information contained in the parameters to create a sql command object. In general, you will want to use the invoke- functions directly,
          but if you need to manipulate a command object in ways that those functions don't allow, you will need this. Also, the invoke-bulkcopy function allows you to pass
          a command object instead of a set of records in order to "stream" the records into the destination in cases where there are a lot of records and you don't want to
          allocate memory to hold the entire result set.
  
      .PARAMETER sql
          The sql to be executed by the command object (although it is not executed by this function).
  
      .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:\> $cmd=new-sqlcommand "ALTER DATABASE AdventureWorks Modify Name = Northwind" -server MyServer
          PS C:\> $cmd.ExecuteNonQuery()
  
  
      .EXAMPLE
          PS C:\> $cmd=new-sqlcommand -server MyServer -sql "Select * from MyTable"
          PS C:\> invoke-sqlbulkcopy -records $cmd -server MyOtherServer -table CopyOfMyTable
  
      .INPUTS
          None.
          You cannot pipe objects to new-sqlcommand
  
      .OUTPUTS
          System.Data.SqlClient.SqlCommand
  
  #>

  function New-SQLCommand{
    param([Parameter(Mandatory=$true,Position=0)][Alias('storedProcName')][string]$sql,
          [Parameter(ParameterSetName="SuppliedConnection")][System.Data.SqlClient.SQLConnection]$connection,
          [hashtable]$parameters=@{},
          [int]$timeout=30,
          [Parameter(ParameterSetName="AdHocConnection")][string]$server,
          [Parameter(ParameterSetName="AdHocConnection")][string]$database,
          [Parameter(ParameterSetName="AdHocConnection")][string]$user,
          [string]$password,
          [System.Data.SqlClient.SqlTransaction]$transaction=$null,
          [hashtable]$outparameters=@{})
       
        $dbconn=Get-Connection -conn $connection -server $server -database $database -user $user -password $password
        $close=($dbconn.State -eq [System.Data.ConnectionState]'Closed')
        if ($close) {
            $dbconn.Open()
        }    
        $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$dbconn)
        $cmd.CommandTimeout=$timeout
        foreach($p in $parameters.Keys){
            if($parameters[$p] -is [Hashtable]){
                $parm=$cmd.Parameters.Add("@$p",[System.Data.SQLDbType]($parameters[$p].Type))
                $parm.Value=$parameters[$p].Value
            } else {
              $parm=$cmd.Parameters.AddWithValue("@$p",$parameters[$p])
            }if (Test-NULL $parameters[$p]){
               $parm.Value=[DBNull]::Value
            }
        }
        Set-outputparameters $cmd $outparameters
    
        if ($transaction -is [System.Data.SqlClient.SqlTransaction]){
        $cmd.Transaction = $transaction
        }
        return $cmd
    
    
    }