How to requery w DataGridView and maintain context?

[RBarryYoung]

I have a DataTable/DataSet from SMO that is being displayed in a
DataGridView. I need to periodically requery the DataTable and
refresh the DGV display with the updated results. This much I can do.

However, I also need it to retain its user context. That is, what
row the user is on (what row or cell is selected) and the current row
sorting from the user clicking on a column. None of the methods that
I have found that can refresh the data from the database will preserve
these, except Merge (below) that preserves the ordering column and the
row *offset* (not the selected row though), but Merge also causes all
of the records to be duplicated every requery.

Can anyone help me to do this?

Here is my current code:

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick
'execute the query and display it's results
Dim DS As Data.DataSet

Timer1.Stop()

If grdOut.DataSource Is Nothing Then
' first time only
DS = smoDB.ExecuteWithResults(strQuery)
grdOut.DataSource = bndSource
m_bndSource.DataSource = DS.Tables(0)
grdOut.DataSource = bndSource
Else
' requery & refresh
DS = smoDB.ExecuteWithResults(strQuery)
Dim prvDs As Data.DataTable = m_bndSource.DataSource
prvDs.DataSet.GetChanges()
prvDs.Merge(DS.Tables(0))
End If

grdOut.Update()
End Sub

 

[RBarryYoung]

Another version that I tried is the following in the Refresh branch:

Else
' requery & refresh
DS.Tables(0).Load(DS.CreateDataReader(),
LoadOption.OverwriteChanges)
End If

This version never updates the data displayed in the DataGridView.