asked on

PostGreSQL - Error Anyone experience this?

Hello all,

I am in the process of changing my single threaded app to a multi-threaded app and when I run an execute query call I am getting this error below:

Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at CmedFileGenerator.Form1.executeQuery(String query, String connectionString) in C:\Applications\Projects\Proj1\Form1.cs:line 43

The call is like this on executing a query, I had to add the check of the connection state as well once I changed it to threaded. I am running 2 threads only currently.:

  private DataTable executeQuery(string query, string connectionString)
        {
            DataSet ds = new DataSet("data");
            try
            {
                // Making connection with Npgsql provider
                conn.ConnectionString = connectionString;
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
                da.Fill(ds);
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            catch (Exception ex)
            {
                SendMail.SendEmail("Generator Run Failed", Properties.Settings.Default.EmailTo, "GOT HERE" + ex.StackTrace);
                Application.Exit();
            }

            return ds.Tables[0];
         
        }

Open in new window

skullnobrains

can't you open separate connections in each thread ?

sbornstein2

ASKER

Do you mean like have two seperate executeQuery methods like executeQuery2?

ASKER CERTIFIED SOLUTION

skullnobrains

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

sbornstein2

ASKER

I think the issue was I had the connection global and it was being closed after so I moved the 'new' connection into the executeQuery and it seems to work now.

skullnobrains

this probably results in the connection being open the first time you actually use it.

beware about those 2 thigs i'm a little afraid of

- recreating a new objet connection each time is likely to leak. you probably had better check wether the connection exists and only build a new one when needed aka the first time.

- opening and closing the connection everytime you perform a query is likely to be a bad idea performance wise, but that mostlky depends on the frequency of the quieries you will perform.

anyways, happy coding, and thanks