Forum Moderators: open
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>");
myConnection.Open();
MySqlCommand myCommand = new MySqlCommand("SELECT * FROM my_table", myConnection);
MySqlDataReader myReader;
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!
Thanks,
Jonathan
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.
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:
public MyClass()
{
MySQLConnection Connection = new MySQLConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
}
public void SomeFunction()
{
MySQLCommand cmd = new MySQLCommand("command text", Connection);
Connection.Open();
...
}
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.
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,
Jonathan
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?
try
{
Connection.Open();
Object Whatever = cmd.ExecuteSomehow();
}
catch(Exception y)
{
throw y;
}
finally
{
Connection.Close();
}
return Whatever;
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).
