Feature request: Support initialization of the database connection with the DbDataSouce-like instance

[luborozporka]

Currently, PetaPoco supports initializing DatabaseConfiguration.Build() with either a direct connection or a connection string. However, these approaches have limitations: a direct connection can disable pooling (as seen with MySqlConnector, see: https://dev.mysql.com/doc/connector-net/en/connector-net-connections-pooling.html), and using a connection string prevents leveraging modern DbDataSource (see: https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdatasource) features like connection callbacks, integrated logging..

[asherber]

PetaPoco uses a connection, so I'm not sure there's value in being able to pass a DbDataSource into the constructor. What about just passing in myDataSource.OpenConnection()?

[luborozporka]

Thank you for the quick reply and time! While passing a connection from myDataSource.OpenConnection() does work to create a PetaPoco Database, it doesn't exploit the MySql's pooling capabilities (see MySQL Connector/NET Connection Pooling - specifically: "To work as designed, it is best to let the connection pooling system manage all connections. Do not create a globally accessible instance of MySqlConnection and then manually open and close it. This interferes with the way the pooling works and can lead to unpredictable results or even exceptions.").

Conversely, initializing with a connection string ensures automatic pooling. However, this approach prevents leveraging connection callbacks and integrated logging features provided by myDataSource -https://mysqlconnector.net/api/mysqlconnector/mysqldatasourcebuildertype/.

[asherber]

Your first paragraph about not reusing a global connection object is completely compatible with using a DbDataSource to provide a connection on demand. I believe the whole idea of DbDataSource is to act as a centralized connection provider, and presumably it would reuse connections from the pool as needed.

The usage pattern here would be to either create one global Database object that holds a connection from DbDataSource (and doesn't close/open it along the way), or to create Database objects as needed (with myDataSource.OpenConnection()) and dispose them when done.

Note that the PetaPoco Database object itself also provides a set of callbacks that can operate on the connection, which you can wire up during fluent instantiation.

[luborozporka]

Thank you for the insights. Upon further investigation, I still believe integrating DbDataSource offers a more future-proof solution. While using a connection string for initializing the database configuration automatically handles connection disposal, initializing with a concrete connection does not, which makes sense, however the direct use of DbDataSource - comparable to using a connection string in terms of automatic connection disposal handling - could make initialization more error-resistant and flexible.

Moreover, with upcoming MySqlConnector changes (like deprecating global logging in v2.4.0, see MySqlConnector Logging), directly integrating DbDataSource could further future-proof connection management by encapsulating logging and connection behaviors.

Thank you again for considering these points.