For some reason, I get the code with an error: System.InvalidOperationException: An operation is already in progress.

I read a little and came to the conclusion that the problem may be that I do not complete the transaction (I do not execute commits). The question is. Is there a point in them, and at what point do I need to commit them? Do I need to start a transaction before each request or how?

public void PGConnect () {

  // UserData [] uds; List<UserData> uds = new List<UserData>(); UserData ud; List<string> dblist = GetListDBsList(); if (dblist.Contains(config.PGdbName)) { Console.WriteLine("Data Base exists: {0}", config.PGdbName); } else { Console.WriteLine("Data Base DO NOT exists: {0}", config.PGdbName); return; } NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=" + config.PGLogin + ";" + "Password=" + config.PGPass + ";Database=" + config.PGdbName + ";"); //select datname from pg_database; try { conn.Open(); Console.WriteLine("PG Connected"); } catch(SocketException e) { Console.WriteLine(e.Message); } //NpgsqlTransaction tr = conn.BeginTransaction(); // Где-то в этом блоке ошибка string tablesListRequestSQL = @"SELECT table_name FROM information_schema.tables WHERE table_schema='public'"; NpgsqlCommand commandGetDBTables = new NpgsqlCommand(tablesListRequestSQL, conn); NpgsqlDataReader drGetDBTables = commandGetDBTables.ExecuteReader(); //tr.Commit(); while (drGetDBTables.Read()) { existsInDBTables.Add(drGetDBTables[0].ToString()); } foreach (string table in requireTablesList) // { if (!existsInDBTables.Contains(table)) // if element from requireTablesList do not found -> DB have not simmilar Tables! { notFoundedTables.Add(table); } } if (notFoundedTables.Count != 0) // if not empty { Console.WriteLine("Next tables are marked as reqired for Sync, but can't be found in DataBase: "); foreach (var table in notFoundedTables) { Console.WriteLine(table); } } // Конец блока где ошибка. Console.ReadKey(); NpgsqlCommand command = new NpgsqlCommand("SELECT city, state FROM cities", conn); try { NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { // UserData ud = new UserData(); ud.id = Int32.Parse(dr[0].ToString()); ud.guid = (dr[1].ToString()); ud.name = (dr[2].ToString()); ud.userblob = (byte[])dr[3]; uds.Add(ud); //File.WriteAllBytes("outputimg.jpg", ud.userblob); //Console.ReadKey(); } } finally { conn.Close(); }
  • A simple principle works here - if a transaction is opened, then you need to close it. Another thing is that a transaction can start automatically. C # and similar languages ​​will teach that GC will come and clean everything up. But it doesn't work with databases yet. If the transaction is not closed, then there may be two options - either the driver will still close for you (but I strongly doubt), or it will simply rollback and the data will simply be lost. - KoVadim
  • A transaction is a set of operations in the database that are either performed all together or none of them are performed. - Nofate ♦
  • The essence of the transaction in the atomic change. Example - You need to modify several tables. If all changes are not made, the base will be "uncoordinated." And the application may accidentally fall in the process of modifying the database. - KoVadim
  • Thanks, but what can cause an error in my code? I have marked the block if I comment out that everything works. - Dmitry Bubnenkov
  • It is odd to translate to one select request. Transactions are made on changes (insert / update / delete). - KoVadim

1 answer 1

The reader drGetDBTables is not closed here, so you cannot make a second request in the connection.

You should always use the DataReader object. It will not be available until the DataReader is closed.
Note: The DataReader is not a DataReader.