Page 1 of 1
PgsqlConnection recommendations
Posted: Sat 28 Feb 2009 09:14
by hmuscroft
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!
Posted: Mon 02 Mar 2009 09:12
by Shalex
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.
Posted: Mon 02 Mar 2009 09:40
by hmuscroft
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.
Posted: Mon 02 Mar 2009 10:58
by Shalex
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).
Posted: Mon 02 Mar 2009 12:30
by hmuscroft
Thanks Shalex - last question : should 'Pooling' be set to true or false in this scenario or does it not matter?
Posted: Mon 02 Mar 2009 13:17
by Shalex
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.
Posted: Fri 24 Apr 2009 15:16
by hmuscroft
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.
Posted: Mon 27 Apr 2009 07:53
by Shalex
The Dispose() method just returns the PgSqlConnection object to the pool.