Maintain a consistent DB connection through Application lifecycle

Hey everyone,

I'm new to .NET and I've recently inheirited a rather large and busy asp.net website. I was asked to add a testimonials section on each page that will randomly pull a testimonial out of the db. This is fine, however, I'm getting random errors about the DB connection either being closed or connecting. Here is the code for the testimonials class:

1public SqlDataReader GetTestimonials(ref SqlDataReader reader,int iCatID,string sLanguageType)
2 {
3 SqlCommand cmd =new SqlCommand("sp_DVX_Testimonials_Fetch", Connection);
4 cmd.CommandType = CommandType.StoredProcedure;
56 cmd.Parameters.Add(new SqlParameter("@.Cat_ID", iCatID));
7 cmd.Parameters.Add(new SqlParameter("@.LanguageType", sLanguageType));
89 reader = cmd.ExecuteReader();
1011return reader;
12 }

I know this isn't the best way to do this (especially for each page[this site averages about 1000 hits a day]), so I was wondering was--is there a way to maintain a single DB connection that's set up in the Application_Start that will maintain the connection so I don't have to worry about this error. If not, does anyone and any ideas as to what would help?

Thanks in advance!

Well, I don't think you need to do this, as ADO.Net connections are pooled. Usually the best practice would be: open connection -> retrive data -> close it immediately. The underlying managed data provider will help you to decide if "a connection" "really" closes physically..

msdn:

ASqlConnection object represents a unique session to a SQL Server data source

And where dose 'Connection' variable come from in your code? A member of the Page? As SqlConnection is not thread safe, there might be situations when one thread opens it and is ready to read, while another thread just closed it, so error occurs~

|||

The Connection variable is initialized in the constructor of whatever class is using it. Currently, every class initializes its own DB connection. Would this have be bad? :-P

Thanks for the info!

|||

Yes, this would have been bad.

It would be better to have your data access layer maintain the connections. Also, I would tell the datareader to close the connection automatically when the datareader closes, and create/open your connection object from within your DAL.

Although 1000 hits per day is an extremely low traffic site, you really should get in the habit of opening your connection when it's needed, do whatever you need to, then close it as quickly as possible.

|||

Would you happen to know of any resources that show how to maintain connections within the DAL? I'm coming to .Net from a php background and I'm still getting the hang of the structure of everything.

Thanks!