How to UPDATE a column field through Npgsql

[mauede]

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

 

[Skydiver]

Seems to me that a generic SQL UPDATE statement would be good enough and that you don't need a specific Npgsql command.

SQL UPDATE Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Skydiver]

Moved thread to SQL Server since this isn't a VS.NET issue.

 

[Skydiver]

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.

 

[mauede]

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,
updated = LOCALTIMESTAMP
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;}) ;
            }
            try
            {
                focusedTrial = ui.GetResponseAndReturnValueFromFunc(options);
                Console.WriteLine($"Selected trial from method ListTrials: {focusedTrial}");
            }
            catch (Exception e)
            {
                ui.WriteError(e.Message);
                return;
            }

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

 

[Skydiver]

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.

 

[Skydiver]

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.

 

[mauede]

Thank you.
However, I do not know the Npgsql command to change a single trial name.
That is my question.
Thank you

 

[Skydiver]

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.

 

[mauede]

Can you please show an example?
The scarce Npgsql documentation looks like Chinese to me.
Thank you