Why is this powershell script taking so long? It is crashing my GUI-CodePudding

$connectionString = "Data Source=iaosd;Initial Catalog=fasdasd;User ID=sdfsdfsfd;Password=sd0fuisdofij;Connect Timeout=60"

$timer = [Diagnostics.Stopwatch]::StartNew()

# test db connectivity
try {
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection -ErrorAction Stop
    $sqlConnection.ConnectionString = $connectionString
    $sqlConnection.Open()
    $sqlConnection.Close()
    Write-Host "connection successful"
} catch {
    Write-Host "ERROR"
}

$timer.stop() 

Write-Host $timer.Elapsed

Hi, This code actually works immediately if $dbServer is localhost and the other details are correct. But when I change the $dbserver to some random text it takes around 1 minute to complete and it crashes my gui. If I don't put any text in $dbServer the try catch works as expected

"Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible

CategoryInfo : NotSpecified (:) [], MethodInvocationException
Fully QualifiedErrorId : SqlException "

CodePudding user response：

Calling the .Open() method from SqlConnection will block your thread which explains why your GUI app is crashing.

The alternatives you could use are either, opening the connection inside a Runspace which runs in a different thread hence the GUI wouldn't freeze. If you decide to go this route, this answers can help you out with the code:

Otherwise, the other alternative you have could be using the OpenAsync() method from the class, and wait for the Task to complete using a loop, this would block the thread but only for milliseconds, 200 to be precise in this example. It's also worth noting you could reduce the TimeOut by changing Connect Timeout=60 in your connection string.

try {
    $connectionString = "Data Source=iaosd;Initial Catalog=fasdasd;User ID=sdfsdfsfd;Password=sd0fuisdofij;Connect Timeout=10"
    $sqlConnection    = [Data.SqlClient.SqlConnection] $connectionString

    $task = $sqlConnection.OpenAsync()

    do {
        $response = $task.Wait(200)
        # Assuming this would be an event of a WinForms App,
        # here you could use:
        # [System.Windows.Forms.Application]::DoEvents()
        # To gain control over your form while waiting.
        # Not the best of practices though useful for simple events.
    } until($response)

    $task.GetAwaiter().GetResult()
}
catch {
    $w32except = $_.Exception.InnerException.InnerException.InnerException
    if($w32except -is [ComponentModel.Win32Exception]) {
        return Write-Error $w32except
    }
    Write-Error $_
}
finally {
    $sqlConnection, $task | ForEach-Object Dispose
}

I've added inline comments assuming this could be an event of a WinForms App, though I definitely recommend you to read Remarks from Application.DoEvents Method in detail.