0

I am writing c# wrapper class for MySQL and I need to know if I can put a "new MySqlConnection(connstr)" into the constructor and then use the same object between the methods that are going to manipulate the database? Should I create a new connection for every operation (select, insert, delete, update) and destroy it when done. Any ideas or better approaches to write a MySQL wrapper?

Kreshnik
  • 2,661
  • 5
  • 31
  • 39

3 Answers3

2

I'd recommend not sharing that connection.

It won't scale as well as getting that connection out from a pool when it's needed, performing the SQL operation, closing resources, and then returning the connection to the pool.

I consider it a best practice to restrict SQL objects to the data access layer and using them in the narrowest scope possible. Don't pass them around - the responsibility for cleaning them up isn't clear if you do.

Connections ought to be owned by the service that knows about use cases, units of work, and transactions. Let it check out the connection, make it available to all DAOs that need it, then commit or rollback and close the connection.

DAOs should be given database connections. They should not acquire them on their own, because they can never know if they're part of a larger transaction or not.

duffymo
  • 305,152
  • 44
  • 369
  • 561
2

For database connection the rule should be Open as Late as possible and close as early as possible,.

So open the connection before executing the query and then close it, rather than sharing a single connection object across different methods.

Habib
  • 219,104
  • 29
  • 407
  • 436
1

There is a MySqlHelper class that should do all your connection pooling, opening, closing, disposing, etc for you. "These take a connection string as an argument, and they fully support connection pooling." That is only is you are using the Oracle provided MySQL connector though.

Ryan
  • 106
  • 4