Connection pooling issues!

I've got a function that returns a connection to a DataReader:

static public SqlDataReader GetDR(string sql,string connString){SqlConnection myConn =new SqlConnection(connString);SqlCommand cmd =new SqlCommand(sql, myConn);SqlDataReader dr;myConn.Open();dr = cmd.ExecuteReader();return dr;}

I call it like so:

SqlDataReader dr = PAS.GetSqlDataReader.GetDR(sql, connString);
And I'm always careful to close the connection when I'm done using it via dr.Close(). Except I just started noticing that when I run my app and
start doing a fair amount of work around it - I very quickly get a timeout that I've run out of connections. D'oh! I've triple checked my code and
verified that Ialways remember to close my connections. It's there -every single time!

So I'm obviously missing something. Should I not be using a function like GetDR since it doesn't close the connection within the function? 

closing a reader does not automatically close the connection.

dr.close() simply closes the reader leaving the connection open.

if you want to close the connection automatically when the reader is closed, you need to use an overload to the ExecuteReader method

 dr = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection);

http://msdn2.microsoft.com/en-us/library/y6wy5a0f.aspx|||What's the proper way to close the connection then?|||

Am I correct that this opens the connection:

SqlConnection myConn = new SqlConnection(connString);

And this closes it:

myConn.Dispose();

I guess my problem is that my GetDR function returns a DataReader leaving me no access to close myConn. Anything I can do about that?

|||

For some reason, I totally looked over your piece of code to close the connection when the reader is closed. It's exactly what I needed (obviously). Thanks for your help. Seems to be working great now!

mbanavige:

closing a reader does not automatically close the connection.

dr.close() simply closes the reader leaving the connection open.

if you want to close the connection automatically when the reader is closed, you need to use an overload to the ExecuteReader method

 dr = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection);

http://msdn2.microsoft.com/en-us/library/y6wy5a0f.aspx