How to execute SqlCommand in PowerShell?-CodePudding

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?

CodePudding user response：

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 (Get-Content -Raw $file), $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((Get-Content -Raw $file), $conn)

CodePudding user response：

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()
}