全局变量在循环外丢失值

I'm working on a script to extract data from BLOBs in a SQL database. The extraction process works great. I want to add some sort of progress indication to the script. I have a total record count from a SQL query, and an incremental counter that increases for each file exported. The incremental counter works, but the total record count - which I attempted to assign to a global variable - does not seem to hold its value. Am I declaring it incorrectly?

我正在编写一个脚本来从SQL数据库中的BLOB中提取数据。提取过程很有效。我想在脚本中添加某种进度指示。我有一个SQL查询的总记录数,以及为每个导出的文件增加的增量计数器。增量计数器有效,但总记录数 - 我试图分配给全局变量 - 似乎没有保持其值。我不正确地宣布它吗?

## Export of "larger" Sql Server Blob to file            
## with GetBytes-Stream.         
# Configuration data     
$StartTime = Get-Date
$Server = "server";
$UserID = "user";
$Password = "password";
$Database = "db";
$Dest = "C:\Users\me\Desktop\Test\";
$bufferSize = 8192; 

# Counts total rows
$CountSql = "SELECT Count(extension) as countall from 
                (
                    SELECT  p.[people_id], right(pi.[file_name],4) as extension
                    FROM dbo.pictures as pi 
                    INNER JOIN dbo.people AS p ON p.person_picture = pi.pictures_id
                    where left([image_type], 5) = 'image'
                ) as countall"

# Selects Data
$Sql = "SELECT p.[people_id], pi.[image_file], right(pi.[file_name],4), ROW_NUMBER() OVER (ORDER BY people_id) as count
        FROM dbo.pictures as pi 
        INNER JOIN dbo.people AS p ON p.person_picture = pi.pictures_id
        where left([image_type], 5) = 'image'";    

# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=False;" + 
                        "User ID=$UserID;" +
                        "Password=$Password;" +           
                        "Initial Catalog=$Database";            
$con.Open();            

# New Command and Reader for total row count
$CountCmd = New-Object Data.SqlClient.SqlCommand $CountSql, $con;
$crd = $CountCmd.ExecuteReader();
While ($crd.Read())
{
    $crd.GetValue($global:1)
}
$crd.Close();

# New Command and Reader for rest of data     
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;            
$rd = $cmd.ExecuteReader();            

# Create a byte array for the stream.            
$out = [array]::CreateInstance('Byte', $bufferSize)            

# Looping through records
While ($rd.Read())            
{            
    $total = $global:1
    $counter = ($rd.GetValue(3));
    Write-Output ("Exporting $counter of $total`: {0}" -f $rd.GetGUID(0));

    # New BinaryWriter            
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetGUID(0) + $rd.GetString(2)), Create, Write;            
    $bw = New-Object System.IO.BinaryWriter $fs;            

    $start = 0;            
    # Read first byte stream            
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    While ($received -gt 0)            
    {            
       $bw.Write($out, 0, $received);            
       $bw.Flush();            
       $start += $received;            
       # Read next byte stream            
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    }            

    $bw.Close();            
    $fs.Close();            
}            

# Closing & Disposing all objects            
$fs.Dispose();            
$rd.Close();            
$cmd.Dispose();            
$con.Close();            
$EndTime = Get-Date
$TotalTime = $EndTime - $StartTime    
Write-Host ("Finished in {0:g}" -f $TotalTime)

OUTPUT

PS C:\Users\me> C:\Scripts\ExportImagesFromNTST.ps1
21380
Exporting 1 of : 3089b464-e667-4bf4-80b3-0002d582d4fa
Exporting 2 of : 04cf7738-ae19-4771-92b8-0003c5f27947
Exporting 3 of : 94485b5d-fe71-438d-a097-000ad185c915

and so on. 21380 should be $1 which should also be $total.

等等。 21380应该是1美元,也应该是$ total。

1 个解决方案

#1

I think PetSerAl hit the nail on the head here. You create a SqlCommand object ($CountCmd), and from that create a SqlDataReader ($crd), and then tell $crd to use the GetValue() method that accepts an integer as a parameter, so that it knows which column to return the value of, but you reference a global variable with a name of '1', which is never defined, so you effectively pass $null to that method, so it doesn't get any value. I'm honestly surprised that it doesn't throw errors at you right there. You would probably want to just pass the integer 1 as the argument for that method, and assign it to $Total in the While loop. I'm honestly guessing here, but from what I see I think it should be:

我认为PetSerAl在这里击中了头。您创建一个SqlCommand对象($ CountCmd),并从中创建一个SqlDataReader($ crd),然后告诉$ crd使用接受整数作为参数的GetValue()方法,以便它知道返回哪个列value的值,但你引用一个名为'1'的全局变量,它永远不会定义,因此你有效地将$ null传递给该方法,因此它不会获得任何值。我真的很惊讶它不会在那里给你带来错误。您可能希望只传递整数1作为该方法的参数,并将其分配给While循环中的$ Total。老实说,我猜这里,但从我看到的,我认为它应该是:

$crd = $CountCmd.ExecuteReader();
While ($crd.Read())
{
    $Total = $crd.GetValue(0)
}
$crd.Close();

I'm pretty sure that will assign the value of the first column (which for that sql command should just be 1 row with 1 column, right? Just the total count?), anyway, assign the first column's value for the current row to $Total. Then later you can reference $Total just fine to update your progress.

我很确定会分配第一列的值(对于那个sql命令应该只是1行,1列,对吗?只是总计数?),无论如何,将当前行的第一列值分配给$总。然后您可以参考$ Total来更新您的进度。

You sir, need to look into the write-progress cmdlet if you want to track progress, it's perfect for your script.

您是先生,如果要跟踪进度,需要查看write-progress cmdlet,它非常适合您的脚本。