By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,570 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,570 IT Pros & Developers. It's quick & easy.

Query database in background thread

P: n/a
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.AcceptChanges, 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.DataAccessServiceWse - A web service

Private QueryThread As Thread
Private Delegate Sub FillDataTableDelegate(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_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnFillData.Click
Dim proxy As DataAccessWS.DataAccessServiceWse = New
DataAccessWS.DataAccessServiceWse

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.ExecuteScalar("SELECT
Count(*) FROM (" & edtSQL.Text & ") as tem"))

GridControl3.DataSource = Nothing
GridView3.Columns.Clear()
QueryThread = Nothing

QueryThread = New Thread(AddressOf QueryDataBase)
QueryThread.IsBackground = True
QueryThread.Name = "QueryThread"
QueryThread.Start()
End Sub

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

If GridControl3.DataSource 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.RetrieveFirstPageRecord(edtSQL.Text, "",
edtKeyFields.Text, 100)
doc = XMLNodeToXMLDocument(xn)
LoadDataTableFromXMLDocument(dt, doc)

' Is is correct to create New FillDataTableDelegate each time
' the BeginInvoke is call ?
Me.BeginInvoke(New FillDataTableDelegate(AddressOf
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.DoEvents()
End While

xn = Nothing
With DirectCast(GridControl3.DataSource, 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.RetrieveNextPageRecord(SaveDataRowAsXMLDocum ent(.Rows(.Rows.Count
- 1)), edtSQL.Text, "", edtKeyFields.Text, 100)
End If
End With

If Not xn Is Nothing Then
dt.Clear()
doc = XMLNodeToXMLDocument(xn)
LoadDataTableFromXMLDocument(dt, doc)

' if RetrieveNextPageRecord return 0 row means Eof
If dt.Rows.Count > 0 Then
Me.BeginInvoke(New FillDataTableDelegate(AddressOf
FillDatatable), New Object() {dt})
Else
Exit While
End If

If QueryThread.ThreadState = ThreadState.Aborted 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(ByVal table As DataTable)
bFilling = True
If GridControl3.DataSource Is Nothing Then
GridControl3.DataSource = table.Copy
Me.ProgressBar1.Value = table.Rows.Count
Else
Dim dr As DataRow

With DirectCast(GridControl3.DataSource, DataTable)
.BeginLoadData()
For i As Integer = 0 To table.Rows.Count - 1
dr = .NewRow
CopyDataRow(table.Rows(i), dr, "*")
dr.AcceptChanges()
.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.DoEvents()
Next
.EndLoadData()
End With
End If
bFilling = False
End Sub

Private Sub btnStopFill_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnStopFill.Click
QueryThread.Abort()
End Sub

Thanks
JCVoon
Nov 21 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.