|Sharing Database Connections|
I'm migrating my website to ASP.Net and everything is fine... But I'm wondering what the best practices are for handling database connections.
I've moved all the logic for the site into classes such as Customer and Product etc... The trouble is, each one of these classes need to access the database. Each time I want to access some data I end up doing...
MySqlConnection myConnection = new MySqlConnection("<snip>connection string</snip>");
MySqlCommand myCommand = new MySqlCommand("SELECT * FROM my_table", myConnection);
myReader = myCommand.ExecuteReader();
...which seems rather clumsy to keep repeating.
I'm wondering if there's some way I can share the connection between all these classes? Perhaps a static class for handling database connections? Which I'm sure would work, but then how do I make sure the connections are closed?!
Any feedback or suggestions would be most welcome!
Well, technically speaking it is the price you pay for object oriented programming. If you did your job on information hiding, no separate class will know which resources are accessed by other classes and hence they cannot rely on the existence or type of SQL connections opened by other classes.
Of course in your particular program you know which classes are used and how the program flow is, so you can open the SQL database at another level and pass information of that connection to each of the classes. But it makes your code less portable, and surely less reusable, if reusable at all.
I can't tell from your code if you've got the connection string hard-coded into the classes, but it's best to keep it in the Web.config and then each class can retrieve it from the same place.
The other thing you could do, and I've gone either way on this, is to create a new MySQLConnection object in the class constructor, that way your code would look something like this:
MySQLConnection Connection = new MySQLConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
public void SomeFunction()
MySQLCommand cmd = new MySQLCommand("command text", Connection);
The plus is that it's less code, using the same connection object and opening and closing it as needed. The minus is that you're creating a connection object neddlessly if you don't use it in that instance of the class.
It is making me wonder what the performance implications are of each class handling their own database connection...
Is it going severely impact performance with several database connections being opening and closed behind the scenes for each page served?
It's probably because I'm still in the mindset of classic ASP coding. In the past I'd open a database connection at the top of a page and close it at the bottom. Any code in between then has access to the connection object. It's just how to transfer my mindset to fit in with the OOP and ASP.NET way of working!
Thanks for the reply ceestand - I think we were typing at the same time, so excuse the double post!
Yes, I'd assumed it was best practice for the connection string to be in the web.config.
In your example, where does the Connection get closed? And for further functions, does the connection get opened again here?
For the sake of brevity I left out a whole bunch of code. Anytime I open a connection, I do so in a try-catch-finally statement like so:
Object Whatever = cmd.ExecuteSomehow();
The exception handling has been left out, but this way if the SQL command throws an exception when I try to execute it, the connection gets closed no matter what. Then you don't have connections stuck open until the system closes it one way or another.
If you wanted to, you could jam more commands into the try statement, a la ASP, but I don't think you'll gain performance from it. For more info you can read up on connection pooling for Connector/Net (assuming this is what you're using).
For oo design you will need a class for your database layer. Take a look at .net patterns