how to close sqlconnection

[Guest]

I have a method that gets sqldatareader as shown below.. my question is how
do i close the sqlconnection (objConn) object within this method?

If i put the objconn.Close(); after the return then i get "Unreachable code
detected" and if i close it before the return i get "Invalid attempt to
FieldCount when reader is closed."

When i refresh the page .. lot of sqlconnection is created.. and then i get
this error message :
Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached.

Also instead of adding this bit of code
[(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand selectCmd = new SqlCommand("sp_DMListFiles",objConn)]
in every method.. how can i put it somewhere and reuse it in every methods??

Many thanks in advance.

public SqlDataReader DMListFiles(int DepID, int FolderID)
{
SqlConnection objConn = new
SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand selectCmd = new SqlCommand("sp_DMListFiles",objConn);
selectCmd.Parameters.Add("@DepID",DepID);
selectCmd.Parameters.Add("@FolderID",FolderID);
selectCmd.CommandType = CommandType.StoredProcedure;
objConn.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
return dr;
}

 

[Teemu Keiski]

Hi,

you can't close it in the same method after you've returned it. It lefts
that responsibility to the caller of this method. Least you can do is to
call ExecuteReader method with CloseConnection argument that is:

SqlDataReader dr = selectCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;

which would force the connection to be closed when Datareader is closed, but
still the caller *must* call Close/Dispose for the data reader instance

However, there is a way around it, simplest being that start using data
tables, but you can check a few articles about these issues how you can
manage with data readers in a case like this.

Using Delegates With Data Readers to Control DAL Responsibility
http://aspalliance.com/526

..NET Data Access Performance Comparison
http://aspalliance.com/626