Keep GUI responsive during SQL Backup

Question

VB.NET 2.0:

I'm issuing a backup command to a SQL server from a VB.NET application. I'm capturing the messages it sends out and appending them to a multiline textbox.

What I'm trying to do, though, is to allow the application to keep responding to GUI control events (mainly so that the user can resize the output window or cancel the backup). So I use BeginExecuteReader and spin in a loop with Application.DoEvents(). But it seems that as soon as the backup starts issuing its print statements, I get IAsyncResult.IsCompleted = True and it drops down to EndExecuteReader, and the GUI is now locked up again. How can I get it to stay in the loop until the backup command completes and yet still get those output statements and keep the GUI responsive? Thanks.

Here's my code:

' Enable retrieve print statements from the server'
AddHandler oConn.InfoMessage, AddressOf LogToBufferHandler

strSQL &= vbNewLine & "begin try"
strSQL &= vbNewLine & " declare @BackupName as varchar(255)"
strSQL &= vbNewLine & " declare @BackupDesc as varchar(255)"
strSQL &= vbNewLine & " declare @backupTime as varchar(50)"
strSQL &= vbNewLine & " set @backupTime = (select convert(datetime, getdate(), 100))"
strSQL &= vbNewLine & " set @BackupName = (SELECT '[' + db_name() + '] Full Backup')"
strSQL &= vbNewLine & " set @BackupDesc = (SELECT 'Automated full backup of [' + db_name() + '] on ' + @backupTime + '.')"
strSQL &= vbNewLine & " "
strSQL &= vbNewLine & " BACKUP DATABASE [#Database#]"
strSQL &= vbNewLine & " TO DISK = @BackupFullPath"
strSQL &= vbNewLine & " WITH stats,"
strSQL &= vbNewLine & "      NAME = @BackupName,"
strSQL &= vbNewLine & "      DESCRIPTION = @BackupDesc;"
strSQL &= vbNewLine & " select [IsSuccessful] = 1"
strSQL &= vbNewLine & " end try"
strSQL &= vbNewLine & " begin catch"
strSQL &= vbNewLine & " SELECT [IsSuccessful] = 0"
strSQL &= vbNewLine & " end catch"

'Workaround: Backup Database requires the name of the object, not a string'
'            and I dont want to use dynamic SQL.'
strSQL = strSQL.Replace("#Database#", sb.InitialCatalog)

oConn.Open()
oCmd = New SqlCommand()
oCmd.Connection = oConn
oCmd.CommandText = strSQL
oCmd.CommandType = CommandType.Text
oCmd.Parameters.AddWithValue("@BackupFullPath", backupFullPath)
oCmd.CommandTimeout = 60 * 5

'Spin until complete, cancel, or timeout'
Dim result As IAsyncResult = oCmd.BeginExecuteReader(CommandBehavior.CloseConnection)
While Not result.IsCompleted
    Application.DoEvents()
    If blnCancel Then
        oCmd.Cancel()
    End If
    System.Threading.Thread.Sleep(50)
End While
Try
    oDataReader = oCmd.EndExecuteReader(result)
    oDataTable.Load(oDataReader)

    'Get results'
    ' (unfourtunately, you cannot do BeginExecuteScalar ASync in .Net 2.0,'
    ' so we are using a DataTable first column, row)'
    If oDataTable IsNot Nothing _
    AndAlso oDataTable.Rows.Count > 0 _
    AndAlso oDataTable.Columns.Contains("IsSuccessful") _
    AndAlso oDataTable.Rows(0).Item("IsSuccessful") = 1 Then
        eBackupResult = BackupStatus.Succeeded
        returnPath = backupFullPath 'Only set return path if the backup succeeded'
    Else
        eBackupResult = BackupStatus.Failed
    End If
Catch ex As Exception
    If Not ex.Message.Contains("cancelled by user") Then Throw ex
    eBackupResult = BackupStatus.Canceled
End Try

It seems stackoverflow's syntax highlighter doesn't like VB, wow... sorry guys — John, Dec 07 '12 at 17:27

score 1 · Accepted Answer · answered Dec 07 '12 at 18:08

1

Polling loops (DoEvents) are considered evil for many reasons. It is probably the easiest way for you to switch to BackgroundWorker and abandon the nasty Begin-poll-End scheme.

If you want to keep it, here is the bug: Accepting the reader can be quick but that doesn't mean that all results have arrived (let me illustrate: what if you had queried 1TB of data? - reading that is a long process). You need to Read in an async (polling) way too. Now the thing gets out of hand. Just abandon it.

In other words, oDataTable.Load is blocking which is hard to fix.

answered Dec 07 '12 at 18:08

usr

168,620
35
240
369

So the BeginASync read in this case would've let the UI remain responsive until the first results started to stream? Once they stream, then it's up to me to to deal with the results? – John Dec 07 '12 at 18:15
It completes with the first result arriving (this makes sense - maybe the user wants the first result right now, or there are terabytes of data which makes buffering impossible). Like I said, you need to make reading asynchronous, too. – usr Dec 07 '12 at 18:35

score 0 · Answer 2 · answered Dec 07 '12 at 17:31

0

You can use TASKS for this. A task is similar in concept to a thread. By putting your code into a task the main thread can continue to run (which is the thread that takes user input and responds to resize commands).

Here's an article that talks more about tasks and has a VB.NET example.

http://www.dotnetcurry.com/ShowArticle.aspx?ID=491

Have fun!

answered Dec 07 '12 at 17:31

jmrnet

548
3
11

1

Sorry, I should've mentioned this: VB.NET 2.0. – John Dec 07 '12 at 17:41

score 0 · Answer 3 · answered Dec 07 '12 at 17:54

0

BackGroundWorker and ReportProgress

BackgroundWorker Class

answered Dec 07 '12 at 17:54

paparazzo

44,497
23
105
176

Yeah, but the advantage of BeginExecuteReader is less complexity because it's all on the GUI thread. – John Dec 07 '12 at 17:58
OK less complex and hangs. Is on the GUI thread when you want a responsive GUI a good thing? – paparazzo Dec 07 '12 at 18:44

Keep GUI responsive during SQL Backup

3 Answers3