How to execute SqlCommand in PowerShell?

Question

I'm getting the following exception:

New-Object : Cannot find an overload for "SqlCommand" and the argument count: "2". At C:\Users\aaaaaaaaaaaaaaaaaaps.ARMTemplate\CoreDbScripts\RunSqlScripts.ps1:128 char:19
... $comm = New-Object System.Data.SqlClient.SqlCommand( $file,$conn) ...
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : InvalidOperation: (:) [New-Object], MethodException

FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Here's my code:

Using-Object ($conn = New-Object System.Data.SqlClient.SqlConnection $connString) {
    $conn.Open();
    
    Using-Object ($tran = $conn.BeginTransaction) {
        foreach ($file in $sqlFiles)
        {
          Write-Host "file: " $file.Name
          $comm = New-Object System.Data.SqlClient.SqlCommand($file, $conn);
          $comm.Transaction = $tran
        [void]$comm.ExecuteNonQuery()
        }
        $tran.Commit();
    };
};

What am I doing wrong? How do we execute the SqlCommand?

As an aside: It's best to pseudo method syntax: Instead of `New-Object SomeType(arg1, ...)`, use `New-Object SomeType [-ArgumentList] arg1, ...` - PowerShell cmdlets, scripts and functions are invoked like _shell commands_, not like _methods_. That is, no parentheses around the argument list, and _whitespace_-separated arguments (`,` constructs an _array_ as a _single argument_, as needed for `-ArgumentList`). However, method syntax _is_ required if you use the PSv5+ `[SomeType]::new()` constructor-call method. See [this answer](https://stackoverflow.com/a/50636061/45375) — mklement0, Oct 29 '21 at 04:33

mklement0 · Answer 1 · 2021-10-29T15:30:47.917

3

The error message implies that at least one of the constructor arguments you're passing is of the wrong data type (given that it isn't the number of arguments that is the problem).

The two-argument constructor of class System.Data.SqlClient.SqlCommand you're trying to call has the following signature:

public SqlCommand (string cmdText, System.Data.SqlClient.SqlConnection connection);

By contrast, your code suggests that you're passing an instance of type System.IO.FileInfo as the first argument, i.e. an object describing a file as a file-system entry. Instead, pass the file's content, which you can obtain in full via Get-Content -Raw:

$comm = New-Object System.Data.SqlClient.SqlCommand ($file | Get-Content -Raw), $conn

Or, using the alternative PSv5+ syntax for invoking constructors, via the intrinsic static ::new() method PowerShell makes available on types:

$comm = [System.Data.SqlClient.SqlCommand]::new(($file | Get-Content -Raw), $conn)

edited Oct 29 '21 at 15:30

answered Oct 29 '21 at 04:27

mklement0

382,024
64
607
775

+ ... ata.SqlClient.SqlCommand (Get-Content -Raw $filesconcatenated), $conn + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: ( /****** Object:String) [Get-Content], DriveNotFoundException + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.GetContentCommand – Alex Gordon Oct 29 '21 at 15:20
As stated, the assumption is that `$file` is a single `FileInfo` instance (and that it refers to an existing file containing SQL commands). I don't know what `$filesconcatenated` is, but it doesn't sound like a single `FileInfo` instance. – mklement0 Oct 29 '21 at 15:23
@AlexGordon, however, I just realized that in Windows PowerShell (no longer in PowerShell Core) `Get-Content -Raw $file` can _situationally_ fail (depending on how the `FileInfo` instance was obtained, it may stringify to the _mere file name_ rather than the _full path_), so I've updated the answer to use the robust `$file | Get-Content -Raw` – mklement0 Oct 29 '21 at 15:33
P.S., @AlexGordon: If you do want to _merge_ the content of _multiple_ files and pass that as a single string, use `(($sqlFiles | Get-Content -Raw) -join "\`n")` – mklement0 Oct 29 '21 at 16:27
@AlexGordon, looking at your previous questions suggests that `$filesconcatenated` may already be the merged content from multiple files, in which case you don't need `Get-Content` and can pass the value as-is - assuming that it is a _single_ string. In essence, testing the first argument with `-is [string]` must return `$true`, and `-is [System.Data.SqlClient.SqlConnection]` must be `$true` for the second one in order for the constructor call to succeed. Let's try to bring closure to this and some of your previous questions: Please accept answers or provide feedback. – mklement0 Oct 30 '21 at 17:53

Phil Nicholas · Answer 2 · 2021-10-29T04:28:25.323

I see quite a few examples here suggesting Using-Object but that's custom function (see some of the other SO pages) to simulate the using() block in C#. Powershell doesn't have that. You simply need to declare the SqlConnection and SqlCommand objects as an objects. Don't forget to dispose of them. I strongly recommend a try-catch-finally block, and be sure to dispose of the SqlConnection object in the finally block.

I think you're looking for this. (Warning: I didn't run test this, but it's very close):

try
{
  $conn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
  $conn.Open();
  $tran = $conn.BeginTransaction()
  foreach ($file in $sqlFiles)
  {
    Write-Host "file: " $file.Name
    $comm = New-Object System.Data.SqlClient.SqlCommand($file, $conn);
    $comm.Transaction = $tran
    [void]$comm.ExecuteNonQuery()
    $comm.Dispose()
  }
  $tran.Commit()
}
catch
{
  # Handle your error here.
}
finally
{
  # Make sure the SQL connection closes.
  $conn.Dispose()
}

How to execute SqlCommand in PowerShell?

2 Answers2

Linked