How to generate update command with PgSqlDataTable?

belidzs · Post by **belidzs** » Tue 20 Dec 2011 16:32

I'm wondering if it's possible to generate update and select commands with a PgSqlDataTable class? I've filled connection, columns, contraints, primary key, selectcommand, refreshingfields, refreshmode, updatingfields, updatingkeyfields and updatingtable.
Is there a member of PgSqlDataTable which generates the insertcommand and updatecommand for me? It would be nice to only update fields which have been modified before.

Pinturiccio · Post by **Pinturiccio** » Thu 22 Dec 2011 09:48

overtime wrote:I'm wondering if it's possible to generate update and select commands with a PgSqlDataTable class?

You can generate commands in design time. Drag PgSqlDataTable from toolbox on your form, double click it, change Type from Text to TableDirect, and select the required table on the 'Select Text' tab. Will be generated isert command. Also you can generate update, insert and delete command on 'Command Generator' tab by pushing 'Generate Commansds' button. For more details please refer to http://www.devart.com/dotconnect/postgr ... ditor.html

overtime wrote:Is there a member of PgSqlDataTable which generates the insertcommand and updatecommand for me? It would be nice to only update fields which have been modified before.

When you modify some field in a row, then RowState will be changed to Modified for this row. When you call PgSqlDataTable.Update() each modified row will be updated and you do not have to specify UpdateCommand for this.

belidzs · Post by **belidzs** » Fri 23 Dec 2011 19:33

Ok, now I get what it's supposed to do. I actually want to build an update command, which only contains the changed field's definition. The point of doing this is that since postgres 9 it is possible to set column-level privileges, but if I use an update query which sets every field of the table, the whole thing becomes pointless.

Let's say there are 3 columns:

Column A
Column B
Column C

User1 has privileges on editing A and B, User2 can edit all of the columns above. If I set the update command to update all columns, User1 won't be able to edit anything (even if he only changes the first columns the UPDATE command will trigger a privilege fault). If I use a command which only affects A and B, everyone will be able to make changes, but C never will be affected.
Is there any practice to circumvent this?
Thanks!

Pinturiccio · Post by **Pinturiccio** » Mon 26 Dec 2011 11:17

There is no possibility to generate an update commands only for changed columns. We will investigate the possibility to add this feature and notify you as soon as possible.