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

Question

$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 "

Sorry, I tried your code, and it worked fine. If I changed the $dbserver to something invalid, the call to $sqlConnection.Open() throws the exception, it is caught by the catch, and 'ERROR' is printed. — toastifer
– toastifer, Commented Oct 13, 2022 at 21:51
Write-Host "[$connectionString]" to check if it is OK. Check from another computer. — filimonic
– filimonic, Commented Oct 13, 2022 at 22:18
@toastifer Oh. What version of Powershell are you using? I'm currently using 5.1.19041.1682 and Powershell is hanging on $sqlConnection.Open() — Tom
– Tom, Commented Oct 13, 2022 at 22:38
Works (well, the catch block is executed) here as well on Windows PowerShell 5.1.22000.832 if I just cut & paste your sample code verbatim. Have you got an example of a literal connection string (i.e. just plain text, no variables) that fails for you? — mclayton
– mclayton, Commented Oct 13, 2022 at 22:40
Data Source=iaosd;Initial Catalog=fasdasd;User ID=sdfsdfsfd;Password=sd0fuisdofij;Connect Timeout=60. Also I'm doing some further tests right now. This section of code does crash my GUI but when I isolate this code (in a separate script) it does eventually hit the catch however it takes about 1 minute to do this (I timed it with Diagnostics.Stopwatch]. I have tested this 4 times and each time it took exactly 1 minute to complete +- 30ms. — Tom
– Tom, Commented Oct 13, 2022 at 22:53

Santiago Squarzon · Accepted Answer · 2023-04-26 21:33:25Z

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, these 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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related