C # Winforms Npgsql 3.0.5 "Running operation" error when trying to run multiple commands inside the same connection

Question

I am trying to run the following snippet in C # Winforms. This piece of code works well with the pgsql 2.2.6 adapter. What correction can be done to work properly with the pgsql3.0.5 adapter? Thanks.

NpgsqlConnection conn = new NpgsqlConnection(MainForm2.MyConString); { conn.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT rfid,name,sc_id from passenger ORDER by name", conn)) { NpgsqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { var obj = new PassengerClass { RFID = dr.GetString(0), Name = dr.GetString(1), sc_id = dr.GetInt32(2) }; s = dr.GetString(0); try { ret.Add(s, obj); } catch (Exception ex) { SM.Debug("Fail to add RFID Name in hash RFID:" + s + ex.ToString()); } } } MainForm2.PassHash = ret; try { using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format("UPDATE place set useridx ={0} where useridx=0", MainForm2.userIDX), conn)) cmd.ExecuteNonQuery(); using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format("UPDATE zonename set useridx ={0} where useridx=0", MainForm2.userIDX), conn)) cmd.ExecuteNonQuery(); } catch (Exception ex) { SM.Debug("Error on update users IDX for place and zone with value 0 :" + ex.ToString()); } 

So, in the second command expression, it causes the following error:

The first exception of type "System.InvalidOperationException" exception occurred in Npgsql.dll

Additional Information: The operation is already in progress.

EDIT Further information:

Answer 1

You need to get rid of the NpgsqlDataReader that you get when you call ExecuteReader the first time: wrap it with the use statement, as you do with your NpgsqlCommand.

Removing NpgsqlDataReader does not close the connection - only connection utilization does this. An open reader corresponds to an open command that you are currently executing, which you must close before executing a new command. For atomicity, you can simply start a transaction that includes several commands.