how to close sqlconnection

[huzz]

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 "Unreachabl e 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
[(ConfigurationS ettings.AppSett ings["DSN"]);
SqlCommand selectCmd = new SqlCommand("sp_ DMListFiles",ob jConn)]
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(C onfigurationSet tings.AppSettin gs["DSN"]);
SqlCommand selectCmd = new SqlCommand("sp_ DMListFiles",ob jConn);
selectCmd.Param eters.Add("@Dep ID",DepID);
selectCmd.Param eters.Add("@Fol derID",FolderID );
selectCmd.Comma ndType = CommandType.Sto redProcedure;
objConn.Open();
SqlDataReader dr = selectCmd.Execu teReader();
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.Execu teReader(Comman dBehavior.Close Connection);
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

--
Teemu Keiski
ASP.NET MVP, Finland
"huzz" <hu**@discussio ns.microsoft.co m> wrote in message
news:50******** *************** ***********@mic rosoft.com...

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 "Unreachabl e
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
[(ConfigurationS ettings.AppSett ings["DSN"]);
SqlCommand selectCmd = new
SqlCommand("sp_ DMListFiles",ob jConn)]
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(C onfigurationSet tings.AppSettin gs["DSN"]);
SqlCommand selectCmd = new
SqlCommand("sp_ DMListFiles",ob jConn);
selectCmd.Param eters.Add("@Dep ID",DepID);
selectCmd.Param eters.Add("@Fol derID",FolderID );
selectCmd.Comma ndType = CommandType.Sto redProcedure;
objConn.Open();
SqlDataReader dr = selectCmd.Execu teReader();
return dr;
}