Update a DataTable using a query

[JORGEMAL]

Maybe this post does not belong to this forum, but I have already asked in ADO.NET forums without response. If this is the case, please disregard my question and I apologize for any inconvenience.

I have a DataTable (dtFuentes) that contains the following columns:

* fte_id
* fte_parent
* fte_porc
* fte_porc_aux

The field "fte_parent" can have the same value for several records in the DataTable and I want to update column "fte_porc_aux" based on certain criteria using a query like this:
UPDATE dtFuentes SET fte_porc_aux = porc_value WHERE fte_parent = parent_value

I will very much appreciate if someone provides a step by step procedure about how to achieve my goal showing connections, commands and so on.

I have tried the code below but it returns 0 rows affected and, using the debugger, I can see that there are several rows with fte_parent = 180.

String strSQL = "UPDATE dtFuentes SET fte_porc_aux = 99 WHERE fte_parent = 180";
// I have also tried using a connection like this defined some lines before.
// PgSqlCommand pgCmd = new PgSqlCommand(strSQL, pgConn);
PgSqlCommand pgCmd = new PgSqlCommand();
pgCmd.CommandText = strSQL;
dtFuentes.UpdateCommand = pgCmd;
Int32 intRecords = dtFuentes.Update();

Respectfully,
Jorge Maldonado

[Pinturiccio]

PgSqlDataTable works in the following way. You retrieve data from a table in a database. Then you modify, add, or delete rows, and these changes are saved to the database when the Update method is called.

If you assign a custom command to the PgSqlDataTable.UpdateCommand property, you override this logic. Your code works in the following way: if you make any changes to PgSqlDataTable data, the command that updates fte_porc_aux fields for all rows having fte_parent = parent_value will be executed during update operation. Besides, this command will be performed regardless of the actual changes you made to the dtFuentes data, and these changes will not be saved to the database. If this is the behaviour you want, you may use your approach. However, if you want just to perform this update command once, we recommend you just to execute pgCmd.ExecuteNonQuery() instead of assigning this command to dtFuentes.UpdateCommand property. In this case, logics is correct.

I have tried the code below but it returns 0 rows affected and, using the debugger, I can see that there are several rows with fte_parent = 180.

If there were no changes in the table and all its rows have the RowState property equal to Unchanged, the Update method is not executed.

Could you please tell us what exactly you want to do? We have two versions:
1. You want to update the dtFuentes table and assign the same value to the fte_porc_aux field for all rows, having fte_parent field equal to some specific value, for example, 180. In such case you don't need to use PgSqlDataTable at all, you just need to execute the PgSqlCommand:

Code: Select all

PgSqlCommand pgCmd = new PgSqlCommand();
pgCmd.CommandText = "UPDATE dtFuentes SET fte_porc_aux = :porc_value WHERE fte_parent = :parent_value";
pgCmd.Parameters.Add("parent_value", 180);
pgCmd.Parameters.Add("porc_value", 99);
pgCmd.ExecuteNonQuery();

2. You want to update the dtFuentes table and assign different values to the fte_porc_aux fields for all rows, having fte_parent field equal to some specific value, for example, 180. In this case you don't need to modify the UpdateCommand property for PgSqlDataTable. You need just to modify values in the rows as shown below:

Code: Select all

foreach (DataRow rows in dtFuentes.Rows)
{
    if ((int)rows["fte_parent"] == 180)
        rows["fte_porc_aux"] = "The value that is written to the database";
}
dtFuentes.Update();

[JORGEMAL]

What I need is to update information in the DataTable only. So, according to your reply, I guess the method I should follow is the one that iterates through the DataTable (your second option). I thought that issuing an update to the DataTable would modify its content, but I see that such a process impacts the database. Just let me know if I understood correctly.

Best regards,
Jorge Maldonado

[Pinturiccio]

I thought that issuing an update to the DataTable would modify its content, but I see that such a process impacts the database.

If you have defined the SelectCommand of your PgSqlDataTable before calling the Update method, but haven't defined UpdateCommand, then UpdateCommand is created automatically by the PgSqlCommandBuilder class, which uses metadata to update only the actually changed rows.

What I need is to update information in the DataTable only. So, according to your reply, I guess the method I should follow is the one that iterates through the DataTable (your second option)...
Just let me know if I understood correctly.

Yes, you understood correctly.