Ginny, thanks for the entry on adding/deleting.
I tried refreshing the SqlCeresultset after updating the data source,
but I took an approach that works well, but I don't know why.
It turns out I didn't have to close the RS, nor did I have to re-issue
the ExecuteResultSet query. Amazingly, all I had to do was re-bind the
datagrid after the update to SQLCe was performed, as in:
dgProducts.DataSource = SqlCeResultSet.ResultSetView;
This leads me to conclude:
- The RS IS sensitive to the changes in the underlying datasource, the
datagrid is NOT
- The datagrid VISIBLE rows, are not being refreshed unless, as you
stated in an earlier post, you MOVE through (read what I found: CLICK
THROUGH) the rows that changed in the datasource.
The only undesirable effect is that, of course, re-binding (as I knew
from re-binding using DataSets) causes the currency manager position to
move to the first row. That's fine, if the app is not depending on the
selected datagrid row to, for example, display detail data. However,
that is exactly how our apps work.
Is there no finer-grained mechanism to tell th datagrid to refresh ONLY
the rows that were affected, either with a bindingsource or
programmatically moving through the row(s) that were affected??
Ginny Caughey [MVP] wrote:
I haven't noticed any flashing when I do this, but if you do when you
get
a
chance to try it, please let me know.
Although SqlCeResultSet is a DataReader and inherits from
SqlCeDataReader,
reissuing that ExecuteResultSet query should be quite fast since you
don't
need to iterate though all the records that match the query. The
DataGrid
control (when you bind it to the new result set) will only fetch the
data
for the visible rows, so that should also be pretty fast.
Let me know how this works out for you,
--
Ginny Caughey
Device Application Development MVP
One observation here is that when I did close the resultset, the
datagrid refreshed its view with "X's". I would assume (and will try
tonight), that if I close and then immediately re-issue the
ExecuteResultSet, there will be some undesirable flashing. If so,
how
can I prevent such flashing? That's why I asked if there was a more
finer-grained row relative way of coaxing the datagrid to display
the
changes only to that row.
However, what I think you are saying here is that if I want to buy
into
the use of SqlCeResultSet, I have realize that what it is doing
behind
the scenes is entirely dependent on re-issuing a DataReader query on
the underlying source, so that's what I have to do.
Ginny Caughey [MVP] wrote:
As far as I've been able to figure out, ExecuteResultSet is exactly
what
you
want. Once you get the new result set from doing that, then when
you
bind
that new result set to the grid, you'll see the new data, and the
whole
operation should be pretty fast. You can use the old
SqlCeConnection
object
and SelCeCommand object if you choose to (perhaps using class-level
variables for those), but you'll have a new result set each time.
Make
sure
you close the old result set (which calls Dispose internally)
before
issuing
ExecuteResultSet again each time so you don't leak resources.
--
Ginny Caughey
Device Application Development MVP
Ahh, OK. I began to realize this last night. However, mere
scrolling
through the datagrid doesn't show the updated row ("moving around
in
it" as you say). It seems the user has to click THRU the updated
row
to
see the row change.
My old DataSet model does raise events to update the row in the
bound
DataSet when it updates the underlying database, so I can I could
adapt
that for SQLCEResult.
Once the UI gets the event, is the most efficient way to do this
is
just re-issue the SqlCeCommand.ExecuteResultSet original query,
or
is
there some finer-grained row-relative mechanism to accomplish the
same
thing? The effect I am trying to achieve is the same one I get
updating
the DataSet for a bound datagrid. That is, if the user is viewing
the
rows with the one that changes, they see an update. If not, when
they
scroll to it, they see the updated row.
I had high hopes that SQLCeResultSet would solve my data capacity
problems with DataSet (3 copies in memory), but you seem to
indicate
that if rows are added and possibly deleted, I will have trouble
pursuing this model with SQLCeResultSet as the key cacheing
mechanism.
Is this true?
Ginny Caughey [MVP] wrote:
Yes, this is what I meant although I didn't realize you weren't
updating
the
result set in the grid, but it's still the same principle. The
result
set
is
sensitive in that any changes made by anything else are
immediately
reflected in the underlying data, but the grid doesn't know to
go
get
new
data until you move around in it.
As for what to do about this, one approach is to periodically
have
the
grid
refresh itself from the data on a timer. The other approach
would
be
to
have
the operation that makes the changes raise an event that
ultimately
causes
the grid to get new data.
What's different about a DataSet is that even if you tell a grid
to
refresh
itself from the DataSet, the DataSet would still have the old
data
in
it.
The other difference with a DataSet is that you can add rows to
a
DataSet
bound to a grid and that works the way you expect, but you
shouldn't
use
databinding with a result set that you need to add rows to.
--
Ginny Caughey
Device Application Development MVP
Weird. I clicked off row 1 in the datagrid (by clicking on row
2),
clicked again on row 1, and the value changed!
Ginny, is this what you meant by "When you move to a new row
in
the
DataGrid, the grid calls EndEdit which calls Update". I
thought
the
Datagrid would update by itself without any user interaction,
the
same
way it does when it is bound to a DataSet and you update a row
in
the
DataSet.
Why does it behave the way it does? How can I get it to
detect a
change and move to the row that was changed?
(e-mail address removed) wrote:
Actually, what I tried to do was ditch the loop and just
update
the
first row as in:
sqlCeCommand = sqlCEConnection.CreateCommand();
sqlCeCommand.CommandText = "UPDATE Products SET [Product
Name] =
'PPP'
WHERE
[Product ID] = 1";
int recs = sqlCeCommand.ExecuteNonQuery();
sqlCeCommand.Dispose();
I have stepped through this code and recs = 1.
Also, Product ID 1 (Product Name field) HAS been updated
"PPP"
in
the
database as independently confirmed by taking the .sdf file
and
reading
it directly with VS 2005.
Graham McKechnie wrote:
Are you sure i in your loop matches your product id. Have
you
stepped
through it in the debugger. What is the result of recs when
cmd.ExecuteNonQuery excutes for your problem records.
Why don't you ditch the data binding and try it manually?
Graham
Here is something even more bizarre.
If I take the following code out of the Form constructor,
create a
button for it, and put the code in the button's click
event,
only
rows
11 thru 20 are updated in the datagrid.
for (int i = 1; i <= 20; i++)
{
sqlCeCommand = sqlCEConnection.CreateCommand();
sqlCeCommand.CommandText = "UPDATE Products SET
[Product
Name]
=
'DDD'
WHERE [Product ID] = " + i.ToString();
int recs = sqlCeCommand.ExecuteNonQuery();
sqlCeCommand.Dispose();
}
==============
(e-mail address removed) wrote:
Ginny, thanks for replying on Sunday!
Maybe I should detail what I am expecting.
I am not using SQLCeResultSet to update the row, or any
other
object
(like CurrencyManager) to update the Datagrid, I am
updating
the
data
source (the first 20 rows in the database with the usual
SQL
Update
statement).
I am expecting to see the datagrid update all 20 rows
automatically
as
a result of that because it is using
ResultSetOptions.Sensitive.
It
shows rows 2-20 are updated with "XXX", but not row 1.
This
works
fine
with the Datagrid bound to a table in a dataset.
Ginny Caughey [MVP] wrote:
When you move to a new row in the DataGrid, the grid
calls
EndEdit
which
calls Update. If you want to update only a single row,
you
need
to
call
EndEdit yourself.
--
Ginny Caughey
Device Application Development MVP
Can someone explain to me why the first row in the
result
set
is
not
updated in the bound datagrid? When I inspect the
database,
ALL
rows
(ProductID 1 to 20) have been updated as expected.
If
I
take
the
loop
out and just updated row 1, it will not be reflected
in
the
grid.
This
ONLY happens with the first row.
It is not an option to use the
SQLCeResultSet.SetValue,
as
the
updates
to SQLCE will occur asychronously in another class.
The
whole
point
in
binding and using ResultSetOptions.Sensitive was
that
any
changes in
the data source would be reflected.
===============================================
Assembly myAssembly =
Assembly.GetExecutingAssembly();
string DBFolder =
Path.GetDirectoryName(myAssembly.GetName().CodeBase);
sqlCEConnection = new SqlCeConnection("data
source='" +
DBFolder
+
"\\"
+ "Northwind.sdf" + "'; mode=Exclusive;");
sqlCEConnection.Open();
sqlCeCommand = sqlCEConnection.CreateCommand();
sqlCeCommand.CommandType =
System.Data.CommandType.Text;
sqlCeCommand.CommandText = "SELECT * FROM Products
ORDER
BY
[Product
ID]";
sqlCeResultSet =
sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable
|
ResultSetOptions.Sensitive |
ResultSetOptions.Updatable);
dgProducts.DataSource =
sqlCeResultSet.ResultSetView;
for (int i = 1; i <= 20; i++)
{
sqlCeCommand =
sqlCEConnection.CreateCommand();
sqlCeCommand.CommandText = "UPDATE Products
SET
[Product
Name]
= 'XXX'
WHERE [Product ID] = " +
i.ToString();
int recs = sqlCeCommand.ExecuteNonQuery();
sqlCeCommand.Dispose();
}
