PgsqlConnection recommendations

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

PgsqlConnection recommendations

Post by hmuscroft » Sat 28 Feb 2009 09:14

I know that the usual ADO.NET recommendation for DbConnection objects is to use deterministic destruction (i.e. create and use the PgsqlConnection object and then call .Close and .Dispose immmedately when we're done with it).

However, for a desktop application which (unlike a web application) has a consistent connection to the pgsql server, is this necessary?

In other words, is there any harm in just have one global instance of the PgsqlConnection object and using it throughout the application? Are there any benefits/drawbacks in terms of performance or memory consumption to either approach?

Many thanks!

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 02 Mar 2009 09:12

We recommend to use one PgSqlConnection object in a whole application. But be aware about the limitations of this approach.

In the synchronous mode (default), the PgSqlConnection object can be used in one thread only. So, if you use only one PgSqlConnection object, you need to carefully design your application to exclude the situation when PgSqlConnection can be used in several threads (e.g., by several PgSqlCommand objects).

In the asynchronous mode ( http://www.devart.com/dotconnect/postgr ... Using.html , the Asynchronous Query Execution section ), PgSqlConnection can be used in several threads that are executed asynchronously.

Please learn more about the PgSqlConnection class in our documentation.

hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Mon 02 Mar 2009 09:40

Thanks for your reply.

I do actually use the Asynchronous mode, but it's only to keep the application responsive during longer database operations. For example :-

Code: Select all

        using (PgSqlConnection con = new PgSqlConnection(ConnectionStr))
        {
          con.Open();
          PgSqlCommand cmd = new PgSqlCommand(sql, con);
          IAsyncResult res = cmd.BeginExecuteNonQuery(null, null);
          while (!res.IsCompleted)
            Application.DoEvents();
          cmd.EndExecuteNonQuery(res);
        }
So if I change con to a global instance as you recommend while the above be safe, or should I perhaps lock the con object whenever it's used to make sure? i.e.

Code: Select all

        lock (con)
        {
          PgSqlCommand cmd = new PgSqlCommand(sql, con);
          IAsyncResult res = cmd.BeginExecuteNonQuery(null, null);
          while (!res.IsCompleted)
            Application.DoEvents();
          cmd.EndExecuteNonQuery(res);
        }
One last question... if using a global PgsqlConnection object, should it be opened and closed before/after each operation, or simply left open throughout the lifetime of the app?

Many thanks for your help.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 02 Mar 2009 10:58

1. It is not necessary to lock your PgSqlConnection object used in asynchronous mode only in your application, because PgSqlConnection can be used in several threads that are executed asynchronously.

2. You can simply leave the PgSqlConnection object opened throughout the lifetime of your application (to open it when starting your application and to close when the application is finishing its work).

hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Mon 02 Mar 2009 12:30

Thanks Shalex - last question : should 'Pooling' be set to true or false in this scenario or does it not matter?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 02 Mar 2009 13:17

If you set Pooling to true and call the Close() method of your PgSqlConnection object, actually the connection will not be closed - it will be only placed to a pool. If Pooling=false, the Close() method will close the connection, and the next Open() method will create a new connection. So, the difference is just in an internal implementation.

For more information, please refer to our online documentation: http://www.devart.com/dotconnect/oracle/docs/?FAQ.html , the Connection pooling section.

hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Fri 24 Apr 2009 15:16

If you set Pooling to true and call the Close() method of your PgSqlConnection object, actually the connection will not be closed - it will be only placed to a pool.
What about Dispose()? If "Pooling=True" and I dispose of the PgSqlConnection object then does that physically close the connection or does it also just return it to the pool?

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 27 Apr 2009 07:53

The Dispose() method just returns the PgSqlConnection object to the pool.

Post Reply