471,325 Members | 1,458 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,325 software developers and data experts.

how to close sqlconnection

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;
}
Jul 21 '05 #1
1 4790
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.CloseConne ction);
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**@discussions.microsoft.com> wrote in message
news:50**********************************@microsof t.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 "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;
}

Jul 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Pierson C | last post: by
4 posts views Thread by Steve Richter | last post: by
3 posts views Thread by FC | last post: by
3 posts views Thread by Woody Splawn | last post: by
35 posts views Thread by Eric Sabine | last post: by
1 post views Thread by huzz | last post: by
6 posts views Thread by mavrick_101 | last post: by
8 posts views Thread by Imran Aziz | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.