Is it possible to implement such deferred read scenario?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
AG
Posts: 13
Joined: Tue 11 Mar 2008 11:26

Is it possible to implement such deferred read scenario?

Post by AG » Thu 27 Mar 2008 18:21

Hi.

I my project I need to implement following deferred read scenario:
1. I have a table with fields A,B,C,D,E. Field A is auto-increment ID, field B is unique name, fields C,D,E are large text fields.
2. Only fields A and B should be displayed in list, but before editing all fields for particular record should be fetched from DB and after editing stored back to DB.
3. Update should be based on A field only (ID) and only changed fields should be updated. So if two users edit the same record, but different fields, both changes should be applied.

Unfortunately I did not find any acceptable solution. What I have tried:

1. ReadComplete - absolutely doesn't work with typed dataset - generated SELECT completely wrong. With untyped dataset works, but selects C,D and E fields only (I need also refresh field B). Also after editing it's impossible to save changes in fields C,D,E - only field B appears in generated UPDATE (UpdateCommand is empty to achieve requirement 3). In WHERE in addition to A field, B field is also used (UpdatingKeyField is set to field A only).

2. RefreshRow - I'm able to select all required fields using RefreshingFields, but the same problem with updating DB as above.

3. Master (short record) - Details (full record). No problem with updating except A and B fields in WHERE instead of expected A. But additional steps for synchronization needed and additional datatable for just one record...

To sum-up: proposed in MyDirect deferred read feature does not work when data should be edited and saved back. There is odd behavior of CommandBuilder, when not only defined UpdatingKeyField appears in WHERE part of UPDATE, but also another unique field (B in my case).

May be there are some other ways to implement such scenario or avoid issue with UPDATE command generation?

Thanks

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 28 Mar 2008 14:22

I can suggest the following scenario:
- create 2 DataGridView components (dataGridView1 and dataGridView2);
- add a MySqlConnection, a MySqlDataTable (mySqlDataTable1) and a DataLink (dataLink1) components onto a form;
- set mySqlDataTable1 as DataSource for dataGridView1. Edit columns in dataGridView1 and leave only “A” and “B” columns;
- likewise configure dataLink1 and dataGridView2;
- don't forget to set Synchronised property of the dataLink1 to true;

So now we have the needed master-details tables on the form!
Using only design time and writing no code. Cool, isn't it? Let's move on:
I suggest to write some code here:

Code: Select all

private void Form1_Load(object sender, EventArgs e) {

            mySqlDataTable1.FetchAll = true;
            mySqlDataTable1.NonBlocking = false;
            mySqlDataTable1.Active = true;
            mySqlDataTable1.CachedUpdates = false;          
        }
At this point you may want to change the code (corresponding to your needs).
- select “configure mySqlDataTable1...”, choose Command Generator tab;
- press the Get Table Fields button and uncheck all Updating columns but “A”;
- click on Generate Commands button;

Well, that's all.
Does this suit you?

AG
Posts: 13
Joined: Tue 11 Mar 2008 11:26

Post by AG » Mon 31 Mar 2008 04:42

Hi Alexey,

The primary idea was:
1. To avoid loading large text fields until they are really need for editing
2. Do not overwrite data on server, which was not changed on client. Btw it's one of standard optimistic concurrency behavior.

Unfortunately your solution does not meet both criteria.
Actually, if declared in MyDirect cool features like ReadComplete, RefreshRow and UpdatingKeyFields worked properly, I would not have any need to find any workaround. Especially it concerns UpdatingKeyFields, because it seems like this setting is ignored during update command generation.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Mon 31 Mar 2008 13:18

I agree, you are right.
Could you please send me a small test project to reproduce the problems
with ReadComplete (RefreshRow, UpdatingKeyFields) features?
It is desirable to use 'test' schema objects, otherwise include the
definition of your own database objects.
We will make everything possible to solve the problem ASAP.

Post Reply