Query database in background thread

[JC Voon]

Hi All:

I'm new in Threading and Web Services, can someone please verify my
code, i'm not sure whether this is the correct way, althought it is
partially work, but some time it will raise exception at FillDatatable
dr.AcceptChange s, and also the XTraGridControl will not response when
click on the column header to do sorting and filtering.

What i'm trying to do is very simple, i just want to retrieve a very
large table from web service and show it on the GridControl, the query
will be execute in the background thread, so that user is able to
scroll, filter, sort the result on the gridcontrol while the query is
execute in the background


Here is the code:

edtSQL - a multiline text box to enter SQL statement
edtKeyFields - a text box to enter the key fields of the SQL statement
GridControl - I use developer XTraGrid control
btnFillData - Button control
btnStopFill - Button Control
DataAccessWS.Da taAccessService Wse - A web service

Private QueryThread As Thread
Private Delegate Sub FillDataTableDe legate(ByVal table As DataTable)
Private bFilling As Boolean


' when use click this button the background thread will start
' filling data to grid's datasource
Private Sub btnFillData_Cli ck(ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles btnFillData.Cli ck
Dim proxy As DataAccessWS.Da taAccessService Wse = New
DataAccessWS.Da taAccessService Wse

Me.ProgressBar1 .Step = 1
Me.ProgressBar1 .Minimum = 0
' call the web method to calculate number of row will be return
' by the SQL statement
Me.ProgressBar1 .Maximum = CInt(proxy.Exec uteScalar("SELE CT
Count(*) FROM (" & edtSQL.Text & ") as tem"))

GridControl3.Da taSource = Nothing
GridView3.Colum ns.Clear()
QueryThread = Nothing

QueryThread = New Thread(AddressO f QueryDataBase)
QueryThread.IsB ackground = True
QueryThread.Nam e = "QueryThrea d"
QueryThread.Sta rt()
End Sub

' Sub routine used by the Background thread to query database
Public Sub QueryDataBase()
Dim proxy As DataAccessWS.Da taAccessService Wse = New
DataAccessWS.Da taAccessService Wse
Dim doc As XmlDocument
Dim xn As XmlNode
Dim dt As DataTable = New DataTable

If GridControl3.Da taSource Is Nothing Then
' Call the web method to retrieve first page of record
' param1=SQL statement, param2=filter criteria,
' param3=key field, param4=Page size
xn = proxy.RetrieveF irstPageRecord( edtSQL.Text, "",
edtKeyFields.Te xt, 100)
doc = XMLNodeToXMLDoc ument(xn)
LoadDataTableFr omXMLDocument(d t, doc)

' Is is correct to create New FillDataTableDe legate each time
' the BeginInvoke is call ?
Me.BeginInvoke( New FillDataTableDe legate(AddressO f
FillDatatable), New Object() {dt})
End If

While True
' wait until FillDatatable completed
While bFilling
' Is it ok to put this line in the thread function ?
Application.DoE vents()
End While

xn = Nothing
With DirectCast(Grid Control3.DataSo urce, DataTable)
If .Rows.Count > 0 Then
' Call the web method to retrieve next page of record
' param1=Last row in current page, param2=SQL
' statement, param3=filter criteria, param4=key field
' param5=Page size
xn =
proxy.RetrieveN extPageRecord(S aveDataRowAsXML Document(.Rows( .Rows.Count
- 1)), edtSQL.Text, "", edtKeyFields.Te xt, 100)
End If
End With

If Not xn Is Nothing Then
dt.Clear()
doc = XMLNodeToXMLDoc ument(xn)
LoadDataTableFr omXMLDocument(d t, doc)

' if RetrieveNextPag eRecord return 0 row means Eof
If dt.Rows.Count > 0 Then
Me.BeginInvoke( New FillDataTableDe legate(AddressO f
FillDatatable), New Object() {dt})
Else
Exit While
End If

If QueryThread.Thr eadState = ThreadState.Abo rted Then
Exit While
End If
End If
End While
End Sub

' Sub routine that is to be executed on Form's thread
Public Sub FillDatatable(B yVal table As DataTable)
bFilling = True
If GridControl3.Da taSource Is Nothing Then
GridControl3.Da taSource = table.Copy
Me.ProgressBar1 .Value = table.Rows.Coun t
Else
Dim dr As DataRow

With DirectCast(Grid Control3.DataSo urce, DataTable)
.BeginLoadData( )
For i As Integer = 0 To table.Rows.Coun t - 1
dr = .NewRow
CopyDataRow(tab le.Rows(i), dr, "*")
dr.AcceptChange s()
.Rows.Add(dr)

Me.ProgressBar1 .PerformStep()

' With this, the grid is able to response to scrolling
' record using arrow key and mouse,
' but unable to resonse the column header click.
Application.DoE vents()
Next
.EndLoadData()
End With
End If
bFilling = False
End Sub

Private Sub btnStopFill_Cli ck(ByVal sender As System.Object, ByVal
e As System.EventArg s) Handles btnStopFill.Cli ck
QueryThread.Abo rt()
End Sub



Thanks
JCVoon