How to UPDATE a column field through Npgsql

mauede

Well-known member
Joined
Sep 1, 2021
Messages
103
Location
Northwood - UK
Programming Experience
Beginner
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

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
Seems to me that a generic SQL UPDATE statement would be good enough and that you don't need a specific Npgsql command.

 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
Moved thread to SQL Server since this isn't a VS.NET issue.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
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

Well-known member
Joined
Sep 1, 2021
Messages
103
Location
Northwood - UK
Programming Experience
Beginner
Thank you.
Updating the content of a column item, as in my example, is easily done . The following lines do the job:
C#:
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:
C#:
            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
 

Attachments

  • Screenshot 2021-12-06 at 21.02.33.png
    Screenshot 2021-12-06 at 21.02.33.png
    240.6 KB · Views: 8
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
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.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
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.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
You already did it in your C# code from post #5: using var cmd = new NpgsqlCommand(sql, conn);. But this time sql would look like the SQL code you had also from post #5:
C#:
UPDATE trial SET trial_name = 'JohnDow', updator_id_fk = 1, updated = LOCALTIMESTAMP WHERE id = 1;

And instead of ExecuteQuery(), you would call ExecuteNonQuery().

Basically all of the ADO.NET object act the same. If you read about how to do things with the SqlCommand or OdbcCommand, the same concepts apply to all the other ADO.NET drivers. Here's the starting point for learning about ADO.NET:

 

mauede

Well-known member
Joined
Sep 1, 2021
Messages
103
Location
Northwood - UK
Programming Experience
Beginner
I am still struggling with replacing the name of a trial, which is replacing the content of an item in a table.
Doing that with fixed hard-coded values in the UPDATE statement was successful since my first attempt.
However, when I have many trial names, that is a long column, I will have to identify which item (row) in the column has to be replaced.
So far I am not handling users' login to access the database. Since I am the only user with writing access, the identifier of the updating user is so far hard-coded. However, the ID of the trial_name to change must be a parameter. So I have
  1. get the ID of the trial_name to be changed, which is tantamount to a database reading access
  2. change the trial_name of the identified item in the column, which is tantamount to a database writing access
Although the built was generated with no error, I kept getting a runtime exception. Basically, the database server complained about receiving a writing request while the previous reading command was being processed.
I googled the exception type and found some complaints posted by other people. The only solution I found is to close the connection after the reading access and reopen it to allow the following writing access.
It worked but that makes the code (attached) pretty clunky.
Is there a more elegant way to finalize database access without resorting to closing and reopening the connection?

Thank you so much in advance
You already did it in your C# code from post #5: using var cmd = new NpgsqlCommand(sql, conn);. But this time sql would look like the SQL code you had also from post #5:
C#:
UPDATE trial SET trial_name = 'JohnDow', updator_id_fk = 1, updated = LOCALTIMESTAMP WHERE id = 1;

And instead of ExecuteQuery(), you would call ExecuteNonQuery().

Basically all of the ADO.NET object act the same. If you read about how to do things with the SqlCommand or OdbcCommand, the same concepts apply to all the other ADO.NET drivers. Here's the starting point for learning about ADO.NET:

 

mauede

Well-known member
Joined
Sep 1, 2021
Messages
103
Location
Northwood - UK
Programming Experience
Beginner
When I delete a trial, I delete all its RT structures first and then I delete the trial itself.
The whole task requires two database accesses to update two tables.
How can I know from my C# script that the first table has been updated before proceeding to update the second one?
If I do not make any check I might end up having inconsistent data in the database.
For example, a deleted trial whose RT structures have not been deleted and bear no relation to any 'live' trial.
How can I tackle such a problem through Npgsql?

Many thanks for your attention
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
Although the built was generated with no error, I kept getting a runtime exception. Basically, the database server complained about receiving a writing request while the previous reading command was being processed.
I googled the exception type and found some complaints posted by other people. The only solution I found is to close the connection after the reading access and reopen it to allow the following writing access.
It worked but that makes the code (attached) pretty clunky.
Unless PostgreSql or Npgsql has a bug, the same connection should be usable for read and write without having to close it. This is going to be very ironic considering a top level Oracle database developer who has worked on MySQL for years actually recommends using PostgreSql is his public resignation letter.

If PostgreSql really does have this issue, you can collect all the ID's in one pass and store them in a list. Do another pass and do the updates.

Can you post some links of what you found in Google?

Anyway, your attachment did not make it through (fortunately). We prefer that you post your code as text in code tags instead of uploading a file attachment. Please post that when you have a chance.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
Unless PostgreSql or Npgsql has a bug, the same connection should be usable for read and write without having to close it.
Apparently this not a "bug", but rather a "feature".


And the easy solution is to use SELECT ... FOR UPDATE, and the harder solution is to use SERIALIZABLE mode.

 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,711
Location
Chesapeake, VA
Programming Experience
10+
After further readings in Github, the Npgsql developers recommends doing a first pass in one connection doing all the reads and storing what you need in memory, and then doing a second pass to do the updates with a new connection.
 
Top Bottom