Wednesday, June 4, 2008

.NET MySQLConnection - How to handle it...

I am trying to figure out the best way to handle a MySQLConnection in C#, though in reality any connection has the same principles (SQLServer, ODBC, etc.) Basically from what I understand Opening and Closing the connection is slow, so I want to minimize my open and closes, however I have to do several things when adding database objects and I also want to minimize the length of functions and allow repititious code to be reused.

So we have conflicting goals:
  • Open and close DB connection as little as possible
  • Break up long functions into smaller callable functions (so that adding relations for instance can be called without adding two or even one object to relate)
It's bad because when you go from one function to another it is always scary to send it the connection out (or byref) which is how I think I would have to send it for its open state to be maintained and so that you do not have to sit there and run the risk of opening multiple connections to the database if you pass it by value.

My ultimate solution to this quandary:
  • Public static MySqlConnection in the DBData Class which can be referenced within each function and its state can be checked and maintained (if you opened it you close it, if it was open you leave it)
I am not sure if this is the best option but it gives me the most flexibility and so far has come off without a hitch, that I have noticed anyway. I welcome comments or other suggestions and thoughts.

No comments: