homepage Welcome to WebmasterWorld Guest from 54.226.80.55
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
Sharing Database Connections
Best practices
jweighell




msg:3547602
 9:20 am on Jan 14, 2008 (gmt 0)

Hi,

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

 

lammert




msg:3547741
 2:19 pm on Jan 14, 2008 (gmt 0)

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.

ceestand




msg:3547791
 3:23 pm on Jan 14, 2008 (gmt 0)

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:


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.

jweighell




msg:3547792
 3:25 pm on Jan 14, 2008 (gmt 0)

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,

Jonathan

jweighell




msg:3547798
 3:30 pm on Jan 14, 2008 (gmt 0)

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?

ceestand




msg:3547810
 3:46 pm on Jan 14, 2008 (gmt 0)

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:

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).

aspdaddy




msg:3570456
 4:48 pm on Feb 9, 2008 (gmt 0)

For oo design you will need a class for your database layer. Take a look at .net patterns

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved