How to UPDATE a column field through Npgsql


Well-known member
Sep 1, 2021
Northwood - UK
Programming Experience
I made some little progress with a lot of sweat. I can list the content of a column in a selected table and allow the user to pick a column item.
Thanks for your help.

My new challenge is to implement a method that allows the user to change the name of an existent trial (that is an item in a specific column of a table).
The table in question is called "trial". The column called "trial_name" contains 3 items (that is 3 rows):
  • Pathos
  • Concorde
  • Adscan
Let's assume the user has picked "Pathos".
Let's assume the user wants to change the name "Pathos" to "JohnDow".
I will have to make sure an existent trial has been selected and if so I will have to replace the name "Pathos" with "JohnDow".
I cannot find the appropriate Npgsql command to do that.
Can you please help?
Thank you in advance
Seems to me that a generic SQL UPDATE statement would be good enough and that you don't need a specific Npgsql command.

Moved thread to SQL Server since this isn't a VS.NET issue.
Sorry, @mauede . I keep on forgetting that you are medical physicist and that being forced to write code is not your primary field of expertise. As someone currently fighting cancer, I do appreciate your efforts in the field.

Let us know if the like above for using the UPDATE statement isn't enough to get you unstuck and we'll try to help you out.
Thank you.
Updating the content of a column item, as in my example, is easily done . The following lines do the job:
UPDATE trial
SET trial_name = 'JohnDow',
updator_id_fk = 1,
WHERE id = 1;
See the attached screenshot showing the name of the trials before and after the update.

The problem is that there will be many similar changes not just for the trial names but also for the related RT structures.
Therefore we need something that does such changes automatically.

I learned I have to send to the database a string like the one shown above. I have no idea which Npgsql class to use.
To read from the data base the following statements do the job:
            using var cmd = new NpgsqlCommand(sql, conn);
            using NpgsqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
                string st = dr[0].ToString();
                options.Add(dr[0].ToString(), ()=> {return st;}) ;
                focusedTrial = ui.GetResponseAndReturnValueFromFunc(options);
                Console.WriteLine($"Selected trial from method ListTrials: {focusedTrial}");
            catch (Exception e)
I have no idea which Npgsql command is used for writing/updating the database.
Any suggestions and help are very welcome

Thank you in advance


  • Screenshot 2021-12-06 at 21.02.33.png
    Screenshot 2021-12-06 at 21.02.33.png
    240.6 KB · Views: 16
Last edited by a moderator:
The problem is that there will be many similar changes not just for the trial names but also for the related RT structures.
Therefore we need something that does such changes automatically.
Typically, one uses normalized databases to minimize the things that need to be touched. And often transactions are used to protect the changes so that they occur as an atomic change from the point of view of any database readers.
Since you are working with a database schema not directly in your control, the hopes of normalizing the database schema goes out the window. The only tool left on your tool belt will be the use of transactions to apply the changes to maintain a consistent view for the other users of the database. There is no magic tool. You'll just have to iterate over each trial name, start a transaction, apply updates, commit the transaction, and then move to the next trial name. Fortunately, solving the problem for one trial will solve it for all. It's just a matter of letting your code loop over the trial names.
Instantiate an NpgSqlCommand and pass in as the CommandText your UPDATE statement from post #5.

Once you have that working, start playing with changing the id value to match and the replacement trial name by building up the string dynamically. This is just a stepping stone because it's bad form to do this in production code.

Once you gain confidence that changing the value to search and the replacement name using a composed string works. Switch over the proper way to do this: SQL parameters.

Latest posts

Top Bottom