Re: SQLCeResultSet does not update first row

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


<aknittel@xxxxxxxxxxxxx> wrote in message
news:1152542349.591841.229780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


<aknittel@xxxxxxxxxxxxx> wrote in message
news:1152536787.455441.3900@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


<aknittel@xxxxxxxxxxxxx> wrote in message
news:1152498730.284916.275410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?

aknittel@xxxxxxxxxxxxx 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

<aknittel@xxxxxxxxxxxxx> wrote in message
news:1152472840.282962.258970@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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();
}


==============
aknittel@xxxxxxxxxxxxx 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


<aknittel@xxxxxxxxxxxxx> wrote in message
news:1152463443.984085.172140@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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();


}







.

Loading