0

I am trying the Postgres Plus 9.5 with .Net 4.5, Npgsql 3.1.6 NuGet package. I have read what is here about this error, but I do not understand why I get it. Everything is disposed. Here the is code:

public override DataTable getActListData(int FunkNr)
{
  using (var cmd = new NpgsqlCommand())
  {
    cmd.CommandText = npgsqlCommand3.CommandText;
    cmd.Connection = this.npgsqlConnection;
    cmd.Parameters.Add(new Npgsql.NpgsqlParameter("ANWENDUNG", NpgsqlTypes.NpgsqlDbType.Numeric));
    cmd.Parameters.Add(new Npgsql.NpgsqlParameter("XFUNKNR", NpgsqlTypes.NpgsqlDbType.Numeric));


    using (var da = new NpgsqlDataAdapter(npgsqlCommand3))
    {
      var tab = new DataTable();
      da.SelectCommand.Parameters["ANWENDUNG"].Value = getAnwendung();
      da.SelectCommand.Parameters["XFUNKNR"].Value = FunkNr;
      da.Fill(tab);  // Here is the error on the 5th call
      return tab;
    }
  }
}

Does this problem comes from Npgsql or is from Postgres?

Some other questions:

I have read here, that lazy loading is impossible, but I didn't understand is is because of the Npgsql or from Postgres?

Is it possible in Postgres to open several cursors and read on demand in the same connection?

Edit: Changed the code:

  using (var npgsqlConnection = new NpgsqlConnection())
  {
    ConnectionString = string.Format(DataClientFactory.DataBaseConnectString, DB, User, PW);
    npgsqlConnection.ConnectionString = ConnectionString;
    npgsqlConnection.Open();
    ....
    the code above here
    ....
 }

The same error in the same call. The error:

System.InvalidOperationException occurred
  HResult=-2146233079
  Message=An operation is already in progress.
  Source=Npgsql
  StackTrace:
       bei Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState)
  InnerException:
Mottor
  • 1,938
  • 3
  • 12
  • 29
  • Your code seems, OK, but something outside of your sample may be accessing `this.npgsqlConnection`... You need to carefully follow all uses of the connection. Note that it's standard practice to simply open new connections and close them for each operation - connection pooling will make sure there's no performance impact. – Shay Rojansky Jul 22 '16 at 13:34
  • Regarding multiple requests on the same connection (I assume that's what you mean by lazy loading)... Npgsql doesn't allow you to have multiple *readers* open at the same time on the same connection. This capability is sometimes called MARS (multiple active result sets), and is supported by SqlClient. However, you can still use PostgreSQL cursors yourself and have multiple cursors open, each time retrieving part of result set. It's up to you to manage this. – Shay Rojansky Jul 22 '16 at 13:35
  • @ShayRojansky The migration is from Oracle and I have to use the same session (connection) There are package variables used between calls. – Mottor Jul 22 '16 at 13:38
  • @ShayRojansky Are there other .Net drivers (nuget) which can do MARS? – Mottor Jul 22 '16 at 13:40
  • I have no idea... You may want to check out DevArt's PostgreSQL driver, or maybe going through ODBC... – Shay Rojansky Jul 22 '16 at 13:45
  • @ShayRojansky OK. What I wanted to ask is 'Not doing MARS is not Postgres limitation?' – Mottor Jul 22 '16 at 13:47
  • That's a complicated question. A PostgreSQL connection is a simple socket, and you can only have one thing going on at a time. Again, you can use server-side cursors to have multiple commands open at a time, and it's possible to simulate MARS over that (https://github.com/npgsql/npgsql/issues/462), but I think SqlServer has some specific protocol features to support MARS beyond that. – Shay Rojansky Jul 22 '16 at 14:02

1 Answers1

0

If the piece of code you posted runs concurrently on the same connection, then that's your problem - Npgsql connections aren't thread-safe, and it's not possible to have multiple readers opened at the same time (MARS).

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • The DevArt has no MARS too (http://forums.devart.com/viewtopic.php?f=3&t=33074&p=114288&hilit=mars#p114288) . Do you now if it is a limitation of Postgres? – Mottor Jul 22 '16 at 14:00
  • There is no concurrent access. There is only one thread. All reader are in using() – Mottor Jul 22 '16 at 14:35
  • Try creating and opening your NpgsqlConnection within your function as well (instead of this.npgsqlConnection), don't forget to dispose it as well – Shay Rojansky Jul 22 '16 at 14:38
  • Thank you. We will not use Postgres for now. Experiment unsuccessful. – Mottor Jul 22 '16 at 15:32
  • That's too bad. It's very easy to code without relying on MARS, but if you're porting a big program that already depends on it it's indeed a problem. MARS support is a future goal at some point, but not anytime soon. – Shay Rojansky Jul 22 '16 at 16:36
  • Hi Shay. This program is done for Oracle. Than was changed to can work with SQLite too. I cannot change the way the application work, but only the DB interface. Thank you again. – Mottor Jul 25 '16 at 08:02