DataSet Updates to/from Sql Server

[Guest]

I'm creating a simple VB.Net application that uses a DataGridView in a form
to show records originating in a Sql database. I have two questions related
to
keeping the data current and synchronized between the DataGridView and the
Sql database.

1) What's the preferred method of invoking the SqlDataAdapter.Update method?
I'd like it to be called anytime the DataTable in my DataSet has changed. I
have added a handler for the DataTable.RowChanged event and am calling it
there.

2) When the data changes in the source Sql Database, how do I propagate
those changes to my DataSet? I seem to remember that the old RecordSet would
do this automatically by declaring a "dynamic" cursor, but I don't see the
equivalent in a DataSet.

TIA

 

[W.G. Ryan - MVP]

I'm creating a simple VB.Net application that uses a DataGridView in a
form
to show records originating in a Sql database. I have two questions
related
to
keeping the data current and synchronized between the DataGridView and the
Sql database.

1) What's the preferred method of invoking the SqlDataAdapter.Update
method?
I'd like it to be called anytime the DataTable in my DataSet has changed.
I
have added a handler for the DataTable.RowChanged event and am calling it
there.

--Preferred is in the eye of the beholder. Let's say that your system is
totally 'real time' and you need updates propogated immediately. Then you
probably want to invoke update when the row changes as you're doing. Let's
say however that you want to let users grab data and use your app all day
even fi the DB is down. Then calling it at the close of business would be
better. These are both simplistic scenarios but I think you get my point.

2) When the data changes in the source Sql Database, how do I propagate
those changes to my DataSet? I seem to remember that the old RecordSet
would
do this automatically by declaring a "dynamic" cursor, but I don't see the
equivalent in a DataSet.

--You can use a SqlNotification but you don't want to use one if this is an
app that needs to scale past one developer working in debug mode. Ok, maybe
that's a bit of an overstatement but if this is a typical winforms scenario,
adding a bunch of notifications is going to cause some serious strain on
your resources. You're working in a disconnected mode in general so getting
immediate notification of changes in the underlying data is anthitetical to
the architecture of ADO.NET. Polling for changes or checking for them before
you try updates is probably going to be the way to go in this case, but it
really will depend on your app and how it's being used. SqlNotifications can
be very effective solutions in an ASP.NET scenario for instance, where the
same dataset/datatable is being shared - you can register an notification
and have it propogate when any changes are made, then your code can
reference the shared dataset/datatable property. They are very powerful and
effective but easy to misuse or overdo