如何在Powershell中实际取消(设置为空) COM-object属性？

Question

我在使此VBA/VB6断开连接的记录集代码适应Powershell时遇到了问题。设置为Nothing无法翻译：

'disconnect the recordset and close the connection
Set rs.ActiveConnection = Nothing

第一次尝试(使用$null)：

$rs.ActiveConnection = $null

ERROR: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
ERROR: + $rs.ActiveConnection = $null
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : OperationStopped: (:) [], COMException
ERROR:     + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

第二次尝试(不要执行设置为空)：

$conn = new-Object -com "ADODB.Connection"
$rs = New-Object -ComObject "ADODB.Recordset"
$conn.ConnectionString = "Provider=SQLNCLI11;Server=myserver;Database=mydb;Integrated Security=SSPI;"
$conn.Open()
$rs.CursorLocation = 3 # adUseClient '<<<< important!
$rs.Open("select * from mytable where 1<>1", $conn, 2, 4) # adOpenDynamic, adLockBatchOptimistic
#$rs.ActiveConnection = $null
$conn.Close()
$rs.AddNew()
...
ERROR: Operation is not allowed when the object is closed.
ERROR: + $rs.AddNew()
ERROR: + ~~~~~~~~~~~~
ERROR:     + CategoryInfo          : OperationStopped: (:) [], COMException
ERROR:     + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

注意到，我对替代解决方案不感兴趣，比如Invoke-Sqlcmd、ADOR和Out-DataTable。我都试过了，他们不适合我的情况。

$PSVersionTable

Name                           Value                                                                                                                                                                                              
----                           -----                                                                                                                                                                                              
PSVersion                      5.1.14409.1012                                                                                                                                                                                     
PSEdition                      Desktop                                                                                                                                                                                            
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                                                            
BuildVersion                   10.0.14409.1012                                                                                                                                                                                    
CLRVersion                     4.0.30319.42000                                                                                                                                                                                    
WSManStackVersion              3.0                                                                                                                                                                                                
PSRemotingProtocolVersion      2.3                                                                                                                                                                                                
SerializationVersion           1.1.0.1      

这里是供参考的工作源代码(它在Excel中使用ADO6.1作为VBA工作)：

Sub Tester()

    Dim con As ADODB.Connection, rs As ADODB.Recordset
    Dim i As Long

    Set con = getConn()

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient '<<<< important!

    'get an empty recordset to add new records to
    rs.Open "select * from Table1 where false", con, _
             adOpenDynamic, adLockBatchOptimistic

    'disconnect the recordset and close the connection
    Set rs.ActiveConnection = Nothing
    con.Close
    Set con = Nothing

    'add some new records to our test recordset
    For i = 1 To 100
        rs.AddNew
        rs("UserName") = "Newuser_" & i
    Next i

    'reconnect to update
    Set con = getConn()
    Set rs.ActiveConnection = con

    rs.UpdateBatch '<<< transfer to DB happens here: no loop!

    rs.Close 

    'requery to demonstrate insert was successful
    rs.Open "select * from Table1", con, _
            adOpenDynamic, adLockBatchOptimistic

    Do While Not rs.EOF
        Debug.Print rs("ID").Value, rs("UserName").Value
        rs.MoveNext
    Loop

    rs.Close
    con.Close
End Sub

Function getConn() As ADODB.Connection
    Dim rv As New ADODB.Connection
    Dim strConn As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" _
     & "Data Source = " & ThisWorkbook.Path & "\Test.accdb"

    rv.Open strConn
    Set getConn = rv
End Function

更新：最终工作代码(感谢下面的答案)：

$ErrorActionPreference = 'Stop'
$src = Import-CSV -Path 'c:\mydata.csv'
$conn = new-Object -com "ADODB.Connection"
$rs = New-Object -ComObject "ADODB.Recordset"
$conn.ConnectionString = "Provider=SQLNCLI11;Server=myserver;Database=mydb;Integrated Security=SSPI;"
$conn.Open()
$rs.CursorLocation = 3 # adUseClient '<<<< important!

#get an empty recordset with fields defined
$rs.Open("select * from mytable where 1<>1", $conn, 2, 4) # adOpenDynamic, adLockBatchOptimistic

#
#disconnect the recordset
#

# Create a wrapper for the value null as per ZiggZagg's answer https://stackoverflow.com/a/49682554/2746150
[System.Runtime.InteropServices.UnknownWrapper]$nullWrapper = New-Object "System.Runtime.InteropServices.UnknownWrapper" -ArgumentList @($null);
# Get the the type for ADODB.Recordset as per ZiggZagg's answer https://stackoverflow.com/a/49682554/2746150
[Type]$recordSetType = [Type]::GetTypeFromProgID("ADODB.Recordset", $true);
# Write the property ActiveConnection as per ZiggZagg's answer https://stackoverflow.com/a/49682554/2746150
$recordSetType.InvokeMember([string]"ActiveConnection", [System.Reflection.BindingFlags]::SetProperty, [System.Reflection.Binder]$null, [object]$rs, [object[]]@($nullWrapper));
# Close connection
$conn.Close()

#fillup code
Foreach ($row in $src) {
    $rs.AddNew()
    Foreach ($col in $row.psobject.Properties.Name) {
        $fld = $rs.Fields.Item($col)
        $fld.Value = $row.$col
    }
}

#reconnect
$conn.Open()
$rs.ActiveConnection = $conn

#final update
$rs.UpdateBatch()
$rs.Close()

此代码用于100+字段(可变的) 10K-50K行的大容量加载。类似的代码使用Out-DataTable有十倍多的行(因为每个字段都必须定义)，并且具有相似的性能。

Answer 1

我不得不承认，我不确定以下内容是否与您的VBA脚本相同。你介意检查一下它是否能达到你的预期吗？

# Create a wrapper for the value null
[System.Runtime.InteropServices.UnknownWrapper]$nullWrapper = New-Object "System.Runtime.InteropServices.UnknownWrapper" -ArgumentList @($null);
# Get the the type for ADODB.Recordset
[Type]$recordSetType = [Type]::GetTypeFromProgID("ADODB.Recordset", $true);
# Write the property ActiveConnection
$recordSetType.InvokeMember([string]"ActiveConnection", [System.Reflection.BindingFlags]::SetProperty, [System.Reflection.Binder]$null, [object]$rs, [object[]]@($nullWrapper));