PgsqlConnection recommendations
PgsqlConnection recommendations
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!
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!
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.
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.
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 :-
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.
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.
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);
}
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);
}
Many thanks for your help.
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).
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).
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.
For more information, please refer to our online documentation: http://www.devart.com/dotconnect/oracle/docs/?FAQ.html , the Connection pooling section.
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?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.
Thanks.