Using Delta commands

Data Abstract
1

So I’ve been experimenting with using delta commands to sort of “write back through a join”.

What I mean is that I have a table with several foreign keys into other tables.
I’ve used a manual SQL statement for this table in my schema, which JOINs in the other tables by their foreign keys, so the resulting field list contains all the fields from the table itself but also values from the other tables.

Normally I’d use lookup fields in the datatable, linked to a separate datatables for the foreign key tables. The reason I’m investigating doing it with a JOIN is because, in some cases, the foreign tables could have thousands of rows and retrieving all of these into a datatable purely to serve as the source for a lookup field is inefficient.

Now, in order that I can perform insert, update and delete functions on this table, I’ve then created the delta commands within the schema. This all works correctly and, because I disabled Log Changes on the foreign key fields, these are correctly omitted from the generated SQL in the delta commands.

Now, on the client, if I update the main table and change a foreign key value, I obviously need the server to requery the database to get the new “lookup” value from the foreign key table.
If I call ApplyUpdates with the first refresh parameter set to True then this works, as it basically runs the query again to get the new values.

My first question is, is this the only way of doing this or is there some way I can get the server to requery only the individual record(s) that have been updated?

My second question is, does using the delta commands mean that the generated SQL will always set all field values, even if they haven’t changed? Normally, with AutoSQL, the generated SQL for an UPDATE will only include fields that have actually changed in the SET clause. With a delta command, the manual SQL obviously includes all fields in the SET clause, meaning the generated SQL will include every field, even if it’s just setting it to the existing value. Is there any way to ‘optimise’ this with delta commands?

If neither of the above are possible then fair enough. Basically I’m just experimenting with this approach at the moment and weighing the pros and cons.

Thanks

2

Hi,

You can set Server AutoRefresh to True for fields that may/should return new value after processing delta commands. in this case, new value for such field will be returned in delta change to client-side.

Check more at the Updates article

another way is use table.RefreshFromServer.

how it works: you set new DynamicWhere expression that correspond to record(s) what you want to refresh. it can be something like like (id =1) or (id=100). it receives data from the database to update dataset’s view of data. After receiving it compares existing records with received records and replaces changed records with new ones or inserts new ones.

The table.RefreshRow method does the same for current record: it auto-creates temporary DynamicWhere for current record based on his PK and calls table.RefreshFromServer.

it is as designed. Delta change contains all values.

You can use the Reduced Delta feature.
in this case, delta commands are generated on fly for each change because it requires unique statements for each update change.

3

Thanks, I’ll look into these options.

I’m sure I tried the Server AutoRefresh option but it didn’t work. I assumed this was for server-side calculated fields that are recalculated by the server code, whereas the fields I’m concerned with would only be refreshed by actually re-running the SQL query.

The RefreshFromServer may well be an option though.